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