SQL Server to Active Directory

A few days ago, I needed to access the Active Directory to get a list of users into a database. When I was looking out for a solution I stumbled upon the following solution, which is pretty simple to use in SQL Server:

1. Create a linked server

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI', @useself=N'True', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL

2. Query your active directory


SELECT * FROM OPENQUERY (ADSI, 'SELECT userPrincipalName, displayName, distinguishedName,

sAMAccountName, mail, company, accountExpires, whenCreated

FROM ''LDAP:///OU=myou,OU=myou2,OU=myou3,DC=myDC,DC=myDC2''

WHERE objectCategory = ''Person'' AND objectClass = ''user''

AND NOT accountExpires=0

AND NOT company = ''a company name''

ORDER BY company')


 There is one drawback however, by default you can only get the first 1000 records from your active directory, but no worries, there is a workaraound! Follow this link to see the solution

Have fun!