[ACCEPTED]-Having trouble adding a linked SQL server-linked-server

To solve the issue above I amended the Linked 4 Server definition to specify TCP specifically:

EXEC sp_addlinkedserver   
    @server='TEST_LINK', 
    @srvproduct='',
    @provider='SQLNCLI', 
    @datasrc='tcp:0.0.0.0'

EXEC sp_addlinkedsrvlogin
    @useself='FALSE',
    @rmtsrvname='TEST_LINK',
    @rmtuser='user',
    @rmtpassword='secret'

This 3 worked for me. This method also allowed 2 me to specify a non-standard port for the 1 remote server:

EXEC sp_addlinkedserver   
    @server='TEST_LINK', 
    @srvproduct='',
    @provider='SQLNCLI', 
    @datasrc='tcp:0.0.0.0,1111'

I hope this helps

It does work with sql authentication. In 10 fact its easier, as you don't have to set 9 up Kerberos if you use SQL authentication.

One 8 thing is confusing about your error messages, your 7 address is an IP address : 123.45.678.90

your 6 error message is talking about Named Pipes. Could 5 this be a clue?

You don't have to reference 4 the server in TSQL by it's IP address. If 3 you add an alias on the local server, pointing to the remote server, you 2 can give a more sensible name. Refering 1 to it by IP address is obviously unsatisfactory.

Figured it out, thanks to a line of instructions 7 in the SMS GUI wizard for adding a linked 6 servers: "If SQL Server is selected, then the Linked Server name is also the network name of the server."

I thought the name of the linked 5 server was just an arbitrary alias.

This 4 works like a charm - it's a shame that I 3 have to type the IP (in brackets) every 2 time I want to use this linked server, but 1 there you have it.

Exec sp_dropserver '0.0.0.0', 'droplogins'
go

EXEC sp_addlinkedserver   
    @server='0.0.0.0', 
    @srvproduct='',
    @provider='SQLNCLI', 
    @datasrc='0.0.0.0'

EXEC sp_addlinkedsrvlogin
    @useself='FALSE',
    @rmtsrvname='0.0.0.0',
    @rmtuser='user',
    @rmtpassword='secret'
go

Select Top 10 * from [0.0.0.0].DatabaseName.dbo.TableName

If it's failing on Named Pipes when you're 3 specifying an IP address, try prefixing 2 the IP address with "tcp:" and see if it 1 helps set it on the right path.

sp_configure 'Ad Hoc Distributed Queries',1 13

next

reconfigure with override

have checked 12 the SQL Surface area configuraton-->Databaseengine-->Remoteconnections-->Loacal 11 and remoteconnections-->shouild be Using 10 both TCP/IP and named pipes option ticked (if 9 not choose that option and restart)

this 8 has to resolve the problem OLE DB provider 7 "SQLNCLI" for linked server "Remote" returned 6 message "An error has occurred while establishing 5 a connection to the server. When connecting 4 to SQL Server 2005, this failure may be 3 caused by the fact that under the default 2 settings SQL Server does not allow remote 1 connections.".

Sometimes the protocols have been enabled 8 and the surface area configuration allows 7 remote connections, but the SQL Server Browser 6 service hasn't been restarted since the 5 configuration was set. This means that the 4 configuration is not active.

Try restarting 3 SQL Server Browser as a troubleshooting 2 step if your configuration settings all 1 appear to be correct.

Mike