July 27, 2012 at 11:07 am
If anyone has ever tried to enlist in a distributed transaction over a linked server and received this error message:
"OLE DB provider "SQLNCLI10" for linked server "DB03" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 6
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB03" was unable to begin a distributed transaction."
When I see that error message I know that the network security on MSDTC needs to be set to allow remote inbound and outbound transactions. The way to do this is by going to Component Services in Administrative Tools, expanding My Computer and navigating down to Distributed Transaction Coordinator. You then have to check a couple of boxes in the Security tab to allow MSDTC over the network.
How is this done on Windows 2008 R2 Core?
My configuration is
Windows 2008 R2 Core Enterprise
Active-Active failover clustering (2 nodes)
SQL 2012 Enterprise Core (2 instances, one per node)
MSDTC is a clustered resource for each of the clustered SQL Server resources
I could find nothing online about this issue, so I'm documenting it here. I've got a ticket with Microsoft open and we're working through the issue. I'll post updates as I get them.
-matt
July 27, 2012 at 11:14 am
Is DTC enabled on the other server connected to DB03 via Linked server?
July 27, 2012 at 11:20 am
Here is MS Recommended MSDTC settings
July 27, 2012 at 11:30 am
Yes, I can do the same thing from the central server (DB01) to any other server. My test harness is this (executed from DB01)
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM DB03.master.sys.databases
ROLLBACK
If I use the DB02 linked server I get data. If I use the 2012 linked server (DB03) then I get the distributed transaction error.
-matt
July 27, 2012 at 1:34 pm
And just to be clear, MSDTC is configured properly on all of my servers except for this Windows 2008 R2 Core cluster. The problem is that I can't configure it.
The Microsoft technician is having me modify and add a bunch of registry keys. This could get messy.
August 1, 2012 at 8:30 am
I spent several days on the phone with the Microsoft dude and it turns out that there were two separate issues.
1. Network access needed to be configured for MSDTC, which had to be done via modifying the registry
2. There's a bug in Windows 2008 R2 clustering where MSDTC won't work out of the box
The registry keys that needed to be added are defined here: http://technet.microsoft.com/en-us/library/cc753620.aspx
The keys that I added were:
[HKEY_LOCAL_MACHINE\Cluster\Resources\<MSDTC GUID>\MSDTCPRIVATE\MSDTC\Security]
"AllowOnlySecureRpcCalls"=dword:00000001
"FallbackToUnsecureRPCIfNecessary"=dword:00000000
"TurnOffRpcSecurity"=dword:00000000
"NetworkDtcAccess"=dword:00000001
"NetworkDtcAccessTransactions"=dword:00000001
"NetworkDtcAccessInbound"=dword:00000001
"NetworkDtcAccessOutbound"=dword:00000001
"LuTransactions"=dword:00000001
"XaTransactions"=dword:00000001
"DomainControllerState"=dword:00000000
"NetworkDtcAccessAdmin"=dword:00000001
"NetworkDtcClients"=dword:00000001
"NetworkDtcAccessTip"=dword:00000000
You can find the MSDTC GUID by looking at services.msc while MSDTC is running. You'll see two MSDTCs, one for local (without the GUID) and the other has a GUID attached to it.
The second half of this was the clustering bug. I found this by tracing MSDTC. The article that fixed me up was this http://support.microsoft.com/default.aspx?scid=kb;en-US;2172085
I just had to rename the cluster resource name to match the DNS name and then it started working.
This was all significantly harder than it should be, but at least it is working.
-matt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply