sql-server - 有时不会创建 MSSql 登录

Question

我正在创建一个脚本，它在 Docker 中启动 MSSQL 服务，并在短暂延迟后创建数据库和用户来访问这些数据库。它通常可以正常工作，但有时它不会创建登录名（但仍会创建数据库）。我的 DockerFile 包含以下内容：

FROM mcr.microsoft.com/mssql/server:2019-latest

USER root
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

COPY . /usr/src/app

RUN chmod +x /usr/src/app/Scripts/Docker/run-initialization.sh

ENV MSSQL_PASSWORD Pass1234
ENV SA_PASSWORD Pass1234
ENV ACCEPT_EULA Y
ENV MSSQL_PID Express

CMD /bin/bash ./entrypoint.sh

入口点.sh： /usr/src/app/Scripts/Docker/run-initialization.sh & /opt/mssql/bin/sqlservr

运行初始化.sh：

sleep 30s
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_PASSWORD -d master -i /usr/src/app/Scripts/Database/create-local-databases.sql

（很多文章和微软本身都建议睡眠作为在mssql服务器启动后应用一些脚本的方法）

最后 create-local-databases 包含以下内容：

CREATE DATABASE [test];
GO
CREATE DATABASE [test2];
GO
CREATE LOGIN TestUser WITH PASSWORD = 'Pass1234';
GO
CREATE USER TestUser FOR LOGIN TestUser;
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [TestUser];

在 docker 容器中，我找到了这些日志：

2021-10-05 11:41:55.78 Logon       Error: 18456, Severity: 14, State: 38.
2021-10-05 11:41:55.78 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'test'. [CLIENT: 172.17.0.1]
2021-10-05 11:42:05.88 Logon       Error: 18456, Severity: 14, State: 38.
2021-10-05 11:42:05.88 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'test'. [CLIENT: 172.17.0.1]
2021-10-05 11:42:06.30 spid74      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2021-10-05 11:42:06.30 spid74      Starting up database 'test'.
2021-10-05 11:42:06.35 spid74      Parallel redo is started for database 'test' with worker pool size [4].
2021-10-05 11:42:06.38 spid74      Parallel redo is shutdown for database 'test' with worker pool size [4].
2021-10-05 11:42:06.56 spid74      Setting database option READ_COMMITTED_SNAPSHOT to ON for database 'test'.
2021-10-05 11:42:06.71 spid74      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2021-10-05 11:42:06.71 spid74      Starting up database 'test'.
2021-10-05 11:42:06.77 spid74      Parallel redo is started for database 'test' with worker pool size [4].
2021-10-05 11:42:06.80 spid74      Parallel redo is shutdown for database 'test' with worker pool size [4].
2021-10-05 11:42:13.13 Logon       Error: 18456, Severity: 14, State: 38.
2021-10-05 11:42:13.13 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'test2'. [CLIENT: 172.17.0.1]
2021-10-05 11:42:23.19 Logon       Error: 18456, Severity: 14, State: 38.
2021-10-05 11:42:23.19 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'test2'. [CLIENT: 172.17.0.1]
2021-10-05 11:42:23.53 spid74      [6]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2021-10-05 11:42:23.53 spid74      Starting up database 'test2'.
2021-10-05 11:42:23.58 spid74      Parallel redo is started for database 'test2' with worker pool size [4].
2021-10-05 11:42:23.61 spid74      Parallel redo is shutdown for database 'test2' with worker pool size [4].
2021-10-05 11:42:23.80 spid74      Setting database option READ_COMMITTED_SNAPSHOT to ON for database 'test2'.
2021-10-05 11:42:23.94 spid74      [6]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2021-10-05 11:42:23.95 spid74      Starting up database 'test2'.
2021-10-05 11:42:24.00 spid74      Parallel redo is started for database 'test2' with worker pool size [4].
2021-10-05 11:42:24.04 spid74      Parallel redo is shutdown for database 'test2' with worker pool size [4].

我在最顶部看到授权错误，但我不明白为什么如果没有完成授权，数据库会正常创建，为什么授权有时不起作用，但在其他情况下却起作用。我唯一的考虑是，由于某种原因，登录创建发生在基础数据库（或类似的数据库）启动之前，而 dbs 创建发生在之后。但它们是以另一种顺序编写的。此外，如果我在创建本地数据库后登录并复制粘贴上面脚本的登录创建部分，它可以完美运行。任何帮助将非常感激。

标签： sql-serverdockerpowershell