Have you ever encountered issues with distributed transactions in your SQL Server environment? If you’ve come across the error message “MSDTC on the server is unavailable,” then this blog post is for you. In this article, we will discuss the steps taken to solve this problem and get your distributed transactions up and running smoothly.

The Problem

When attempting to run a T-SQL query using the “begin distributed transaction” command, you may have encountered the error message “MSDTC on the server is unavailable.” This error can be frustrating, especially when the MSDTC service appears to be online and functioning correctly.

The Solution

To troubleshoot and resolve this issue, we engaged the Microsoft SQL Server Premier Support Team. They helped us identify the root cause and provided the necessary steps to fix the problem.

The first step was to ensure that MSDTC was indeed working correctly. We used three tools from the Microsoft arsenal: DTCPing, WinRM / RMClient, and DTCTester. These tools confirmed that MSDTC was functioning without any issues.

Next, we focused on the permissions assigned to the SQL Server Service Account. It was pointed out that the failure to run the “begin distributed transaction” command was likely due to insufficient permissions. To check the permissions, we used the sc command to examine the Access Control List (ACL) on the server.

According to Microsoft, the Authenticated Users (A;;CCCR;;;AU) entry was missing from the ACL. This entry grants permissions to the Microsoft Distributed Transaction Coordinator. To add this setting, we used the following command:

sc sdset msdtc D:(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCCR;;;AU)
(A;;CCLCSWLOCRRC;;;IU)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;NS)
(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)S:S:(S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

After making this setting change, we attempted to run the “begin distributed transaction” command again. Unfortunately, we still encountered the same error.

At this point, we suspected that the SQL Server service account might not have permissions to query the MSDTC service on the server. To address this, we downloaded the SubInACL tool, which allows administrators to obtain security information and transfer it between users, groups, and domains.

Using the SubInACL tool, we granted full control to the SQL Server service account for the MSDTC service with the following command:

subinacl /service msdtc /grant="USER"=F

Replace “USER” with the domain user account used for the SQL Server service.

Finally, after granting full control to MSDTC, we were able to successfully run the “begin distributed transaction” command.

Conclusion

If you’re experiencing issues with distributed transactions in your SQL Server environment, it’s important to ensure that MSDTC is functioning correctly and that the necessary permissions are in place. By following the steps outlined in this article, you can troubleshoot and resolve MSDTC issues, allowing your distributed transactions to work seamlessly.

For more detailed information on how Microsoft Support determined the root cause and resolved the issue, refer to the previous tip here.