How to query Active Directory from a linked server

Question

Post reply

How to query Active Directory from a linked server

webrunner

SSC-Dedicated

Points: 31711
More actions
March 25, 2010 at 3:55 pm

#146118

Hello,
I've been trying to follow along with the example on these pages for querying Active Directory from a linked server in SQL Server 2005.
I used this link to create the linked server and the linked server login:
http://msdn.microsoft.com/en-us/library/aa772380%28VS.85%29.aspx
Then I tried to use one of the sample queries on this page (based on seeing the link in another SSC post):
http://sql.dzone.com/news/querying-active-directory-thro
The linked server and linked server login creation appears to succeed, and when I right-click on the linked server and choose Test Connection, it says "The test connection to the linked server succeeded."
One problem with that is that the connection test succeeds even when I change the linked server login password to something wrong. So something is not checking the credentials correctly.
And when I try to run an example query, I get this error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "
...
" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
(I omitted the query text for now, since it doesn't seem relevant yet.)
I admit that I am new to Active Directory, so I'm not sure if I am constructing the LDAP URLs correctly or whether I'm getting all of that wrong and also not properly understanding how to refer to accounts within the AD hierarchy.
But I'm wondering if there is a step-by-step introduction to how AD implements LDAP, especially:
1. how to link to and successfully query an AD directory from SQL Server
2. how to reference fields and groups in SQL queries against an AD linked server to get the right information
3. what errors such as 7321 indicate
etc.
I'm pretty much flying blind at this point and would be grateful if anyone could point me in the right direction to learn more.
Thanks in advance for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 1

Please post the error message you receive. Might be a rights issue. Are you on the right domain?

It would help to see the code you are using to create the link server. I use the following:

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

'ADSDSOObject', 'adsdatasource'

GO

You can then create a view to the AD data like:

CREATE VIEW [vADUsers] AS

SELECT samAccountName AS TM_Number

,displayName AS Workstation_User

,Department

FROM OPENQUERY(ADSI, '

SELECT samAccountName, displayName, Department

FROM ''LDAP://DC=<DOMAIN NAME>,DC=<DOMAIN NAME>,DC=COM''

WHERE objectClass=''user'' AND objectClass<>''computer''

')

You may only need one DC entry for the name and the last DC might be NET or COM - depends on your domain name.

To test you can run a query against the linked server, like:

SELECT samAccountName As WinNT_ID

,displayName AS Display_Name

FROM OPENQUERY(ADSI, '

SELECT samAccountName

,displayName

FROM ''LDAP://DC=<DOMAIN NAME,DC=<DOMAIN NAME,DC=net''

WHERE objectClass=''user'' AND objectClass<>''computer''

') AS tblADSI

WHERE samAccountName LIKE 'XYZ%'

Order by samAccountName

Hope this helps

webrunner SSC-Dedicated Points: 31711 More actions · Answer 2

Thanks for your reply.

Here is the command I used to create the linked server:

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

'ADSDSOObject', 'adsdatasource'

GO

Then I tried this query (domain changed for confidentiality):

select sn, givenName

from openquery(ADSI, '

select givenName,

sn,

sAMAccountName,

displayName,

mail,

telephoneNumber,

mobile,

physicalDeliveryOfficeName,

department,

division

from ''LDAP://DC=mydomain,DC=org''

where objectCategory = ''Person''

and

objectClass = ''user''

')

It seems from the error that something's wrong with the query itself - syntax, or the way I'm specifying the domain for the LDAP:// part. Is there a place in the AD Users and Computers control panel that indicates what the AD domain is, to help me specify the "DC=" parameters?

But I don't know if it could be a firewall issue instead, or something else. As I mentioned before, I used the Test Connection option, and it said the test was successful even when I entered the wrong password for the linked server login. That can't be right.

Thanks for any further advice you can give.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 3

Have you tried creating a view to AD? You can use the following:

CREATE VIEW [vADUsers] AS

SELECT samAccountName AS TM_Number

,displayName AS Workstation_User

,Department

FROM OPENQUERY(ADSI, '

SELECT samAccountName, displayName, Department

FROM ''LDAP://DC=<DOMAIN NAME>,DC=<DOMAIN NAME>,DC=COM''

WHERE objectClass=''user'' AND objectClass<>''computer''

')

If that goes okay you can query it to see if you are talking to AD.

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 4

I did try your query, substituting our Domain name and it ran sucessfully.

Does your domain have two names? Like kmhg.kmma.net? If so you need three DC= entries, one for each name and one for the net.

webrunner SSC-Dedicated Points: 31711 More actions · Answer 5

Yes, it does have 3 parts. I will try that.

Thanks again!

-webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 6

Also, error 7321 indicates that it is a rights, permissions issue. see:

http://help.wugnet.com/windows2/SQL-AD-Security-Query-AD-Database-ftopict517188.html

webrunner SSC-Dedicated Points: 31711 More actions · Answer 7

Thanks. The account I'm using is a domain account, and it is being used successfully in another context. However, I have asked to meet with our AD administrator to review what might be going wrong.

Just out of curiosity, do you know why the Test Connection feature in the linked server shortcut menu would appear to succeed if there is some kind of permissions issue in the linked sever given the way I configured it?

Thanks again for any help,

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31711 More actions · Answer 8

Hello again,

I'm writing to let everyone on this thread know that I think I resolved this problem.

Summary:

1. One of the steps in the AD linked server setup instructions that I used was to create a SQL login and associate it with the remote AD lookup login in the ADSI linked server. This is the login in the linked server properties, under Security, Local Login.

2. When I ran the sample query that kept failing, I was logged in to SQL Server as myself.

3. When, after exhausting many other avenues, I decided to log in to SQL Server using the SQL login mentioned in item 1 above, the query worked!

So you can chalk this up to my ignorance in using this new kind of linked server setup. I'm now working on how to deal with the 1000 record limit, but there seems to be a lot of available information on that for me to research.

Thanks again for all of your help.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Dird SSCertifiable Points: 5081 More actions · Answer 9

Hi,

Everywhere I look I see identical TSQL...what requires changing?

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

'ADSDSOObject', 'adsdatasource'

I assume the only part that changes is 'ADSI' with an AD server name? Or is 'adsdatasource' changed to something too?

Oh, seems it's pointing locally; no option to delete this post.

Dird