Search code examples
asp.netdockerasp.net-coresql-server-2014-express

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

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

  • 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

    1. 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. EDIT: the error

    Cannot authenticate using Kerberos. Ensure Kerberos has been initialized on the client with 'kinit' and a Service Principal Name has been registered for the SQL Server to allow Kerberos authentication.

    Connection String show look something like this;

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

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

    2. 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.