Allow distributed transactions
The place I am currently working wants to automate everything so not content with a standalone installation of SQL Server they also wanted to configure MSDTC by script as well, so I needed to create a script to do this for them. Here it is.
It may not have create the exact settings you want but should give you a good place to start if you need to do the same thing.
IMPORTANT: You may wish to backup the registry before making changes as this may present a risk, though I have seen no issues using this script.
DECLARE @Key nvarchar(100)
SET @Key='LuTransactions'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'NetworkDtcAccess'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'NetworkDtcAccessAdmin'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'NetworkDtcAccessClients'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'NetworkDtcAccessInbound'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'NetworkDtcAccessOutbound'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'NetworkDtcAccessTransactions'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'XaTransactions'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC\Security',@Key, REG_DWORD, 1
SET @Key = 'AllowOnlySecureRpcCalls'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC',@Key, REG_DWORD, 0
SET @Key = 'TurnOffRpcSecurity'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSDTC',@Key, REG_DWORD, 1
GO
sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'remote proc trans', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @SQL VARCHAR (1000)
SET @SQL = 'NET STOP MSDTC'
EXEC MASTER..XP_CMDSHELL @SQL
WAITFOR DELAY '00:00:05'--waits for 5 seconds to ensure service has time to stop
SET @SQL = 'NET START MSDTC'
EXEC MASTER..XP_CMDSHELL @SQL
GO
sp_configure 'xp_cmdshell', 0 --Closes use of command shell as security risk.
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options',0
GO
RECONFIGURE WITH OVERRIDE
GO
