mssql-docker
mssql-docker copied to clipboard
microsoft
Why I can't login SA with docker
Hello im trying to install Sql server in Docker to can use it in my Mac. but since 2 days ago I can not fix this error, I've noticed is a popular issue but I don't know how to solve it. someone can help me? before of all Thank you!
MacBook-Air-de-Joel:~ joelgarcia$ docker run --name sql_server_demo -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Jko3va-D9821jhsvGD'. -e 'MSSQL_PID=Enterprise' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest d4848181f0a171f0b1b29cc59489961432894016c3ac9f0304d180a66c7343e6 MacBook-Air-de-Joel:~ joelgarcia$ mssql -s localhost -u sa -p Jko3va-D9821jhsvGD Connecting to localhost... Error: Login failed for user 'sa'.
I also tried with this
MacBook-Air-de-Joel:~ joelgarcia$ sudo docker exec -it sql_server_demo /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Jko3va-D9821jhsvGD Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..
Have you tried a different password? Try something relatively simplistic like Banana100 Also when connecting on a macbook try using 127.0.0.1 instead of localhost.
it doesn't work, I've changed more than 30 times the password.
MacBook-Air-de-Joel:~ joelgarcia$ docker run --name sql_server_demo -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Banana100'. -e 'MSSQL_PID=Enterprise' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest a0a1a0fe5e3946f36f747a24f43bb8f6372529391b9a153b799eb699f27480a0
MacBook-Air-de-Joel:~ joelgarcia$ mssql -s 127.0.0.1 -u sa -p Banana100 Connecting to 127.0.0.1... Error: Login failed for user 'sa'.
MacBook-Air-de-Joel:~ joelgarcia$ sudo docker exec -it sql_server_demo /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P Banana100 Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..
I have the same problem. On my Windows 10 Pro (v 1803) I created my container from a powershell prompt:
docker run -d -p 1433:1433 --name sql1 -e sa_password=SecurePassword -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer:2017-latest
Afterward, the following command works:
docker exec -it sql1 sqlcmd -S localhost
The above gives me access as "User Manager\ContainerAdministrator". However, the following command fails with "Login failed for user 'sa'":
docker exec -it sql1 sqlcmd -S localhost -U sa -P SecurePassword
I tried omitting the "-P SecurePassord" portion, so that it would interactively request the password. When I enter the password interactively, it still does not work. I have also tried a "docker rm" of the container, to start fresh. The second time around with "docker run" I put the password in quotes -e "sa_password=SecurePassword". Still didn't work.
Incidentally, I have yet to find any MS documentation online that explains the "ContainerAdministrator" account and why it requires no password. Elsewhere I read that the 'sa' account is disabled by default for SQL Server. Perhaps that is the problem in this Docker context? I've not yet seen any documentation online, from any source, that explains how to enable the 'sa' account in a Docker container.
Here is my "docker info":
Containers: 1 Running: 1 Paused: 0 Stopped: 0 Images: 1 Server Version: 18.03.1-ce Storage Driver: windowsfilter Windows: Logging Driver: json-file Plugins: Volume: local Network: ics l2bridge l2tunnel nat null overlay transparent Log: awslogs etwlogs fluentd gelf json-file logentries splunk syslog Swarm: inactive Default Isolation: hyperv Kernel Version: 10.0 16299 (16299.431.amd64fre.rs3_release_svc_escrow.180502-1908) Operating System: Windows 10 Pro OSType: windows Architecture: x86_64 CPUs: 4 Total Memory: 15.84GiB Name: PHXL1000009 ID: TYSY:KAL5:IJHZ:HKIE:KL57:RXGU:C5YC:2JRK:P4V7:2E3M:TBTC:6FYX Docker Root Dir: C:\ProgramData\Docker Debug Mode (client): false Debug Mode (server): true File Descriptors: -1 Goroutines: 35 System Time: 2018-05-29T13:45:12.4098754-07:00 EventsListeners: 1 Registry: https://index.docker.io/v1/ Labels: Experimental: false Insecure Registries: 127.0.0.0/8 Live Restore Enabled: false
I think I found the problem for both myself and @joelGarcia93
Password Complexity
In my case, the password "SecurePassword" does not pass complexity validation. Instead of receiving an error, the container starts without any sign of an error. However, I can then no longer login as SA. I stopped and removed my container, then re-created it with "docker run" but this time I changed the password to "1Secure*Password1". Now having a complexity-valid password, finally I am able to log in as SA.
Environment Variable Name
In Joel's case, I believe he is using an incorrectly named environment variable. Instead of:
-e 'MSSQL_SA_PASSWORD=Jko3va-D9821jhsvGD'
I believe it should be:
-e 'SA_PASSWORD=Jko3va-D9821jhsvGD'
Conclusion
If the the SA password is not specified, or if it does not pass a complexity test - the "docker run" command will not provide any indication that there is a problem. Perhaps this is where the real problem is?
Cheers!
at least for me, It doesn't work yet :(! I changed the Password as @brentarias said and also I changed SA_PASSWORD instead MSSQL_SA_PASSWORD.
MacBook-Air-de-Joel:~ joelgarcia$ docker run --name sql_server_demo -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=1Secure*Password1'. -e 'MSSQL_PID=Enterprise' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest 3c970aad439fc3ea765203f151bca77bb56ff1d49ab167c3301af7dd27c7a7f7
MacBook-Air-de-Joel:~ joelgarcia$ mssql -s localhost -u sa -p 1Secure*Password1 Connecting to localhost... Error: Login failed for user 'sa'.
MacBook-Air-de-Joel:~ joelgarcia$ docker exec -it sql_server_demo opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 1Secure*Password1 Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..
some advice? thanks
@joelGarcia93 - I tried your exact commands via copy/paste and got the same error on my macbook. I tried various combinations of things and ultimately got it to work by removing the MSSQL_PID env var. Ah hah! I thought I must have found a bug, but when I added it back to be sure, it still worked with the MSSQL_PID env var. Then I noticed what I think is the actual problem you are having. There is a sneaky little period after your password environment variable that is for some reason throwing everything off.
Your original command:
docker run --name sql_server_demo -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=1Secure*Password1'. -e 'MSSQL_PID=Enterprise' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest
Should be:
docker run --name sql_server_demo -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=1Secure*Password1' -e 'MSSQL_PID=Enterprise' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest
Output on my mac:
$ docker run --name sql_server_demo -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=1Secure*Password1' -e 'MSSQL_PID=Enterprise' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest
a340c909afafad73571029674603b973fefab5a141780ee9cc02ed11557d5bb4
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
a340c909afaf microsoft/mssql-server-linux:2017-latest "/bin/sh -c /opt/mss…" 19 seconds ago Up 1 second 0.0.0.0:1433->1433/tcp sql_server_demo
$ docker exec -it sql_server_demo /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 1Secure*Password1
1>
Please confirm and hopefully we can close this one out! :)
@twright-msft Thank you so much, now it works like a charm!! Im gonna continue working with .Net Mvc Framework with Sql server.
You are very kind, Thank you so much!!
@twright-msft Hi, I tried to run the same command but it failed for me.
C:\Users\mulchr>docker run --name sql1 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=1Secure*Password1' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest
312fd83bc65cc8eaa432355c70f0bf4061cd38045c075f3c747cd9d6acaaa561
C:\Users\mulchr>docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 1Secure*Password1
Error response from daemon: Container 312fd83bc65cc8eaa432355c70f0bf4061cd38045c075f3c747cd9d6acaaa561 is not running
C:\Users\mulchr>docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
312fd83bc65c microsoft/mssql-server-linux:2017-latest "/opt/mssql/bin/sq..." 21 seconds ago Exited (1) 12 seconds ago sql1
Could you please help me in this? I don't know why the container exits immediately.
Couple of things to try -
- A different password - no symbols, just uc/lc letters and numbers
- run
docker logs sql1
In my case I was running into the same thing but just with docker-compose. Turns out the the environment vars need to be quoted to handle special chars. Newbie mistake :)
mssql: image: mcr.microsoft.com/mssql/server:latest ports: - "1433:1433" environment: - "ACCEPT_EULA=Y" - "SA_PASSWORD=P@ssword66" - "MSSQL_PID=Express" networks: - aspnetcore-network
I met this issue too. And I soled it. Juse saw the logs:
docker logs mssql ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..
To change a password and use SQL Server password policy.It works.
container
Thanks for the suggestion. Worked in my case regarding password complexity part
Hi there
I'm having issues with my SA user and password that I created. according to the rules, the password should be valid. Here is a printscreen regarding what I have done in powershell:
Any ideias ?
I found it.
looks like it the " cannot be used to represent the password string. it should be used the ' !
Under Windows
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Manager2017" -e "MSSQL_PID=Enterprise" -p 1433:1433 --name mssql -d mcr.microsoft.com/mssql/server:2017-latest
pay attention to doube " for the password, the single ' does not work when I login.
Under Mac no issue with single '.
The problem is the password strangeness. I tried the @brentarias method and it worked!
Try:
sudo docker start 'sql1'
It is like the image is there and ready to run, you might only need to start the image.
sudo docker run --name honeywell --hostname SQL --network="docker-network" --ip=172.18.0.8 -p 1434:1433 -e 'sa_password=Xkp7VmE6YHVDM' -e 'ACCEPT_EULA=Y' -d microsoft/mssql-server-linux:latest
docker inspect -f "{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" honeywell 172.18.0.8
8861458b9fe02f6fc35e445ec1813965e0a8b11fcae57ccd5b7a57167787fd9a
sudo docker exec -it honeywell /opt/mssql-tools/bin/sqlcmd - S 172.18.0.8 -U sa -P Xkp7VmE6YHVDM Sqlcmd: ' ': Unknown Option. Enter '-?' for help.
sudo docker run --name honeywell --hostname SQL --network="docker-network" --ip=172.18.0.8 -p 1434:1433 -e 'sa_password=Xkp7VmE6YHVDM' -e 'ACCEPT_EULA=Y' -d microsoft/mssql-server-linux:latest docker inspect -f "{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" honeywell 172.18.0.8 8861458b9fe02f6fc35e445ec1813965e0a8b11fcae57ccd5b7a57167787fd9a sudo docker exec -it honeywell /opt/mssql-tools/bin/sqlcmd - S 172.18.0.8 -U sa -P Xkp7VmE6YHVDM Sqlcmd: ' ': Unknown Option. Enter '-?' for help.
went inside the docker container
docker exec -it honeywell bash /opt/mssql-tools/bin/sqlcmd - S 172.18.0.8 -U sa -P Xkp7VmE6YHVDM /opt/mssql-tools/bin/sqlcmd - S localhost,1432 -U sa -P Xkp7VmE6YHVDM /opt/mssql-tools/bin/sqlcmd - S 127.0.0.1 -U sa -P Xkp7VmE6YHVDM**
nothing worked
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'SA'.
Try with this: sudo docker exec -it honeywell /opt/mssql-tools/bin/sqlcmd -U sa -P 'Xkp7VmE6YHVDM'
You don't have to specify the server (-S) when connecting to the local host SQL Server instance. However, you have do it when you are connecting remotely. Another option to avoid the password issue, is to set the SQLCMDPASSWORD environment variable within your container, something like this: SET SQLCMDPASSWORD=Xkp7VmE6YHVDM. So the next time you connect, you don't need to specify the password, something like this:
sudo docker exec -it honeywell /opt/mssql-tools/bin/sqlcmd -U sa
Good luck!
@Hal1987 Just checking, was your issue solved with the example above? If so, I think this issue can be closed :)
It seems like if the SA password isn't set or is set to something invalid, this should fail the container creation. Is this not a bug, even though there is a workaround?
My issue was resolved after stopping the SQL Server instance (and its services) running on the host.
I forgot to delete the <> sign while creating the password. So when I added this sign, I was able to log in.
For me above resolutions did not work, @damian5996 solution works well for me!
For someone who get this problem 2022, if I didn't add the following with MSSQL_PID I could not login.
docker run -d --name database_name-e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=MyPassword123' -e "MSSQL_PID=Developer" -p ${PORT}:1433 mcr.microsoft.com/mssql/server
I have the same error when tried to pass -P "anything" (with double quotes).
For someone who get this problem 2022, if I didn't add the following with MSSQL_PID I could not login.
docker run -d --name database_name-e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=MyPassword123' -e "MSSQL_PID=Developer" -p ${PORT}:1433 mcr.microsoft.com/mssql/server
Still doesn't work for me ;(
This is my terminal, if i could get any help
`still getting same error, after having tried multiple times.If anyone could identify whats wrong from my terminal below🥲
mac@Ghosts-MacBook-Pro ~ % docker images REPOSITORY TAG IMAGE ID CREATED SIZE mcr.microsoft.com/mssql/server 2019-latest 9ab26f8e5685 5 weeks ago 1.61GB mac@Ghosts-MacBook-Pro ~ % docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES mac@Ghosts-MacBook-Pro ~ % sudo npm install -g sql-cli Password: npm WARN config global --global, --localare deprecated. Use--location=global` instead. npm WARN deprecated [email protected]: The sprintf package is deprecated in favor of sprintf-js. npm WARN deprecated [email protected]: core-js@<3.23.3 is no longer maintained and not recommended for usage due to the number of issues. Because of the V8 engine whims, feature detection in old core-js versions could cause a slowdown up to 100x even if nothing is polyfilled. Some versions have web compatibility issues. Please, upgrade your dependencies to the actual version of core-js.
changed 48 packages, and audited 49 packages in 6s
1 package is looking for funding run npm fund for details
found 0 vulnerabilities mac@Ghosts-MacBook-Pro ~ % docker run --name GHOST -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=1Unitedrange22’ -e 'MSSQL_PID=Developer’ -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest 5db465b250a8da5e47db009efa694dec025dd30cd0641fd5df84eb062774c424 mac@Ghosts-MacBook-Pro ~ % mssql -u sa -p 1Unitedrange2022 Connecting to localhost... Error: Login failed for user 'sa'.`