Cannot connect externally or locally to a SQL Server 2019 image
westerdaled opened this issue · 4 comments
I thought I would create a basic setup of SQL Server 2019 by running a few commands in a PowerShell terminal
docker pull mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<MyPassword>" `
-p 1433:1433 --name sql1 --hostname sql1 `
-d `
mcr.microsoft.com/mssql/server:2019-latest
for the time being I haven't changed the password as recommended
run some checks
docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
b2061de2a836 mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 5 hours ago Up 34 minutes 0.0.0.0:1433->1433/tcp sql1
get the ip address of SQL1
docker inspect --format='{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $(docker ps -aq)
/sql1 - 172.17.0.2
docker exec -it sql1 "bash"
mssql@sql1:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<my pw>'
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'SA'..
lets see if we can check what SQL Server is listening to
ssql@sql1:/$ /opt/mssql/bin/mssql-conf list
Warning: could not create log file for mssql-conf at /var/opt/mssql/log/mssql-conf/mssql-conf.log.
This program must be run as superuser or as a user with membership in the mssql
group.
And my bash shell doesn't know about "sudo", so I am stuck once again!
Oh I get the same depressing results outside the container in
sqlcmd -S 172.17.0.2,1433 -U sa -P '<my pw>'
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: The wait operation timed out.
🥹....hmmmm I am already missing windows.
I thought I would have another attempt at this in PowerShell Core Teminal. Now the docs seem to suggest single quotes but I didn't get good results with this approach
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_PID=Standard" -e "MSSQL_SA_PASSWORD = MYPASSWORD" -e "MSSQL_TCP_PORT=1433"
-p 1433:1433 --name sql2019_2 --hostname sql2019_2 -d mcr.microsoft.com/mssql/server:2019-latest
Looking at the the logs , I seem to have a valid server temp db started and Database 'model' upgraded to 904.
and
Server is listening on [ 127.0.0.1 <ipv4> 1431].
2023-06-26 14:02:29.57 spid27s **SQL Server is now ready for client connections**. This is an informational message; no user action is required.
ok still from my SQL Server terminal
docker exec -it sql2019_2 "bash" /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "MYPASSWORD"
2023-06-26 14:25:29.39 Logon Error: 18456, Severity: 14, State: 7.
2023-06-26 14:25:29.39 Logon Login failed for user 'SA'. Reason: An error occurred while evaluating the password. [CLIENT: 172.17.0.2]
Note the password follows this criteria
The password contains characters from three of the four categories:
Latin uppercase letters: "F", "I", "C"
Latin lowercase letters: "r", "e", "a", "m"
Base 10 digits: "1", "3"
Non-alphanumeric characters: "&", "!"
The password is 14 characters long, which exceeds the minimum requirement.
Same issue here! But using a docker compose file
version: '3.4'
services:
mssql-server:
image: mcr.microsoft.com/mssql/server:latest
user: root #required due to issues with permissions with the mssql image writing data file
environment:
ACCEPT_EULA: "Y"
MSSQL_SA_PASSWORD: "Pa$$word12345678"
MSSQL_PID: Developer
ports:
- "1433:1433"
platform: linux/amd64
Possible solution for some.
This was just updated a couple of days ago and this has been an issue for me. Running this on Ubuntu 20.04 via WSL2. I follow all of these steps and my SA password meets all the requirements. I grep the container logs once it's running and I see the "SQL Server is now ready for client connections". I exec into the container and run the sqlcmd as instructed.
Over and over I get "Login failed for user 'sa'" Extremely frustrating.
It seems you can't use certain special characters when you set up the password. I had a $
and/or !
every time I set up a different password. When I went with this: ThisIsMyP*ssw0rd
. It worked! So don't use bang or dollar sign...I guess?