Cannot connect with SSMS to SQL Server on Docker

Question

I have followed the official Microsoft documentation and I have installed SQL Server Docker image

As result I have a SQL Server image running on Docker at the IP address 172.17.0.2

I also can easily connect to it using sqlcmd with my dummy password

The problem is that I cannot connect to it through SSMS:

Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

Of course I read other StackOverflow posts before posting this question and I have tried multiple logins:

• localhost,1433
• localhost:1433
• 172.17.0.2,1433
• etc...

How can I connect if localhost doesn't work as well as the IP address of the docker image?

Wellrounded

How are you launching your container? When using docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Your$trongPw" -p 1433:1433 then do not put any quotes around your password. Also note to use double quotes when running in Windows Powershell and single quotes in Linux. When running Express edition you will have to connect to 172.17.0.2,1433\SQLEXPRESS. Any firewall blocking the SSMS connection? – Electrolyse

The error suggests you have connectivity to the container instance. Click the show details in the error dialog to see if the state code provides more info. Also, check the sql server error log in the container for additional info about the failed login attempt: cat /var/opt/mssql/log/errorlog – Condolent

Is the docker instance running on the same computer you are running the sql client? – Percentile

@rfkortekaas, yes, I have SSMS and Docker on the same computer – Wellrounded

Hi @TWP, the command docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=HelloWorld123" -p 1433:1433 -t 3c7ee124fdd6 returned me Error response from daemon: driver failed programming external connectivity on endpoint vibrant_shirley (064f1bfa7b66d68995f76c98cb80594807ac30e9659b35e1f217885dedb9094b): Bind for 0.0.0.0:1433 failed: port is already allocated. – Wellrounded

Answer 1

I'm a little late, but I hope this answer helps someone in the future. Guys, I experienced the exact same problem reported.

What worked for me was connecting as follows:

127.0.0.1\{container_name},1433

I used the following image:

mcr.microsoft.com/mssql/server

With MSSQL_PID Express and ports :

• "1433:1433"
• "1434:1434/udp"

Hereto

This helped me, thanks. – Rollet

The same yaml using mcr.microsoft.com/mssql/server:2019-latest worked for a colleague with Docker Desktop while we required this trick with Rancher Desktop. – Abbevillian

this helped once I played around trying to understand things. My docker machine is called docker.local and my image is mssql so it equated to using docker.local\mssql,1433 from Azure Data Studio – Anarchic

Thank you for posting. This unblocked me. :) – Marcy

Answer 2

In my case I've been running MSSQL Sever on my local machine + one on docker. Turning off mssql server service on host solved the issue.

[Edit]:

Adding technical reason as pointed by Francesco and it holds true in general for any ports:

That is not weird, if the port 1433 is taken by your host MSSQL, your MSSQL on docker cannot use the same port.

Pulpiteer

This is weird, thanks, I would have never realized if this was the issue. – Rizika

How do we check if that is the case? – Radiotelephone

@Koder101, that is not weird, if the port 1433 is taken by your host MSSQL your MSSQL on docker cannot use the same port. – Wellrounded

So strange even running on another port doesn't work. You need to kill the existing SQLSERVER process. – Radiotelephone

Answer 3

I have the same issue and answer was found here

https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver16&pivots=cs1-bash

As it mentioned there, "Your password should follow the SQL Server default password policy, otherwise the container can't set up SQL Server and will stop working"

I used following 2 commands

PS C:\Users\xxx>docker pull mcr.microsoft.com/mssql/server

PS C:\Users\xxx> docker run --name sqldb -p 1533:1433 -e 'SA_PASSWORD=Strong2@PWD12' -e ACCEPT_EULA=Y -d mcr.microsoft.com/mssql/server

then when you use sql server login

 - Server : 127.0.0.1, 1533

Monteria

Answer 4

In my case, using 127.0.0.1, 1433 instead of localhost, 1433 does solve the problem for SSMS and Azure Data Studio

Coquet

Interesting because 127.0.0.1 is localhost – Wellrounded

yes. exactly. I didn't find the reason but 127.0.0.1 works – Coquet

The reason will be because you can bind to a hostname or to an IP or to both, in your case you are only binding to 127.0.0.1 (in case you were wondering) – Stumpy

@MickeyPerlstein sorry what? didn't understand anything that you wrote – Coquet

Answer 5

I think, if you follow MS document, your cmd for init container is missing MSSQL_PID parameter, I don't know why it is required for SSMS, we can find out later. But you should try this

 docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Helloworld123" -e "MSSQL_PID=Express" -p 1433:1433  --name sql1 -d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04

Kestrel

if you dont provide MSSQL_PID your edition will become a Developer edition. – Tractile

Answer 6

The server setup looks fine. You need to only give 'localhost' instead of 'localhost,1433'.

Kiser

Answer 7

According to the screenshot of error message, its a login issue. Not, a connection issue. If SSMS can not access the docker container which runs the MS SQL server, it gives a message something like this. "A network-related or instance-specific error occurred while establishing a connection to SQL Server" There for this must be credential issue.

Sackville

Answer 8

For me this turned out to be a clash of ports with my locally-installed sql server instance. I had my docker instance configured with -p 1434:1434

Somehow using port 1434 in SSMS on my host, it was still connecting to my local host sql instance.

When I changed my docker port container to -p 1450:1434 I was then able to connect from SSMS on my host using port 1450.

Blether

Answer 9

For me.... it was because it was an upper case "SA" for the login, not the lower case "sa" that I'm used to. Kinda really irritated about that. I've basically spent over an hour to figure that out.

Keyhole

Answer 10

The connection string is okay. The issue lies within the provided credentials. Probably quotes around the password added during creating.

Can you run docker exec -it sql1 "bash" this will provide a container shell. Run echo $SA_PASSWORD to see if the password includes quotes and which one. Copy the result and paste it in the password field and see if it works.

You can also change the password with the following command:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
   -S localhost -U SA -P "<YourStrong@Passw0rd>" `
   -Q "ALTER LOGIN SA WITH PASSWORD='<YourNewStrong@Passw0rd>'"

Percentile

Thank you @rfkortekaas, nice trick to show the password in plain text, no there are no quotes: snipboard.io/6bAInJ.jpg – Wellrounded

I also changed the password from HelloWorld123 to HelloHell123, no joy. But I can login through command line, is just SSMS that cannot login with the same User/Password – Wellrounded

Answer 11

Probable causes of SQL server login error code 18456

SQL Server login failures can happen due to various reasons.

• The username or password entered is incorrect.
• Wrong authentication mode is enabled.
• A single username may have different passwords on different servers. So the user must be sure that he is inputting the right combination.
• Password of the user account is expired.
• User account is deleted from the server.

Probably, to resolve this, try this

When an SQL Server is started for the first time, there is a possibility that ‘Windows authentication’ is enabled under the security option. In such a situation, the server will not recognize the user and user will get the failed login 18456 error.

Worth looking at this post as well:- Unable to login to SQL Server + SQL Server Authentication + Error: 18456

Middy