March 18, 2009 at 7:01 pm
Ok not to much of a n00b but at a loss why I am having connection issues.
Current Configuration:
2 Nodes Clustered Windows 2003 Enterprise
-SQL01A - Node A
-SQL01B - Node B
SQL 2005 Enterprise
-SQL Instance A
-SQL Instance B
Setup active/active, Node A primary for instance A, Node B primary for instance B. Each are passive for the other. No problem setting up, all works well. Both nodes and instances are internal LAN. Server needing database connection is located in the DMZ. I need help/verify firewall settings...
When checking to see why both nodes are not listening on 1433 I did a packet trace and noticed for internal connections (from clients) a UDP on 1434 request and returned IP and Dynamic port. Connection with no issues (LAN request). I checked .1 .2 instance registry settings and noticed that node A TCPDynamic Port is 4133, and node B TCPDynamic Port is 2017.
Firewall rules I am proposing:
Open UDP 1434 to/from Node A & B and DMZ server
Open TCP 4133 and 2017 to/from Node A & B and DMZ server
Primary questions:
1. Are the dynamic port settings in the registry static (never have to worry about it changing)?
2. Is this the most affective or best way to get connected?
Any help really appreciated...
March 18, 2009 at 10:34 pm
By default each SQL Server instance will choose a random free TCP port at startup. When a client tries to connect to a server/instance without specifying the TCP port
1. A UDP 1434 packet is sent to the server
2. A UDP 1434 packet is returned to the client containing the TCP port under which the instance is running
3. All subsequent communications between the client and the server are on that TCP port.
What you need to do is hard-code each SQL Server instance to use a fixed TCP port. Make the hole in your firewall on those two TCP ports only. Then configure the clients to use that TCP port to connect to the server: eg. under ODBC add ", PORT = xxxx" to the connection string or under OLEDB suffix the servername with ",xxxx" (where "xxxx" is the TCP port the instance is running under). If you specify the TCP port you don't need to specify the instance name.
March 19, 2009 at 5:14 am
Is there any way to create an alias on the network so I don't have to reconfigure all the ODBC connections with the new ports as I migrate databases to the new environment?
March 19, 2009 at 5:35 am
Also does that mean if I reboot either of the nodes it may pick a different port?
March 19, 2009 at 3:39 pm
If you leave it at the default then it can choose a different TCP port after a failover or other restart. Hard-coding the TCP port that each SQL Server instance uses means that it will always start up on that port.
As far as adding a "central" alias that includes the port I'm not aware of a way to do that, but there may be a way. It's not required in our environment because all software is centrally managed and deployed as necessary, including any accompanying configuration such as ODBC definitions, so it's not something we've ever needed to investigate. I'll have a bit of a dig around if/when I get the chance to see if I can find out anything about that.
March 24, 2009 at 12:00 pm
Thanks for everyones help, we are up and running with no issues using the above firewall rules...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply