October 21, 2009 at 1:24 pm
Hi,
I get the following error:
OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "repl_distributor" 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].
When I try to configure a remote distributor on a dev server using my pc (sql server developer edition) as the distributor
--on distributor
use master
go
exec sp_get_distributor
exec sp_adddistributor @distributor = 'mynamepc', @password = 'strongpw'
exec sp_adddistributiondb 'Distribution'
exec sp_adddistpublisher @publisher ='myserver',@distribution_db = 'Distribution', @working_directory ='\\myserver\ReplData'
--on publisher
exec sp_adddistributor 'mynamepc', @password = 'strongpw'
My pc/sql server instance is configured to allow remote connections. And it's a linked server in sys.servers although the islink field is zero.
Any help would be grrreatly appreciated.
October 21, 2009 at 1:59 pm
The first thing you need to do is verify connectivity using both the TCP/IP and named pipes protocol is in place between both the host and the target. For the SQL Server, launch the Surface Area Configuration tool and check the "Remote Connections". Your connection is attempting to use named pipes so make sure that both TCP/IP and named pipes are configured. If they aren't, set it and restart SQL Server.
The second thing to check is the client side connectivity. Launch the SQL Server configuration manager and verify that both named pipes and TCP/IP are enabled.
You can also configure an ALIAS here to force the connection to use a specific protocol. Try creating an alias with the same name as the server but force it to use TCP/IP. I've had to do that in the past when the firewall is configured to allow TCP/IP connections to port 1433 but it blocks the named pipes connections which I think are on port 445.
The last thing to check is to open a command prompt on the client and try to telnet to the correct port on the server.
TELNET servername 1433 (or whatever port SQL is running under_
TELNET servername 445
Try this and post back the results.
October 21, 2009 at 2:44 pm
Both the 'client' and 'server' have named pipes enabled. So I went ahead and created an alias with my pc name on port 1433. However, when I check it, I can't telnet into port 1433 but I can with 445.
Thanks for the reply, George.
October 21, 2009 at 3:29 pm
Try adding an exception in your desktop firewall for port 1433, then try the TELNET 1433 again from the client. If that works, re-register the server and choose TCP/IP as the protocol on the connection properties of the New Server Registration dialog box.
October 23, 2009 at 8:16 am
Hi George, I can't get them to allow me to add exceptions to my firewall. So I'm going to do the testing through a virtual machine instead. Thanks for your help, much appreciated!
June 15, 2012 at 5:04 am
Execute on Publisher
exec sp_adddistributor 'DISTSVRNAME' --Server Name on which Distributor is configured
,@password = 'abc'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply