Apr 122011
 

You may come to a situation when you need to retrieve list of users, groups or other information from Windows Active Directory (AD) or another LDAP (Lightweight Directory Access Protocol) from within SQL Server.  You may want to store the information from AD in SQL Server tables for later use, or for example determine list of users belonging to particular group etc..

There are basically 3 ways of accessing the Active Directory on SQL Server.

  1. Use Linked Server
  2. User OPENROWSET statement
  3. Using CLR

This article will focus on Querying the Active Directory using the first 2 options. The CLR way is describe in my next article Querying Active Directory on SQL Server using CLR.

Access Active Directory using Linked Server

First I will focus on the Linked Server way to access the AD. So the first step is to create a Linked Server. Tis can be done using the sp_addlinkedserver system stored procedure or using Management Studio and we will use the OLE DB Provider for Microsoft Directory Services (ADSDSOObject).

Creating the Linked Server using the sp_addlinkedserver system stored procedure

To get the linked server working we need to add the linked server itself and also provide credentials to be used by linked server. Here we have two possibilities – use the login’s current security context – this mean use credentials of currently authenticated user (will not work for SQL Server authentication) or provide some AD account credentials to access the linked server.

EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

Prior we can use the linked server we have to add a login to that linked server which will be used to authenticate against AD. There are three possibilities:

