Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Querying Active Directory Data from SQL Server


By:   |   Read Comments (30)   |   Related Tips: 1 | 2 | 3 | More > Linked Servers

Problem

My boss is asking for a list of email addresses and phone numbers for all users in the company. I know this data exists in Active Directory, so how can I access this data from SQL Server?  In this tip we walk through how you can query Active Directory from within SQL Server Management Studio.

Solution

In this tip I'll show you how to query Active Directory using linked servers and the OPENQUERY command.

Create Linked Server

First thing we'll do is create our linked server, Active Directory Service Interface also known as ASDI, to Active Directory using the code below:

USE [master]
GO 
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\USER',@rmtpassword='*********'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Make sure you change the @rmtuser and @rmtpassword variables to a login and password that has access to your Active Directory.


Querying Active Directory

Once the linked server is created we can now setup our query to return the information we need.

First, you'll need to ask your Network/Systems Administrator for your LDAP info then we can continue to the query. 

Here is how the LDAP connection is broken down:

  • For our example it looks like this: LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com
  • LDAP://Domain.com - is the name of a domain controller
  • /OU=Players - this is the Organization Unit, in our case (Players)
  • ,DC - this is the Domain Name broken up by domain and extension name
  • So....LDAP://DomainControllerName.com/OU=OrganizationalUnit,DC=DOMAIN,DC=NAME

According to the problem, this user needs to return the companies email addresses and phone numbers. To do this we can use the code below:

(note - you will need to change your domain information for this to work)

