Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Troubleshooting SQL Distributed Transactions (Part 2 of 2)

MSSQLTips author Matteo Lorini By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | More > 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

sc command prompt

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)

windows command prompt sc sdset

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:



Last Update: 9/14/2010


About the author
MSSQLTips author Matteo Lorini
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, September 29, 2010 - 5:29:17 AM - Mart Read The Tip
Good description of the troubleshooting and repair Matteo,

Do you understand what caused the mis-configuration so we can avoid the problem.

Thanks, Mart


Thursday, September 30, 2010 - 10:52:49 AM - Matteo Read The Tip
  mart,

sorry we never understood what caused the mis-configuration to happen.

 

Thanks


Thursday, February 14, 2013 - 3:23:10 PM - Matt Read The Tip

We have MSDTC working on stand-alone servers but in order to get it to work on a cluster we have to grant the SQL Login db_owner on the database it is using. Is db_owner really needed in order to use MSDTC on a cluster or are we missing something?

Thanks


Friday, February 15, 2013 - 9:59:29 AM - Matteo Read The Tip

We never found the root cause of the issue however, if you grant mstc full permission using

subinacl /service msdtc /grant="USER"=F where USER is the SQL Server Service Account.  this will fix the issue.

Thanks


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.