Can't connect to sql server in Dockerfile or Docker-Compose

[marshallw]

Hello! I'm trying to set up an integration test environment with the the mssql image, and i'm having a lot of difficulty restoring a database backup after spinning up a mssql docker container. Virtually everything I try just results in a timeout connecting to the sql server with sqlcmd. I've tried setting the -S parameter to localhost, localhost,1433, 127.0.0.1,1433, 127.0.0.1, 0.0.0.0, 0.0.0.0,1433 and integrationtest.

For reference, I am using the mcr.microsoft.com/mssql/server image.

Here's the docker-compose file I tried creating:

  db:
    image: mcr.microsoft.com/mssql/server
    container_name: integrationtest
    user: root
    volumes:
      - ./integrationtests:/backup
    command: 
      |
      /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P Test.Password.1 -Q "EXEC sp_configure 'contained database authentication', 1; RECONFIGURE; RESTORE DATABASE Catalogs_IntegrationTest FROM DISK = '/backup/Catalogs_IntegrationTest.bak' WITH MOVE 'Catalogs_IntegrationTest_Data' TO '/var/opt/mssql/data/Catalogs_IntegrationTest_Data.mdf', MOVE 'Catalogs_IntegrationTest_Log' to '/var/opt/mssql/data/Catalogs_IntegrationTest_Log.ldf'" 
    ports: 
      - 1433:1433
    environment:
      ACCEPT_EULA: Y
      MSSQL_SA_PASSWORD: Test.Password.1
    healthcheck:
        test: /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P Test.Password.1 -Q 'SELECT 1' || exit 1
        interval: 10s
        retries: 10
        start_period: 10s
        timeout: 3s

Here are the logs from running this:

This container is running as user root.

To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.

/opt/mssql/bin/permissions_check.sh: line 4: [: : integer expression expected

/opt/mssql/bin/permissions_check.sh: line 59: [: : integer expression expected

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I also tried just building a docker image with the database already set up, but I ran into the same issue. In this one I copy over the backup file, start and wait for the sqlservr service then run my command. Same issue; Login timeout expired. (I just noticed I forgot the command to kill sqlservr, which i trimmed off while troubleshooting)

FROM mcr.microsoft.com/mssql/server:2022-latest
COPY Catalogs_IntegrationTest.bak /var/opt/mssql/data/Catalogs_IntegrationTest.bak

USER root
RUN chown -R mssql:root /var/opt/mssql/data
USER mssql

ENV ACCEPT_EULA=Y
ENV MSSQL_SA_PASSWORD=Test.Password.1

ARG pass="Test.Password.1"
ARG server="127.0.0.1"
ARG user="SA"

RUN ( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started" && ( echo "SQLServer started" ) || ( echo "SQLSERVER failed to start" && exit )
RUN /opt/mssql-tools/bin/sqlcmd -S ${server} -U ${user} -P ${pass} -Q "EXEC sp_configure 'contained database authentication', 1; RECONFIGURE; RESTORE DATABASE Catalogs_IntegrationTest FROM DISK = '/var/opt/mssql/data/Catalogs_IntegrationTest.bak' WITH MOVE 'Catalogs_IntegrationTest_Data' TO '/var/opt/mssql/data/Catalogs_IntegrationTest_Data.mdf', MOVE 'Catalogs_IntegrationTest_Log' to '/var/opt/mssql/data/Catalogs_IntegrationTest_Log.ldf'" 

Any ideas on what I'm doing wrong here? Thanks in advance!

[marshallw]

I'll also add that If I remove any sqlcmd commands, let the image start up, I can connect to it after startup is complete. It seems to be during building or composing that I'm having issues.

[tracker1]

You kind of need 2 containers.. the first for the sql server, the second to restore from backup command.

Also, you should do a volume mount to /backup inside your sql server container... this will let you access the backup file from "inside" sql, which is needed...