Skip to main content

Querying Active Directory from SQL Server

At many companies, access to employees PC resources (USB drive, CD ROM drive, root of C: etc) is managed by Group Policy. Sometimes Group Policy with the requisite settings is applied to a Security Group so members of that group inherit the settings of that GPO. So you may end up with many different policies applied to many different groups.

This is a straight forward easy way to manage GPOs - if you want to know what access a user has to their PC simply look at their group membership.

Being a database type person it occurred to me, it would be nice if we could query Active Directory at regular intervals to see who has what permissions to their PC (for security auditing), populate a table with the results and perhaps point Excel at the table to create a nice report for management. (I won't cover the Excel part in this post but there is very little to it)

I looked (Googled) various different ways this could be achieved but nothing quite did it the way I wanted so in the end had to come up with my own solution.

A popular solution can be found at using a linked server to a Domain Controller and ADSI but the problem I found with it is that I couldn't find an easy way to drill down through AD to find the group objects - If all the groups you are interested in are in the same OU then it worked fine but if they were spread about in different OUs, some at the same level of the tree and others off to one side or the other then you had to write separate code to address each OU. Which is fine until someone moves a group to a different OU due to tidy up and then your query breaks.

Other solutions suggested using WMI but I had no luck there either. So in the end I went for the command line tools DSQuery and DSGet called by xp_CmdShell. With these tools you don't have to use an LDAP path, you just run them as you would any other command line tool from with SQL Server. Both tools are available in the Remove Server Administration Tools.

There are several issues with the Remove Server Administration Tools approach:

  1. Some shops don't like xp_CmdShell to be enabled due to a perceived security issue (not a biggy IMHO)
  2. The account you run the tools under needs permissions to Active Directory
  3. DSGet and DSQuery have to be installed on the SQL Server you will be querying from. See here for instructions on how to do it.
  4. The output from DSGet and DSQuery is nasty and requires a lot of cleaning up before it fits nicely into fields in a table. A big part of the query below is making the results returned usable.
Your business may have a policy for the first three points which may restrict your use of this script, but assuming you are good to go feel free to use it (please read the comments in line for further info and as usual I take NO responsibility for any issues AT ALL if you execute the script on ANY server - but it should be fine!). The guts of the script are taken from this command:

dsquery group -name GG-C_Drive_Access | dsget group -members -expand -c | dsget user -samid -display -dept -c | Find /V "dsget succeeded" | Find /V "samid" | Find /V "_"

What this is saying is query the group GG-C_Drive_Access and pipe the results to dsget group and get the member attributes samid, display and dept. This is piped to Find to not return the lines that contain dsget succeeded, samid and the underscore (you may have different/additional requirements).

The query depends on a function called ufn_Split

Name    : Show Groups, members and departments from Active Directory.sql
Author  : Paul Hewson (IT Operations DBA)
Purpose : This script uses dsquery and dsget (from the Remote Administration
                Tools) to query the membership of the specified Global Security Groups. These groups determine the rights a user has to their PC. 

Version    :     1.0
Date       : 02 May 2014
Info       : Initial version
Depends on : ufn_split

/*Drop the tables that will be used if they exist*/
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Groups')) DROP TABLE #Groups
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Temp')) DROP TABLE #Temp
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#GroupsToCheck')) DROP TABLE #GroupsToCheck
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#GroupMembershipTemp')) DROP TABLE #GroupMembershipTemp
IF EXISTS (SELECT 1 FROM DBA..sysobjects WHERE id=OBJECT_ID('DBA..GroupMembership')) DROP TABLE GroupMembership

/*Create the tables */
CREATE TABLE #Groups (GroupName VARCHAR(100), Member VARCHAR (400))
CREATE TABLE #Temp (Member VARCHAR (400))
CREATE TABLE #GroupMemberShipTemp(ID INT IDENTITY (1,1),GroupName VARCHAR(50), Field VARCHAR(100), Member VARCHAR(100), Polldate DATETIME) 
CREATE TABLE GroupMemberShip(ID INT, GroupName VARCHAR(50), Username VARCHAR(100), Fullname VARCHAR(100), Dept VARCHAR(100), Polldate DATETIME)

/*The list of Security Groups to check. You may add/delete from this list as required*/
CREATE TABLE #GroupsToCheck(ID INT IDENTITY (1,1), GroupName VARCHAR(50))
INSERT #GroupsToCheck
SELECT 'GG-Allow_Command_Prompt_Access' UNION ALL
SELECT 'GG-Local_Admin'

/*Cursor to populate the #groups table with the unformatted  output of dsquery*/

OPEN curADGroups

FETCH NEXT FROM curADGroups INTO @Groupname

                TRUNCATE TABLE #temp
                SET @SQL = 'EXEC MASTER..XP_CMDSHELL ''dsquery group -name "'
                SELECT @SQL = @SQL + @groupName
                SET @SQL = @SQL + '" | dsget group -members -expand -c | dsget user -samid -display -dept -c | Find /V "dsget succeeded" | Find /V "samid" | Find /V "_"'''
                --PRINT @SQL
                INSERT #temp
                EXEC (@SQL)

                INSERT #groups
                SELECT @GroupName, Member
                From #temp

              FETCH NEXT FROM curADGroups INTO @GroupName

CLOSE curADGroups

/*The unformatted output from dsquery includes the Username, Fullname and Department of an AD user
in one column separated by two spaces. This query splits these values into separate rows with an ordinal number
that is renamed appropriately.*/  
INSERT #GroupMemberShipTemp
          ,CASE s.ordinal WHEN 1 Then 'UserName' WHEN    2 Then 'FullName' WHEN 3 THEN 'Dept' END AS Field
FROM #groups u
          CROSS APPLY master.dbo.ufn_split(REPLACE(REPLACE(REPLACE(RTRIM(lTRIM(u.member)),'  ','<>'),'><',''),'<>',','), ',') s

/*Pivot the output from the previous query into the correct columns*/
    ,(SELECT member FROM #GroupMemberShipTemp g2 WHERE field = 'UserName' AND g2.ID = g.ID ) AS UserName
    ,(SELECT member FROM #GroupMemberShipTemp g3 WHERE field = 'FullName' AND g3.ID = g.ID + 1 ) AS FullName
    ,(SELECT member FROM #GroupMemberShipTemp g4 WHERE field = 'Dept' AND g4.ID = g.ID + 2 ) AS Dept
 FROM #GroupMemberShipTemp g
INSERT GroupMembership
       ,LTRIM(RTRIM(UserName)) AS UserName
       ,LTRIM(RTRIM(FullName)) AS FullName
       ,LTRIM(RTRIM(Dept)) AS Dept
ORDER BY GroupName




Popular posts from this blog

How to create a custom Windows Event Log view and email trigger

The filtering on Windows event logs can be slow, clunky and although you can do it on fields like event ID, it seems that many event IDs are shared amongst many different errors – the event ID may match but the body of the error (therefore the actual error) may be completely unrelated. Fortunately, it is possible to filter on the contents of the body of the error message but it requires creating a custom XML query. Also, it would be handy to send out a notification email when this event gets logged. Read on to find out how to work this magic…. This example is looking for a  Warning  event  1309  for  ASP.NET 4.0.30319.0  on a web server. If you were to just filter the log on the criteria above today it would return 435 results because it is a fairly general error ID. If I filter it using XML for SqlException (what I’m really interested in) only 5 results are returned. So the first step is go to the Application Log and choose  Create Custom View…  Select the  XML  tab, check  Edit

How to configure the SSAS service to use a Domain Account

NB Updating SPNs in AD is not for the faint hearted plus I got inconsistent results from different servers. Do so at your own risk! If you need the SSAS account on a SQL Server to use a domain account rather than the local “virtual” account “NT Service\MSSQLServerOLAPService”. You may think you just give the account login permissions to the server, perhaps give it sysadmin SQL permissions too. However, if you try and connect to SSAS  remotely  you may get this error: Authentication failed. (Microsoft.AnalysisService.AdomdClient) The target principal name is incorrect (Microsoft.AnalysisService.AdomdClient) From Microsoft: “A Service Principle Name (SPN) uniquely identifies a service instance in an Active Directory domain when Kerberos is used to mutually authenticate client and service identities. An SPN is associated with the logon account under which the service instance runs. For client applications connecting to Analysis Services via Kerberos authentication, th

Fun and games with the Management Data Warehouse (MDW and Data Collectors)

The SQL Server Management Data Warehouse (when you first come across it) seems to promise so much if the verbiage from Microsoft and some other websites is to to believed. But when you install it you may find that it is not as useful as it could be. This is a shame but we are currently only on v2 of the product with SQL 2012 so one hopes it will improve in subsequent versions. However, it probably is worth playing with if you have never used it before - at least you can show your boss some reports on general server health when he asks for it and you have nothing else in place. There is one big problem with it though if you decide that you don't want to use it any more, uninstalling it is not supported! Mad, I know. But as usual some very helpful people in the community have worked out, what seems to me, a pretty safe way of doing it. I had a problem with my MDW. The data collector jobs were causing a lot of deadlocking on some production servers and impacting performance. I