Solved

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

Posted on 2016-10-05
11
67 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
[X]
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
11 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 41830990
--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 27

Accepted Solution

by:
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.
0
 

Author Comment

by:Tessando
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!
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 41832085
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 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 ...
0
 

Author Comment

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


...
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41833617
SSMS uses port 1433.
0
 
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. 10.1.1.2, 12324
0
 

Author Comment

by:Tessando
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.
0
 
LVL 27

Expert Comment

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

Author Closing Comment

by:Tessando
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:

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Active Directory security has been a hot topic of late, and for good reason. With 90% of the world’s organization using this system to manage access to all parts of their IT infrastructure, knowing how to protect against threats and keep vulnerabil…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

751 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