ME Trial 12 – AD Group Members and Login Access Method Audit utility

Hidden, ME Trial / By

minion enterprise-01

A quick glance behind, and ahead

So last time, we introduced the Objects, Tables, and Columns modules. This time we’ll look at the AD Group Members module, and the related Login Access Method Audit utility. This is one collection that we are super, totally, and completely in love with.

The AD Group Members Module

It is very common to have Windows groups as logins in SQL Server. The problem for DBAs is that they then have no idea who has access, because you can’t see who is a member of which Active Directory groups.  Now you can, and quite easily. Minion Enterprise pulls in all of the relevant AD information to repository tables, and also provides you with a set of stored procedures for analyzing and reporting on the data.

What can we do with this? First, of course, we can list out every individual user that has access to SQL Server, whether they have direct access, access through an AD group, and/or access via nested AD groups. You can use this data to audit your servers – for example, getting a list, or even a simple count, of sysadmins on each server. (One client found to their surprise that they had over 500 sysadmins on a single server!)  And of course, anything else that you can think of. The data is all there, waiting for your query.

Objects in the AD Group Members Module

Tables

  • Collector.ADGroupMember – Holds collected information about AD group membership from each managed server.

Views

  • Collector.ADGroupMemberCurrent – Holds the most recent collection of AD group membership.
  • Collector.ADGroupMemberPrevious  – Holds the next-to-most recent collection of AD group membership.

Notable Stored Procedures

  • Report.ADAcctsInSQLAll – This procedure gets an expanded list of Active Directory groups for all servers. Minion Enterprise comes with a utility script that allows you to save the results of this SP to a temporary table, and thereby limit the result set by any criteria (e.g., by sysadmins): \ManualScripts\Logins\Report.ADAcctsInSQLAll.sql
  • Report.ADAcctsInSQLByApp – Gets an expanded list of Active Directory groups, filtered by a specific AppID  (as defined in dbo.Application).
  • Report.ADAcctsInSQLByEnviro – Gets an expanded list of Active Directory groups for a specific environment (as defined in dbo.ApplicationEnvironment).
  • Report.ADAcctsInSQLByID – Gets an expanded list of Active Directory groups for a specific Server by InstanceID.
  • Report.ADAcctsInSQLByServerName – Gets an expanded list of Active Directory groups for a specific Server by ServerName.
  • Report.ADAcctsInSQLBySLA – Gets an expanded list of Active Directory groups for a specific SLA (e.g., Gold).

Check out the Active Directory Group Expansion video on our YouTube channel, which demonstrates finding what users are in which Windows group. And, see Passing Your Security Audits for quite a bit more!

Utility: Login Access Method Audit

This utility is an auditing feature that shows you how users are gaining access to SQL, and whether they are getting in through multiple methods.  This is an extremely powerful module; only Minion Enterprise brings you this type of functionality.

SQL logins can be AD accounts, SQL accounts, or AD groups.  When a login is an AD group, there is no way for the DBA to know who is in that group.  This is why Minion Enterprise takes a look at the AD groups that are registered as logins on each managed SQL instance, and queries Active Directory for all of the members of those groups.

This is data is saved to the Collector.ADGroupMember table.  Look at this table, and you may notice that there is no InstanceID.  This is because Active Directory group members themselves have nothing to do with SQL. An AD group can be a login on multiple SQL instance, so saving the AD data independent of any server information is the way to go.  To match AD accounts with logins on a specific box, join Collecgtor.ADGroupMember with Collector.Logins – or let the following procedure do it for you.

Procedures:

  • Audit.LoginPermAccessMethod – This stored procedure shows the path by which an account obtains access to SQL Server.  This procedure can be called with or without an account, and with or without a server name.

 

NOTE: There is one limitation to the AD data. If a user has an Active Directory account with a completely different name from their SQL login, there is simply no way ME can know those two accounts are the same person.  They are independent, unrelated accounts.  So if you call the audit procedure above with a user account in mind, and that account doesn’t match any other accounts, you won’t see it in the results.

As always, feel free to write your own queries to find what you need. Minion Enterprise is meant to be customized.

This utility depends on “current” views from two collections:

  • Collector.LoginsCurrent
  • Collector.ADGroupMemberCurrent

 

NOTE: If for whatever reason the Logins or AD Groups data is not up to date, run the jobs associated with these collections:

  • CollectorLoginsGet-GOLD (and/or the Silver or Bronze job, as appropriate)
  • CollectorADGroupsGet

AD Group Members is a powerful module – especially when you count in the Login Access Method Audit utility – and it’s already doing a lot of shops quite a lot of good. Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

The next article is our very last one in this series!