Having trouble adding a linked SQL server

To solve the issue above I amended the Linked 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 worked for me. This method also allowed me to specify a non-standard port for the 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 fact its easier, as you don't have to set up Kerberos if you use SQL authentication.

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

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

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

Figured it out, thanks to a line of instructions in the SMS GUI wizard for adding a linked 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 server was just an arbitrary alias.

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

Exec sp_dropserver '0.0.0.0', 'droplogins'goEXEC 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'goSelect Top 10 * from [0.0.0.0].DatabaseName.dbo.TableName