Authentication Question: Setting Up Transactional Replication Between 2 Untrusted Domains Over the Internet

I need to setup MS SQL Transactional Replication between two untrusted domains over the Internet.

One of the SQL Servers running sql2008 (the Publisher that acts as it's own Distributor) is in a physical Co-Location and the other SQL Server running sql2012 (that is the Subscriber)  is in AWS with an EIP.

I started with Networking and have opened direct ports of the following on both the Co-location Firewall and in the AWS Security Group:


Open in new window

I am an administrator for both Networks, so setting up close-to identical accounts between these two is completely possible.

I've already setup the Publisher and Distributor and I'm ready to connect to setup the Subscription but I can't figure out a good way to do this.

The actual "replication" I should be able to handle, but how the heck to I authenticate between these two SQL Servers so that I can add the Publication to the Subscriber Database?

Thanks in advance for your help!

TessandoIT AdministratorAsked:
Who is Participating?
Basically what you need is to make sure that the 2 SQL servers can "see" each other and that the ports used by them are opened both ways between them. After that you will uses SQL authentication to connect from distributor to the subscriber. You will have to create a SQL authentication login on the Subscriber with the proper permissions and then use it to connect from distributor.
Eugene ZCommented:
--Use SQL Server Authentication.
HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet

Replication Security Best Practices
TessandoIT AdministratorAuthor Commented:
Great! Thank you. So, I am able to use Telnet to verify that port 445 is open and talking to each server.

I opened tickets to have port 1433 opened between them and even though the hosting company claims it's been completed, I still can't use Telnet to verify that the port is open. This is also the case for TCP 139, UDP 137 and UDP 138.

1). What's the port range that needs to be opened on both servers in order for this replication to work?
2). Is telnet a valid way to make sure that 1433 is open?

Thanks in advance for your help!
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

If you can connect from one SQL server to the other with Management Studio, replication should be OK.

2. It should be good.
Eugene ZCommented:
as per the best security practice recommendations for 'over internet' setup :

try to avoid using the  default  port 1433 -- set your Sql server  to use some XYZ port ...
TessandoIT AdministratorAuthor Commented:
This is really great, folks. Thank you SO MUCH for your help so far. The only consistent port that is open on both machines that I can verify is "445", so I figured I'd start there.

I've gone into the SQL Server Configuration Manager:
SQL Server Network Configuration -> Protocols -> TCP/IP -> IP2 ->
Active: Yes
Enabled: Yes
IP Address: 52.***.***.*** (Put in the Public IP Address)
TCP Port: 445

I then did this for BOTH servers, with the exception of leaving the IP Addresses as Public.

I still can't connect using SQL Server Management Studio.

1). Is SQL Server Configuration Manager the best way to configure this?
2). If so, should I change the IP Addresses back to Private addressing?

Thank you for the help.

SSMS uses port 1433.
Eugene ZCommented:
if you can ping your sql server (IP)  -- from SSMS try  to  use    yourserver IP, portnamber e.g., 12324
TessandoIT AdministratorAuthor Commented:
Thank you guys for your assistance and your patience. The only thing that I don't have control over is the Co-Location firewall and the hosting company is claiming that Port 1433 is open on the machine in question, however I cannot Telnet to that port. I can ping the server, so I know that they changed something, but when I telnet to it I get a connection error.

I'm Pacific time, so there is a chance that this might get resolved today, but it's not feelin' like it will. Otherwise, this may leak into next week. Thanks again.
Any news? Please choose the answer that helped you and award point.
TessandoIT AdministratorAuthor Commented:
Thanks all. This was a huge project that I'm going to quickly outline in order for future folks to benefit:

1. I created a LOCAL User (let's say "sql_replication")
   1a. This local user is the one with permissions to the Subscription Database and the SQL Job associated with replication.

2. When I connected to the Distributor/Publisher (I run them on the same instance of SQL) I connected using "sa", so that there was no qualms about permissions.

Additional Parameters that I had to setup and pay attention to:


Added Aliases to the remote servers in SQL Server Configuration Manager:
   1. SQL Native Client 11.0 -> Aliases: I did this for BOTH 32-bit and 64-bit, even though they are both running 64-bit
   2. On the remote servers, I had to find either the hard-coded IP or Dynamic IP of the port. Again, in SQL Server Configuration Manager: SQL Server Network Configuration -> Protocols for SQL2012 -> TCP/IP -> IPALL section.

Then, outside of the Server work and the Subscription setup, I did have to make sure that individualized ports were open on both the Co-location side and in AWS (via Security Groups). That IPALL section in the SQL Server Configuration Manager is where I got that port number.

If you don't know and you are at a loss, use this command on the Server to determine what port SQL is listening on: netstat -abn

Hope this helps. Thanks again for pointing me in the right direction! :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.