--Use current user security context (Linked server will use security context of currently autheticated user
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

--Use one remote account for all users (Linked server will be available to all users) and will use 'DOMAINUserAccount' for authetication to AD
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAINUserAccount',@rmtpassword=N'DomainAccountPassword'

--The Linked Server will be available to SQL Server Login 'SQL Server Login Name' and will use the 'DomainUserAccount' for authetication to AD
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=N'SQL Server Login Name',@rmtuser=N'DOMAINUserAccunt',@rmtpassword=N'DomainAccountPassword'
Creating the Linked Server using Management Studio

Create Linked Server Step 1Create Linked Server Step 2Create Linked Server Step 3

Querying AD using the Linked Server

Once we have created the linked server we can start querying the AD and we have two possibilities to construct the query against AD (LDAP).

  1. Use SQL Dialect (MSDN)
  2. Use LDAP Dialect (MSDN)

I will show some sample queries using both dialect and details you can find on the above links.

SQL Dialect

SQL Dialect uses the constructs of SQL language and we can write queries against the AD

--Select all groups
SELECT
*
FROM OPENQUERY(ADSI,'SELECT cn, ADsPath FROM ''LDAP://DC=Fabricam,DC=com'' WHERE objectCategory=''group''')

--Select all users
SELECT
*
FROM OPENQUERY(ADSI,'SELECT cn, ADsPath FROM ''LDAP://OU=Sales,DC=Fabricam,DC=com'' WHERE bjectCategory=''person'' AND objectClass=''user''')

Be aware, the the LDAP is case sensitive, so you have to write LDAP:// and not ldap:// otherwise you will not receive any results. Also remember that the LDAP path needs to be enclosed in single quotes and therefore when it is specified in the OPENQUERY statement, it needs to be double single quoted as in example.

Also remember, that LDAP query returns only first 1000 records matching the specified conditions. Once the limit is met, you will receive and error message. You can limit the number of records using the TOP clause to not receive the error. To receive more than 1000 records is possible by using pagination, but it’s quite problematic in T-SQL and I will show this method in the upcoming article which will focus on getting AD data using CLR.

LDAP Dialect

As mentioned above, the other option to make query against the AD is using the LDAP dialect and details about the dialect you can find on the link above. Details how you write the condition in LDAP dialect you can find on MSDN Search Filter Syntax. Using the LDAP dialect we have a greater possibilities in writing conditions, which allow us easily retrieve members of AD Groups etc..

--Select all groups
SELECT
*
FROM OPENQUERY(ADSI,'<LDAP://DC=Fabricam,DC=com>;(&(objectCategory=group));cn,ADsPath;subtree')

--Select all users
SELECT
*
FROM OPENQUERY(ADSI,'<LDAP://OU=Sales,DC=Fabricam,DC=com>;(&(objectCategory=person)(objectClass=user));cn,ADsPath;subtree')

--Select all users belonging to group 'MyGroup'
SELECT
*
FROM OPENQUERY(ADSI,'<LDAP://DC=Fabricam,DC=com>;(&(objectCategory=user)(objectClass=user)(memberOf=MyGroups,OU=Security Groups,OU=Groups,DC=Fabricam,DC=com));cn,ADsPath;subtree')

Access Active Directory using OPENROWSET statement

The method of accessing Active Directory using the OPENROWSET statement is similar to the Linked Server solution. The difference is, that we do not have to create the linked server, but instead of this we provide all the information related to driver, login etc. directly as parameters of the OPENROWSET statement.

Again we have the possibilities to use SQL Dialect or LDAP dialect whatever suits your needs better. Here are some Examples.

--Select all Groups which name starts with "D" (SQL Dialect)
SELECT
*
FROM OPENROWSET('ADSDSOObject', '','SELECT cn, ADsPath FROM ''LDAP://DC=Fabricam,DC=com'' WHERE objectCategory=''group'' AND cn=''D*''')


--Select all users (LDAP Dialect) providing also credentials and flags
SELECT
*
FROM OPENROWSET('ADSDSOObject', 'User ID=UserName;Password=UserPassword;ADSI Flag=0x11;','<LDAP://OU=Sales,DC=Fabricam,DC=com>;(&(objectCategory=user)(objectClass=user));cn,ADsPath;subtree')

In the second example with LDAP Dialect I’ve used also an “ADSI Flag” option. This option specifies binding authentication option and can be a combination of values from the ADS_AUTHENTICATION_ENUM.

Conclusion

The ADSI interface provides us an easy and simple way how to query Active Directory from SQL Server directly sing T-SQL commands. The biggest limitation of that approach is, that it cannot return more than 1000 records in a single batch. This is possible using a Paged Search, but unfortunately this is not available in the T-SQL approach. Although we are limited to 1000 records per batch, we can partially avoid this limitation by adding additional conditions to the query e.g. retrieving all users in batches by the first letter of Common Name etc.. The real power in querying AD brings CLR solution described in Querying Active Directory on SQL Server using CLR.

  23 Responses to “Querying Active Directory on SQL Server using T-SQL”

  1. Is possible to create users in active directory since the information of a table’s database mssql?

  2. The WHERE clause in your example:

    SELECT * FROM OPENROWSET(‘ADSDSOObject’, ”,’SELECT cn, ADsPath FROM ”LDAP://DC=Fabricam,DC=com” WHERE objectCategory=”group” AND cn=”D*”’)

    uses nonstandard TSQL syntax ‘cn=”D*”‘.

    Usually I would write ‘cn LIKE ‘D%’.

    Has the syntax changed for LDAP queries?

    • Yeah, you may be right (I didn’t tested this), but these samples are based on the examples on the MSDN SQL Dialect site provided by Microsoft, so I’ve used it also here.

      I will try to test it if I will have a little bit time.

  3. Thanks for the example! I am noticing that my query crashes at a certain record like record 901 ha. I think it is a record where there is an ‘ in the department field of this ad user. I have been trying work arounds for this, but i’m not too firmilair with openquery and ldap. Any thoughts? I still need to get the record returned to me, i just need to figure out a way to get around the error.

    Error: Msg 7330, Level 16, State 2, Line 2
    Cannot fetch a row from OLE DB provider “ADSDSOObject” for linked server “LS”.

    • Hi,

      if you are experiencing a problem with particular record due to some field, then suggest trying to remove that field form the query. if the query then do not crash, then I would update a filter to test the record causing problems . So I would update query filter to return ony the problematic record and try to find out what is causing the issues.

      Anyway if there is some character stored in AD, whiich is causing the query to crash, than there is probably no other way than fixing the filed on the AD side.

      Also I would try to use the CLR way for querying the data from AD, as there are much more possibilities. See Querying Active Directory on SQL Server using CLR

  4. You guys might want to look at this MSDN article:
    http://msdn.microsoft.com/en-us/library/ms817827.aspx
    (The article title is “Enumerating Members in a Large Group” in case they move the page.)
    They use a method of a query “range”, e.g. querying a “page” of records in the range 201-300:
    searcher.PropertiesToLoad.Add(“member;range=201-300″);
    That’s the property to load on a group query to enumerate users in the group, and the query returns disinguishedName values.

    You’d need to translate the code to T-SQL if you’re not into writing CLR sprocs, but it should be doable in T-SQL.

    BTW, I use such a looping query to enumerate groups with over 150,000 users and it runs in mere seconds. :)

    • Yes, this is interesting and helpful, however it is related to retrieving values of multi-valued properties (is usefull to retrieve names of single group members).

      But whenever you query the AD and there is more restults than 1000, you have to use the Pagination – PageSize property of the DirectorySearcher.

      The example shows getting values of the member property of the distribution list. It queries a single AD object (distribution list) and retrives all the values of a multi-valued property.

      However if you would like to query and return more than 1000 AD objects you will have to use the Pagination.

  5. That’s only partially true. The 1000 limit was only true until Server2008 where the default limit is now 1500. Regardless, you miss the point of using “range” in a query which has nothing to do with the NUMBER of objects returned; instead, it’s equivalent to a WHERE BETWEEN clause in a SQL query. “Range” delimits the 1st row returned.

    For example, lets say want to process 10,000 objects. Make your first query with a range of 1-1000, your next 1001-2000, and so on. Each query returns exactly 1000 object, but the 2nd query returns the SECOND set of 1000 objects. Here’s a log entry for a group I processed last night to show you how well it works:

    AD group user DN list retrieval FINISH: 07/24/2012 16:26:57. Count=157964, Duration=0:02:01

  6. I forgot something above: with successive queries, the last one won’t necessarily return the full number of ojects, so as you see in Microsoft’s code example, they adjust for that. Still, the method is reliable and blazing fast as you can see in my example above where I processed (logging to a List collection) over 150,000 AD user distinguishedNames.

    As for retrieving DNs for users, that’s because my query was enumerating a group object. If you’re going to search AD for a non-collection object, set SizeLimit=0 and PageSize=1500 (1000 for pre-Server2008).

  7. Oh, and why distinguishedNames? Because those are quaranteed unique paths to the objects you’re looking for. It’s analagous to loading a list of primary keys. :) Retrieve a collection of DNs, loop through them, loading each with DirectoryEntry entry = new DirectoryEntry(DN). You can of course load only a subset of the DNs you retrieve if you’d like to filter on OUs or another part of the object path, but you don’t get object properties until you load the object itself by using its path (i.e. DN).

    • Lloyd, we are still discussing about the CLR Way. And with the CLR way, there is no issue.. If I use my method mentioned in http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-clr/ and loading only dn for the object, I’m able to load over 300 000 AD objects in under 1 minute, so it is not a problem.

      And as mentioned above, the range is for iterating members of collection properties. So if searching the whole AD and need to retrieve object according some filter condition it is not necessary to use the range at all. (of course if you would like to retrieve the members of collection properties and there is a lot of them (over 1500), then sure, a modification with the range for retrieving all the member properties comes as a solution and can be effectivelly added to the CLR method mentioned in the link provided here.

      Also as mentioned in this article. Using the LDAP dialect, you can retrieve members of a group with properly written condition. Then you do not need to iterate the member property in a loop and simply fetch all the member returned by the reuqry using the paged search. But again in T-SQL you are limited to the 1000 (1500) records as you are not able to effectively limit the number of rows returned. (The Range applies to properties of a object and not the whole objects).

  8. HI, i have been trying to query our AD and keep hitting a wall. not sure if you can help??

    I have created the linked server as suggested above. I’ve gone for the second security option (have also tried 1 2) : EXEC sp_addlinkedsrvlogin @rmtsrvname=N’ADSI’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’DDDLTDadministrator’,@rmtpassword=N’myPassword’

    but i keep getting this message: An error occurred while preparing the query

    I’ve searched far and wide and yours appears to be the most complete/understandable solution. Do you have any idea whether i am missing something?

    Many Thanks.

    • Cannot it be some typo in your query? First try the security of the context of current user. If you are running SQL Server on your local computer than nothing additional is necessary. If it is remote computer, launch SSMS on RD of the SQL Server to avoid double hop (Kerberos) authentication issue.

      Then try to query your AD.

      Let me know if it works.

  9. Hi,

    I am trying to get memberof data from active directory groups which works, but I am finding if you have a group that has multi domains I am getting a listing for only the domain the sql server is a member of.

    below is sample query I am using.

    SELECT cn,AdsPath
    FROM OPENQUERY (ADSI_H, ‘;(&(memberOf:1.2.840.113556.1.4.1941:=CN=Administrators,CN=Builtin,DC=hamilton,DC=local));cn, adspath;subtree’)
    ORDER BY cn;

    If I use powershell or vbscript it will list all the memberof not matter which domains are listed. I really want to have an all sql method if possible to keep things simpler. Any ideas on how to get around this?

  10. When a user has several group membership, this will suffix will cause
    the import to take the user’s first (initial) added group in active directory how can i fetch it in mysql by a query..
    plzzz Help Me.
    Thanx
    as soon as possible it’s urjent
    advance thanx…

 Leave a Reply

(required)

(required)

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>