Connecting to the Active Directory with a Linked Server

[Ian Baldwin]

I am trying, without success, to obtain information from
the Active Directory using a linked server. I've trawled
the internet and found abundant information (including
others with a similar problem) but as yet no solution.

SQL server 2000 SP3 is running on a member server (not a
domain server) in a domain named bs.brookes.ac.uk. As per
the various directions I've created a linked server using
the command:

sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'

I've then tried a number of variations on the theme of:

SELECT * FROM OpenQuery( ADSI, 'SELECT name
FROM "LDAP://dc=bs,dc=brookes,dc=ac,dc=uk" WHERE
objectCategory = "Person" AND objectClass= "user"')

Everything I try results in the error:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution
against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].

Any suggestions on how to correct this or track the
problem down will be very warmly received!

Ian

[Rebecca L. Casselman [MSFT]]

Hi Ian,

As I understand your issue, you have receiving an error trying to execute a
query against a Linked Server. Please try the following:

- Check the following registry key and make sure it is set to 1:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ADSDSOObject

Changing this key to 1 sets the driver back to running in SQL Server's
process space. If this registry key was not set to 1, you will need to sun
sp_recompile against all views that reference the Linked Server to remove
their execution plans from cache.

- If you try this from another development machine, do you receive the same
error?

- Will this query execute successfully in Query Analyzer?

- How did you create the Linked Server? Did you execute sp_addlinkedserver
and sp_addlinkedserverlogin? If not, execute sp_addlinkedserverlogin
'ADSI', false

- Are you starting the SQL Server with a domain account? Is it the same
domain as ADSI?

- Can you execute sp_tables and sp_tables_ex successfully?

Best Regards,

Rebecca Casselman MCDBA, MCSA, MCSE NT4/2000
Microsoft Online Support Engineer

Get Secure! - www.microsoft.com/security

=====================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

[Ian Baldwin]

Dear Rebecca

Many thanks for your response. Still not getting much joy
out of it but you may have helped me get one step further
on (see later). Answers to your questions are below.

Post by Rebecca L. Casselman [MSFT]
- Check the following registry key and make sure it is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Provider

s\ADSDSOObject
The only value key in Providers is AllowInProgress which
is set to 1. There is an ADSDSOObject container which also
contains a value key AllowInProgress which is also set to
1. The default key in the container has no value.

Post by Rebecca L. Casselman [MSFT]
- If you try this from another development machine, do

you receive the same

Post by Rebecca L. Casselman [MSFT]
error?

Yes, I've also tried it directly on the server.

Post by Rebecca L. Casselman [MSFT]
- Will this query execute successfully in Query Analyzer?

No - I'm only using the query analyser to test this at
present.

Post by Rebecca L. Casselman [MSFT]
- How did you create the Linked Server? Did you execute

sp_addlinkedserver

Post by Rebecca L. Casselman [MSFT]
and sp_addlinkedserverlogin? If not, execute

sp_addlinkedserverlogin

Post by Rebecca L. Casselman [MSFT]
'ADSI', false

I have now (sp_addlinkedsvrlogin). Makes no difference.

Post by Rebecca L. Casselman [MSFT]
- Are you starting the SQL Server with a domain account?

Yes (a Domain Administrator account)

Post by Rebecca L. Casselman [MSFT]
Is it the same domain as ADSI?

Yes. I have now also purchased and installed a second copy
of SQL server which is running on one of the domain
servers and the result is the same.

Post by Rebecca L. Casselman [MSFT]
- Can you execute sp_tables and sp_tables_ex successfully?

sp_tables - yes
sp_tables_ex 'ADSI' - no... error return is:

Server: Msg 7301, Level 16, State 2, Procedure
sp_tables_ex, Line 20
Could not obtain a required interface from OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002:
IDBSchemaRowset].

I'm now investigating this error but if it throws any
light on things for you any help is very welcome (I'll add
a posting if I find the answer myself)

Many thanks for your help.

Ian

[a***@discussions.microsoft.com]

Dear Rebecca

I've drawn a bit of a blank on the error message:

Server: Msg 7301, Level 16, State 2, Procedure
sp_tables_ex, Line 20
Could not obtain a required interface from OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002:
IDBSchemaRowset].

All I've found that remotely deals with it is a Microsoft
Knowledge Base Article (248156) referring to SQL server 7
and a different query interface.

However, I get the same response if I try to look at the
tables for the ADSI remote server from the Enterprise
Manager. Should you, in theory, be able to do this on a
remote server when it's connected to the AD?

I'm unsure now as to whether the problem is associated
with the OLE/DB provider as this message suggests (in
which case why doesn't everyone have the problem) or
whether I have a problem with my AD.

Thanks again

Ian

[Kris]

I am having this issue as well. Was a solution ever discovered?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Beginner]

Amazing!!! Thanks.

I'm trying to change the page size so that it can return more than 1,000
rows.

Any thoughts on that? Hope you're still following the thread.

Post by Rebecca L. Casselman [MSFT]
Hi Ian,
As I understand your issue, you have receiving an error trying to execute a
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ADSDSOObject
Changing this key to 1 sets the driver back to running in SQL Server's
process space. If this registry key was not set to 1, you will need to sun
sp_recompile against all views that reference the Linked Server to remove
their execution plans from cache.
- If you try this from another development machine, do you receive the same
error?
- Will this query execute successfully in Query Analyzer?
- How did you create the Linked Server? Did you execute

sp_addlinkedserver

Post by Rebecca L. Casselman [MSFT]
and sp_addlinkedserverlogin? If not, execute sp_addlinkedserverlogin
'ADSI', false
- Are you starting the SQL Server with a domain account? Is it the same
domain as ADSI?
- Can you execute sp_tables and sp_tables_ex successfully?
Best Regards,
Rebecca Casselman MCDBA, MCSA, MCSE NT4/2000
Microsoft Online Support Engineer
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[cbeatley]

I am getting the exact same erro that Ian is getting. There must be
somethign that I'm missing about constructing the linked server. I've
tried it in the EM security directory, and with Query Analyzer. I
always get the sma error.

My code is here

select * from openquery(ADSI, 'SELECT * FROM
"LDAP://gsb/dc=gsb,dc=uchicago,dc=edu"')

go


this is the full set of queries:

exec sp_addlinkedserver 'ADSITest', 'My Active DirectoryTest',
'ADSDSOObject',
'adsdatasource'
go

exec sp_addlinkedsrvlogin 'ADSITest', false, 'domain\accountName',
'remote_account name', 'password'
go



--
cbeatley
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message31877.html

[Kris]

I am having this same issue. Were you able to find a solution?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Francis Gauthier]

A solution that worked for me :

SQL server group => Security => Linked server => ADSI (the name of your linked server)

right click on it then go in the "security menu" then choose the option "be made using this security context"

Then you enter your server admin account and password for the remote server logi

From http://www.developmentnow.com/g/100_2003_10_0_0_389094/Connecting-to-the-Active-Directory-with-a-Linked-Server.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com