title: query Active Directory for email address from SQL Server 2008 R2?
body: <p>We have a scheduled job in a SQLS 2008R2 DB that queries a legacy DB directly for a users email address based on their username (sAMAccountName). </p>
  
  <p>I would like to query AD directly so I can break the link between these two systems, but having tried the same advice that shows up on every page, I can't get it to work, I think either it's not finding the DC or pass-through authentication / Security is not working. We're using windows authentication on a single domain which all machines are part of, the database is running as the Network Service account.</p>
  
  <p>looked at the following:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/1251038/querying-active-directory-from-sql-server-2005">Querying Active Directory from Sql Server 2005</a></li>
  <li><a href="https://stackoverflow.com/questions/1342409/querying-active-directory-from-sql-server-2008">Querying Active Directory from Sql Server 2008</a></li>
  <li><a href="http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users/" rel="nofollow noreferrer">Create a SQL Server View of your AD Users</a></li>
  <li><a href="http://msdn.microsoft.com/en-us/library/aa772380%28VS.85%29.aspx" rel="nofollow noreferrer">Distributed Query (MS KB article)</a></li>
  </ul>
  
  <p>Lets say one of our DC's (we have several) is called "fredDC" and the ldap root is "DC=fred,DC=com".</p>
  
  <p>So I linked the Server (no mention of the hostname here, found by magic?)</p>
  
  <pre><code>EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
  </code></pre>
  
  <p>then I try to run a query:</p>
  
  <pre><code>SELECT sAMAccountName
  FROM OPENQUERY(ADSI, 'SELECT sAMAccountName
  FROM ''LDAP://DC=fred,DC=com''
  WHERE objectCategory = ''Person''
  AND objectClass = ''user'' ')
  </code></pre>
  
  <p>and get the following error:</p>
  
  <pre><code>Msg 7321, Level 16, State 2, Line 1
  An error occurred while preparing the query "SELECT sAMAccountName
  FROM 'LDAP://DC=fred,DC=com'
  WHERE objectCategory = 'Person'
  AND objectClass = 'user' for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". 
  </code></pre>
  
  <p>In case it was a Kerberos pass-through problem I tried running the query directly on the DB Server and got the same error :(</p>
  
  <p>I googled and <a href="http://social.technet.microsoft.com/Forums/en-US/winserverDS/thread/583ef23d-9c10-473f-9968-b9e84c3e2231/" rel="nofollow noreferrer">didn't</a> <a href="http://social.msdn.microsoft.com/Forums/en/transactsql/thread/083229e7-58f2-44cf-ae5d-c9acf366818c" rel="nofollow noreferrer">find</a> <a href="http://www.sqlservercentral.com/Forums/Topic265745-149-1.aspx" rel="nofollow noreferrer">much</a> on the error message, nothing that was helpful except for one comment advising to "Check if the linked server (ADSI) allow remote accessing.", which I'm not sure what that means? The KB article mentions no other configuration required by the AD server???</p>
  
  <p>Can anyone suggest how to do this properly? I feel I'm missing something obvious (example, I'm not configuring the DC host anywhere) but I don't know what...</p>
  
  <p>cheers!</p>