Microsoft Distributed Transaction Coordinator (MSDTC) is an important component in SQL Server that allows for the coordination of distributed transactions across multiple servers. In this article, we will discuss the configuration of MSDTC and troubleshoot a common error that users may encounter.

Background

Recently, a client requested the configuration of MSDTC for their application server and database server. Both servers were running on Windows Server 2016 OS, with the database server running SQL Server 2016 using the Availability Groups feature. It is important to note that SQL Server 2016 SP2 is the first version that provides full support for distributed transactions in availability groups.

Configuration

To configure MSDTC correctly, it is recommended to follow the checklists provided in the “How to cluster the DTC service for an Always On availability group” guide. This guide will ensure that all necessary settings are properly configured.

Troubleshooting

When testing or troubleshooting the configuration of MSDTC, two main utilities can be used: DTCTester and DTCPing. DTCTester tests transactions between two computers with SQL Server installed, while DTCPing tests transaction support between two computers without involving SQL Server.

During the testing process, it is common to encounter errors. One common error is “Access is Denied.” In most cases, the troubleshooting steps provided in the documentation should resolve the issue. However, in some scenarios, such as using Windows Server 2016, the provided resolution may not apply.

In such cases, it is worth trying alternative solutions. In this particular scenario, changing a registry key mentioned in an older article for Windows XP and Windows Vista machines resolved the “Access is Denied” error on the Windows Server 2016 machines.

Conclusion

When faced with configuration issues in SQL Server, it is important to exhaust all available resources and try alternative solutions. Assumptions and recommendations may change over time, and what may not seem applicable to your scenario could actually be the solution. By documenting our experiences and sharing them with others, we can help fellow SQL Server users overcome similar challenges.

Thank you for reading this article. We hope it has provided valuable insights into configuring MSDTC in SQL Server. If you have any questions or comments, please feel free to reach out.