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