1

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?

migrated from serverfault.com Jul 10 '14 at 6:02

This question came from our site for system and network administrators.

  • Would you like us to migrate this question to DBA.Stackexchange.com? It might get more love there. – Katherine Villyard Jul 9 '14 at 20:07
2

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.

  • Interesting, though I was aware of the way it supported both native MSDTC APIs and XA. Doesn't really address the question of how MS-SQL securely gives MSDTC the ability to rollback/commit transactions without giving everything else running as NETWORKSERVICE access to the database too, though. – Craig Ringer Jul 10 '14 at 10:48
  • @CraigRinger Well, to me it looks like the MSDTC does not need that ability. The RM(SQL Server) is the one doing the actual sql rollback/commit when it gets a call in it's implementation of XaSwitch. And I guess SQL Server could internally do the rollback/commit of the prepared transaction without the need of any credentials from a client. Same thing for the OLE Transactions. On recovery SQL Server reenlists the prepared transaction checks the DTC if it needs to rollback or commit and then SQL Server does what needs to be done. – Mikael Eriksson Jul 10 '14 at 10:57
  • Even if it's an OLE/COM interface between the MS SQL Server process and MSDTC.exe, it has to be authenticated in some way - right? Otherwise any other process running as NETWORKSERVICE (alongside MSDTC.exe) could access the same interface and abort transactions. – Craig Ringer Jul 10 '14 at 14:24
  • @CraigRinger looks like it, at least for XA. Not for OLE since the communication is started from the RM in that case. – Mikael Eriksson Jul 10 '14 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.