SQL Replication with Port Numbers
Recently I attempted to setup a replication service with SQL server 2008, bound to an alternative port. The publication was setup fine, however when attempting to subscribe to the publication from a remote network, I was coming up with the following error.
Cannot connect to Server_Name,xxxx
Sql Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘Server_Name‘. (Replication.Utilites)
To begin with I thought it was because I was entering the IP address/domain of the remote server; Publications weirdly require the connecting hostname to be the name of the servers computer name, what I can only assume is to tighten security maybe. I initially resolved this by adding the ip and computer name to the ‘hosts’ file located ‘%WINDIR%\system32\drivers\etc\hosts’, however I later found that the error was still occurring.
I looked at the error more closely, and saw that it was showing the server name used in the connection, and also what the server name should be at the end of the message, the difference between these two was the port number. Removing the port number and setting the publication server to the default binding demonstrated that this was indeed the problem, that you cannot establish a subscription by including a connection to a port via appending ‘,1234′ to the hostname.
Solution
Initially this seems to be a rather evil restriction in replication, preventing you to subscribe to publications bound to a non-default port number, however SQL Server does enable aliases to be set. To do this follow the following instructions:
- Load up ‘Sql Server Configuration Manager’
- Expand ‘SQL Native Client 10.0 Configuration’ (or whichever SQL version you have), and select ‘Aliases’
- Right click and select ‘New Alias…’
- Under ‘Alias Name’ enter the computer name of the server where the publication is hosted
- Fill in the ‘Port No’ and ‘Server’ with the ip address or domain name
This enables you to simply type the alias within the connection details emitting the need to supply a port number.
