Can't create db and run scripts in Dockerfile

[Edgaras91]

Describe the bug
When trying to create a docker image of SQL server + schema using grate linux tool, it gives this error:

2025-11-10 16:46:05.77 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'xxx'. [CLIENT: 127.0.0.1]
2025-11-10 16:46:15.79 Logon       Error: 18456, Severity: 14, State: 38.
2025-11-10 16:46:15.79 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'xxx'. [CLIENT: 127.0.0.1]

The key in the error is: Error: 18456, Severity: 14, State: 38.
Couldn't find database requested by user.
The grate command in docker file:

RUN /opt/mssql/bin/sqlservr & \
sleep 10; \
 for dbdir in $(ls -d /tmp/db/* | sort -r); do \
 dbname=$(basename "$dbdir"); \
        echo "Using connection string: Server=localhost;User ID=sa;Password=$SA_PASSWORD;Database=$dbname;TrustServerCertificate=True;Encrypt=false"; \
        /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "${SA_PASSWORD}" -C -Q "SELECT 1"; \
          echo "Connection test completed. Running Grate now."; \
    /tmp/grate \
            --adminconnectionstring "Server=localhost;User ID=sa;Password=$SA_PASSWORD;Database=master;TrustServerCertificate=True;Encrypt=false" \
            --connectionstring "Server=localhost;User ID=sa;Password=$SA_PASSWORD;Database=$dbname;TrustServerCertificate=True;Encrypt=false" \
            --files "$dbdir" \
            --output "/tmp/rh_output_$dbname" \
            --env "DEV" \
            --noninteractive \
            --schema=RoundhousE; \
    done  \
    && wait || true

Key takeaway:

• Using --adminconnectionstring to master, which successfully created the databases
• Using --connectionstring which impatiently tries to run the scripts, but fails with the above error

In same docker file, if I do "wait 30" at the end, and duplicate the code again, it will:

• Not try to re-create
• It will successfully run roundhouse scripts to update the database

To Reproduce
In Dockerfile, try to create db and run scripts

Expected behavior
Create DB and run the scripts

Desktop (please complete the following information):

• OS: Linux
• Version: 1.8.0
• Package: grate-linux-x64-self-contained-1.8.0.zip
• Base image: mcr.microsoft.com/mssql/server:2019-latest

[wokket]

Thanks for the report Edgaras91. If you remove the -adminconnectionstring does it run correctly (as you're using sa creds for both)? Are you trying to test the connection to $dbname before grate has been run to create it?

[Edgaras91]

Tried it now, it gives the same error, but does create empty databases too. I guess -adminconnectionstring is redundant in my case.

[Edgaras91]

I've been trying hard to fix it/find a workaround, but a simple "retry" to run the command is not working, because I believe when a database is created, part of that grate tries to run "runAfterCreateDatabase" scripts, which fails. Trying to run grate script again when the database is online fails, because it notices that the database already exists, and doesn't try to run "runAfterCreateDatabase" scripts.

If it's not already in place, could some sort of "wait" check be added in Grate, something like:
SELECT state_desc FROM sys.databases WHERE name=N'$dbname' to look for state_desc = "ONLINE" after a database is created?
I suspect this is an issue in Docker only, because it has limited resources, and database creation takes a bit longer than on a normal OS.

[Edgaras91]

Found a workaround:
Add below script in "createDatabase" folder to wait for 5 seconds:

WAITFOR DELAY '00:00:5';
SELECT 'Query completed after 5 seconds' AS Message;

For example, I have 2 files now:
CreateDB.sql
CreateDB_wait.sql <= workaround
This prints the logs like this:
#10 30.67 2025-11-12 16:29:31.07 Logon Error: 18456, Severity: 14, State: 38.
2025-11-12 16:29:31.07 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'Test'. [CLIENT: 127.0.0.1]
Running 'CreateDB.sql'.
...Creation messages...
Running 'CreateDB_wait.sql'. <= This hangs for 5 seconds
...the db specific connection opens up successfully and runs all of the update scripts (up folder and others)...

This should prove that we need to add some sort of reliable wait mechanism before we try to use the -connectionstring with the database name.

This also shows that the error (Error: 18456, Severity: 14, State: 38.) is expected, as I believe it checks to see if database exists, before deciding if it needs to run scripts in "createDatabase" folder. It's the next repeated error when it tries to use connectionString that is the problematic one.

[wokket]

Thanks for putting in the effort to confirm it's a timing thing, rather than a connection/transaction etc issue, and it means you have a workaround for the short term.

I'm not sure when I'll get to adding a wait check in grate, if you want to put together a PR please feel free 👍

[Edgaras91]

Spent more time on this, and the issue is that the newly created database will show all signs that it's "Online" and can run Select 1 command under the database creation connection, but new connections (grate makes a second connection directly to the database to run update scripts), that fail. So the "wait" mechanism has to be when attempting to connect to the newly created database.

Maybe default to like 5 seconds retry, and make it configurable?

Personally won't have time to put together a PR, but very tempted.

If someone wants to replicate the issue, this is the full working dockerfile, that requires your database scripts to be in "DB" folder:

FROM mcr.microsoft.com/mssql/server:2019-latest
ARG SA_PASSWORD
ARG GRATE_VERSION=1.8.0
ENV ACCEPT_EULA=Y

#Download curl and unzip tools
USER root
RUN apt-get update && apt-get install -y curl unzip

#Download and extract Grate tool
RUN curl -fsSL -o /tmp/grate.zip "https://github.com/erikbra/grate/releases/download/${GRATE_VERSION}/grate-linux-x64-self-contained-${GRATE_VERSION}.zip" \
    && unzip /tmp/grate.zip -d /tmp \
    && chmod +x /tmp/grate

COPY DB/ /tmp/db/

RUN /opt/mssql/bin/sqlservr & \
sleep 10; \
 for dbdir in $(ls -d /tmp/db/* | sort -r); do \
 dbname=$(basename "$dbdir"); \
    /tmp/grate \         
            --connectionstring "Server=localhost;User ID=sa;Password=$SA_PASSWORD;Database=$dbname;TrustServerCertificate=True;Encrypt=True" \
            --files "$dbdir" \
            --output "/tmp/rh_output_$dbname" \
            --env "DEV" \
            --noninteractive \
            --schema=RoundhousE; \
          done \
 && pkill sqlservr || true \
 && wait || true

RUN apt-get remove --purge -y curl unzip && apt-get autoremove -y && apt-get clean

RUN rm -rf /tmp/*

RUN mkdir -p /var/opt/mssql \
 && chown -R mssql:root /var/opt/mssql

USER mssql
# When running the final image, use -p <hostport>:1433 to expose SQL Server.
# If you already running sql server locally, make port <hostport> different from default 1433, like 11433.

[wokket]

Thanks for putting this much effort into a repro, unfortunately I've just spent an hour with it and haven't been able to repro the error.

I'm anything but a docker expert, but I've just altered the existing examples/docker example to use your dockerfile, rename db to DB and while the scripts directory obviously isn't in the format you're expecting, it built and ran the migration just fine: