Problem Creating Linked Server from SQL 2000 to SQL 2005

  • woogoob

    SSC-Addicted

    Points: 481

    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

    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

    SSC-Addicted

    Points: 481

    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

    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?

  • SSMfana

    SSC Eights!

    Points: 877

    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

    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

    SSC-Addicted

    Points: 481

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

  • woogoob

    SSC-Addicted

    Points: 481

    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

    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

    SSC-Addicted

    Points: 481

    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

    SSC-Addicted

    Points: 481

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

  • StaticFix

    SSC Veteran

    Points: 225

    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

    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

    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

    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!

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

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