====== Searching Active Directory from TSQL ======

This assumes the existence of a linked server called ''ADSI'' - if a linked server does not exist, you must [[#Creating an AD data source|create one first]].

===== Searching AD =====
Use ''OPENQUERY''. This example is searching for a user with the login name 'Richard':
<code tsql>SELECT * FROM OPENQUERY(ADSI, '
  SELECT givenName, sn, mail
  FROM ''LDAP://<base_dn>''
  WHERE objectCategory = ''Person''
  AND objectClass = ''User'' 
  AND samAccountName = ''Richard''
' )
</code>

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

===== Parameterised search =====
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:

<code tsql>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)
</code>

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

<code tsql>EXEC sp_addlinkedserver 
  'ADSI'
, 'Active Directory Service Interfaces'
, 'ADSDSOObject'
, 'adsdatasource'
</code>

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. 

{{tag> tsql dynamic ad}}