[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
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.
More Related questions