MSDTC problem set: Linked server OLE DB provider "SQLNCLI" failed to start a distributed transaction

Respect the original works: reproduced in this article from http://sfwxw456.blog.163.com/blog/static/631359742009321112120248/

First, the problem phenomenon
When you execute a distributed transaction, you receive the following error under SQL Server 2005:

The OLE DB provider "SQLNCLI" of the linked server "xxxxxxx" returned the message "There is no active transaction." "。

Msg 7391, Level 16, State 2, procedure xxxxx, line 16th

The operation could not be performed because the OLE DB provider "SQLNCLI" of the linked server "xxxxx" could not start the distributed transaction.

You receive the following error under SQL Server 2000:

The operation failed to execute because the OLE DB provider ' SQLOLEDB ' could not start the distributed transaction.

[OLE/DB provider returned message: The new transaction cannot enlist into the specified transaction processor. ]

OLE DB Error tracing [ole/db Provider ' SQLOLEDB ' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Second, the solution
1. Both sides start the MSDTC service (startup mode: Allow net start MSDTC command)
The MSDTC service provides a distributed transaction service that, if you want to use distributed transactions in a database, you must start the MSDTC (Distributed Transaction Coordinator) service on both sides of the participating servers.

2. Open both sides 135 port (Open mode: Start the corresponding service can be)
The MSDTC service relies on the RPC (Remote Procedure call (RPC)) service, RPC uses port 135 to ensure that the RPC service starts, and if the server has a firewall, ensure that port 135 is not blocked by the fire wall.

Use the Telnet IP 135 command to test whether the other port is open to the outside. Port scanning software (such as advanced Port Scanner) can also be used to scan the port to determine if the port is open.

3. Ensure that the statement in the linked server does not have access to the initiating Transaction Server
In a query, view, or stored procedure on a linked server where the server initiating the transaction contains access to the initiating transaction Server, such operations are called loopback (loopback) and are not supported, so ensure that no such operations exist on the linked server.

4. Add the SET XACT_ABORT on statement before the transaction begins
For most OLE DB providers, including SQL Server, you must set the Xact_abort in the data modification statement in the implicit or display transaction to ON. The only case where this option is not required is when the provider supports nested transactions.

5. MSDTC Settings
Open "Component Services-Computer" by opening "Administrative Tools-Component Services" and right-click on "My Computer". On the MSDTC tab, click the "Security Configuration" button.

In the Security Configuration window, make the following settings:

• Select "Network DTC Access"

• In client management, select Allow remote clients to allow remote administration

L Select "Allow Inbound" "Allow Outbound" "do not require authentication" in transaction management communication

L Ensure DTC Login account is: NT AUTHORITY\NetworkService

6. Linked server and name resolution issues
There are two common scenarios for establishing a linked SQL Server server:

L first case, product select "SQL Server"

EXEC sp_addlinkedserver

@server = ' Linkservername ',

@srvproduct = N ' sql Server '

In this case, the @server (linkservername) is the SQL Server name or IP address to be linked.

In the second case, Access interface selected "Microsoft OLE DB Provider sql Server" or "SQL Native Client"

EXEC sp_addlinkedserver

@server = ' Linkservername ',

@srvproduct = ' ',

@provider = ' SQLNCLI ',

@datasrc = ' SQLServerName '

In this case, the @datasrc (SQLServerName) is the actual SQL Server name or IP address to link to.

The SQL Server database engine accesses the linked server through the server name or IP address set above, and the DTC service accesses the linked server through the server name or IP address, so ensure that both the database engine and the DTC can access the linked server through the server name or IP address.

The database engine and the DTC parsing server are not the same way, the following respectively describes

6.1 Database Engine
When the first case of @server or the second case @datasrc is set to an IP address, the database engine accesses the linked server based on the IP address, which does not require name resolution.

The first case of @server or the second case of @datasrc is set to the SQL Server server name, you need to do name resolution, that is, to resolve the server name to an IP address.

There are two ways to parse the server name:

One is to set an alias in the SQL Server client configuration that corresponds to the server name above to the IP address of the linked server.

The second is to add a record in the "C:\WINDOWS\system32\drivers\etc\hosts" file:

xxx.xxx.xxx.xxx Server Name

The function is also the IP address that corresponds to the server name to the linked server.

6.2 DTC
In either case, as long as the @server set the server name instead of the IP address, you need to do name resolution, the method with the second method above, in the Hosts file to add parse records, the first method above the DTC does not work.

If the @server is set to an IP address, the same does not need to do domain name resolution work.

=======================================================================

In fact, to set the MSDTC Security section, allow remote clients this can not be ticked, but the most important is the following step, in the transaction management communication, select "Allow Inbound" "Allow Outbound" "Do not require authentication", in windows2003 the default is the first request to verify the two sides, The situation that I met, changed this to be no more error. In addition, the following login user can only use the network service, and other in SQL Server will say that MSDTC is not available.

MSDTC problem set: Linked server OLE DB provider "SQLNCLI" cannot start a distributed transaction