Using PowerShell To Restrict MSDTC Ports

Ever tried to create a linked server that uses MSDTC only to find yourself blocked by your company firewall? Ever tried to run a WMI query against a server just to find yourself blocked? Ever had the firewall team tell you that they aren’t going to open up ports 49152-65535 so that you can use RPC?

Let’s be fair, your network team shouldn’t have to open up all those ports because RPC responds somewhere within a large dynamic range.

How to configure RPC dynamic port allocation to work with firewalls will tell you how to edit your registry to restrict that port range and make your network admin a little happier.

Working with the registry is not fun at the best of times, and when you are setting up a bunch of machines it takes time. Sure, you could create a .reg file and run that on each machine, but this is 2011 and we have PowerShell now.

The following script checks and if necessary adds the required registry keys to restrict that port range. In the example below windows is being limited to ports 5000-5200.

<#

.SYNOPSIS

   Restricts the RPC ports to be used on Windows from 5000-5200

.DESCRIPTION

   Execute to add registry entries on the local machine to restrict the RPC ports from 5000-5200. Requires a reboot once executed.

.PARAMETER <paramName>

   NONE

.EXAMPLE

   NONE

#>

if (Test-Path 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' -ErrorAction SilentlyContinue) { "Registry Key Exists" } 

else { md 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' }

if (Get-ItemProperty -Name "Ports" -Path 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' -ErrorAction SilentlyContinue ) { "Ports value exists" }

else { New-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' -Name 'Ports' -Value '5000-5200' -PropertyType 'MultiString' }

if (Get-ItemProperty -Name "UseInternetPorts" -Path 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' -ErrorAction SilentlyContinue ) { "UseInternetPorts value exists" }

else { New-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' -Name 'UseInternetPorts' -Value 'Y' -PropertyType 'String' }

if (Get-ItemProperty -Name "PortsInternetAvailable" -Path 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' -ErrorAction SilentlyContinue ) { "PortsInternetAvailable value exists" }

else { New-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Rpc\Internet' -Name 'PortsInternetAvailable' -Value 'Y' -PropertyType 'String' }

MSDTC Failing In A Cluster

I’m merrily working away on installing SQL 2008R2 on a bunch of new clusters. The SQL installs have gone fine and I’m getting set to install MSDTC, one for each SQL instance (read the awesome post by Cindy Gross on this).

The install of MSDTC went smoothly and it seemed very happy. Then I failed over the cluster…

MSDTC failed.

It wouldn’t restart.

I failed back to the original node and it wouldn’t start there either.

What’s the error?

I dumped the last few minutes of the cluster log by opening a dos box command prompt and running…

cluster log /gen /span:5

This dumps the last five minutes of cluster logs into C:\Windows\Cluster\cluster.log

I scrolled through to get to the MSDTC error piece:

INFO  [RES] Physical Disk: Failed to get vol MP root for path \\?, status 123
ERR   [RHS] Error 123 from ResourceControl for resource <instance>_MSDTC.
WARN  [RCM] ResourceControl(STORAGE_IS_PATH_VALID) to <instance>_MSDTC returned 123.

I checked the disk resource for MSDTC and it was online. Looking at the filesystem on that disk and there was an MSDTC directory, so I knew there were no access problems. It didn’t make any sense.

So what’s going on?

The key error here is the failure to get MP root for path \\?

Apparently MSDTC is not supported does not work with Mount Points, which is what I had set the dependency to. There were no warnings on this when setting MSDTC up and I’d not seen or heard of any documentation that speaks to this.

I was finally pointed to a Connect item opened by someone who’d had the same issue https://connect.microsoft.com/SQLServer/feedback/details/576545/msdtc-fails-to-restart-in-sql-server-2008-r2-clustered-group

_{Side note: I love it when Connect items such as this are closed as by design. Why is this by design? Can someone explain to me why MSDTC shouldn’t be supported on Mount Points?}

I deleted the MSDTC resource and added it again, this time using a regular drive as a dependency and everything worked perfectly. I was able to failover and have SQL perform distributed transactions.