The other day Allan Hirt (blog|twitter) write a fantastic post around “How to properly configure DTC for Clustered Instances of SQL Server with Windows Server 2008 R2”. He included a PowerShell script to handle all the funky setup stuff. The only downside was that after that you had to manually go through and configure Network Access and security for the clustered DTC using the GUI.
“There has to be a better way” I thought. I recalled how security back in 2003 could be set in the registry so I went digging. Eventually I was able to find the requisite keys under the Distribute Transaction Coordinator resource. From there it was just a case of tying things back so that those keys could be easily found from the SQL Resource Name and then updating them so that they matched what Allan demonstrated on his page.
I’ve tested it on a couple of my clusters and it’s worked exactly as designed, you just need to change the ServiceName variable so that it matches your clustered service name and then decided what authentication method you want to use (it defaults to the most secure level: Mutual Authentication Required).
Get the code below or download ConfigureMSDTC.ps1
<#
.SYNOPSIS
Configures MSDTC on a cluster
.DESCRIPTION
This configures a basic networked config for MSDTC for clustered SQL Server instances.
Uncomment the transaction manager security setting and enter the correct ServiceName that can be found in FC manager
.PARAMETER <paramName>
NONE
.EXAMPLE
NONE
#>
$ServiceName = "SQL Server (SOX2)"
#--What MSDTC Transaction Manager Security setting is requested? (uncomment one)
$TranManSec = "Mutual" #Mutual Authentication Required
#$TranManSec = "Incoming" #Incoming Called Authentication Required
#$TranManSec = "None" #No Authentication Required
#Grab a list of cluster groups
$GroupPath = "HKLM:ClusterGroups"
$GroupList = dir $GroupPath
#Iterate through the groups to find the one matching the service name
foreach ($Group in $GroupList)
{
$GroupChildPath = $Group.PSPath
if ((Get-ItemProperty -path $GroupChildPath -name Name).Name -eq $ServiceName)
{
#we got a match! Now grab a list of the groups in this service
$ReferencedResources = (Get-ItemProperty -path $GroupChildPath -name Contains).Contains
foreach ($Resource in $ReferencedResources)
{
#Query each of the resources for their type and work with MSDTC
$ResourcePath = "HKLM:ClusterResources$Resource"
if ((Get-ItemProperty -path $ResourcePath).Type -eq "Distributed Transaction Coordinator")
{
#We found MSDTC resource for that service group, let's configure it
$SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTCSecurity"
Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccess" -value 1
Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessClients" -value 0
Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessTransactions" -value 0
Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessInbound" -value 1
Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessOutbound" -value 1
Set-ItemProperty -path $SecurityPath -name "LuTransactions" -value 1
#Now configure the authentication method for MSDTC (defaulting to Mutual Auth as it's most secure)
$SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTC"
if ($TranManSec -eq "None")
{
Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 1
Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0
Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0
}
elseif ($TranManSec -eq "Incoming")
{
Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0
Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0
Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 1
}
else
{
Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0
Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 1
Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0
}
}
}
}
}
SirSQL.net 