connection string for sqlserver in Docker container

Question

I'm using Visual Studio 2017 for mac with dotnet Core and EF Core. After setting up the mssql image in Docker container , I was trying to add the connection string but throwing connection error. I tried with different options such as ip address , container name , host name etc. as server name but none of them worked.

 "Default": "Server=172.17.0.2; Database=ERPDb; User=sa; Password =******;"

with container name

 "Default": "Server=ecstatic_hermann; Database=ERPDb; User=sa; Password=******;"

with hostname :

 "Default": "Server=f45840a59623; Database=ERPDb; User=sa; Password=******;"

While connecting through using localhost in Terminal its successfully connecting

$ mssql -s localhost -p Technocrat123
Connecting to localhost...done

sql-cli version 0.6.2
Enter ".help" for usage hints.

But when running the application the connection fails.

Appreciate any help. Thanks in advance.

If using localhost then error is

Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only.

is the sql server running inside a container ? Is the connecting code also running inside a container? If yes to both, are they on the same network, and if not, is the server's SQL port exposed? — omu_negru, Aug 16 '17 at 11:38
@omu_negru The sql server is running inside the container. The application is running inside the machine not inside the container. How to expose the SQL port? Can you please guide me? — user2695433, Aug 16 '17 at 11:48
expose the MySql port by passing the -p param to the docker run command: docker run -p 3306:3306 ....... Also set the host to localhost, then you can check if the port is exposed by running docker ps and checking the ports section on the right. — omu_negru, Aug 16 '17 at 11:50
@omu_negru I have already done that docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Technocrat123' -p 1433:1433 -d microsoft/mssql-server-linux — user2695433, Aug 16 '17 at 11:51
Is port 1433 the one exposed by the DB server? I thought the normal MySQL port was 3306 — omu_negru, Aug 16 '17 at 11:53
@omu_negru docker ps -> 56e88580a807 microsoft/mssql-server-linux "/bin/sh -c /opt/m..." 2 hours ago Up 2 hours 0.0.0.0:1433->1433/tcp ecstatic_hermann — user2695433, Aug 16 '17 at 11:54
@omu_negru 1433 was exposed by DB server . got from their documentation — user2695433, Aug 16 '17 at 11:54
are the services running on different machines? If yes, can you ping the DB host from the code host? — omu_negru, Aug 16 '17 at 11:55
@omu_negru visual studio and the docker is in the same machine. how to ping? you mean try connecting like connect to db option? — user2695433, Aug 16 '17 at 11:58
in that case try setting the server ip to 127.0.0.1, or localhost — omu_negru, Aug 16 '17 at 11:59
@omu_negru I tried using 127.0.0.1 and localhost , didnt work . Also, not able to explore the DBs since there is no object explorer to directly connect to DB in VS mac version. — user2695433, Aug 16 '17 at 12:02
@omu_negru when I tried localhost it throws error Login Failed for user — user2695433, Aug 16 '17 at 12:05
@user2695433 did you find the solution? I'm having the same problem. — Bruno Serrano, Sep 12 '17 at 21:23
@BrunoSerrano Appreciate if you can share in case you find a fix for it. — user2695433, Sep 13 '17 at 06:50
Here is the connection string that worked for me: `Data Source=localhost;Initial Catalog=;User ID=sa;Password=` — Bruno Serrano, Sep 14 '17 at 09:52
@BrunoSerrano thanks for your message. I had initially started with localhost , but was not successful . Appreciate you shared it here. — user2695433, Sep 14 '17 at 11:38
@user2695433 have you tried with `Data Source` instead of `Server` and `Initial Catalog` instead of `Database`? When I changed this names it started working. — Bruno Serrano, Sep 14 '17 at 15:36
Try off "Encrypt connection" and / or "trusted server certificate" — Cesar Alvarado Diaz, Apr 12 '21 at 04:24

rjminchuk · Accepted Answer · 2020-03-30T18:18:03.703

41

I just wrote up a blog post on this. Take a look at richminchuk.io. Otherwise:

sudo docker pull microsoft/mssql-server-linux:2017-latest
docker run \
   -e 'ACCEPT_EULA=Y' \
   -e 'MSSQL_SA_PASSWORD=YourSTRONG!Passw0rd' \
   -p 1401:1433 \
   -n sql1 \
   -d microsoft/mssql-server-linux:2017-latest

then,

private static string _connStr = @"
   Server=127.0.0.1,1401;
   Database=Master;
   User Id=SA;
   Password=YourSTRONG!Passw0rd
";

edited Mar 30 '20 at 18:18

answered Apr 16 '18 at 14:44

rjminchuk

585
7
13

2

Is there a way to connect using the server=(LocalDb)\InstanceName format? – amorimluc Dec 12 '18 at 17:33
Can you provide any more information on your setup? Why are you using named instances? – rjminchuk Dec 13 '18 at 19:10
Because our software team is mostly using Windows, but I'm on a mac. They are connecting using the format I referenced. I'm having trouble connecting that way to my docker SQL Server container. – amorimluc Dec 13 '18 at 21:06
I'm not sure it is possible from the command line on MacOS to connect with the servername\instance format. I've tried a myriad of different ways to connect based on the @@servername and @@servicename (instance name) on MacOS with no success. I keep getting network errors. – rjminchuk Dec 16 '18 at 23:25

score 20 · Answer 2 · answered Feb 19 '18 at 02:25

20

Most likely your server name is localhost and port 1401 (which is the default for Docker container setup). Therefore, you'll need to use the following connection string:

"Default": "Server=localhost,1401; Database=ERPDb; User=sa; Password =******;"

answered Feb 19 '18 at 02:25

David Poindexter

358
3
12

score 6 · Answer 3 · edited Dec 10 '18 at 09:28

I had this problem today and I resolved it using a separate network (instead of using default "bridge" network).

docker network create test_network
docker container run -p 1433:1433 -d --name mssql -v mssql_data:/var/opt/mssql -e SA_PASSWORD=********** -e ACCEPT_EULA=Y --network=test_network microsoft/mssql-server-linux
docker container run -p 5000:80 --rm -e ASPNETCORE_ENVIRONMENT=Development --name aspnetcore --network=test_network aspnetcore-image

Also I have such connection string:

Server=mssql;Database=master;User=sa;Password=**********;

About previous answers regarding Connection String with IP address, it is not a good approach, because this address can be changed dynamically, it is better to use container names, as hostnames.

IP 127.0.0.1 never changes. It is a self reference just like localhost. — Paul - Soura Tech LLC, Nov 12 '20 at 20:53

score 5 · Answer 4 · answered Dec 16 '20 at 14:01

Rather than use IP addresses, which would be problematic in a team environment, you can also use host.docker.internal which will resolve to your host IP.

Data Source=host.docker.internal,1433;Initial Catalog=MyDB;User ID=MyUser;Password=MyPassword

alphacoder · Answer 5 · 2020-04-13T08:47:41.863

0

I solved it by using SQL server Container IP address by inspecting the sql server container for its IP address as shown below

docker inspect -f "{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}" <ContainerID | ContainerName>

edited Apr 13 '20 at 08:47

answered Apr 13 '20 at 08:35

alphacoder

405
1
6
20

connection string for sqlserver in Docker container

5 Answers5

Linked

Related