Linked Server to SQL 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Linked Server to SQL 2000

Linked Server to SQL 2000

Rate Topic

Display Mode

Topic Options

Author

Message

russds

Posted Wednesday, October 24, 2012 3:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 2:02 PM
Points: 7, Visits: 26

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

Post #1376701

sanket kokane

Posted Thursday, October 25, 2012 3:54 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:04 AM
Points: 323, Visits: 966

did you check RPC and RPC out options in linked server properties ?
Make it true and Try

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

Post #1376875

russds

Posted Thursday, October 25, 2012 9:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 2:02 PM
Points: 7, Visits: 26

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!

Post #1377061

Robert klimes

Posted Thursday, October 25, 2012 11:07 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 890, Visits: 1,863

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
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1377144

russds

Posted Thursday, October 25, 2012 11:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 2:02 PM
Points: 7, Visits: 26

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.

Post #1377151

Robert klimes

Posted Thursday, October 25, 2012 11:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 890, Visits: 1,863

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
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1377157

russds

Posted Thursday, October 25, 2012 11:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 2:02 PM
Points: 7, Visits: 26

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!

Post #1377164

« Prev Topic | Next Topic »

Permissions