Solved

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

Posted on 2016-10-05
11
45 Views
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:

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!


...
0
Comment
Question by:Tessando
  • 4
  • 4
  • 3
11 Comments
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
--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
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:Tessando
Comment Utility
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!
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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 ...
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Tessando
Comment Utility
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.


...
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
SSMS uses port 1433.
0
 
LVL 42

Expert Comment

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

Author Comment

by:Tessando
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Any news? Please choose the answer that helped you and award point.
0
 

Author Closing Comment

by:Tessando
Comment Utility
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! :)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now