Configure Cluster MSDTC with Powershell

Today, I configured a new SQL Server Failover Cluster with command line. The goal is to have a fully automatic installation of a SQL Server Failover Cluster.  By using Powershell, I was already able to add the MSDTC resource.  My next challenge was to configure it automatically Knipogende emoticon
I figured out that the MSDTC settings were in the registry under HKEY_LOCAL_MACHINEClusterResources. The problem was that the folders below this registry key are having an ID, so I had to find a way to know which one is used for MSDTC.
Well, I managed to configure it with the following script.
Import-Module FailoverClusters;
$InstanceName=$args[0]
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”

Let’s have a detailed look
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
First, I get all the child items of the registry HKEY_LOCAL_MACHINEClusterResources.  In each of the child items, I’m searching for a name that matches “MSDTC-SQL Server (<MyinstanceName>)”
Of course, you should change the “match” parameter when you have used another name for your MSDTC. Note: The parameter $InstanceName should be the name of your SQL Server Instance
As a result, I get the correct key!
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
I have used that key to set the correct registry values, according to my customer needs.
More information can also be found on
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”
Finally, you need to stop and start the cluster resource in order to take the changes into effect.
Note: The parameter $InstanceName should be the name of your SQL Server Instance
Have fun with it!

Leave a Reply

Your email address will not be published. Required fields are marked *