MSDTC – zagadkowa usługa

MSDTC jest jedną z bardziej tajemniczych usług wykorzystywaną przez między innymi MS SQL server. Administrator Windows woli tego nie dotykać, bo to SQL, a administrator SQL uparcie twierdzi, że to komponent modelu COM, więc Windows…

Ponieważ jednak w przypadku problemów z MSDTC cierpi baza danych czasami trzeba wziąć zadanie w swoje ręce. W moim przypadku uruchamiana była procedura, która miała zmodyfikować dane na serwerze SQL zdalnym. Ta procedura wywoływana była w lokalnym triggerze. Jest to transakcja rozproszona i MSDTC jestt tu konieczne. Polecenia kończyły się błędem:

OLE DB provider „SQLNCLI10” for linked server „XYZ” returned message „The partner transaction manager has disabled its support for remote/network transactions.”.

Msg 7391, Level 16, State 2, Procedure SyncClientList, Line 42 The operation could not be performed because OLE DB provider „SQLNCLI10” for linked server „XYZ” was unable to begin a distributed transaction.

Po piwersze należy sprawdzić czy usługa Distributed Transaction Coordinator jest włączona na obu serwerach.

Po drugie skonfigurować obie usługi do tego, aby ze sobą rozmawiały. W tym celu:

1. Start >> Administrative tools >> Component Services

2. Kliknij prawym na Local DTC i wybierz properties

 

 

 

 

 

3. Skonfiguruj opcje, jak widać na obrazku

 

 

 

 

 

 

 

 

Network DTC Access oznacza, że MSDTC będzie obsługiwał żądania z sieci

Jeśli chcesz zezwolić tylko na obsługę transakcji, a nie zdalną administrację włącz „Allow remote clients”

Określ dopuszczalny sposób komunikacji – Inbound/Outbound

Określ sposób uwierzytelniania transakcji. Na początku zezwoliłem na „No authentication required”, ale potem przełączyłem na bezpieczniejsze roziązanie.

Dokładniejsze znaczenie opcji, skopiowane z helpa poniżej.

 

Po wprowadzeniu zmian MSDTC musiało się zrestartować, ale działo się automatycznie po kliknięciu OK.

 

Security Settings Network DTC Access

Select this check box if you want to allow any network traffic for the Distributed Transaction Coordinator (DTC).

If this check box is not selected, the DTC will not flow any transactions to the network, and it will not accept any incoming traffic. Remote administration of this DTC will also be disabled.

Security Settings  Client and Administration

Allow Remote Clients

: Select this check box if you want this DTC to coordinate transactions for remote clients.

Allow Remote Administration

: Select this check box if you want to allow administration of this DTC from remote computers.

Security Settings Transaction Manager Communication

Allow Inbound

: Select this check box to allow a remote computer to flow transactions to the local computer. Typically, this option is needed on the computer that is hosting the DTC for a resource manager such as Microsoft SQL Server.

Allow Outbound

: Select this check box to allow the local computer to flow transactions to a remote computer. Typically, this option is needed on the client computer, where the transaction is initiated.

Mutual Authentication Required

: If this option is selected, the local DTC (proxy or service) communicates with a remote DTC service using only encrypted messages and mutual authentication (Windows Domain authentication). If a secure communication cannot be established with the remote system, the communication is denied. This option can be used only for communication with computers running Windows Server 2003, Windows XP SP2, Windows Vista, or Windows Server® 2008.

Incoming Caller Authentication Required:

If this option is selected, if mutual authentication cannot be established but the incoming caller can be authenticated, the communication is allowed. This option can be used only for communication with computers running Windows Server 2003 or Windows XP SP2.

No Authentication Required

: If this option is selected, the DTC communication on the network can fall back to a nonauthenticated and nonencrypted communication if the attempts to start a secure communication fail. This option is used primarily to allow the DTC to communicate with computers running Windows 2000, Windows XP SP1, and earlier versions. This setting can also be used if one of the systems has turned off remote procedure call (RPC) security.

Security Settings  Enable XA Transactions

Select this check box to allow transactions that use the XA standard. Resource managers that run on different operating systems can communicate with a DTC transaction manager by using the XA standard.

XA interfaces are a standard set of programming interfaces that allow COM+ application developers to access XA-compliant databases and create resource managers that operate with relational databases, message queuing, transactional files, and object-oriented databases. Although Microsoft does not directly support the XA protocol, Microsoft does support translation facilities between OLE Transactions and XA.

DTC Logon Account

Specify which account the DTC service runs under.

By default, DTC runs under the Network Service account. This account is specifically designed to allow services such as the Distributed Transaction Coordinator service to run with the appropriate set of privileges. To minimize potential security problems, we recommend that you use the default Network Service account.

If you choose to change the default DTC logon account, type the name of another account in Account, or click Browse. Then, type and confirm a password.