Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to query Active Directory from a linked server Expand / Collapse
Author
Message
Posted Thursday, March 25, 2010 3:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,327, Visits: 2,656
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #890278
Posted Friday, March 26, 2010 1:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, June 29, 2014 6:10 AM
Points: 447, Visits: 3,923
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










Post #891010
Posted Friday, March 26, 2010 2:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,327, Visits: 2,656
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #891107
Posted Friday, March 26, 2010 2:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, June 29, 2014 6:10 AM
Points: 447, Visits: 3,923
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.

Post #891110
Posted Friday, March 26, 2010 2:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, June 29, 2014 6:10 AM
Points: 447, Visits: 3,923
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.

Post #891113
Posted Friday, March 26, 2010 2:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,327, Visits: 2,656
Yes, it does have 3 parts. I will try that.

Thanks again!
-webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #891114
Posted Friday, March 26, 2010 3:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, June 29, 2014 6:10 AM
Points: 447, Visits: 3,923
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

Post #891118
Posted Monday, March 29, 2010 1:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,327, Visits: 2,656
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #892143
Posted Tuesday, April 20, 2010 3:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,327, Visits: 2,656
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #907282
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse