query Active Directory for email address from SQL Server 2008 R2?

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).

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.

looked at the following:

• Querying Active Directory from Sql Server 2005
• Querying Active Directory from Sql Server 2008
• Create a SQL Server View of your AD Users
• Distributed Query (MS KB article)

Lets say one of our DC's (we have several) is开发者_如何转开发 called "fredDC" and the ldap root is "DC=fred,DC=com".

So I linked the Server (no mention of the hostname here, found by magic?)

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

then I try to run a query:

SELECT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName
FROM ''LDAP://DC=fred,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')

and get the following error:

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". 

In case it was a Kerberos pass-through problem I tried running the query directly on the DB Server and got the same error :(

I googled and didn't find much 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???

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

cheers!

It looks like a permissions error. How have you defined the authentication settings for the linked server arrangement? Run it as a domain admin account and see if it works.