Can't Link 2008 Server to 2005 Server, But the Other Way Works!

Question

Post reply

Can't Link 2008 Server to 2005 Server, But the Other Way Works!

yoffes

SSC-Addicted

Points: 497
More actions
June 16, 2011 at 7:03 am

#237921

Hello,
My problem:
I am trying to link a SQL SERVER 2005 box to a SQL SERVER 2008 box and I can only get the link to work one way - from 2005 to 2008.
My environment:
- 2005 SQL Server, call it SERVER_2005
- 2008 SQL Server, call it SERVER_2008
- SQL Server Management Studio
I have admin rights on the 2008 server and am working with an admin on the 2005 server.
The 2005 administrator created the TEST_USER user (server AND particular database logins) on SERVER_2005.
I did the same on the 2008 server.
The 2005 administrator created a link to SERVER_2008.
We were successfully able to query from SERVER_2005 to SERVER_2008:
select top 10 * from [SERVER_2008].[Test2008Database].[dbo].[Test2008Table]
I ran these two commands on SERVER_2008 to set up the link to SERVER_2005.
sp_addlinkedserver
@server='SERVER_2005',
@srvproduct='Any',
@provider='SQLNCLI10',
@datasrc='SERVER_2005',
@provstr='Data Source=SERVER_2005;Initial Catalog=Test2005Database;User Id=TEST_USER;Password=xxx;'
sp_addlinkedsrvlogin @rmtsrvname='SERVER_2005', @locallogin='TEST_USER', @rmtuser='TEST_USER', @rmtpassword='xxx'
When I try this:
select top 10 * from [SERVER_2005].[Test2005Database].[dbo].[Test2005Table]
I get this error:
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "SERVER_2005".
But this works:
select top 10 * from OPENQUERY(SERVER_2005, 'SELECT * from [Test2005Database].[dbo].[Test2005Table]')
Does anyone know of a step-by-step process to get this working?
It doesn't seem like a Windows/networking issue because OPENQUERY works. Right?
Thank you kindly,
Seth
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

ravikanth. Hall of Fame Points: 3472 More actions · Answer 1

Have you tried changing the provider to "Microsoft OLE DB Provider for SQL Server"

yoffes SSC-Addicted Points: 497 More actions · Answer 2

Yes, I did. I still get the error message

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "SERVER_2005".

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

How about:

EXEC master.dbo.sp_addlinkedserver @server = N'SERVER_2005', @srvproduct=N'SQL Server'

CEWII

yoffes SSC-Addicted Points: 497 More actions · Answer 4

Tried it - same error:

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "SERVER_2005".

This still works after:

select top 10 * from OPENQUERY(SERVER_2005, 'SELECT * from [Test2005Database].[dbo].[Test2005Table]')

I log in to both servers (SERVER_2005 and SERVER_2008) using Windows authentication. I have admin rights on the 2008 box, and I own

Test2005Database on the 2005 box.

I have also tried creating a specific user (same name and password) on both boxes.

I'm at a loss. Anyone have an idea?

Thanks.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

It might be worth checking permissions by running this in the Test2005Database:

EXECUTE AS user = 'TEST_USER';

SELECT * FROM fn_my_permissions('dbo.Test2005Table', 'OBJECT')

REVERT;

Just to see exactly what the user can do.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

yoffes SSC-Addicted Points: 497 More actions · Answer 6

entity_name permission_name

dbo.Test2005Table SELECT

dbo.Test2005Table UPDATE

dbo.Test2005Table REFERENCES

dbo.Test2005Table INSERT

dbo.Test2005Table DELETE

dbo.Test2005Table VIEW DEFINITION

dbo.Test2005Table ALTER

dbo.Test2005Table TAKE OWNERSHIP

dbo.Test2005Table CONTROL

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

well that looks ok, can you do the same for the database permissions?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Just out of interest, what happens if you try to expand the Linked Server Catalogs in SSMS Object Explorer?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

yoffes SSC-Addicted Points: 497 More actions · Answer 9

>Just out of interest, what happens if you try to expand the Linked Server Catalogs in SSMS Object Explorer?

I can see all of the tables in the schema.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

also out of mild interest, can you query ok from the command line on the 2008 server to the 2005 server using that user?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

yoffes SSC-Addicted Points: 497 More actions · Answer 11

I have the same problem trying to run a remote query from 2005 to a 2000 server:

select * from SERVER_2000.some_db.dbo.table_2000

Msg 7314, Level 16, State 1, Line 1

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

Is there some backward compatibility software I need to install?