Subscription Wizard Fail to Connect Remote Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Subscription Wizard Fail to Connect Remote Server

Author

Topic

hthikey
Starting Member

4 Posts

Posted - 2009-07-27 : 08:27:03

I am trying to create a new replication subscription on a remote server which is hosted at an ISP
site from a database in our corporate network using the New Subscription Wizard.

Both are SQL Server 2005 Standard Edition databases and running on Windows 2003 servers. The
remote connection and port 1433 are enabled and I can connect and work from MS Server

management studio happily. Plus can connect and work on the remote database using ODBC
connection.

But the Subscription Wizard is not letting me connect to the remote database. When I try adding
using the Add SQL Server Subscriber button, provide the server/instance name and login (SQL
Server Authentication) details the following error message is displayed on a popup window:

<-----------------

Cannot connect to mydbname\mssqlserver01 (Note: this is my remote database and instance
name)

Additional Information:

Failed to connect to server mydbname\mssqlserver01 (Microsoft.SQLServer.ConnectionInfo)
A network-related or instance specific error occured while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider:SQL NEtwork Interfaces, error: 26
- Error Locating Server/Instance Specified) ( Microsoft SQL Server)

-------->

Can you help?

[P.S.: Please note that I have also setup a subscription to another remote database using the

same method without any problem. The diffrerence in this is that the databases are created in the

default instance.]

hthikey
Starting Member

4 Posts

Posted - 2009-07-28 : 13:18:50

Found the solution:

Go to SQL Server Configuration Manager of your client computer (In my case one in the corporate network)

Select SQL Native Client Configuration

Select Aliases

Add a new alias:

Alias Name: mydbname\mssqlserver01 (this is remote server and instance name)
Port Number: 1433 (which is default port)
ProtocolL TCP/IP
Alias Server: IP address of the server

Save it. There is no need to restart any thing.

Try now creating subscription and add mydbname\mssqlserver01 as server name. Magic.

BrianBurgit
Starting Member

12 Posts

Posted - 2010-01-29 : 15:17:20

I have basically the same problem, have a Publisher on a remote server. Both servers are W2003/SQL2005. I connect to the remote server using Remote Desktop Coonection. From the remote server, I can open SQL Server and connect to my SQL Server instance on my local server by using the IP address, I can see all of the Tables, SPs, etc.

Now trying to push a Subscription from the Publisher on the remote server. I created the alias as you described. As a walk thru the Subscription wizard, I am asked for the subscribing server. I choose Add New SQL Server Subscriber and enter my alias. I also choose use SQL Server Authentication. When I click connect, I get the same error as described in your post.

Any other ideas?

Thanks,

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources