Query the Members of an AD Group using a Linked Server

Using LDAP (SQL Dialect) Queries from SQL Server

By Dan Meyers

I was recently asked by a client about the best way integrate information from AD (Active Directory Services) into SQL and more specifically into Reporting Services reports. They wanted to be able to grab the userid of the person running the report and do a lookup in AD to see if the user was the member of a specific AD group or not. Based on whether or not the person running the report belonged to certain groups they wanted the report to behave a different ways. Based on the results on the AD query they wanted the reports to show or hide certain sections of the report and manipulate the data returned too.

The simplest and most effective solution for this problem is to use a linked server to query AD directly. Using the ADSI provider and a linked server in SQL Server we can query AD live and check to see if the report user is a member of a specific group or not. LDAP, the directory protocol defines the language that we need to use to query AD for user information. You can use either SQL Dialect or LDAP Dialect. I will be using SQL Dialect in my sample code. There are some known restrictions:

Known Restrictions:
· The number of records returned by the query is limited by a setting on the AD server. I believe the default value is 1000 records and can be changed by an administrator.
· The linked server cannot return multi-value fields such as the ‘memberOf’ field, but nothing restricts you from using them in the WHERE clause.

Since the client wants to do this at the report level we can use the UserID function that is built into Reporting Services to return the userid of the person running the report. This makes it easy for us to pass the userid down as a parameter to a SQL query.

It should be easy to use these examples to create a set of views, stored procedures, and/or functions that you can use to query AD over the linked server and integrate the data from AD into your reports.

Below is some sample code that gives you examples of how to:
· How to get the container/ADsPath information for specific groups
· Wildcard Searches Based on Names
· Searching for Groups and Users
· Using Multi-Value Fields in the WHERE Clause
· Returning the Members of a Group
· Checking to See If a User is the Member of a Group

--Run this to create the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

--Run this to get the ADsPath for each of the groups that have a names that starts with "BI_"
--Note the use of the "*" as wildcard characters
SELECT ADsPath
FROM OPENQUERY(ADSI,
'SELECT ADsPath
FROM ''LDAP://DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''group''
    AND CN=''*BI_*''
ORDER BY CN')

--Run this to get information about the "BI_" groups
--Note the use of the "*" as wildcard characters
SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN
FROM ''LDAP://DC=<<MyDomainName>>,DC=COM''
WHERE objectCategory=''group'' AND CN=''*BI_*''
ORDER BY CN')

--Run this to get the information about the members of a specific group
--The value being used to filter on the memberOf field is the ADsPath value returned by the first query above
SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN
FROM ''LDAP:// DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''person''
   AND objectClass=''user'' 
   AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator
Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''
ORDER BY CN')

--Run this to see if a specific user is a member of a specific group
--The value being used to filter on the memberOf field is the ADsPath value returned by the first query above
SELECT CONVERT(BIT,COUNT(*)) AS IsMemberOfGroup
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname
FROM ''LDAP:// DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''person''
   AND objectClass=''user''
   AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator
Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''
   AND samaccountname=''<<MyUserName>>''')

.