Determine AD Group membership in SQL server

Today, one of the customers I work for, asked me to review the Authentication method used in 2 applications. The group membership of the application’s users was stored in tables on a SQL Server and the application called an Stored Procedure to determine if a user was a member of a specific group by passing the username of the logged in user and groupname. To make the applications easier to maintain, the customer preferred to use Active Directory groups. As I did not want to change the code of the application (and test it, and deploy the new version), I thought about modifying the Stored Procedures so they would query the Active Directory instead of the database. As the applications use SQL Server 2000, writing a CRL routine and using that from the SQL Server was not an option. Luckily an provider exists allowing you to connect to Active Directory: the ‘OLE DB Provider for Microsoft Directory Services’. I started with adding a Linked server to the SQL server name ‘ActiveDirectory’ and defined that connections must be made by an existing user account with sufficient access rights to Active Directory. Next I played around a bit in Query Analyzer and managed to execute some simple queries on AD For example, to return all the users for the domain ‘somedomain.local’ you would execute

SELECT * FROM openquery(ActiveDirectory, ‘select ADsPath, sAMAccountName FROM ”LDAP://dc=somedomain,dc=local” where objectCategory = ”Person” and objectClass= ”user” ‘)

The openquery statement has one major drawback: you have to provide a string contstant for the query and cannot use variables. You have to use ‘sp_executesql’ instead. Also, in Active Directory, it is possible to have nested groups (i.e. a group within a group). To make sure all the users that belong to certain group are returned, a recursive method was needed. After banging my head on my desk several times to get the quoting right, I ended up with the 2 stored procedures listed below. The stored procedure ‘sp_getADGroupUsers’ expects 2 parameters:

  • @LDAPRoot: the root of the Active Directory domain. (e.g. ‘ldap://dc=somedomain,dc=local/‘)
  • @FriendlyGroupName: the name of the group to list the users for (e.g. ‘domain users’).

The basic flow of the SP is as follows:

  • Create a temp table (#ADSI) to stored the users while the other Stored Procedures is called recursively
  • Determine the full ADsPath for the specified group
  • Call ‘sp_getadgroupusers_recursive’ passing the LDAPRoot and the full ADsPath

The Stored Procedure ‘sp_getadgroupusers_recursive’ expects 2 parameters:

  • @LDAPRoot: the root of the Active Directory domain.
  • @group_name: the full ADsPath (without the LDAP:// portion) of the group to list the users for.

The basic flow of the SP is as follows:

  • Insert all the users of the specified group into the temp table (#ADSI)
  • Create a cursor with all the Groups within the specified group
  • Call ‘sp_getadgroupusers_recursive’ (itself) for each childgroup

 

sp_getADGroupUsers (click ‘expand source’ to open)

CREATE PROCEDURE [dbo].[sp_getADGroupUsers]
@LDAPRoot nvarchar(200),
@FriendlyGroupName nvarchar(200)

AS

BEGIN
DECLARE @SQLString nvarchar(4000)
DECLARE @group_name nvarchar(500)
--Create a temp table to hold the results
CREATE TABLE #ADSI (
ADsPath nvarchar(500),
SamAccountname nvarchar(100))

--build an SQL string to return the ADsPath for the requested group
--(the 'LDAP://' part will be stripped off)
set @SQLString = N'SELECT @group_name_int = SUBSTRING(ADsPath,8,LEN(ADsPath))
FROM OPENQUERY(ACTIVEDIRECTORY,''SELECT ADsPath FROM ''''' + @LDAPRoot + '''''
WHERE SamAccountname=''''' + @FriendlyGroupName + ''''' '')'

--Execute the statement (will return the ADsPath in @group_name
EXEC sp_executesql @SQLString,
N'@group_name_int nvarchar(200) OUTPUT',
@group_name_int=@group_name OUTPUT

--if the group_name is null, give up (raise error?)
IF @group_name is null
BEGIN
RETURN -1
END

--recursively get all the users for the group
EXEC sp_getadgroupusers_recursive @LDAPRoot, @group_name

--return the resultset
--(use DISTINCT as a user might be a member of several groups)
SELECT DISTINCT * FROM #ADSI

--explicitely drop the temp table
DROP TABLE #ADSI

END

sp_getadgroupusers_recursive (click ‘expand source’ to open)

CREATE PROCEDURE [dbo].[sp_getadgroupusers_recursive]
@LDAPRoot nvarchar(200),
@group_name nvarchar(500)

AS

BEGIN
DECLARE @SQLString nvarchar(4000)
DECLARE @LDAPPath nvarchar(500)

--build an statement to insert all the user from the specified
--group into the temp table (temp table is created in parent sp)
SET @SQLString = N'INSERT INTO #ADSI SELECT ADsPath,SamAccountname
FROM OPENQUERY(ACTIVEDIRECTORY,''SELECT ADsPath,
SamAccountname FROM ''''' + @LDAPRoot + '''''
WHERE objectCategory = ''''Person'''' and objectClass = ''''user''''
and memberOf=''''' + @group_name + ''''' '') ';

--execute the statement
EXECUTE sp_executesql @SQLString;

--declare a variable to hold a cursor (need a variable because we
--need a Local cursor since the SP is called recursively)
DECLARE @group_cursor CURSOR

--Load all the groups within the specified group into the cursor
SET @SQLString = N'SET @group_cursor = CURSOR STATIC FOR
SELECT SUBSTRING(ADsPath, 8, 200)
FROM OPENQUERY(ACTIVEDIRECTORY,''SELECT ADsPath
FROM ''''' + @LDAPRoot + '''''
WHERE objectClass = ''''group''''
and memberOf=''''' + @group_name + ''''' '')
FOR READ ONLY; OPEN @group_cursor';

--execute
EXECUTE sp_executesql @SQLString, N'@group_cursor CURSOR OUTPUT',
@group_cursor OUTPUT

--loop through the Cursor (it's opened in the sp_excecutesql statement above)
--the only column in the cursor contains the ADsPath with the 'LDAP://' part
--already stripped off)

FETCH NEXT FROM @group_cursor INTO @LDAPPath
WHILE @@FETCH_STATUS = 0
BEGIN
--recursive call using the 'inner group'
EXEC sp_getadgroupusers_recursive @LDAPRoot, @LDAPPath
--get next
FETCH NEXT FROM @group_cursor INTO @LDAPPath
END

--clean up cursor
CLOSE @group_cursor
DEALLOCATE @group_cursor
END

3 thoughts on “Determine AD Group membership in SQL server

  1. Iain

    Hi
    This looks really useful. Just one point:
    instead of using SUBSTRING(ADsPath, 8, 200) you could use distinguishedName.
    Many thanks
    Iain

    Reply
  2. Barbara Roy

    If you have apostrophes in your ADsPath, you may need to add the following line at the beginning of the recursive stored proc:

    SET @group_name =(SELECT TOP 1 REPLACE(@group_name,””,”””) )

    Reply
  3. daka

    To the OP,

    As Iain said, it’s easy to use DistinguishedName instead of the SUBSTRING in both of the procedures.

    Many thanks for the code. This thread helped with the recursive calls, for which I was pulling my hair out, due to the multiple escape quotes (‘). As if that wasn’t enough trouble already, DistinguishedName for our groups had ‘ embedded in them.

    Thanks,
    dakaujunk

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>