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

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.

Answer 2

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

Answer 3

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'
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

Answer 4

sp_configure 'Ad Hoc Distributed Queries',1

next

reconfigure with override

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

this has to resolve the problem 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.".

Answer 5

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 6

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