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
