A day with .Net

My day to day experince in .net

Posts Tagged ‘docker sql server remote connection’

Connecting to local or remote SQL Server from Docker Container

Posted by vivekcek on June 10, 2018

In this post, I will show you how to connect from a docker container to local sql express running in your machine.
Please note that the SQL Server is not running inside a docker container. SQL is installed in my machine.

My docker container host an asp.net core application with EF Core. During the local debugging the app was perfectly connecting to my sql database. But after deploying the app to docker container I got exception like unable to connect to sql server.

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.

1. You must 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.

3.Now select Protocols for SQL and then select TCP/IP now open the Properties by right click.
4.In the TCP/IP properties window enable TCP/IP for SQL Server (Enabled=Yes, Listen All=Yes).

5.Now select IP Addresses tab in properties window. And under IPAll give port number as 49172.

6. Now restrat SQL Server service.

7.Make sure SQL Server Browser is running, if not start it.

7. Now setup your firewall to accept inbound connection to 49172.

8.To connect to sql server from docker, you can’t use the host computer name. You need to find the right ip address of you host.

9. Open command prompt and issue “ipconfig” command, Then you can see a nat ip address of Docker NAT ip address copy it.

10. Now we need to make sure, we can connect to this ip from our docker container.
11.User “docker ps” command to find the id of your running container.
12. Now use the “docker exec -i cmd” to get command prompt from your container. And use the ping command to chek the ip address connectivity.

13. If the connectivity is fine you can use below connection string.

Data Source=<IPAddress>\\SQLEXPRESS,49172;Database=<Database name>;User ID=sa;Password=<Your password>;MultipleActiveResultSets=true;
Advertisements

Posted in Docker | Tagged: , , , | Leave a Comment »