Learn more about SQL Server tools

   
   















































Querying Active Directory Data from SQL Server

MSSQLTips author Brady Upton By:   |   Read Comments (24)   |   Related Tips: 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: 12/27/2011


About the author
MSSQLTips author Brady Upton
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, December 02, 2014 - 8:18:12 AM - Tomasz Read The Tip

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 Read The Tip

 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 Read The Tip

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 Read The Tip

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 Read The Tip

 

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 Read The Tip
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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip
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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Thank you very much...very helpfull


Monday, October 15, 2012 - 5:55:37 PM - pdtechguru Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

HI Brady,

What versions of sql server does this work on?


Tuesday, December 27, 2011 - 10:30:09 AM - carlos_sfc Read The Tip

Thanks Brady.

Happy New Year!!


Tuesday, December 27, 2011 - 10:17:25 AM - Casey Read The Tip

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 Read The Tip

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 Read The Tip

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

 




 
Sponsor Information