29

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?

Improve this question
5
  • 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? Commented May 20, 2020 at 6: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 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 Commented May 20, 2020 at 11:34
  • 2
    Is the docker instance running on the same computer you are running the sql client? Commented May 21, 2020 at 13:11
  • 1
    @rfkortekaas, yes, I have SSMS and Docker on the same computer Commented May 24, 2020 at 22:15
  • 1
    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. Commented May 24, 2020 at 22:24

9 Answers 9

Reset to default
52

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"
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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
I must be logged in to like this solution. tks a lot. You save my life
28

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.

Improve this answer

4 Comments

This is weird, thanks, I would have never realized if this was the issue.
How do we check if that is the case?
@Koder101, that is not weird, if the port 1433 is taken by your host MSSQL your MSSQL on docker cannot use the same port.
So strange even running on another port doesn't work. You need to kill the existing SQLSERVER process.
8

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

enter image description here

Improve this answer

5 Comments

Interesting because 127.0.0.1 is localhost
yes. exactly. I didn't find the reason but 127.0.0.1 works
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)
@MickeyPerlstein sorry what? didn't understand anything that you wrote
In my case 127.0.0.1, 1443 instead of 127.0.0.1:1443.....
6

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
Improve this answer

Comments

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
Improve this answer

1 Comment

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

The problem is MSSQL password complexity. I have spent 4hrs trying to resolve the same issue but later found that if you sa password does not meet the minimum password requirement policy, authentication will fail. In my case, Your password must include at least (2 numeric values),(2 uppercase letters)(2 special char) and other letters.

- https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy?view=sql-server-ver17

Improve this answer

Comments

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.

Improve this answer

Comments

0

I had problem only when running docker compose, not with adding sql server container separately, apparently problem is with volumes, some master.mdf (who the hell knows what it is) gets copied to your volume which holds login creds for core system databases (if we trust chatgpt on this) and which may hold default credentials or be messed up probably by using sql server prior to initializing it from docker on first time and doesn't get overwritten by your volume.

My advice is, try to add custom directory in that location with this line in docker compose:

- MSSQL_DATA_DIR=/var/opt/mssql/customdata

and mount volume accordingly

- sqldata:/var/opt/mssql/customdata

your docker compose will look something like this

sql:
  image: mcr.microsoft.com/mssql/server:2022-latest
  container_name: sql
  ports:
    - "1433:1433"
  environment:
    - ACCEPT_EULA=Y
    - SA_PASSWORD=Password123?
    - MSSQL_PID=Express
    - MSSQL_DATA_DIR=/var/opt/mssql/customdata
  volumes:
    - sqldata:/var/opt/mssql/customdata

If you are setting it up for .NET project and you won't be able to run database update because of access denied error then after that try to return volume to - sqldata:/var/opt/mssql and reconnecting, dont know what the hell but it worked after this mumbo jumbo

Improve this answer

Comments

0

I have the same issue, and the answer is to stop the Services below in your system

  1. SQL Server (MSSQLSERVER)

  2. SQL Server Agent (MSSQLSERVER)

Now it will work.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.