Can not connect to sql server from docker supported asp.net core project

Question

I have a ASP.NET Core 2.2 project for which I turned on docker support. The project runs fine as long as it does not require database connection. For e.g Login. When I enter user credentials, I get the error as below.

An unhandled exception occurred while processing the request. Win32Exception: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

Unknown location SqlException: 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: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling) InvalidOperationException: An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy.

Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy+d__7.MoveNext()

Previously the error was different when TCP/IP was not enabled in my sql server configuration manager.(Do not remember error) I followed the steps mentioned in the link https://jack-vanlightly.com/blog/2017/9/24/how-to-connect-to-your-local-sql-server-from-inside-docker to solve that issue.

I have enabled TCP/IP and named pipes as well. Also I can connect using IP from SQL Management Studio.

Current connection string:

"ConnectionStrings": {
    "DefaultConnection": "Server=xxx.xx.xx.x,1433;Database=TestDB;User ID=username;Password=pwd;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

Please let me know if I am missing any step. I want to connect to a local sql from my docker project

Solution 1

This took me some time to work out, there are a number of small issues that may be wrong, which makes it frustrating. This can get even more frustrating as the .net core web application visual studio 2017 project that gets auto-generated doesn't work straight out of the box. Having this experience as your first exposure to Docker isn't ideal.

I initially also had the incorrect assumption that the docker image would come with SQL Server inside the container, this is not the case, it's trying to access the database server that must be pre-installed on the host machine.

Steps to follow (tested for a windows docker image, rather than linux);

1) Get the IP address of your host machine, by running a command prompt and typing IPCONFIG

2) Set the database connection string within you appsettings.json file to this Ip address, followed by the SQL Server port number, i.e.;

192.168.X.X,1433

3) Set the connection string not to use Trusted_Connection (delete this from the connection string) and hard code in the User Id and Password;

User Id=sa;Password=SuperSecurePassword;

If I didn't do this, on certain SQL Server configurations I'd get an unusual error (cannot quite remember the specifics!)

Connection String show look something like this;

"Server=192.168.X.X,1433;Database=MyDatabase;User Id=sa;Password=SuperSecurePassword;MultipleActiveResultSets=true"

4) now on the host machine you need to open up the windows firewall, and add a new inbound connection rule, for TCP port 1433.

5) After all this, and it still doesn't work, try a reboot; I struggled for a long time and had to reboot, which brought it into life; I'm not sure Docker had initialised correctly, this is mostly speculation though! I don't really like to say rebooting is a fix for problems, it's not really an answer, but in certain cases it does the trick.

edit, one final thing, running visual studio in administrator mode (right click icon, "run as administrator") helps too.

Apologises for the resurrection of an old thread, but this issue seems to still exist and the information available is a bit patchy on how to fix this, considering the small number of things that need to be done.

Solution 2

So what has worked for me is:

public static string DockerHostMachineIpAddress => Dns.GetHostAddresses(new Uri("http://docker.for.win.localhost").Host)[0].ToString();

docker.for.win.localhost is an address that inner dns of docker will resolve as localhost (not localhost inside docker but the hosting machine). The above code provides u with an ip address of your localhost and you are able to create connection string like this:

$"Server={DockerHostMachineIpAddress}\\SQL2017;Database=YourDB;User Id=Admin;Password=123;"

This will work if you are using named sql server instance - replace SQL2017 with ur instance name and crediatials with correct user.

HOWEVER! For migrations and update-database localhost works fine. Probably since its not handled from docker container.

EDIT:

Second solution is a bit cleaner but you need to have a project that supports docker-compose.

In docker-compose.yml file in definition of your service below image property definition add:

extra_hosts:
- "localhost:192.168.65.2"

You still have to know what IP address (which you can find out by using the original answer) you have to add alias for, but once you know it it's a bit more clean. On more than 5 different machines that I worked on this IP is correct.

Solution 3

I ran into the same problem, and followed all the steps mentioned by Phil but I still could not solve this. Finally, in addition to the steps in the specified answer, I had to enable my SQL Server for TCP protocol by following these steps.

1. Open SQL Server Configuration Manager
2. Select "SQL Server Network Configuration" > "Protocols for MSSQLSERVER"
3. Edit "TCP/IP" properties, and change "Enabled" to "Yes"

Solution 4

I had the same issue, followed the same approach as on the website you mentioned. It should work like that if you have the networks with standard configuration.

Simply disable the firewall for your public network.

My issue was simply, that windows seemed to classify the network created by Docker as public network and so the firewall blocked it, same as for this one:

Can't connect to SQL Server express from .net core app running on docker

It's not marked there as an answer but rather edited in the question.

View more solutions

Comments

[Alisson Reinaldo Silva]

Where are we supposed to insert that piece of code? Startup.cs?

[KrzysztofG06]

If you are using built-in IoC container than yes in startup.cs. Basically wherever you are registering your DB object.

[Alex]

How to utilize this in the appsettings.json?

[NoughT]

Does anyone faced the System.Net.Internals.SocketExceptionFactory+ExtendedSocketEx‌​ception (00000005, 6): Device not configured error with above code?

[Pavan Dhariwal]

After lots of head scratching, changing the docker file, wanting to throw the laptop out the window, this resolved it for me. I had stupidly unchecked public on the firewall rule.

[Mr. B]

I was able to get this working without worrying about templates or extra hosts using host.docker.internal as the server in my connection string. So your example connection string would look like: "Server=host.docker.internal\\SQL2017;Database=YourDB;User Id=Admin;Password=123;"

[ossentoo]

this worked for me. thanks. Forgot that sql developer installs with tcp/ip disabled by default