Active Directory Linked Server Fails After Upgrade to SQL Server 2

We had an SQL Server 2000 instance running on Windows 2003. This was
querying a linked AD server running on Window 2000.
The query we run is as follows:
SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
sAMAccountName, givenName, telephoneNumber,
facsimileTelephoneNumber,
sn, mail,department, physicalDeliveryOfficeName FROM
''LDAP://DC=mycompany,DC=com''
where sn<''k''
and objectClass = ''User''')
The SQL Server instance was upgraded to SQL Server 2005. No other changes
were made.
We now get an error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADSDSOObject" for linked server "python" reported an
error. The provider did not give any information about the error.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "ADSDSOObject" for
linked server "python".
Any suggestions?
--
Jonathan PalmerJonathan Palmer wrote:
> We had an SQL Server 2000 instance running on Windows 2003. This was
> querying a linked AD server running on Window 2000.
> The query we run is as follows:
> SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
> sAMAccountName, givenName, telephoneNumber,
> facsimileTelephoneNumber,
> sn, mail,department, physicalDeliveryOfficeName FROM
> ''LDAP://DC=mycompany,DC=com''
> where sn<''k''
> and objectClass = ''User''')
> The SQL Server instance was upgraded to SQL Server 2005. No other changes
> were made.
> We now get an error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "ADSDSOObject" for linked server "python" reported an
> error. The provider did not give any information about the error.
> Msg 7350, Level 16, State 2, Line 1
> Cannot get the column information from OLE DB provider "ADSDSOObject" for
> linked server "python".
> Any suggestions?
>
Hi
I've just tried you statement on a SQL2005 server, and it works fine for
me. The only thing I've changed, is the part where you define your LDAP
server. Instead of ...''LDAP:/DC=nycompany, DC=com''... i've changed
it to ...''LDAP://DC-1''... where DC-1 is the name of my domain
controller. I don't know if that will solve your problem because I don't
think this should be any different from SQL2000 to SQL2005. Only
specifying the domain controller name has worked for me in SQL2000 as
well... actually I've never got it working with anything else...:-).
--
Regards
Steen Schlüter Persson
DBA|||The error message is the same if I use the FQDN for the server, the server
name alone or DC=.. etc approach.
If I type in the wrong FQDN, I do not get the error message about "cannot
get the column information"|||Jonathan Palmer wrote:
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"
That's a bit strange. Have you tried to remove the linked server and
recreate it?
What happens if you use OPENROWSET like in my example below -Does that
make any difference?
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
Servername Flag=0x11;Page Size=10000'
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://YourDomainCOntrollerName''
WHERE objectClass = ''organizationalPerson''
'
)
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||try recreate the linked server and rerun sp_addlinkedsrvlogin. SIDs might be
wrong after upgrade.
--
-oj
"Jonathan Palmer" <nospam@.nospamnospam.nospamnospam> wrote in message
news:92564888-4415-4C84-8475-5DA44BFE655C@.microsoft.com...
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"|||If I use OpenRowSet, I do get results, e.g.
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,
''
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://MYSERVER''
WHERE objectClass = ''organizationalPerson''
')
I tried recreating the linked server and running the query:
SELECT * FROM OpenQuery(ADSI, 'SELECT mail
,SamAccountName FROM
''LDAP://myserver'
where
objectClass = ''User''')
This query returns no results if it is made without a security context and
rows if I enter a username and password. How can the first query run without
any loggin details and the second only run with loggin details?
--
Jonathan Palmer
"Steen Persson (DK)" wrote:
> That's a bit strange. Have you tried to remove the linked server and
> recreate it?
> What happens if you use OPENROWSET like in my example below -Does that
> make any difference?
>
> SELECT *
> FROM OPENROWSET(
> 'AdsDsoObject'
> ,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
> Servername Flag=0x11;Page Size=10000'
> ,'SELECT mail
> ,ExtensionAttribute3
> ,SamAccountName
> FROM ''LDAP://YourDomainCOntrollerName''
> WHERE objectClass = ''organizationalPerson''
> '
> )
>
> --
> Regards
> Steen Schlüter Persson
> Databaseadministrator / Systemadministrator
>