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 mssqltips.com 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
*******************************************************************************************/



USE DBA;
GO
/*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-C_Drive_Access' UNION ALL
SELECT 'GG-D_Drive_Access' UNION ALL
SELECT 'GG-Allow_Command_Prompt_Access' UNION ALL
SELECT 'GG-Local_Admin'

/*Cursor to populate the #groups table with the unformatted  output of dsquery*/
DECLARE @GroupName VARCHAR(100)
DECLARE @Member VARCHAR(400)
DECLARE @SQL VARCHAR(MAX)

DECLARE curADGroups CURSOR FOR
SELECT
       GroupName
FROM
       #GroupsToCheck
      
OPEN curADGroups

FETCH NEXT FROM curADGroups INTO @Groupname

WHILE @@FETCH_STATUS = 0
       BEGIN
                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
       END

CLOSE curADGroups
DEALLOCATE 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
SELECT 
          GroupName
          ,CASE s.ordinal WHEN 1 Then 'UserName' WHEN    2 Then 'FullName' WHEN 3 THEN 'Dept' END AS Field
          ,s.StringValue
          ,GETDATE()
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*/
;WITH groupCTE AS
(
SELECT
    ID
    ,GroupName
    ,(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
    ,Polldate
   
 FROM #GroupMemberShipTemp g
 )
INSERT GroupMembership
SELECT
    ID
       ,GroupName
       ,LTRIM(RTRIM(UserName)) AS UserName
       ,LTRIM(RTRIM(FullName)) AS FullName
       ,LTRIM(RTRIM(Dept)) AS Dept
       ,Polldate
FROM groupCTE
WHERE UserName IS NOT NULL
ORDER BY GroupName
       ,UserName


<end>





 

Comments

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

How to import a large xml file into SQL Server

(Or how to import the StackOverflow database into SQL Server) Introduction NB  This process can be generalised to import any large (>2G) xml file into SQL Server. Some SQL Server training you can find online including that by Brent Ozar uses the StackOverflow database for practice. The tables from it are available online for download in xml format. In the past it was possible to use the scripts found here, https://www.toadworld.com/platforms/sql-server/w/wiki/9466.how-to-import-the-stackoverflow-xml-into-sql-server , to import them but as each xml file is now over 2GB you will get an error like this when you try to execute them: Brent Ozar, has a link to SODDI.exe, https://github.com/BrentOzarULTD/soddi , which can import the files (I haven’t tried it) but it means downloading and importing eight tables: Badges, Comments, PostHistory, PostLinks, Posts, Tags, Users, and Votes tables which amounts to >30GB of compressed xml increasing to ~200GB when decompre