linked server error - "Invalid connection string attribute"??

Question

linked server error - "Invalid connection string attribute"??

Vivien Xing

SSChampion

Points: 12297
More actions
August 12, 2008 at 3:08 pm

#189317

I am following this link to create a linked server from SQL2005 (64-bit/sp2) to SQL2000 (32 bit/sp4)
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
and get this error when I tested on sql2005 server/box using "select * from [mysql2000svr].master.dbo.sysdatabases"
OLE DB provider "SQLNCLI" for linked server "mysql2000svr" returned message "Invalid connection string attribute".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "mysql2000svr".
The script for creating the linked server is:
EXEC sp_addlinkedserver
@server= 'mysql2000svr',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='mysql2000svr',--the data source
@provstr="Integrated Security=SSPI; "
Anything wrong? or what the problem here?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

narcosleepy@live.com SSC Enthusiast Points: 196 More actions · Answer 1

I am having the same issue. What is crazy, is that the linked server WAS working fine. Spent a few days on the phone with Microsoft a couple months ago getting the double-hop authentication to work (client -> sql server -> linked sql server). Originally, the primary issue was a DNS issue where the servers were not showing the same FQN depending on where you pinged it from.

It's been working for several weeks until yesterday, one of the developers noticed that queries to the linked servers (via the client) were bring back strange results. What is happening, is that the query is executing, but instead of executing on the linked server, it is bringing back local server results.

For example, if I have the following servers: CERT and PROD. On CERT, there is a database called TEST, but it does not exist on PROD. On the server "PROD", I have a linked server to "CERT" and execute the following query:

SELECT * FROM CERT.Test.dbo.SomeTable

I get the following error:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "CERT" does not contain the table ""TEST"."dbo"."SomeTable"". The table either does not exist or the current user does not have permissions on that table.

If we run a query against objects that do exist in CERT and PROD, we get data from PROD, not CERT. There is no telling how long this has been happening or WHY... This is probably going to take another call to Microsoft to get this cleared up if I can't find something in the newsgroups. I am going back through the documentation for fixing kerberos, but unfortunately, this requires coordination between me, and two other units within the company (one for dns/network and one for Active Directory administrators) to even troubleshoot. Around here, that is something similar to achieving world peace.....

Cheers!

Brandon

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Apply instacat.sql on your SQL2000 server. This can be found in your SP4 install.

Here is a KB article referencing the issue: http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

Adam Bean (8/21/2008)
Apply instacat.sql on your SQL2000 server. This can be found in your SP4 install.
Here is a KB article referencing the issue: http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I did already. Without this fix, I can not even get this error.

Our system admin admitted that SPN for the remote server is not registered yet and will do soon. He thought he did but not. Hope this can clear the problem.