Querying Active Directory on SQL Server using T-SQL

2011-04-12 Pavel Pawlowski SQL Server, T-SQLActive Directory, LDAP, Query, SQL Server, T-SQL

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.

Use Linked Server
User OPENROWSET statement
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

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).

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.

31 thoughts on “Querying Active Directory on SQL Server using T-SQL”

gerson says:

2011-07-25 at 03:17

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

Reply
- Pavel Pawlowski says:
  
  2011-07-25 at 07:14
  
  It is possible however not by pure T-SQL. If you take a look on my post Querying Active Directory on SQL Server using CLR, I’m using there objects from the System.DirectoryServices assembly. If you take a closer look on the assembly, there are objects like DirectoryEntry etc, which can be used to manage the AD.
  
  You can create a CLR Stored procedures or CLR Functions to encapsulate the functionality and then call them from withing T-SQL code for example.
  
  Reply
- Pavel Pawlowski says:
  
  2011-07-25 at 07:33
  
  Related to managing AD objects there are two links you can find interesting for taking inspiration how to manage the objects.
  
  On Code Project: Howto: (Almost) Everything In Active Directory via C#
  On C# Online: User Management with Active Directory
  
  Reply
frogola says:

2012-06-12 at 18:02

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?

Reply
- Pavel Pawlowski says:
  
  2012-06-12 at 19:22
  
  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.
  
  Reply
Eric says:

2012-06-18 at 15:18

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”.

Reply
- Pavel Pawlowski says:
  
  2012-06-19 at 08:30
  
  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
  
  Reply
Lloyd M says:

2012-07-26 at 16:28

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. 🙂

Reply
- Pavel Pawlowski says:
  
  2012-07-26 at 20:08
  
  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.
  
  Reply
Lloyd M says:

2012-07-27 at 15:19

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

Reply
Lloyd M says:

2012-07-27 at 15:27

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).

Reply
Lloyd M says:

2012-07-27 at 15:41

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).

Reply
- Pavel Pawlowski says:
  
  2012-07-30 at 09:59
  
  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).
  
  Reply
T-SQL to Query Active Directory « Preston's Development says:

2012-09-28 at 05:26

[…] http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-t-sql/ […]

Reply
george says:

2012-12-06 at 23:35

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.

Reply
- Pavel Pawlowski says:
  
  2012-12-17 at 09:46
  
  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.
  
  Reply
Querying Active Directory from SQL | B3IT on Microsoft says:

2013-10-10 at 15:13

[…] http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-t-sql/ […]

Reply
Consultas contra Directorio Activo desde SQLServer | Curiosities says:

2013-12-06 at 16:46

[…] Source: http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-t-sql/ […]

Reply
Bradley says:

2014-08-06 at 00:50

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?

Reply
Sonu Sharma says:

2014-08-07 at 07:44

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…

Reply
- Sonu Sharma says:
  
  2014-08-07 at 07:59
  
  Is In MSSQL 2008
  
  Reply
- Pavel says:
  
  2014-08-07 at 09:00
  
  Hi,
  
  I dont’ think it is possible to specify what will be returned in case of multi value (array) attributes in the LPAD Query. For this purposes you can use the CLR method. The sample converts the array (multi value) attributes to a comma separated list.
  
  Reply
  - Sonu Sharma says:
    
    2014-08-08 at 07:58
    
    Thanx For Reply Pavel…!!!!!
    
    Reply
Pravin Wade says:

2014-12-15 at 15:24

Hi Pavel,

I am in trouble in reading values from LDAP to sql server.

I have created Linked server with using below command –
“sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource'”

Now, when I am trying to query on ADSI, somehow query is not giving me output.

I think, the sql script is not correct.

See below LDAP configuration in web application-
“LDAP://ASIAPACIFIC.cpqcorp.net/CN=pravin.wade@hp.com,OU=IN,OU=Users,OU=Accounts,DC=asiapacific,DC=cpqcorp,DC=net”

Now, I want to add in sql server query –

I am doing like this-

SELECT * FROM OPENQUERY(ADSI,
‘SELECT sn FROM ”LDAP://DC=asiapacific,DC=cpqcorp,DC=net”
WHERE objectClass=”user”’)

What I am missing, any parameter or wrong values?

Reply
- Pavel says:
  
  2014-12-15 at 17:47
  
  Do you get some kind of error or the query simply does not return anything?
  
  Maybe you have to authenticated and therefore the query does not return anything.
  
  If it is query against AD, check proper users mapping fro the linked server.
  
  For general LDAP, you can also use the version with LDAP dialect and provide user name and password to authenticate.
  
  Reply
  - Pravin Wade says:
    
    2014-12-16 at 14:08
    
    Its giving me error now-
    —————————
    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider “ADSDSOObject” for linked server “ADSI”.
    
    ——–
    I have created- LinkedServer = “ADSI”
    
    —————–below is my query-
    SELECT * FROM OPENQUERY(ADSI,
    ‘SELECT sn FROM ”LDAP://DC=asiapacific,DC=cpqcorp,DC=net”
    WHERE objectClass=”user”’)
    
    Please tell me how I can proceed step by step, I am following your blog but something I am missing here.
    
    Reply
    - Pavel says:
      
      2014-12-16 at 17:37
      
      Ah.. it is probably because you reached the limit of 1000 rows which can be fetched at once from AD.
      
      Using the T-SQL and Linked server you can only fetch up to 1000 rows. If you have more, you have to narrow your condition so it returns less than 1000 rows.
      
      The only reasonable way to retrieve all records Is using CLR where you can page the results and fetch all the rows.
      
      You can find this in my article: Querying Active Directory on SQL Server using CLR.
      
      Reply
Pravin Wade says:

2014-12-17 at 06:14

Thanks Pavel, I will try this and let you know.

Reply
Frankie says:

2016-02-17 at 08:56

Hi Pavel

I’m getting the error “Cannot execute the query (Msg 7320)” the whole time, no matter which of the dialects i try. I’m using a domain username / password for all users which is valid.

Any help would be greatly appreciated

Tnx
F

Reply
Luis Agustin Azario says:

2017-07-21 at 00:50

Thanks Pavel, I am working with a similar query to retrieve information about user in AD, I only need sAMAccountName and a part of the field description, now I learned that description in AD is a multivalue field that I can not retrieve as mentioned in the following error.
I also understand that this can be solved using VB, CLR, POWERSHELL, but I am not able to develop such a solution and need to solve with more pure SQL or TSQL procedure, perhaps with a Cursor fetching each row with rowset ?
What do you think ?

Msg 7346, Level 16, State 2, Line 23
Cannot get the data of the row from the OLE DB provider “ADSDSOObject” for linked server “ADSITARJETA”. Could not convert the data value due to reasons other than sign mismatch or overflow.

This is my query:
SELECT *
FROM OPENQUERY(ADSITARJETA,’SELECT cn,description, ADsPath FROM ”LDAP://xxxx/OU=xxx,DC=xxxx,DC=XXXX”
WHERE ObjectCategory=”person” AND objectClass=”user”’)

Reply
Querying Active Directory on SQL Server using T-SQL – SharePoint Learning says:

2022-05-16 at 15:53

[…] https://pawlowski.cz/2011/04/12/querying-active-directory-sql-server-t-sql/ […]

Reply