SQL Server Replication Publishers and non-standard SQL Server Ports

People use non-standard ports for the default instance of SQL Server. What I mean by this is a port number other than 1433. In you use a non-standard port for SQL Server and you plan on using a remote publisher for SQL Server Replication, then this post is for you.

When you go to setup replication and tell your new publisher where your distributor is you’ll get an error message back which is less then helpful, telling you that you can’t connect due to a password error.

This error message is lying to do. The problem is that replication doesn’t understand non-default ports all that well and you need to trick the replication setup process to accept that you are using non-default ports. In order to fix this, connect to the distributor in Object Explorer, right-click on Replication and click on Distributor Properties. When the Distributor properties window opens, click on Publishers on the left. Then click Add, to add a new publisher. (You should already have a new publisher listed with with server name and the port number.) You’ll want to add in another entry for just the server name.

When you add the server name version, the wizard will give you an error saying that it can’t connect to the server name, and it will ask if you want to keep the server entry. When it error comes up, click Yes. This will add the server name entry to the list, and allow you to connect your publisher to the distributor.

Once you have added in the connections to the server name as well as to the port number version of the server name, the server list should look something like this.



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?