We upgraded our servers to Windows 2003 SP1, and now our data-driven application fails with strange errors!
Problem Description
After upgrading an application server or database server to Windows 2003 SP1, the application encounters errors originating from data-access components. Typically we'll see this when one server in the application server/database server scenario is updated and the second server is left on a previous version of the OS (particularly Windows 2000 Server).
There can be a variety of errors involved, the most obvious is along the lines of:
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
However, we've often seen much less obvious errors as well
"Operation is not allowed when the object is closed."
The particular error message can be largely dependent on how thoroughly the component that executes the data-access code examines the results of method calls.
Possible Solutions
Most of the times when customers call in with this scenario, the solution has been to simply modify the security settings for MSDTC on the Windows 2003 SP1 machine.
Windows 2003 SP1 and Windows XP SP2 add new security enhancements to MSDTC, including support for secure communication between two machines participating in a distributed transaction.
The catch is that by default, MSDTC on Windows 2003 SP1 will attempt to authenticate the other machine with these new features. Consequently, if the other machine is not Windows 2003 SP1 or later, it will not be able to participate in a distributed transaction with the updated server.
In order to disable MSDTC authentication on the Windows 2003 SP1 server:
1) Open Control Panel -> Administrative Tools
2) Open the Component Services msc.
3) Expand the Console RootComponent ServicesComputers node.
4) Right Click on My Computer and select properties
5) Select the MSDTC tab, and click the Security Configuration… button.
6) Make sure the following values are checked:
a. Network DTC Access
b. Allow Inbound
c. Allow Outbound
d. No Authentication Required
e. If you’re using JDBC or Oracle, you’ll also want Enable XA Transactions checked
7) Click OK until all the dialog boxes are closed, and exit the Component Services tool.
8) While not strictly required, it may be worth recycling your application process, or even rebooting to make sure the changes are in effect.
9) Retest your application.
Further Information
If you need more information regarding what the different settings in the Component Services MSDTC dialog mean, this blog post does a good job of explaining them:
Florin Lazar's WebLog -- XP SP2 and Transactions
A common question in regards to these settings is - does turning off Mutual Authentication mean we’re not secure?
The answer is that you’re no less secure than you were before installing Windows 2003 SP1, so it should not be a problem for most organizations. On the other hand, if you can use Mutual Authentication (which would require updating all servers involved in distributed transactions to Windows 2003 SP1), then it’s a good idea to do so.
Hope this helps!
Posted By: Justin Trobec
Comments
Anonymous
January 29, 2007
Thanks for your help, Best Regard to youAnonymous
February 29, 2008
This error occurred when running queries joining to a linked server, when the linked server was upgraded