February 3, 2012 at 4:01 pm
I'm trying to add a linked server using sp_addlinkedserver. The remote server name is an IP address. I am able to connect to both servers (local and remote) in SSMS. I am sysadmin on local and have dbo rights to specific databases on the remote server.
I work in an environment such that if I asked this question, it would precipitate a bunch of meetings about whether it should be allowed. Frankly, I am already allowed to connect to the remote server. If I have the ability to do it, I intend to. So I'm not asking any admins. Locally, I am the admin. Remotely... that's where the meetings start.
The error specifies the Named Pipes provider. I strongly suspect that the Named Pipes protocol is not enabled on the remote server. Is Named Pipes required for linked servers? Isn't TCP/IP sufficient? I can't find this answer.
SSMS says the connection test failed. When I execute the script, the sp_addlinkedserver and sp_addlinkedsrvlogin procedures succeed, but a select statement against the linked server fails.
The message is below. I've replaced the actual IP address with "0.0.0.0".
OLE DB provider "SQLNCLI10" for linked server "0.0.0.0" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "0.0.0.0" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
February 6, 2012 at 10:42 am
Is the remote server a named instance? If so, specify the port number on which the named instance is configure and follow the Linked server config process
February 8, 2012 at 10:47 am
I'm not entirely sure if the remote server is a named instance. There are three servers on one IP address. One of them is using the default port (1433) and the others use different port numbers. Maybe I should specify the port number. I suppose it must be a named instance to have three servers on one machine.
February 8, 2012 at 12:15 pm
I've got this working, mostly. I used this script: (IP address replaced with zeros)
exec sp_addlinkedserver @server = 'LINK1',
@srvproduct = '',
@provider= 'SQLNCLI',
@provstr = 'Library=DMBSSOCN;ServerName=000.000.000.000,1433'
Now I have the challenge of linking to the other servers, which use different port numbers on the same IP. I tried this:
exec sp_addlinkedserver @server = 'LINK2',
@srvproduct = '',
@provider= 'SQLNCLI',
@provstr = 'Library=DMBSSOCN;ServerName=000.000.000.000,1432'
It appears to work, but it points to the server at LINK1, not LINK2. It seems to be ignoring the port number.
February 8, 2012 at 12:38 pm
No, no, no, it doesn't work at all.
The server I'm using is a named instance. The "successful link" was actually referrring to the default instance on the same box. It appeared to work becauase it's an old copy of the server I was attempting to link to.
Apparently, the linked server is ignoring the IP address that I specified. Time to start over.
I have no idea how to do this. I've been searching the web, and most of the information is inapplicable, outdated or wrong.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply