Sql-server – Can’t create linked server SQL Server 2005

linked-serversql server

I'm new to doing linked server and I have this error.

"The linked server has been created but failed a connection"

On the same virtual machine I have two SQL Server instances

  • one 2000 (VMDEVCIAQ\NMP)
  • one 2005 (VMDEVCIAQ\NMPUTIL).

My setup :

  • The 2005 try to modify tables in the 2000.
  • I enabled remote connection on both instance.
  • I tested manually connecting to the instance of 2000 and it worked.

What I input in the form

  • Linked server: NMP
  • Provider : Microsoft OLE DB Provider for SQL Server
  • ProductName: SQLServer
  • Data source : VMDEVCIAQ\NMP

In the security tab I set "Be made using this security context" with the tested login and password.

So any idea why this would fail ? Any pointer or doc would be appreciated.

Cheers

Best Answer

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

"

Related Question