Linked Server to SQL 2000

Question

Post reply

Linked Server to SQL 2000

russds

Mr or Mrs. 500

Points: 509
More actions
October 24, 2012 at 3:54 pm

#283554

Hello, sorry, newbie with question. I'm pulling my hair out and hoping someone here smarter than me can point me in the right direction.
I'm setting up a linked server in SQL Server 2008 R2 to a server with SQL 2000 on it. Everything goes smooth, I'm using MSQLSMS -> Server Objects -> Linked Servers right click, New linked server. i add the server, and the local login mapped to remote login. All works fine, but when i try to run the stored procedure I get error message:
OLE DB provider "SQLNCLI10" for linked server "SQL2000SERVER.COM" returned message "Client unable to establish connection".
Msg 20, Level 16, State 1, Line 0
Encryption not supported on SQL Server.
And i'm just stuck with that, I've tried various provider types, and what not, but nothing pans out. I've googled, and it looks like it could be a 64bit machine to a 32bit machine connection issue. Any direction or possible threads in this forum would help. I'm still searching threads around here.
Thanks!
-Russ

Viewing 7 posts - 1 through 6 (of 6 total)

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

sanket kokane Hall of Fame Points: 3465 More actions · Answer 1

did you check RPC and RPC out options in linked server properties ?

Make it true and Try

-----------------------------------------------------------------------------
संकेत कोकणे

russds Mr or Mrs. 500 Points: 509 More actions · Answer 2

yes, i did check those as true.

I believe the problem to be around the fact that the 2008 machine is 64 bit. I have setup the linked server on a 32 bit machine and that works fine.

Is anyone aware of issues with linking a 32bit machine with a 64bit machine?

Thanks!

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

have a look at this http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx. not exactly the same error but same symptom.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

russds Mr or Mrs. 500 Points: 509 More actions · Answer 4

Thanks Bob. I had seen that before, different error message and plus I don't have access to the SQL 2000 machine, so I didn't fully go down that path. However i did find a solution for those interested:

This is what I did:

1. Create ODBC connection to SOMESERVER.COM, call it dsnSOME

2. Create a Linked Server to dsnSOM:

- Linked server: "SOMESERVER" (I don't think it matters)

- Provider: Microsoft OLE DB Provider for ODBC Drivers

- Product Name: dsnSOME

- Data source: dsnSOME

And that's it. Now when I call the stored procedure I call:

[SOMESERVER].database.owner.storedprocedure

And it works!

It seems that for whatever reason, when trying to run a stored procedure on SQL 64 bit to a SQL 32 bit linked server directly, it gives this weird message about Encryption. However going through the ODBC and it works great.

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

check this out although i think it's a moot point because it would require you to change settings on the 2000 box

http://support.microsoft.com/default.aspx?kbid=318605

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

russds Mr or Mrs. 500 Points: 509 More actions · Answer 6

Thanks Bob. Yeah I had found that article too during my research. As you said, trouble is i don't have access to the SQL 2000 box. I'm not sure why it was giving me the Encryption error message, but oh well, I got it working using odbc connection. Thanks for your help!