1

I have an SQL Server 2012 server set up replicating one Database. It is behind a firewall. I have poked a hole in the firewall so I can access the server on Port 1433. I am using an IP Address and a different port number to access from a external hosted 2016 Sql Server.

When I try to subscribe to the Replicated database the Subscription goes thru, however the Job continually fails with "Cannot access Distribution Server [RemoteSQLServerName]"

How can I modify the Job/script to make it point an IP Address and a Port number?

1 Answer 1

1
+50

Usually the following ports must be opened for inbound traffic: TCP 1433, 1434, 135, 80, 443; UDP 1434.

Replication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 is the default instance)

Web synchronization and FTP/UNC access for replication snapshot require more ports to be opened on the firewall. To transfer initial data and schema from one location to another, replication can use FTP (TCP port 21), or sync over HTTP (TCP port 80) or File Sharing. File sharing uses UDP port 137 and 138, and TCP port 139 if used along with NetBIOS. File Sharing uses TCP port 445. For sync over HTTP, replication uses the IIS endpoint (configurable; port 80 default), but the IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance).

usually you can set up subscribers in this way: exec sp_addsubscription @subscriber = 'Server,port'. try with IP and custom port

source

8
  • I copied a recent backup of the database and restored it to my subscriber in hopes that I won't need to use those extra ports. Are you saying I still need to open more than the single 1433 port for replication?
    – Randy
    Commented Apr 1, 2022 at 13:24
  • depend, are you sync over http/https?
    – MBuschi
    Commented Apr 1, 2022 at 13:42
  • what do you mean with "I am using an IP Address and a different port number to access from a external hosted 2016 Sql Server."? your target server should be available from the source server. try to open ssms on source server and the connect to the target sql
    – MBuschi
    Commented Apr 1, 2022 at 13:45
  • I am not syncing over http/https. I can connect from my subscriber SQL server to my replication server with SSMS just fine using the IP Address and port I described.
    – Randy
    Commented Apr 1, 2022 at 14:26
  • Have you tried to add a dns entry for that ip in the publisher network naming it as [RemoteSQLServerName] (CNAME or ALIAS)?
    – MBuschi
    Commented Apr 2, 2022 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.