Searching Active Directory from TSQL

This assumes the existence of a linked server called ADSI - if a linked server does not exist, you must create one first.

Use OPENQUERY. This example is searching for a user with the login name 'Richard':

SELECT * FROM OPENQUERY(ADSI, '
  SELECT givenName, sn, mail
  FROM ''LDAP://<base_dn>''
  WHERE objectCategory = ''Person''
  AND objectClass = ''User'' 
  AND samAccountName = ''Richard''
' )

(substituting <base_dn> for the required base DN).

<note tip>AD will return no more than 1000 records from a search, so for larger searches (e.g. to get a list of all users) you'll have to be a bit more imaginative.</note>

You can't use parameters directly in an OPENQUERY clause – so you can't even pass in a parameter containing a formed query string. Instead, build the entire SELECT statement as a string and execute that:

DECLARE @uname VARCHAR(255)
SET @uname = 'richard'
 
DECLARE @sql VARCHAR(MAX)
SET @sql = '
SELECT * FROM OPENQUERY(ADSI, ''
  SELECT givenName, sn, mail
  FROM ''''LDAP://<base_dn>''''
  WHERE objectCategory =''''Person''''
  AND objectClass = ''''User'''' 
  AND samAccountName = ''''' + @uname + '''''
'')'
 
--PRINT @sql
EXEC(@sql)

Set the AD data source up as a linked server. You must have (DB) server admin rights to do this.

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

You only need to do this once per domain on any given SQL Server instance. Note that domain information isn't specified here – it's passed in as an LDAP URL in the query's FROM clause – but multiple AD domains will require multiple linked servers to allow configuration of domain-specific credentials.