Request AD group membership recursively through SQL

Question

Background

I am creating SQL to assist in a security audit; this will allow you to obtain security information from various system databases and from Active Directory and display a list of all anomalies (for example, cases when accounts are closed on one system, but not others).

Current code

To get a list of users in the security group, I run the following SQL:

if not exists(select 1 from sys.servers where name = 'ADSI') EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' SELECT sAMAccountName, displayName, givenName, sn, isDeleted --, lastLogonTimestamp --, lastLogon (Could not convert the data value due to reasons other than sign mismatch or overflow.) FROM OPENQUERY(ADSI , 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted FROM ''LDAP://DC=myDomain,DC=myCompany,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' AND memberOf = ''CN=mySecurityGroup,OU=Security Groups,OU=UK,DC=myDomain,DC=myCompany,DC=com'' ') order by sAMAccountName 

Problem / question

I want this code to work recursively; that is, if the user is a member of a group that is a member of the specified group, they must also be included (for a complete hierarchy). Does anyone know how to do this through SQL?

UPDATE

Now I have solved a few questions (not related to the problem quoted, but some other problems that I had).

• lastLogon threw an error. This happened because the server version was x86. Using the x64 database resolved the issue.
• lastLogon was returned as a number. Added code to convert to DateTime2.
• I managed to move the group name from a hard coded string, making OpenQuery dynamic itself, so in the context of OpenQuery the generated string looks static.

..

 --create linked server if not exists(select 1 from sys.servers where name = 'ADSI') begin --EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' EXEC master.dbo.sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true' end declare @path nvarchar(1024) = 'DC=myDomain,DC=myCompany,DC=com' declare @groupCN nvarchar(1024) = 'CN=My Security Group,OU=Security Groups,OU=UK,' + @path , @sql nvarchar(max) --construct the query we send to AD set @sql = ' SELECT sAMAccountName, displayName, givenName, sn, isDeleted, lastLogon FROM ''LDAP://' + replace(@path,'''','''''') + ''' WHERE objectCategory = ''Person'' AND objectClass = ''user'' AND memberOf = ''' + replace(@groupCN,'''','''''') + ''' ' --now wrap that query in the outer query set @sql = 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted , case when cast([lastLogon] as bigint) = 0 then null else dateadd(mi,(cast([lastlogon] as bigint) / 600000000), cast(''1601-01-01'' as datetime2)) end LastLogon FROM OPENQUERY(ADSI, ''' + replace(@sql,'''','''''') + ''') order by sAMAccountName' --now run it exec(@sql) 

Answer 1

Despite the fact that this is an old post, Google still likes to throw it at the top of the results, since I struggled a lot with this problem, I wanted to publish my results / solution, with a loan on Riverway to get me on the right track.

Create a saved procedure:

 CREATE PROCEDURE [dbo].[GetLdapUserGroups] ( @LdapUsername NVARCHAR(max) ) AS BEGIN DECLARE @Query NVARCHAR(max), @Path NVARCHAR(max) SET @Query = ' SELECT @Path = distinguishedName FROM OPENQUERY(ADSI, '' SELECT distinguishedName FROM ''''LDAP://DC=DOMAIN,DC=COM'''' WHERE objectClass = ''''user'''' AND sAMAccountName = ''''' + @LdapUsername + ''''' '') ' EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(max) OUTPUT', @Path = @Path OUTPUT SET @Query = ' SELECT cn AS [LdapGroup] FROM OPENQUERY (ADSI, ''<LDAP://DOMAIN.COM>; (&(objectClass=group)(member:1.2.840.113556.1.4.1941:= ' + @Path + ')); cn, adspath;subtree'') ORDER BY cn; ' EXEC SP_EXECUTESQL @Query END 

Then call your SP by simply passing the username:

 DECLARE @UserGroup table (LdapGroup nvarchar(max)) INSERT INTO @UserGroup exec Datamart.dbo.GetLdapUserGroups @LdapUser 

Then I use a hash table to correctly map the AD group to the SQL data and what the end user should see.

 DECLARE @RptPermissions table (ldapGroup nvarchar(max),scholarshipCode nvarchar(50),gender nvarchar(2)) INSERT INTO @RptPermissions VALUES('EMP_Enrollment_Admissions','ALL','MF') 

In my case, I use this to pull out a custom SSRS variable and pass it into the query to select records based on membership in the AD group.

 ;WITH CTE_Permissions AS ( SELECT p.scholarshipCode ,p.gender FROM @UserGroup AS g JOIN @RptPermissions AS p ON g.ldapGroup = p.ldapGroup ) 

... later in the request

 JOIN CTE_Permissions AS p ON s.SCHOLARSHIP_ID = p.scholarshipCode OR p.scholarshipCode = 'ALL' 

Hope this helps.

Answer 2

Like this?

 --Get all members of a group SELECT cn,AdsPath FROM OPENQUERY (ADSI, '<LDAP: dc="corp,dc=mycorp,dc=com">;(&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=CN=Administrators,CN=Builtin,DC=corp,DC=mycorp,DC=com));cn, adspath;subtree') ORDER BY cn; --get all groups a user is a member of SELECT cn,AdsPath FROM OPENQUERY (ADSI, '<LDAP: dc="corp,dc=mycorp,dc=com">;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=CN=John Doe,OU=Developers,OU=Staff,DC=corp,DC=mycorp,DC=com));cn, adspath;subtree') ORDER BY cn; 

See http://msdn.microsoft.com/en-us/library/aa746475(VS.85).aspx for conditions for a recursive search.