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

Posted on 2016-10-05
Last Modified: 2016-10-24
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!

Question by:Tessando
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
LVL 43

Expert Comment

by:Eugene Z
ID: 41830990
--Use SQL Server Authentication.
HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet

Replication Security Best Practices
LVL 27

Accepted Solution

Zberteoc earned 500 total points
ID: 41831772
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.

Author Comment

ID: 41832072
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!
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 27

Expert Comment

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

2. It should be good.
LVL 43

Expert Comment

by:Eugene Z
ID: 41832290
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 ...

Author Comment

ID: 41832687
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.

LVL 27

Expert Comment

ID: 41833617
SSMS uses port 1433.
LVL 43

Expert Comment

by:Eugene Z
ID: 41833679
if you can ping your sql server (IP)  -- from SSMS try  to  use    yourserver IP, portnamber e.g., 12324

Author Comment

ID: 41834485
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.
LVL 27

Expert Comment

ID: 41857152
Any news? Please choose the answer that helped you and award point.

Author Closing Comment

ID: 41857245
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! :)

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A hard and fast method for reducing Active Directory Administrators members.
Microsoft Office 365 is a subscriptions based service which includes services like Exchange Online and Skype for business Online. These services integrate with Microsoft's online version of Active Directory called Azure Active Directory.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question