SELECT * FROM OpenQuery ( 
  ADSI,  
  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber 
  FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com'' 
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
ORDORDER BY displayname

As you can see this query will return Active Directory's Display Name, Telephone Number, Email Address, Mobile Number, and Fax Number. Also note, that when you query Active Directory it actually creates the SELECT statement backwards. I started the SELECT statement with SELECT displayname... but in the results pane it displayed displayName last as shown below.

use sql server to query active directory

If you wanted to view more columns for each user we can use the below code to display fields such as: FirstName, Office, Department, Fax, Mobile, Email, Login, Telephone, Display Name, Title, Company, Pager, Street Address, and more.

SELECT * FROM OpenQuery
  ( 
  ADSI,  
  'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName, 
  mail, mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname 
  FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
ORDER BY displayname

querying active directory from sql server management studio

You can also filter out columns using a WHERE clause. In this example I only want to return results where users have a fax number.

SELECT * FROM OpenQuery
  ( 
  ADSI,  
   'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName, mail,  
  mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname
  FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''   
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
WHERE facsimileTelephoneNumber IS NOT NULL
ORDER BY displayname

writing a query to selectively query active directory from sql server
Next Steps
  • To view all the Active Directory attributes click here
  • To view how to get Active Directory Users and Groups with SSIS check out this tip from Ray Barley


Last Update:





About the author





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, August 29, 2016 - 5:22:37 PM - richard Back To Top

 

 GOOD AFTERNOON,

WHY COULD PLEASE HELP ME REGARDING LDAP INTEGRATION WITH SQL ?
I noticed that NO QUERY RETURN , THE LIMIT AND RECORDS 900 . WOULD BE A BETTER WAY THAT?
thank you

Friday, July 08, 2016 - 8:35:35 PM - Rufus Back To Top

How long did it take for your query to return?  It took me ave 6-7 secs.


Wednesday, June 29, 2016 - 11:19:14 AM - ndemet Back To Top

Is there any way to query all OU's except one?  Something like: WHERE OU <> ''SOME OU''


Monday, January 25, 2016 - 9:49:16 AM - Nick Back To Top

This is fine if you have only one domain. If you're a large company with multiple domains, you have to query the Global Catalogue.

FROM  ''GC://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''  


Friday, May 29, 2015 - 1:06:32 PM - Dan Faustine Back To Top

Never mind, I found it... You have to right-click on the linked server in SSMS, and go to the Security tab.


Friday, May 29, 2015 - 1:04:31 PM - Dan Faustine Back To Top

Great article, thanks!

Does anyone know how to update the password of the user once it's entered into the linked server?


Tuesday, December 02, 2014 - 8:18:12 AM - Tomasz Back To Top

Hi Niraj, maybe this will be helpful. Try to use LDAP://CN=External Users,CN=SDE,CN=execldr,DC=ss,DC=com instead of LADP://xxx/OU=...


Tuesday, December 02, 2014 - 4:32:06 AM - Niraj Back To Top

 Hi Mr. Brady

I wanted to access maxpwdage (Maximum password age) from the Active directory i am using following query but its getting null can you please give some explanation on it that will help me 

i used follwing query 

SELECT  top 100 *    FROM OpenQuery ( 

 

  ADSI,  

 

  'SELECT  displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber ,pwdLastSet,name,maxpwdage

 

  FROM  ''LDAP://xxx/OU=External Users,OU=SDE,OU=execldr,DC=ss,DC=COM'' 

 

  WHERE objectClass =  ''User''  

 

  ') AS tblADSI  

 


Monday, December 01, 2014 - 8:20:11 AM - Tomasz Back To Top

Hi Brady, Very good post but its not working everytime. Many times there is an error 7330 (when LDAP path is wrong like LDAP://DC=Folder,DC=domain,DC=com instead of LDAP://CN=Folder,DC=domain,DC=com) or 7321 (when there is more than 900-1000 rows returned by the query). Could you provide some solution based on above ? Tomasz


Friday, November 07, 2014 - 3:32:35 AM - Dird Back To Top

Everyone online is using the exact same code snippet which doesn't help with seeing what to change.

 

Am I right in assuming the only things required to be changed are @server (and every other ADSI equivalent), @rmtuser & @rmtpassword?

 

@datasrc and everything else remain unchanged?


Thursday, October 16, 2014 - 3:38:36 PM - eric81 Back To Top

 

I'm running a query against Active Directory on a SQL 2012 Server and getting the following error. Now I have the same linked server setup on a SQL 2008 Server , but have no issues running this query.

 

Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server


Tuesday, August 19, 2014 - 3:11:26 PM - bordwalk2000 Back To Top
WHERE objectCategory = ''User''
Will give you alot better search results than WHERE objectClass = ''User'' because it will strip out all the computer accounts from the list.
 

Tuesday, July 29, 2014 - 12:35:35 PM - Jason Armour Back To Top

Brady,

 

I am trying to do this within a Trigger... Where do i put the Linked Server Code? Or is it best to have the Trigger exec a Stored procedure, which would then execute the Linked Server, pull the data, and update the record? Otherwise, this is awesome help. Thanks!

 

Thanks

Jason


Wednesday, July 02, 2014 - 11:57:52 AM - rach Back To Top

I am gettting an error.  I don't understand your setup one.  I am having security issue.

"Once the linked server is created we can now setup our query to return the information we need.

First, you'll need to ask your Network/Systems Administrator for your LDAP info then we can continue to the query. "

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT displayName, telephoneNumber, mail, mobile

FROM "LDAP://ad1.peelsb.com/OU=ad1,DC=peelsb,DC=com"

WHERE objectClass = "User"

" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

 


 


Wednesday, May 28, 2014 - 12:01:57 PM - Allen Back To Top

Thanks for this one!  It brought me one step closer to being able to automate one of my routine tasks.

 

 


Thursday, May 22, 2014 - 11:02:15 AM - maurer Back To Top
On following your procedure below is the error massage i got can you give me any insight?
 
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber FROM  'LDAP://mydomain.local/DC=mydomain,DC=local'  WHERE objectClass =  'user' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". 
 
 
 
Thank
 
 

Thursday, May 22, 2014 - 9:47:48 AM - maurer Back To Top

Great post it is insightfull. Please tell me, should the login and password that has access to your Active Directory be an administrator



Wednesday, October 02, 2013 - 6:58:48 AM - Menaka Back To Top

Can I get the AD user's password also to the database? It may be encrypted format? so the same format that sql server save the passwords?

My requirement is to replicate the AD user + password to Sql server "SQL server logins."

Any helpful comment would be highly appeciate

Thanks

Menaka


Tuesday, July 23, 2013 - 2:45:54 AM - Ahmet Turan YIGIT Back To Top

SELECT * FROM OpenQuery ( 

  ADSI,  

  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber 

  FROM  ''LDAP://OGM.GOV.TR'' 

  WHERE objectClass =  ''User'' 

  ') AS tblADSI

ORDER BY displayname

 

Question : How can I do?


Tuesday, October 23, 2012 - 11:19:53 PM - Wanpen_ake Back To Top

Thank you very much...very helpfull


Monday, October 15, 2012 - 5:55:37 PM - pdtechguru Back To Top

How to Query Active Directory Objects

 

http://pdtechguru.wordpress.com/2012/10/15/how-to-query-active-directory-objects-3/


Wednesday, June 13, 2012 - 3:30:08 PM - Vaishnav Vinjamuri Back To Top

Hi

Can anyone help me out in resolving this issue... thanks!!!

 

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT samaccountname
  FROM  'LDAP://ARCBRIDGE3.com/DC=ARCBRIDGE3,DC=com'
  WHERE objectClass =  'User'
  " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI"


Tuesday, February 28, 2012 - 9:52:59 AM - midwest guy Back To Top

Why not use powershell you get the same results in a few lines of code no linked server needed. Using the Quest free powershell tools makes this a very easy process to implement. 


Wednesday, December 28, 2011 - 5:09:07 AM - Dmytro Back To Top

Further to my earlier message - sorry, I have misspecified the active directory path. Works a treat. Thousand rows limit is very annoying, but completely understandable - our AD has way over 10k entries!!!


Wednesday, December 28, 2011 - 4:01:45 AM - Dmytro Back To Top

Hi Brady,

Thank you for an extremely useful article. Tried to give it a go, but get this not very informative error and cannot proceed. Does it make any sense to you?

 Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT displayName
  FROM  'LDAP://ADINTERNAL.com/OU=sageukie,DC=ADINTERNAL,DC=com'
  " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

Thank you very much in advance,

Happy new year,

Dmytro

 


Tuesday, December 27, 2011 - 4:18:14 PM - Juanita Back To Top

HI Brady,

What versions of sql server does this work on?


Tuesday, December 27, 2011 - 10:30:09 AM - carlos_sfc Back To Top

Thanks Brady.

Happy New Year!!


Tuesday, December 27, 2011 - 10:17:25 AM - Casey Back To Top

The LDAP query limit is set on the domain.  If you can't get your domain admin to increase the limit you can use a filter in the OpenQuery SELECT (e.g. OpenQuery(ADLINK, 'SELECT sAMAccountName FROM ''LDAP://OU=Users,DC=YOUR,DC=com'' where objectClass = ''User''
AND sAMAccountName = ''D*''') returns all user names that begin with the letter D) and union the results.


Tuesday, December 27, 2011 - 10:10:45 AM - Brady Back To Top

Thanks carlos. I'm glad you can find this useful. Here is something I found on MSDN regarding the 1000 row limitation:

http://blogs.msdn.com/b/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx


Tuesday, December 27, 2011 - 9:56:52 AM - carlos_sfc Back To Top

Very useful post Brady. How to avoid the limit of page size?, when I execute the openquery it just returns 1000 records and no more.
Thanks

 


Learn more about SQL Server tools