asked on

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:

UDP:137
UDP:138
TCP:139
TCP:445
TCP:1433

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!

...

EugeneZ

--Use SQL Server Authentication.
check
HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet
https://support.microsoft.com/en-nz/kb/321822

more
Replication Security Best Practices
https://msdn.microsoft.com/en-us/library/ms151227.aspx

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tessando

ASKER

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!

Zberteoc

1. https://blogs.technet.microsoft.com/fort_sql/2012/07/03/sql-server-ports/
If you can connect from one SQL server to the other with Management Studio, replication should be OK.

2. It should be good.

EugeneZ

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 ...

Tessando

ASKER

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.

...

Zberteoc

SSMS uses port 1433.

EugeneZ

if you can ping your sql server (IP) -- from SSMS try to use yourserver IP, portnamber e.g. 10.1.1.2, 12324

Tessando

ASKER

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.

Zberteoc

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

Tessando

ASKER

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:

**These are done BEFORE SETTING UP REPLICATION**

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