Querying Active Directory from SQL Server

Sometimes source systems do not store Windows credentials as an attribute of a person, and when that happens security becomes an interesting challenge.

For this project, I needed to retrieve the EmployeeNumber attribute from Active Directory to be able to link the user back to the source system (very useful for Reporting, but same logic applies for any service from the SQL Server BI stack).

First we need to create a ‘Linked Server’ from a SQL Server instance with the following system stored prod:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
GO

After you just create a Data Source pointing to the instance where you added that Linked Server and start querying the attributes you need from the Active Directory domain you need:

SELECT 
     WinUsername
     ,EmployeeNumber 
FROM OPENQUERY(ADSI,
    'SELECT 
          sAMAccountName
          ,employeeNumber
     FROM 
          ''LDAP://ou=users,ou=YourDomain,dc=YourDomainControler''
     WHERE 
          objectCategory = ''Person'' 
          AND objectClass= ''user'' 
          AND sAMAccountName='''+ YourUsername + '''')

If you need to get groups:

SELECT 
     User
     ,GroupName
FROM OPENQUERY( ADSI, 
     'SELECT 
          Name
          ,distinguishedname
     FROM 
          ''LDAP://ou=YourDomain,dc=YourDomainControler'' 
     WHERE 
          objectCategory = ''GROUP'' 
          AND CN = ''* *''')

If you need to know the people that belong to a specific Group:

SELECT 
     samaccountname
     ,Name
FROM OPENQUERY(ADSI, 
     'SELECT 
          samaccountname
          ,Name
     FROM 
          ''LDAP://ou=YourDomain,dc=YourDomainControler'' 
     WHERE 
          objectCategory = ''user'' 
          AND memberof = ''CN=NameOfTheGroup,OU=Groups,OU=YourDomain,DC=YourDomainControler''')