How to Connect from Docker Container to local or remote SQL Server
SQL Server is installed in a local machine and Docker Container is hosted to this local machine In this post, I will show you how to connect from a docker container to local SQL Server running in your machine.
My docker container host an asp.net core application with EF Core. I can run this with IIS Express (in Visual studio) without problem. But after deploying the app to docker container and running it via Docker- Composerit was not possible to start browser for Swagger UI and /Or even connect to the SQL Database
So why this happen?. Here the docker container is running as a separate machine inside your host computer. So to connect to the sql database in your host machine, you need to enable remote connections to sql server.
So follow below steps to enable remote connections to sql server.
- configure SQL Server with Mixed Mode Authentication. For remote connection
You need to supply user name and password.
2. Open SQL Server Configuration Manager as Administrator. (In Widows 10 run SQLServerManagerxx.msc, where xx is sql server version. e.g. SQLServerManager12.msc is for sql server 14.
Select Protocols for Sql Server: TCP/IP Enabled: yes and then select IP Addresses and find TCP port under All IPs then you see TCP port (1433) as figure above press OK and restart Sql Server.
2. Setup your firewall to accept inbound connection to 1433.
Setup your firewall to accept inbound connection to 1433. you can start Windows Defender Firewall with Advance Security from this select Inbound Rules: New Rule: Ports: Protocols Ports and the select TCP and write 1433 in the Specific local prots as shown in the following figure:
Press Next and then select: Allow All connection and press to Next and Next, Next, Next and write: under Name: 1433 and press to Finnish button.
Then it shall show the following image:
3 In the Dockerfile add EXPOSE 1433 under EXPOSE 80
4 Change the connection string mentioned in the appsettings.json
In appsettings.jsonfile points to the data source as local which the docker container does not understand. It needs proper IP addresses with port and SQL authentication. So, provide the relevant details i.e. Data Source as Ip address, port number and SQL authentication details as shown below.
where IP address can be found from the >ipconfig command for IPv4 address in your local machine (depends on your machine is connected via cable:Ethernet adapter, or Wireless LAN adapter Wi-Fi
We need to make sure, we can connect to this IP from our docker container.
Start cmd: run: > docker ps to find the id of your running container.(917bf2d389b3)
Then run command: >docker exec -i containerId cmd, to get command prompt from your container. And use the ping command to check the IP address connectivity.
As we see above we could connect from docker container to the local machine.
If the connectivity is fine you can use below connection string.
If the connectivity is fine you can use below connection string.
Data Source=<IPAddress>\\SQLEXPRESS,1433;Database=<Databasename>;User ID=sa;Password=<Your password>;MultipleActiveResultSets=true;
Use this connection string in the appsettings.json file:
Now again run the application with via Visual Studio: Docker- compose
Press to Get and then Try it out : execute, then you see that docker container is connected to Sql Server and returned the response and result as following
Conclusion
Docker container is running as a separate machine inside your host computer. So to connect to the sql database in your host machine, you need to enable remote connections to sql server. Setup your firewall to accept inbound connection to TCP Port. In the Dockerfile add EXPOSE TCP port under EXPOSE 80 Change the connection string mentioned in the appsettings.json.
In my next post I will explain Dockerfile for Production (Release) and Debug
This post is part of “Microservices-Step by step”.