Problem Creating Linked Server from SQL 2000 to SQL 2005

Question

Problem Creating Linked Server from SQL 2000 to SQL 2005

Viewing 15 posts - 16 through 30 (of 31 total)

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

woogoob Mr or Mrs. 500 Points: 511 More actions · Answer 1

I set up another SQL Server 2005 box identical software version as my other box and I'm able to create a link server from my SQL Server 2000 to this new one!

The only difference is that in the SQL Server Configuration Manager, this box is "log on as" Built-in Account: Local System.

The box that I'm unsuccessful in created a linked server to is "log on as" to a domain user account with admin privelages.

I'm not sure if this is causing me the issue. Any ideas?

SSMfana SSC Eights! Points: 877 More actions · Answer 2

Yes you are right. Your enviroment is exactly the same as my one and I just did what you did. Guess what its work.

I'm still wondering why not on the server with network/domain.

woogoob Mr or Mrs. 500 Points: 511 More actions · Answer 3

I just tried changing the box that didn't work to log in with the built in system account.

It didn't fix the problem for me, but at least it's working for you. I'm glad it's works on my other server:)

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 4

If it returns a blank screen, that is a good thing 🙂 Ruling out any FW issues. Can you reproduce this from the server that is hosting the linked server connection?

Tommy

Follow @sqlscribe

SSMfana SSC Eights! Points: 877 More actions · Answer 5

Hi all

Well I've just found something strange with Linking server sqlserver2000 and sqlserver2005. I test with sql2000 and sql2005 (Standard edition 32bit) using Local System account and its worked fine. I then try its with sql2000 and sql2005 (Standard edition 64bit) using Local System account IT DOESN'T WORK. Does anyone know what could be the reason?

Samuel Ignatius-355834 SSC Enthusiast Points: 107 More actions · Answer 6

run instcat.sql in SQL 2000 server...

http://support.microsoft.com/kb/906954

the issue is pretty much documented in MS, while creating a linked server from a 32 bit SQL server to a 64 bit SQL2005 instance you would need to run the instcat.sql

woogoob Mr or Mrs. 500 Points: 511 More actions · Answer 7

That article is for issues creating linked servers from 2005 to 2000, not 2000 to 2005.

woogoob Mr or Mrs. 500 Points: 511 More actions · Answer 8

SSMfana,

Are both your SQL 2005 boxes on a domain? What OS are they running?

The 2005 box that I'm successfully able to link to happens to be a server that is not part of the domain. It's part of a workgroup. I'll be adding it to the domain at a later point. THe OS is Windows Server 2003.

The other 2005 box is just a workstation running Windows XP Professional and it is on the domain.

This one I have been unsuccessful in creating a linked server to from by SQL 2000 box.

SSMfana SSC Eights! Points: 877 More actions · Answer 9

greyswoo

Initially I was setting up a workstation running sql2005 and

server Windows server 2003 running sql2000 that didn't work sql2005 (using Network account).

2nd Test server sql2005(local system account) Standard edition 32bit, windows server 2003 and sql2000 this one did work perfectly fine.

3rd one Live server sql2005(local system account) Standard edition 64bit,windows server 2003 and sql2000 didn't work access was denied when you querying sql 2005 from 2000 but if you query sql2000 from 2005 its fine.

woogoob Mr or Mrs. 500 Points: 511 More actions · Answer 10

SSMfana,

I turned off the firewall on my Windows XP box running SQL Server 2005 and I'll now able to create a linked server to it from SQL 2000 no matter the log on method (local or domain).

See if this is the same for you. We probably need to open up a port.

woogoob Mr or Mrs. 500 Points: 511 More actions · Answer 11

Opening up the default ports 1433 or 1434 works for me.

StaticFix SSC Veteran Points: 225 More actions · Answer 12

Hello,

Looking at the threads, issue seems to be that you have not enabled the remote connections on the SQL 2005, to do this

1. Go to Surface area COnfiguration from Start - Programs - Microsoft SQL 2005 - Configuration Tools - SQL Server Surface Area Configuration

2. Select Surface area configuration for services and connections

3. Select Remote Connections from Database Engine dropdown list and enable 'Local and Remote Connection'

This should work as i see you are unable to telnet to port 1433 to your SQL 2005.

Let me know if this helps

-Cheers

J

zavhrye SSC Veteran Points: 216 More actions · Answer 13

Guy,

From 2000 to 2005 try using the ip address or adding the server name to the host file of the 2000 sql server. The domain restrictions are causing the access denied error.

abhijit jadhav Valued Member Points: 53 More actions · Answer 14

Though its old thread... We had same issue with Linked server created on SQL 2K5 to SQL 2K and we resolved by adding service a/c of SQL 2K5 as local admin on SQL 2K server. We were runing xp_cmdshell from SQL 2K5 to SQL 2K server through Linked Server and failing on "access denied".

Regards,

Abhijit

SevereIdea SSC Enthusiast Points: 146 More actions · Answer 15

I too am having a problem running a query from SQL 2000 Standard SP4 (32bit) against SQL 2005 Standard SP3 (64bit). I am failing on login for 'NT AUTHORITY\ANONYMOUS LOGON'. The Linked Server seemd to create ok, and after running INSTCAT.SQL on the 2000 instance, I can query from 2005 to 2000 without a problem, but going the other way is a different story. I have made sure that I have Remote Connections enabled for both servers and my login credentials should be the same as I am the DBA. Also, I have SQL Native Client installed on my 2000 box.

We are in the process of upgrading the first database to 2005 from 2000 and I have a few remaining DBs that use linked servers to view data from the upgraded DB...

Thanks in Advance for any help!