How does MS SQL Server authenticate MSDTC connections when using SSPI?

Question

I'm trying to get SSPI authentication to play well with two-phase transactions via MSDTC, using PostgreSQL with psqlODBC. I can't figure out how it can work securely - so I'm hoping someone can explain how it works with MS SQL Server.

I'm coming from a PostgreSQL background, so please forgive any major misunderstandings of MS SQL.

The TL;DR is: To complete prepared transaction recovery after an app exits or the server crashes, MSDTC.exe running as NETWORKSERVICE has to be able to connect to the database and commit prepared transactions on behalf of the original user. When authentication was done over SSPI originally I don't understand how that's possible.

Background: SSPI, DTC, ODBC and XA

A user establishes one or more psqlODBC connections to PostgreSQL, and authenticates to PostgreSQL with SSPI. Windows confirms to PostgreSQL that the requested PostgreSQL username is the same as the current Windows username, and access is granted.

The user binds the connections into a DTC session with the MSDTC transaction manager and begins a distributed transaction (which begins a tx on the connections being managed by MSDTC). When they're done with their work on the connections they request a distributed commit on the DTC transaction. This does a two-phase commit - in PostgreSQL, calling PREPARE TRANSACTION on both as phase 1, then when phase 1 confirms success calling COMMIT PREPARED on each as phase 2.

Standard-ish stuff so far, just Microsoft's version of XA.

XA recovery

Part of the point of XA and MSDTC is that if the client crashes after phase 1 commit confirms, the transaction is guaranteed to get committed.

For that to happen something must keep track of outstanding prepared transactions and commit them. That's the job of the MSDTC.exe, the microsoft distributed transaction co-ordinator daemon.

At least with psqlODBC, when an abandoned prepared transaction is recovered, MSDTC loads the XA DLL for psqlODBC and calls xa_recover, which makes a connection to PostgreSQL using the credentials of the user who began the DTC transaction. It then calls COMMIT PREPARED and closes the connection.

MSDTC.exe runs as NETWORKSERVICE. That's the where the problem starts, because those stored credentials are not valid for NETWORKSERVICE if the original connection wasn't also by the user NETWORKSERVICE. SSPI will reject the connection because of a username mismatch.

How does MS SQL handle this?

MS SQL Server is heavily used with SSPI, and MSDTC is designed in part around the needs of MS SQL Server. So presumably, MS SQL Server has a secure way to complete distributed transaction recovery for a connection that was made using SSPI by a user other than NETWORKSERVICE even though msdtc.exe runs as NETWORKSERVICE.

I don't understand how it could, though.

The obvious options are all insecure, like:

• Configure and store credentials in the registry, accessible by NETWORKSERVICE, so msdtc.exe can commit/rollback any user's prepared transactions;

• Trust NETWORKSERVICE to connect to MS-SQL without credentials for the purpose of prepared transaction management;

and in the end, I don't see how MS SQL can prevent other services running as NETWORKSERVICE from messing with its prepared transactions.

Is this just one of those things where people accept somewhat weaker security? You're expected to reasonably trust everything running as NETWORKSERVICE and MS SQL just allows MSDTC to connect without additional checks?

Or is there something I'm missing?

The only way I can think of to do this fairly securely (allowing for the inherent limitations of running as NETWORKSERVICE) is to generate a one-time cookie with each prepared transaction, which cannot be discovered by listing the transactions. Then allow anyone in NETWORKSERVICE to make a database connection that can do nothing except commit/rollback prepared transactions, and only if it knows the transaction's cookie. That seems awfully fiddly though.

Does MS-SQL just assume that NETWORKSERVICE is trusted?

Answer 1

There are two Distributed Transaction Standards Supported by the DTC. XA Transactions and OLE Transactions and recovery is implemented differently.

XA Transaction Recovery Process use xa_recover_entry of GetXaSwitch to get a list of xid from the transaction resource manager (SQL Server is a resource manager) that it then uses to rollback or commit the prepared transactions. I assume that running as NETWORKSERVICE is enough to do that based on what is documented in Disabling TIP, LU and XA Transactions

In Windows XP , Windows Server 2003, and subsequent versions you can disable XA transactions to help prevent the security risk that arises when a user-specified DLL, used by the DTC to communicate with the XA partner's transaction manager, is loaded directly into the DTC process. This situation exposes a resource manager's databases to serious data corruption and can cause Denial of Service (DOS) attacks. The disabling of XA transactions helps to protect the DTC from this DLL attack.

The recovery process for OLE is implemented a bit differently. The resource manager has a list of prepared transactions and is responsible for reenlisting them in the DTC. Have a look at the Performing Recovery section of Implementing DTC Interfaces.

_{Not sure I actually provided a definitive answer for you here but I thought this was too much for a comment.}