Changing sample password prevents login

[heaivilin]

Hello Everyone,

So, this is just weird, and I'm really hope I'm just missing something completely obvious.

Working on restoring a database to a SQL Server docker container and was running into issues logging in with SA after adding a volume. Found the 'Configuration Option: Mount a host directory as a data volume' and ran the default sample without any changes. All good. Added a name, and still good. Changing the password, and I get the following error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'SA'..

Running docker logs sql1 and I get the following additional error data:

Login failed for user 'SA'. Reason: Password did not match that for the login provided. [CLIENT: 172.17.0.1]

Here are the commands I've been working with:

Original command (works) docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 --name sql1 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest

Updated Password (no workey): docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<TestMeWorld!Passw0rd>' -p 1433:1433 --name sql1 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest

Validating connectivity with the following command: (with password changes of course) sqlcmd -S localhost,1433 -U SA -P '<YourStrong!Passw0rd>' -Q "select name from sys.databases"

Potential Pertinent Information OS: Ubuntu 18.04.2 LTS Docker: v. 18.09.7

I'm really at a loss here. It makes no sense why changing the password to something nearly identical, would cause it to fail. What am I missing here? Does anyone else face the same issue running the same commands?

Nathan

[minican]

Try to use the original password to test . The following link might offer help for you: Use the persisted data

[heaivilin]

@minican , that's the issue. It works when I use the original password. But when I change the password, it doesn't work. I just can't figure out why.

Nathan

[vin-yu]

@heaivilin - what's likely happen is that your master database files were also preserved from your first docker run command (which also has your original SA password). Your master database files were created under sqlvolume and then reused again during your second docker runcommand. To change the password of an existing instance, you can use T-SQL to achieve this.

[heaivilin]

@vin-yu, that worked!!!! Thank you!

This is very interesting. I's assumed, apparently incorrectly, that those were recreated each time. That isn't the case.

Additionally, I added a new database and then drop and recreated the container, without removing the docker volume, and magically, the new database also persisted.

Now I'm very curious how it knows to attach the db's? Is there some scripts that are run after the master is brought in, that says, "Hey, remember here is all of the databases you previously had attached? Well, let's go and import them for you."

Thoughts?

Nathan

[banerjeeamit]

SQL containers work on a binary install method which when pointed to a persisted volume picks up the same files during startup.