Cannot connect with SSMS to SQL Server on Docker

user_4652358 0
Viewed 10253

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?

docker sqlcmd containers ssms sql-server
7 Answers

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.

user_103775130

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"
user_119522190

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
user_47667750

stop local mssql server;

Windows + R => SQLServerManager13.msc (installed version 13)

sql server configuration manager

after trying this my problem was solved

user_66073340

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

user_15106100

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>'"
user_23458700

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

user_80901740
Related