Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server to SQL 2000 Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 3:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:26 AM
Points: 7, Visits: 59
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
Posted Thursday, October 25, 2012 3:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
did you check RPC and RPC out options in linked server properties ?
Make it true and Try


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1376875
Posted Thursday, October 25, 2012 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:26 AM
Points: 7, Visits: 59
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
Posted Thursday, October 25, 2012 11:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 1,427, Visits: 2,692
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
Post #1377144
Posted Thursday, October 25, 2012 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:26 AM
Points: 7, Visits: 59
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
Posted Thursday, October 25, 2012 11:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 1,427, Visits: 2,692
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
Post #1377157
Posted Thursday, October 25, 2012 11:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:26 AM
Points: 7, Visits: 59
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 »

Add to briefcase

Permissions Expand / Collapse