Connect to SQL Server database from a docker container
Solution 1
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.
Solution 2
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.
Learning Docker
Updated on October 10, 2020Comments
-
Learning Docker over 3 years
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 messageA 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.