Having trouble adding a linked SQL server

Question

I'm trying to pull in data from a remote SQL Server. I can access the remote server using SQL authentication; I haven't had any luck using the same credentials with sp_addlinkedserver.

I'm trying something like this:

Exec sp_dropserver 'Remote', 'droplogins'
go

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

EXEC sp_addlinkedsrvlogin
    @useself='FALSE',
    @rmtsrvname='Remote',
    @rmtuser='User',
    @rmtpassword='Secret'

Select Top 10 * from Remote.DatabaseName.dbo.TableName

Here's what I get:

OLE DB provider "SQLNCLI" for linked server "Remote" returned message 
"Login timeout expired".

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

Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53]. 

Again, I can access the server directly (in SQL Management Studio) using these exact credentials, so it's not a problem with my network or credentials.

Most of the examples I've seen online seem to involve Windows domain accounts, as opposed to SQL security logins. Does this not work with SQL authentication?

Answer 1

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

Answer 2

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

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

Mike