SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
Linked Server Queries Stop Working After Applying Windows Server 2003 SP2
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
Why do SQL Server 2005 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:

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:
The Component Services Window Context Menu
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:

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!
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form