Distributed Transaction Issue for Linked Server in SQL Server 2008

When you run distributed transaction query on linked server in SQL Server 2008 on Windows Server 2008 like:

BEGIN DISTRIBUTED TRANSACTION

SELECT * FROM [SQLVillage].Sales.dbo.CustMaster

then you may get following error message:

OLE DB provider "SQLNCLI10" for linked server "SQLVillage" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SQLVillage" was unable to begin a distributed transaction.

Intent of this article to guide you how to resolve this issue. You might have already resolved several times similar issue when SQL Server running Windows Server 2003 using following steps using Component Services (Start -> Program -> Administrative Tools -> Component Services)

1. Expand Computers
2. Right Click My Computer and open Properties Page
3. GO to MSDTC tab and Click on Security Configuration that will open following page:

In above page (Security Configuration), make sure Network DTC Access, Allow Inbound and Allow Outbound are checked. You may not need to turn on Allow Remote Clients.

4. Click on OK on Security Configuration page  and then Click on OK on My Computer Properties page
5. Above steps will restart MSDTC service

Now Problem that you may have in doing same settings on Windows Server 2008 machine because steps are little different for getting Security Configuration page for MSDTC but not difficult if you follow following steps. First of all lets open Component Services screen (Start -> Program -> Administrative Tools -> Component Services)

1. Expand Computers
2. Expand Distributed Transaction Coordinator
3. Right Click on Local DTC

4. Click on Properties and go to Security tab as shown below:

In above page (Security Configuration), make sure Network DTC Access, Allow Inbound and Allow Outbound are checked. You may need to turn on Allow Remote Clients.

5. Now Click on OK
6. Above steps will restart MSDTC service

Now you will not have any issue in running same query that resulted error in beginning of this article.
Summary
Method are same to resolve Distributed transaction issue on Linked Server whether you are using Windows Server 2003 or Windows Server 2008 but options are available at different places under Component Services.