Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Querying Active Directory Data from SQL Server

MSSQLTips author Brady Upton By:   |   Read Comments (13)   |   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




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
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

 


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 - 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:30:09 AM - carlos_sfc Read The Tip

Thanks Brady.

Happy New Year!!


Tuesday, December 27, 2011 - 4:18:14 PM - Juanita Read The Tip

HI Brady,

What versions of sql server does this work on?


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

 


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!!!


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, 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"


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/


Tuesday, October 23, 2012 - 11:19:53 PM - Wanpen_ake Read The Tip

Thank you very much...very helpfull


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?


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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.