MSDTC configuration issues with SQL Server in Docker (Linux) and Windows Host

Question

I'm migrating a local SQL Server development database to run in a Linux docker container (on the same dev machine). When running my integration tests in Visual Studio 2019 on Windows, I receive MSDTC errors:

Exception thrown: 'System.Transactions.TransactionManagerCommunicationException' in System.Data.dll An exception of type 'System.Transactions.TransactionManagerCommunicationException' occurred in System.Data.dll but was not handled in user code Communication with the underlying transaction manager has failed.

Here's my latest iteration of SQL Server in my docker-compose:

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: SqlServer
    restart: always
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=verySecretPassword
      - MSSQL_RPC_PORT=13500
      - MSSQL_DTC_TCP_PORT=51000
    ports:
      - "1401:1433"
      - "135:13500"
      - "51000:51000"
    volumes:
      - sqldata:/var/opt/mssql

I've tried all sorts of ways to adjust the RPC port to get this working. This is the main MS article. I've tried port 135:135 but it gives the same error. The note in the article at the bottom appears to be related to my issue.

For SQL Server outside of a container or for non-root containers, a different ephemeral port, such as 13500, must be used in the container and traffic to port 135 must then be routed to that port. You would also need to configure port routing rules within the container from the container port 135 to the ephemeral port.

Also, if you decide to map the container's port 135 to a different port on the host, such as 13500, then you have to configure port routing on the host. This enables the docker container to participate in distributed transactions with the host and with other external servers.

SQL Server 2019 containers run as a non-root user. I've tried port routing using netsh in windows... and also the MS article links to how to perform port forwarding in Ubuntu... which I'm unable to do even when logged in as root in the SQL Server container... iptables is not installed, and it doesn't let me apt-get install it?? I also updated the DTC options in windows to make it as open as possible, but it had no effect. Not sure what the secret sauce is. Hoping someone else has a similar setup that works.

Bordy

Answer 1

Thanks for the tip on msdtc config, I got mine working with this compose:

version: '3.4'

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
    container_name: sqlserver
    user: root
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=[yourPwd]
      - MSSQL_RPC_PORT=135
      - MSSQL_DTC_TCP_PORT=51000
    ports:
      - "1433:1433"
      - "135:135"
      - "51000:51000"
    volumes:
      - D:\DockerVolumes\sqlserver:/var/opt/mssql/data

Bedesman

Are you certain that DTC is working here? Doesn't the host also bind to port 135 (it's the RPC Endpoint Manager), and thus cannot share it with the container? We're trying to get DTC working here as well. We wrote a simple DTC tester console app that we run on the host. It tries to execute two TransactionScope-wrapped update commands against 2 separate databases in the container. This causes an escalation to a distributed transaction, and it fails with an exception that says it cannot promote the transaction to a distributed one. – Crocked

yeah, this doesn't seem to work for with distributed transactions. Just curious, if anyone has figured a solution for this. thanks. – Drinking