15

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

enter image description here

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

enter image description here

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

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

enter image description here

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?

Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
  • 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? – TWP May 20 '20 at 06:37
  • 1
    The error suggests you have connectivity to the container instance. Click the show details in the error dialog to see if the [state code](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error) 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` – Dan Guzman May 20 '20 at 11:34
  • 1
    Is the docker instance running on the same computer you are running the sql client? – rfkortekaas May 21 '20 at 13:11
  • @rfkortekaas, yes, I have SSMS and Docker on the same computer – Francesco Mantovani May 24 '20 at 22:15
  • 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.` – Francesco Mantovani May 24 '20 at 22:24

9 Answers9

31

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"
Nilo Alan
  • 730
  • 5
  • 8
  • 2
    This helped me, thanks. – rAhulD Feb 07 '22 at 15:31
  • 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_. – ofthelit May 02 '22 at 08:04
  • 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 – pcnate Jun 07 '22 at 18:49
  • Thank you for posting. This unblocked me. :) – Peter Bernier Jul 19 '22 at 17:51
18

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.

Koder101
  • 844
  • 15
  • 28
Tazi
  • 435
  • 5
  • 9
2

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
RusArtM
  • 1,116
  • 3
  • 15
  • 22
Jayampathi Weerasinghe
  • 21
  • 4
1

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

devilpreet
  • 749
  • 4
  • 18
1

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
JimmyN
  • 579
  • 4
  • 16
0

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.

Chamath Jeevan
  • 5,072
  • 1
  • 24
  • 27
0

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.

Shawn de Wet
  • 5,642
  • 6
  • 57
  • 88
-1

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>'"
rfkortekaas
  • 6,049
  • 2
  • 27
  • 34
  • Thank you @rfkortekaas, nice trick to show the password in plain text, no there are no quotes: https://snipboard.io/6bAInJ.jpg – Francesco Mantovani May 27 '20 at 20:54
  • 1
    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 – Francesco Mantovani May 27 '20 at 20:55
-3

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

nischay goyal
  • 3,206
  • 12
  • 23