What IPs have to be allowed through a corp firewall to allow Relication Traffic to\from a clustered SQL Server Instance

I have a SQL Server 2008 R2 cluster with two nodes (active/passive). Here's the configuration :

Cluster name : MYCLUSTER .
Cluster IP : 123.123.0.10
Node 1 (active node) hostname : NODE1
Node 1 IP : 123.123.0.11
Node 2 (passive node) hostname : NODE2
Node 2 IP : 123.123.0.12
Instance Name: MYCLUSTER/MYINSTANCE
Instance IP: 123.123.0.13

I need to have my Network Folks open the Firewall to allow SQL Replication Traffic to a SQL Server outside of our Network. We opened the Firewall using the IP of the SQL Instance (123.123.0.13) but that did not do the trick. Can someone tell me what IPs have to be opened at the Firewall to allow the Replication Traffic?
itsonlyme4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to open the port for the instance. Usually in a cluster the port is dynamic, so you have 2 solutions here:

1.

Set static port to the SQL Server instance so you can create the rule in the firewall

2.

Let it dynamic and create a rule in the firewall to allow the sqlservr.exe pass through
0
itsonlyme4Author Commented:
The IP for the Instance is Static.    That was my assumption as well - add the IP for the Instance to the Firewall rule.

My Network guys added a rule for TCP traffic for the IP for Instance Name: MYCLUSTER/MYINSTANCE
 Instance IP: 123.123.0.13.

I then went on my Server and added a host file entry for the Destination SQL Server so that I can get to it by name.

I then went to the SQL Server MGT Studio on the Source SQL Server and attempted to connect to the Destination Server by name from Management Studio and I get this error:

Cannot connect to SQLSERVER.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Now I am thinking that I either have to add the IP for the Cluster itself or the Nodes (or both) but I cannot find where this is documented anywhere..      

Any ideas?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The IP for the Instance is Static.    That was my assumption as well - add the IP for the Instance to the Firewall rule
I'm not referring to the IP but the port. In which port SQL Server is listening?

If you are using  Named Pipes the you should also open the port 1434 (UDP) in the firewall.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

itsonlyme4Author Commented:
SQL Server is listening on 1433.  When we opened the firewall for  a standalone Server running SQL Server to this same Destination server outside of our network, we did not have to open port 1434.  is that something specific to the Cluster?
0
Deepak ChauhanSQL Server DBACommented:
You can try to connect with port.
IP, 1433
0
itsonlyme4Author Commented:
I am not sure what you mean
0
Deepak ChauhanSQL Server DBACommented:
I then went to the SQL Server MGT Studio on the Source SQL Server and attempted to connect to the Destination Server by name from Management Studio and I get this error:

I mean to say connect sql server from source server using destination IP with port no.

123.123.0.13, 1433

Are you able to perform Telnet on port 1433 from source to destination.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, a telnet to that port will confirm that SQL Server is listening on that port and that it's opened in the firewall.
Using named pipes will make the need to have port 1434 UDP opened as well. Also the SQL Browser service to be running.
0
itsonlyme4Author Commented:
for the UPD traffic on 1434, would that need to be opened for the Cluster IP ONLY  : 123.123.0.10 or for the NODES as well?
 
Node 1 (active node) hostname : NODE1
 Node 1 IP : 123.123.0.11
 Node 2 (passive node) hostname : NODE2
 Node 2 IP : 123.123.0.12
0
Deepak ChauhanSQL Server DBACommented:
UDP port 1434 is for SQL server browser service which is not cluster aware , so you have to open it on both node i think. Let us know SQL cluster instance is Default or named instance on this cluster.

Error 53 indicating server name not found.
1. ping the SQLserver IP 123.123.0.13 from the client server.
2. telnet on port 1433 from the client server.
3. Connect SQL server using IP\instance name (123.123.0.13\InstanceName, 1433) in case of Named Instance. If default instance use sql cluster IP (123.123.0.13, 1433).

Please let us know result after above three steps.
0
itsonlyme4Author Commented:
Our Network guys put a packet sniffer on the network and this is what we found.  

I set up a sandbox environment where I replicated a Database from a clustered instance of SQL Server to a standalone server.

In our environment, with the destination SQL Server listening on port 1433, we had to open TCP traffic (outbound) using the IP addresses of each Node of the Cluster. so far this is working in our sandbox.    we will put it into production later in the week and see if it works the same for the external network we are trying to replicate to..   that is the real test.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
How many network interfaces the nodes has?
0
itsonlyme4Author Commented:
How many network interfaces the nodes has?    I'm not sure I understand the question.

Since it is a two node cluster then I suppose the answer would be two
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I meant how many IPs exists in a node.
What could happen is that SQL Server is receiving from one IP and answering from another and that's why you needed to add the physical IP's in the firewall rules.
0
itsonlyme4Author Commented:
one IP per node
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
At least 2 for sure. The virtual and physical IPs.
It might be listening on physical IP and answering on the virtual IP.
0
itsonlyme4Author Commented:
I selected my own comment by mistake
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.