SQL Server on Mac with Docker : Login failed for user 'sa'.

Question

I'm trying to work with SQL Server on my Mac with Docker. I have already installed the image and I'm running the SQL Server.

When I run the above code and check the running containers using 'docker ps' the container is always stopped. Therefore I ran the following command to start the container with the container id.

docker start 0e84faabe9b35b2e4e8e39be3f389b38c151a3a6e7c3c82995f3ea6dc0e8ed1e Then the sql container keeps running.

I want to connect to the sql server with VS Code but the message keep saying Login failed for user 'sa' I have installed the sql-cli to check the sql connection but it didn't work as well.

I tried to connect to sql server with the following credentials: mssql -u sa -p sa123! which I given to start the sql container.

Can anyone please help me with this ?

This is what I'm getting in VS Code.

Answer 1

Debugging:

Let's start the container:

[fedora@myhost ~]$ sudo docker run -d -p 1433:1433 -e SA_PASSWORD='sa123!' -e ACCEPT_EULA=Y -i microsoft/mssql-server-linux   
0031f2c49b66596cb8dc8cfec6d14351406fb924e39bd8227dfbcf7e23b67d54

The container is not running :(

[fedora@myhost ~]$ sudo docker ps -a
CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS                      PORTS               NAMES
0031f2c49b66        microsoft/mssql-server-linux   "/bin/sh -c /opt/m..."   21 seconds ago      Exited (1) 15 seconds ago                       festive_meninsky

Let's look at the logs:

[fedora@myhost ~]$ sudo docker logs 0031f2c49b66596cb8dc8cfec6d14351406fb924e39bd8227dfbcf7e23b67d54 
Configuring Microsoft(R) SQL Server(R)...
Microsoft(R) SQL Server(R) setup failed with error code 1. Please check the setup log in /var/opt/mssql/log for more information.

Oh, okay, let's see. Start the container again:

[fedora@myhost ~]$ sudo docker start 0031f2c49b66                                                                                                                                                                  
0031f2c49b66

Is it running now?

[fedora@myhost ~]$ sudo docker ps -a
CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES
0031f2c49b66        microsoft/mssql-server-linux   "/bin/sh -c /opt/m..."   28 seconds ago      Up 1 second         0.0.0.0:1433->1433/tcp   festive_meninsky

Let's go inside and see why it failed the last time:

[fedora@myhost ~]$ sudo docker exec -it 0031f2c49b66 /bin/bash       
root@0031f2c49b66:/# cd /var/opt/mssql/log/
root@0031f2c49b66:/var/opt/mssql/log# ls
HkEngineEventFile_0_131307476136660000.xel  errorlog    errorlog.2  log_1.trc                  system_health_0_131307476143850000.xel
HkEngineEventFile_0_131307476639820000.xel  errorlog.1  log.trc     setup-20170205-055329.log  system_health_0_131307476647330000.xel
root@0031f2c49b66:/var/opt/mssql/log# cat errorlog.1 
...
2017-02-05 05:53:34.65 spid17s     Server setup is starting
2017-02-05 05:53:34.65 spid17s     Error: 33062, Severity: 16, State: 2.
2017-02-05 05:53:34.65 spid17s     Password validation failed. The password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters.
2017-02-05 05:53:34.66 spid17s     An error occurred while setting the server administrator (SA) password: error 33062, severity 16, state 2.
2017-02-05 05:53:34.66 spid17s     An error occurred during server setup. See previous errors for more information.
...

Okay. Let's take a glance at the documentation. Relevant section:

A strong system administrator (SA) password: At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

Hmm, let's start from beginning, keeping above in mind:

[fedora@myhost ~]$ sudo docker run -d -p 1433:1433 -e SA_PASSWORD='$uP3RC0mpl3Xp@$$w0rD' -e ACCEPT_EULA=Y microsoft/mssql-server-linux                                                                             
8fcc46d9ae498948dc3cb458fb3169347fcb4f582dc6266b6569f540f2badf1d

Let's see if the container runs now:

[fedora@myhost ~]$ sudo docker ps -a
CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES
8fcc46d9ae49        microsoft/mssql-server-linux   "/bin/sh -c /opt/m..."   8 seconds ago       Up 6 seconds        0.0.0.0:1433->1433/tcp   competent_kalam

Let's see if a db connection can be made:

[fedora@myhost ~]$ tsql -H localhost -p 1433 -P '$uP3RC0mpl3Xp@$$w0rD' -U sa
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "ISO-8859-1"
1> quit