By: Matteo Lorini | Comments (4) | Related: 1 | 2 | > SQL Server Configurations
Problem
Distributed transactions are not working in our environment. When we run a T-SQL query using "begin distributed transaction" it fails with the following error: "MSDTC on the server is unavailable" however it looks like the MSDTC service is online and working fine. In this tip (part 2), I will show the steps that were taken with the Microsoft SQL Server Premier Support Team to solve the problem.
Solution
In Part 1 of this tip, we went through the steps taken by the Microsoft Windows Support team to find out if MSDTC was working or not. We used three major tools from the Microsoft arsenal to help with the issue: DTCPing,WinRM / RMClient and DTCTester. All the tools used showed us that MSDTC was working fine without any problem.
To figure out why we were still not able to successfully run a begin distributed transaction command, we engaged Microsoft Premier Support for SQL Server.
The person that helped us out was very sharp and he pointed out that most likely the failure to run a begin distributed transaction T-SQL command was due to a lack of permissions assigned to the SQL Server Service Account.
The first thing we used was the sc command to check the ACLs (Access Control List) on the server. The sc command is a command line program used for communicating with the Service Control Manager and services. Keep in mind that unless instructed by the Microsoft Support team, it could be dangerous to try to work directly with the contents of an ACL. Reference to Access Control Lists can be found at: http://msdn.microsoft.com/en-us/library/aa374872(VS.85).aspx
The first thing we did was to check the permissions that were granted to the Microsoft Distributed Transaction Coordinator by issuing the following command:
sc sdshow msdtc
According to Microsoft, the Authenticated Users (A;;CCCR;;;AU) was completely missing from the ACL. The A means "Allow", the CCCR is two commands CC means SDDL_CREATE_CHILD and CR means SDDL_CONTROL_ACCESS. The last part AU is the user access and in this case stands for Authenticated Users. Not that this fully explains what this does, but it gives you a little more insight into the command. You can read this article for more information or refer to this Microsoft KB article.
So based on this, this was the command we used to add the setting:
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 the setting change, we ran a begin distributed transaction T-SQL command again, but unfortunately we still got the same error.
In our environment, the SQL Server service is running under a domain account, so we added this account to the local admin group, but it did not help either. Premier Support at this point was suspecting that the SQL Server service account might not have permissions to query the MSDTC service on the server therefore we downloaded the SubInACLool tool in order to give full control to the SQL Server service account for the MSDTC service.
SubInACL is a command-line tool that enables administrators to obtain security information about files, registry keys, and services, and transfer this information from user to user, from local or global group to group, and from domain to domain. For example, if a user has moved from one domain (DomainA) to another (DomainB), the administrator can replace DomainA\User with DomainB\User in the security information for the user's files. This gives the user access to the same files from the new domain.
SubInACL enables administrators to do the following:
- Display security information associated with files, registry keys, or services. This information includes owner, group, permission access control list (ACL), discretionary ACL (DACL), and system ACL (SACL).
- Change the owner of an object.
- Replace the security information for one identifier (account, group, well-known security identifier (SID)) with that of another identifier.
- Migrate security information about objects. This is useful if you have reorganized a network's domains and need to migrate the security information for files from one domain to another.
The syntax to grant full control on MSDTC service is the following:
subinacl /service msdtc /grant="USER"=F
The USER has to be replaced with domain user account used for the SQL Server service.
Finally, after the full control to MSDTC was granted, we were able to successfully run a begin distributed transaction.
Conclusion
SQL Server was not able to initiate Distribute Transactions due to the fact that it was not able to communicate with the MSDTC service. Please review how Microsoft Support was able to determine the root cause and to resolve the issue by reviewing this previous tip. If you run into this issue in your environment I hope this gives you information you can use to help troubleshoot and resolve your MSDTC issue.
Next Steps
Check the following links:
- Troubleshooting SQL Distributed Transactions - Part 1
- Microsoft Distributed Services Team Blog
- Access Control Lists
- SubInACL
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
