New transaction cannot enlist in the specified transaction coordinator

I was tying to set up a special feature in the software I work with that involved linked servers. This was test setup so I configured some SQL servers I already had set up and ran into this error.

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

The only message the application gave me was the first line but I uncovered a sql command that let me test it without the overhead of running my application. Here is that command:


BEGIN DISTRIBUTED TRANSACTION
select * from linkname.databasename.dbo.tablename
COMMIT TRAN

I was able to get the inner command to run on its own. As a distributed transaction, it would fail every time.

I eventualy found this KB from microsoft to get me in the right direction. http://support.microsoft.com/kb/839279

You may receive a 7391 error message in SQL Server 2000 when you run a distributed transaction against a linked server after you install Windows Server 2003 or Windows XP Service Pack 2

It turned out to be a settings issue with the Microsoft Distributed Transation Cordinator (MS DTC). I had no idea what that realy was. It turns out MS DTC should be enabled when SQL is installed but by default its locked down (or is with 2003/xp sp2).

I used dcomcnfg to get to the component services. Console Root -> Component Services -> Computers -> My computer -> Properties . On the Default properties enable distributed COM on the computer. Under MSDTC -> Security Configuration check the option for Network DTC Access.

I did make those changes on both servers, I don't know if that was needed or not. I did the linked server last and it worked as soon as I applied the changes. I know those were the exact changes I made on the 2nd server. The first one prabably has every thing checked or opened up trying to figure it out.

Other thing you may try if having issues it to make sure the Distributed Transaction Coordinator is running as network service and not local system. Reinstalling it as a service and rebooting were a few other tips I read while trouble shooting this problem.

Single Level Active Directory Domain

If you are considering giving your Active Directory name just a single level name, don't do it. Just stop and pick a new name. If your already stuck with a single level AD domain, I feel your pain. I see the warning now when I try to fix my problems after the fact.

So whats the big deal? Windows XP and Server 2003 do not update DNS records to a single level domain. They also have issues joining a domain cross subnet. Our biggest problems show up when dealing with more then one subnet.

The main problem is that DNS will be missing records. Domain controllers have lots of integration with DNS. So if one of those is not updating its DNS records, you have a huge mess. Domain controllers failing to replicate was our big issue. Every time we added a domain controller, we ended up rebuilding the DNS records by hand. Adding the new server keys where needed. Most of the time replication would work one way but not the other. Fixing DNS fixed replication.

Over time I have uncovered more documentation and most importantly this registry key:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dnscache\Parameters]"UpdateTopLevelDomainZones"=dword:00000001

I run this on every server just to make sure. Once you run this, you will have to reboot the server so the netlogon process can register the DNS.

You can run this on your workstations if you want them in your DNS too. we opted not to for most of ours.

I believe there is now a group policy setting that sets the same value. But I do not recall where it is. If I find it, I will update this post.

The second big issue we ran into was we could not join the domain from the other subnets. In XP and 2003, Microsoft changed them to use DNS more. The computer could see the domain and tell you that it exists. It would even point out that the SRV records in DNS are correct. But it would fail to join. At some point in the process it sees the single level domain name and used netbios instead of DNS. It would work fine if on the same subnet, but with out a wins server or lmhost file it would fail.

For the longest time, we used a lmhost file to point to the domain. We built it into the default ghost image and it worked great. Then came the network restructure that ended up changing our IP range. Evey one of those file we used in the last several years had to be updated. The symptom was a 10 min login on those computers for existing users on the computer.

Here is a sample of what we put in the lmhost file

10.0.0.1 servername1 #PRE #DOM:doaminname
10.0.0.2 servername2 #PRE #DOM:domainname
10.0.0.1 "domainname \0x1b" #PRE
10.0.0.2 "domainname \0x1b" #PRE

Inside the quotes on the bottom line must be exactly 20 characters or it won't work.After saving the changes to the LMHOSTS file you have to enable NetBIOS over TCP/IP and import theLMHOSTS file.

The correct way to do this would be to set up a wins server. One experiment that I tried was to put one of the domain controllers address as the wins server and it solved our import issue. The only thing we needed a wins server for was to join computers to the domain on a different subnet. By telling the computer that the domain controller is the wins server, it was able to find it and import just fine.

So our work around was to run a registry hack on every server so they update DNS and to put in a fake wins server address that points to our domain controller for workstation importing.

Stop or Disable Terminal Server Logons (Change Logon)

Here is a simple command that I had a hard time finding.

Change Logon /Disable
Change Logon /Enable

It prevents users from logging on to the server that the command was ran on. All new connections will be disabled. Existing connections will not be disconnected. So you can run this with remote users logged in to stop new connections while you wait for people to exit.

You can run that from a terminal session. If you get disconnected, you will have to get physical access to the machine to activate it.

From the website: Enables or disables logons from client sessions, or displays current logon status. This utility is useful for system maintenance.

http://technet2.microsoft.com/WindowsServer/en/Library/15a2f502-1fa3-4d3b-a74b-cec04ee52d061033.mspx?pf=true