Sunday, 7 April 2013

Sourcing Data from Active Directory

Something I've found useful in the past is being able to source user details from Active Directory.
Its nice being able to link the User Names from your systems up to actual Names of the users when providing data.
It is also handy when you want to find which users have certain security access. For example who has access to Database X.

--Check if Ad Hoc Distributed Queries is Visible/Turned on

--Make Visible if it isnt
sp_configure 'show advanced options', 1

--Turn it on
sp_configure 'Ad Hoc Distributed Queries', 1

--Return All Active Directory Users
--Replace ABC.DEF with your active directory server name

'adsdatasource;', 'SELECT Title, Department, Mail, DisplayName, Sn, GivenName, Cn
FROM ''LDAP://ABC.DEF'' where objectClass = ''User'' AND objectClass<>''computer'' '

--Find All Active Directory Groups
--Replace ABC.DEF with your active directory server name
select *, substring(AdsPath, charindex('CN=', adspath), 300) as GroupName , substring(AdsPath, 0, charindex('CN=', adspath) - 1) as Domain FROM OPENROWSET('ADSDSOObject', 'adsdatasource;', 'SELECT AdsPath, name FROM ''LDAP://ABC.DEF'' WHERE objectCategory=''Group'' ' ) --Find Users from X Active Directory Group
--Replace 'YourADGroup' with the name of the Group you are after

--Replace ABC.DEF with your active directory server name
--Also again in the DC=ABC,DC=DEF section
select * FROM OPENROWSET('ADSDSOObject', 'adsdatasource;', 'SELECT Cn FROM ''LDAP://ABC.DEF'' WHERE memberOf=''CN=YourADGroup,OU=Security,OU=Groups,DC=ABC,DC=DEF'' ' )


  1. Nice post...I've used a similar solution to query AD, and pull the data into SQL. The only thing I've run into is that SQL can't page through the AD records correctly. So if you have more than 1000 records, SQL will return an error.

    Check out my post (linked below) for an easy work around to this solution:

  2. Thanks Chandler, I have tried the linked server method in the past but had no luck. I think maybe my security may of caused issues.

    Anyway I will have to give the method you describe a try some time.