Share via

Windows 2008 SQL Server Cluster (with MSDTC) when using the Windows Firewall with Advanced Security

  • Article

Take the scenario whereby you have a Windows 2008 Cluster used as a SQL Database Cluster. A common requirement of SQL Server is a Transaction Manager (MSDTC).

If your configuration is to have one clustered instance of MSDTC in its own resource group and for any of your SQL server instances to be in their own separate resource groups, then there is a potential for MSDTC and SQL Server to be active on different nodes at a given time.

Primarily the SQL Server process loads the MSDTC Proxy in order to communicate with the MSDTC Service. When this is on the same node there will likely be no problems, however if they are active on different nodes and you have the Windows 2008 Firewall enabled you may encounter problems. The communication between the MSDTC Proxy (loaded in to SQL) and the MSDTC service is done through RPC. The obviously symptom of such a problem will be that you cannot flow a transaction from your application towards the SQL Server instance in question, however more specifically if you connect to the SQL Server instance using the management studio and run a query “Begin distributed Transaction” it will fail.

If you examine the exception rules (for the MSDTC exceptions) within the “Windows firewall and Advanced Security” you will see the following rules:

Inbound Rules:

-Distributed Transaction Coordinator (RPC) -> Limited to SVCHOST Process communication

-Distributed Transaction Coordinator (RPC-EPMAP) -> Limited to SVCHOST Process communication

-Distributed Transaction Coordinator (TCP-IN) -> Limited to MSDTC Process communication

Outbound Rules:

-Distributed Transaction Coordinator (TCP-Out) -> Limited to MSDTC Process communication

 

As can seen there is no “TCP-IN” or “TCP-Out” for the SQL Server process. This is important as it is the SQL Server process initiating the contact with MSDTC. In order to keep security as tight as possible, instead of disabling the firewall, you can create these rules and ensure endpoint addresses must be within the cluster boundary. When creating these new rules, select the SQL Server process executable in the options (remember each is instance specific) and add all virtual and physical IP ranges within the cluster to the rules scope properties.

There are already configured DTC rules (named above) to MSDTC traffic from any application server to the Database Cluster so there is no concern about this for now.

Once these rules are enabled, repeat the “Begin Distributed Transaction” query when MSDTC is on a different node to SQL Server and it should now be successful.

 

Chris Forster

Comments