April 10, 2009 at 9:42 pm
SELECT [Name], SAMAccountName [AccountID]
FROM OPENQUERY( ADSI,
'SELECT Name, SAMAccountName
FROM ''LDAP://DC=corporate,DC=CC,DC=local''
WHERE objectCategory = ''Person'' AND
objectClass = ''user'' ')
runs from SQL Server 2005 on a Windows XP laptop
but does not run on SQL Server 2005 on a Windows Server 2003 box.
The error I received reads:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any ideas why I am getting that error? Thanks
April 13, 2009 at 6:38 am
What account is the SQL Server running under?
Is the provider for Active Directory installed on the server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2009 at 8:00 am
Is there a way to find out if Active Directory provider is installed on the SQL server box? (find out if Adsldp.dll, Adsldpc.dll, Adsmsext.dll, and Activeds.dll exist?)
Also our group has had a turnover. Is there a way for me to query what account SQL Server runs under( Network Service, LocalSystem etc...)
Thanks
April 13, 2009 at 8:54 am
I'd just check using SSMS for the ADsDSOObject in the provider under the LinkedServers Providers node to see if the provider is installed.
What is the security set on the ADSI linked server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2009 at 8:57 am
SSMS for the ADsDSOObject exists on the list of providers.
security set on the ADSI linked server is use identity
where the identity is a domain service account that can read Active Directory. Thanks
April 14, 2009 at 10:10 am
First of all define a Linked Server to the Active Directory Services using the below t-sql command.
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
Enable QPENQUERY in SQL Server 2005 and run QPENQUERY T_SQL Statements
Then try running the below LDAP SELECT queries using t-sql OPENQUERY command.
Be aware that, Microsoft SQL Server 2005 by default prevents the usage of "OpenQuery", in a way OpenQuery is disabled by default.
You might have to run the below t-sql script batch in order to enable OpenQuery in a MS SQL Server instance.
/*
sp_configure 'show advanced options', 1
reconfigure with override
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
*/
SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=myserver,DC=com'' WHERE objectCategory=''user'' ')
SELECT * FROM OpenQuery(ADSI, 'SELECT mail, displayName, userPrincipalName FROM ''LDAP://DC=myserver,DC=com'' WHERE objectCategory=''user'' ')
You can see that you should use the attribute names properly in the t-sql OPENQUERY statements.
April 15, 2009 at 9:16 am
Thanks to everyone for the help. It turned out to be a mutiple hops issue and my company does not use Kerberos. Thanks!
July 9, 2009 at 8:00 am
Hi hopefully someone can help me with this as well.
as above, I'm trying to query my LDAP server.
This is what I have setup:
I have access to our Active Directory and can view users and machines in active directory
- SQL Server 2005 Develper Edition installed on my machine.
- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service
- OpenQuery is enabled
(I can run open query commands to other systems)
- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')
where the domain is MyDOMAIN.NET.
I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??
Sorry - I know this has been covered several times, but I' struggling to see what the issue is!
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 9, 2009 at 8:00 am
Hi hopefully someone can help me with this as well.
as above, I'm trying to query my LDAP server.
This is what I have setup:
I have access to our Active Directory and can view users and machines in active directory
- SQL Server 2005 Develper Edition installed on my machine.
- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service
- OpenQuery is enabled
(I can run open query commands to other systems)
- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')
where the domain is MyDOMAIN.NET.
I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??
Sorry - I know this has been covered several times, but I' struggling to see what the issue is!
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 9, 2009 at 8:02 am
Hi hopefully someone can help me with this as well.
as above, I'm trying to query my LDAP server.
This is what I have setup:
I have access to our Active Directory and can view users and machines in active directory
- SQL Server 2005 Develper Edition installed on my machine.
- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service
- OpenQuery is enabled
(I can run open query commands to other systems)
- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')
where the domain is MyDOMAIN.NET.
I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??
Sorry - I know this has been covered several times, but I' struggling to see what the issue is!
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 9, 2009 at 8:02 am
Hi hopefully someone can help me with this as well.
as above, I'm trying to query my LDAP server.
This is what I have setup:
I have access to our Active Directory and can view users and machines in active directory
- SQL Server 2005 Develper Edition installed on my machine.
- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service
- OpenQuery is enabled
(I can run open query commands to other systems)
- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')
where the domain is MyDOMAIN.NET.
I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??
Sorry - I know this has been covered several times, but I' struggling to see what the issue is!
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 9, 2009 at 8:04 am
Hi hopefully someone can help me with this as well.
as above, I'm trying to query my LDAP server.
This is what I have setup:
I have access to our Active Directory and can view users and machines in active directory
- SQL Server 2005 Develper Edition installed on my machine.
- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service
- OpenQuery is enabled
(I can run open query commands to other systems)
- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')
where the domain is MyDOMAIN.NET.
I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??
Sorry - I know this has been covered several times, but I' struggling to see what the issue is!
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
August 5, 2009 at 2:44 am
.
August 5, 2009 at 2:45 am
Correction to above post...
I had your issue, and managed a temp fix as below (at least it will work)....
In SSMS, browse to your linked server: ADSI.
Right-click ADSI, select properties, click "security" on left.
Change security from "Be made using login's current security context"
to-> "Be made using this security context"
Use a domain account (of same domain you are querying),
supply the password- which you would need to know.
UserName format: DOMAIN_NAME\USERNAME
Click OK.
Run queries supplied in above posts..
NOTE: this is not the proper solution, this will just highlight the fact that if this change works it means:
1) the security context under which SQL runs does not have permission to connect to the domain controller
2) you need to change SQL server service to use an account which does have permission to the domain controller
3) once option 2 is applied, change security back to "Be made using login's current security context"
Best of luck!
Hope you resolve the issue.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply