Connect to SQL Server database from a docker container

Question

I have docker for windows installed on my machine. There is a console application targeting .net core 1.0.0 that tries to access a SQL Server database running on a different VM. I can ping the VM running SQL Server from my machine.

When I try to run the console application using dotnet run from the command prompt on my machine it works fine. But when the same application is run inside a docker container I get a message

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)

I tried using

docker run --add-host sqldemo:<VM running sql server ip here>

but that made no difference.

@Brad using (var conn = new SqlConnection(@"Data Source=;Initial Catalog=DemoDb;User id=demo;Password=demo")) — Learning Docker, Jul 26 '16 at 20:50
Try using the host name `sqldemo` you're adding with `--add-host` instead of the IP address. — Brad, Jul 26 '16 at 23:18
@Brad I already tried it. Doesn't work. From docker host i am unable to ping the VM on which SQL Server is running. I read a lot of articles on docker networking but unable to figure this one out. Most of the articles talk about overlay network driver to connect multi host. In my case that does not apply since SQL Server is not running in a docker container. — Learning Docker, Jul 27 '16 at 04:37
Have you checked the firewall settings on the Sql Server VM? Is the firewall allowing traffic from the docker container IP? — Brad, Jul 27 '16 at 05:30
marc_s, did you resolve this issue? I have SQL Server installed in the docker host (not even another VM), and I have a .NET application inside the container that is failing to connect to SQL Server in the host. I tried many options `--expose`, `--add-host`, `--net`... either I am not coming up with the right combination or missing something. — Web User, Aug 22 '17 at 20:35
Make sure that the container port is set to default recommended 1433 for SQL Server, otherwise you will have to do more configuration. For example: -p 8000:1433 Here is an article that explains how to run SQL Server inside Docker https://thecodeframework.com/run-sql-server-inside-a-docker-container/ — Gagan, May 09 '20 at 19:03

score 9 · Answer 1 · edited May 18 '18 at 06:30

Assumptions

Microsoft SQL Server 2016
Windows 10 Anniversary Update
Windows Containers
ASP.NET Core application

Add a SQL user to your SQL database.

In MS SQL expand the database
Right click on 'Security / Logins'
Select 'New Login'
Create a user name and password.
Assign a 'Server Role(s)'...I used sysadmin since I'm just testing
Under 'User Mapping' I added my new user to my database and used 'dbo' for schema.

Change SQL Authentication to allow SQL Server Authentication Mode

Right click on your database, select 'Properties / Security / Server Authentication / SQL Server and Windows Authentication Mode' radio button. Restart the MS SQL service.

Update your appsettings.json with your new user name and password

Example

"ConnectionStrings": {
        "DefaultConnection": "Server=YourServerName;Database=YourDatabaseName;MultipleActiveResultSets=true;User Id=UserNameYouJustAdded;Password=PassordYouJustCreated"
},

Make sure you remove Trusted_Connection=True.

Create a Docker file

My example Docker file

FROM microsoft/dotnet:nanoserver
ARG source=.
WORKDIR /app 
EXPOSE 5000 
EXPOSE 1433 
ENV ASPNETCORE_URLS http://+:5000 
COPY $source .

Publish Application

Running from the same location as the Docker file in an elevated PowerShell

dotnet publish

docker build bin\Debug\netcoreapp1.0\publish -t aspidserver

docker run -it  aspidserver cmd

I wanted to run the container and see the output as it was running in PowerShell.

Once the container was up and running in the container at the command prompt I kicked off my application.

dotnet nameOfApplication.dll

If everything went to plan one should be up and running.

Camilo Silva · Answer 2 · 2016-07-27T14:44:45.027

4

You can run a docker container with network settings set to host. Such a container will share the network stack with the docker host and from the container point of view, localhost (or 127.0.0.1) will refer to the docker host.

docker run --net=host ...

Then you should get the SQL Server database from inside the docker container as you do from your host.

edited Jul 27 '16 at 14:44

answered Jul 27 '16 at 00:23

Camilo Silva

8,283
4
41
61

1

Nope. Right now SQL Server cannot run in a container (except SQL Server 2016 which is available in a container on linux and is in private preview only). In my case it is running on a different windows VM (like a stand alone installation). – Learning Docker Jul 27 '16 at 04:33
5

I think he refers to SQL Server running on the local host (not in a container, just connecting to the instance via th local host port). Also, refer to my earlier answer, WinDocks provides a Docker Engine for the Windows OS family which supports SQL Server in containers (ie., SQL Server 2008 to 2016 image support). – paul stanton Jul 30 '16 at 03:32
Will this work with with trusted connections, meaning, if the host connects to database with windows authentication, will that authentication pass through to the docker container? – Frank May 01 '20 at 06:42

score 0 · Answer 3 · answered Dec 14 '16 at 06:45

as in this answer

SQL Server instance string connection in Linux Docker

According to Saurabh Singh from Microsoft:

The Instance name support is available in v 1.1 of .Net Core. In v1.0 of .Net Core, Instance names are not supported on OS other than Windows.

So I don't think you can connect from .Net Core 1.0 running on Linux to an SQL Server using instance name.

Your choices seem to be:

don't use instance name
wait for .Net Core 1.1 (planned for "Fall 2016")
use pre-release version of .Net Core 1.1