bwkoger
asked on
Query Activer Directory in Sql Server 2008
I am trying to query Active Directory.
I have:
1. Configured for distributed queries
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
2. Created Linked server for Active Directory
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfces', 'ADSDSOObject', 'adsdatasource'
3. Tried to Query:
SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC={domainname},D
Where {domainname} is our domain.
After executing the query:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
One repsonse to this issues was:
You must have Adsldp.dll Adsldpc.dll Adsmsext.dll Activeds.dll installed on your client computer to work with the provider
After searching the server, the dll's exist in the Windows system folders.
Environment:
Running Sql Server 2008 SP2 64 bit
Windows Server 2008
Thank you for any help!
I have:
1. Configured for distributed queries
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
2. Created Linked server for Active Directory
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfces', 'ADSDSOObject', 'adsdatasource'
3. Tried to Query:
SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC={domainname},D
Where {domainname} is our domain.
After executing the query:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
One repsonse to this issues was:
You must have Adsldp.dll Adsldpc.dll Adsmsext.dll Activeds.dll installed on your client computer to work with the provider
After searching the server, the dll's exist in the Windows system folders.
Environment:
Running Sql Server 2008 SP2 64 bit
Windows Server 2008
Thank you for any help!
ASKER
I entered the statement:
SELECT * FROM OpenQuery(ADSI, '<LDAP://DC=imerys,DC=COM>
Returned:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "<LDAP://DC=domainname,DC=
SELECT * FROM OpenQuery(ADSI, '<LDAP://DC=imerys,DC=COM>
Returned:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "<LDAP://DC=domainname,DC=
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I simply copied / pasted the line above......
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Does the account you are using to run the query have read permissions on Active Directory? Are you running the query on your local machine or directly on the server?
You may also want to review the forum (http://www.sqlservercentral.com/Forums/Topic695128-359-1.aspx) as there are some posts there that may point to a solution.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am running the query directly on the Sql Server. Logged in using a username the network guys have assured me has read access toi the domain AD.
I re-created ADSI with the domain name in the provider string. no change.
I also checked the referenced forum link.
This shouldn't be this difficult. :(
I re-created ADSI with the domain name in the provider string. no change.
I also checked the referenced forum link.
This shouldn't be this difficult. :(
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried adding the reomte user.
The network guys reset the delagation for the user id to ensure I havce read access.
Please see the attached for queries and AD network query / access.
I have a new error message for any of the queries in the attached Word doc.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=doma,DC=com' WHERE objectCategory='user' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
SQL-AD-Queries.doc
The network guys reset the delagation for the user id to ensure I havce read access.
Please see the attached for queries and AD network query / access.
I have a new error message for any of the queries in the attached Word doc.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=doma,DC=com' WHERE objectCategory='user' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
SQL-AD-Queries.doc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! Almost there!!! except....
Using this query:
SELECT * FROM OpenQuery(ADSI, '<LDAP://DC=doma,DC=imgln,
It DOES return rows, but then erors with the following:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
So, I'm guessing there is still an area in the AD structure for which I do not have proper access.
Is there a way to filter in the "where" clause to limit the search to part of the structure?
Thank you so much for sticking with this. Obviously, I'm a SQL hack.
Using this query:
SELECT * FROM OpenQuery(ADSI, '<LDAP://DC=doma,DC=imgln,
It DOES return rows, but then erors with the following:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
So, I'm guessing there is still an area in the AD structure for which I do not have proper access.
Is there a way to filter in the "where" clause to limit the search to part of the structure?
Thank you so much for sticking with this. Obviously, I'm a SQL hack.
ASKER
I can list up to 901 rows after which it aborts with the above error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The query still aborts after 901 rows are returned. Perhaps it has to do with some additional secutiry in the structure.
Did you ever resolve your last issue about the query aborting after returning 901 rows? I'm currently having the same issue with 997 rows before it crashes. Please let me know if you ever found a solution. Thanks.
...OpenQuery(ADSI, '<LDAP://DC={domainname},D