I had created a SQL Server docker container instance previously and was able to successfully log in. I removed that image and was migrating the code from a docker run command into a docker-compose with a backed Dockerfile.

I have the ENV MSSQL_SA_PASSWORD set in the dockerfile but, now, when I try and log in, it gives me an error: Login failed for user 'sa'". It seems like it is caching it somewhere and I no longer remember what that password was.

How do I remedy this and have the container only use the password set in the Dockerfile?

FROM mcr.microsoft/mssql/server:2022-latest

COPY source/database/scripts /usr/src/app

ENV MSSQL_SA_PASSWORD "MyPassword1"
ENV ACCEPT_EULA Y

# Run SQL Server process
ENTRYPOINT [ "/opt/mssql/bin/sqlservr" ]

Thanks

I had created a SQL Server docker container instance previously and was able to successfully log in. I removed that image and was migrating the code from a docker run command into a docker-compose with a backed Dockerfile.

I have the ENV MSSQL_SA_PASSWORD set in the dockerfile but, now, when I try and log in, it gives me an error: Login failed for user 'sa'". It seems like it is caching it somewhere and I no longer remember what that password was.

How do I remedy this and have the container only use the password set in the Dockerfile?

FROM mcr.microsoft/mssql/server:2022-latest

COPY source/database/scripts /usr/src/app

ENV MSSQL_SA_PASSWORD "MyPassword1"
ENV ACCEPT_EULA Y

# Run SQL Server process
ENTRYPOINT [ "/opt/mssql/bin/sqlservr" ]

Thanks

• sql-server
• docker-compose
• dockerfile

Share Improve this question Follow asked Mar 8 at 19:34 FleaFlea 1,54033 gold badges1919 silver badges4343 bronze badges 3

• Additional notes: I can log into the container and run a SQLCMD command, using the password that was set in the Dockerfile. Its when I try and connect with the SA credentials in SSMS from my local machine that I get the Login failed for user 'sa'" message. – Flea Commented Mar 8 at 20:57
• 1 You might try with something more complex than MyPassword1 - hopefully that's just a dumbed-down example. Next, if you use special characters like $ in your password, you need to escape them. See "How to create a password" here. – Aaron Bertrand Commented Mar 9 at 1:35
• That was the problem. I had special characters in the MSSQL_SA_PASSWORD variable that I hadn't escaped. And, yes, the one in the sample above was just dumbed down for the sample. Thank you! – Flea Commented Mar 9 at 2:06

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

The three most common issues with the sa password in Docker:

• The command used SA_PASSWORD instead of MSSQL_SA_PASSWORD. The latter is the correct argument, Microsoft just likes to keep us on our toes.

• It's not complex enough (see rules here and here) - though in your case you would have had trouble starting the container at all.

• It is complex enough, but it contains special characters that require escaping. When you pass via a string the character $ at the command line, for example, you need to escape it ($$). Or just avoid non-alphanumeric characters that are "special." Underscore can be a good alternative to $. I talk about this in the section "How to create a password" here:

  • Best Practices for Docker to run SQL Server on a Mac
  • In spite of the title, this section is not just for Mac

I am not a docker-master, but I remember that SQL Server supports different methods of authentication and "If you select Windows Authentication during setup, the setup creates the sa account for SQL Server authentication but it's disabled" Please have a look https://learn.microsoft/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver16