Query Active directory form SQL server

A couple of weeks ago we had the needed to get some users data and our best option was querying directly to Active directory from an SQL server, this could be the best option in some cases, taking in mind that a healthy AD with a good maintainment is allways our best shot. Microsoft Active directory is a secure database with a lot of fields which are possible to fill with all user and employees information, and at the same time can provide us with some other important data, like creation time, last logon, expiration date….

In this first example we are going to get complete name of all users, email address and phone number.

Ok, let’s start, first of all we’ll create an interface between our SQL server and active directory this is known as ASDI and is the right way to link this two systems

You must change @rmtuser and @rmtpassword values with a valid user credentials which is allowed to read AD

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

After this we can start to query directly to AD in this way. Remember to change the LDAP link with your complete OU Link

SELECT TOP (100) PERCENT displayName, mail, telephoneNumber, mobile
FROM OPENQUERY(ADSI, 
'SELECT displayName, mail, telephoneNumber, mobile 
FROM ''LDAP://YouDomainName/OU=OneOfYourOrganizationUnit,DC=YourDomain,DC=com'' 
WHERE objectClass = ''User'' 
') 
AS tblADSI
ORDER BY displayName

If you want to learn more, the is an Excel sheet about the AD properties, how they map to the “Active Directory Users & Computers” tool and others at  Richard Mueller’s site

Hope this was useful

2 Replies to “Query Active directory form SQL server”

  1. Hello – Excellent article and related links.
    I have a question:
    How can I pull the attribute “OtherMailbox” from transact SQL???
    I found that using powershell we need to use:
    @{name=”otherMailbox”;expression={$_.otherMailbox[0]}}
    … but it doesn’t with for SQL
    Thanks

  2. Yes, it’s easy you only have to add in your query. If you had a connection to active directory then you can add any attribute in your queries, try something like this:

    SELECT displayName, mail, telephoneNumber, mobile, otherMailbox
    FROM OPENQUERY(ADSI, 
    'SELECT displayName, mail, telephoneNumber, mobile, otherMailbox 
    FROM ''LDAP://YouDomainName/OU=OneOfYourOrganizationUnit,DC=YourDomain,DC=com'' 
    WHERE objectClass = ''User'' 
    ') 
    AS tblADSI
    ORDER BY displayName
    

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.