Linked Server Queries Stop Working After Applying Windows Server 2003 SP2

Symtoms: DTC (Distributed Transaction Controller) calls from a remote server to the server which has recently been service packed stop working.

This problem is experienced because the security settings for the Distributed Transaction Controller have been tightened up to prevent unauthenticated remote connections.

This is by design, and part of Microsoft's increasing aim to make the Windows systems more secure.

If the connection existed prior to applying Windows Server 2003 SP2, it will not work as MS DTC has now been set to require mutual, two way authentication by default.

This is because the default setting for MS DTC when an RPC call is made now requires two way (mutual) authentication as a result of applying the Windows Server 2003 Service Pack 2 upgrade.

However, the good news is that you can still have an unauthenticated connection, you just need to reset the MSDTC authentication settings. But as is often the case, finding how to do this simple task can be quite difficult.

So How Do I Fix it?

It’s very simple. On the server that has been upgraded with Windows Server 2003 SP2, perform the following actions from the server's desktop:

Start > Settings > Control Panel > Administrative Tools > Component Services

This opens the following the following Component Services Window:

Figure 1: The Component Services Window

Click on the Console Root node to expand the tree > Component Services > Computers > My Computer. Then right click and choose the Properties menu item from My Computer as shown below:

Figure 2: My Computer Menu - Choose Properties

Click on the MSDTC tab and click on the Security Configuration button in the bottom left hand corner of the tab.

The MSDTS security settings should be set to the following options:

Figure 3: MSDTC Security Configuration Settings

Click on the OK button to close the tab, and re-run your distributed query (linked server query).

However if you still have authentication problems, check that the correct account details are entered in the DTC Logon Account box, and that it has sufficient permissions to allow DTC to operate correctly.

If in doubt, enter the name of a domain account (a domain admin account is not needed), which has administrator privileges on both servers.

Your server should now be working as it was prior to installing Windows Server 2003 SP2!

End of Technical Article

SQL Server Club is a free community service from Norb Technologies - Making SQL Server Faster - www.norbtechnologies.com