Dot net Core – How to fix: TimeOut-Error to MSSQL 2017 (which does not happen with .Net 4.7.1)

Question

Can't connect to a MS SQL Server 2017 Express from dotnet core 2.2 console application.

Checked Server Configuration as in Connection to SQL Server Works Sometimes

I have installed a new Microsoft SQL Server 2017 Express. Then tested the connection to this server with a console application (under .Net Framework 4.7.1). Works!.

Then I created a console application under Dot Net Core 2.2. Installed NuGet package System.Data.SqlClient and tried connect to the sql server using the same connection string I tested before and got a timeout error. How can this be fixed? (I also used the package Microsoft.Data.SqlClient, with the same result.)

If I try to connect to another SQL-Server (2008) the connection is established without problems.

using System;
using System.Data.SqlClient;

namespace ConsoleClient
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connecting");

            using (var conn = new SqlConnection(@"server=<IP>\SQLEXPRESS;user id=sa;password=<PASSWORD>;database="))
            {
                Console.WriteLine("Try to open connection");

                conn.Open();

                Console.WriteLine("Connection opened");
            }
            Console.ReadLine();
        }
    }
}

Following Exception occured:

Microsoft.Data.SqlClient.SqlException: 'Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21064; handshake=50; '

Possible duplicate of [Connection to SQL Server Works Sometimes](https://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes) — Rahul Sharma, Jun 19 '19 at 09:37
@RahulSharma: I tried this out, but didn't work. Solution is to force named pipes in the connection string. But why does the connection works from .Net Framework 4.7.1 with out the forcing to named pipes? The connect form 4.7.1 works, but is definitly faster with forcing named pipes. — Bernhard, Jun 19 '19 at 10:42
this link might help you to fix Connection Timeout expired [Connection Timeout expired](https://stackoverflow.com/a/56525834/9347361) — Hiba T, Jun 25 '19 at 16:49
@Bernhard , just on curious .. have you tried in the .Net framework instead of core framework.. to know what happens ..? — goofyui, Jun 25 '19 at 17:06

score 0 · Accepted Answer · edited Jul 05 '19 at 19:28

0

Forcing to use named pipes by specifying np: qualifier in the server parameter in the connection string does the job.


Console.WriteLine("Connecting");

using (var conn = new SqlConnection(@"server=np:<IP>\SQLEXPRESS;user id=sa;password=<PASSWORD>;database="))
{
    Console.WriteLine("Try to open connection");

    conn.Open();

    Console.WriteLine("Connection opened");
}
Console.ReadLine();

edited Jul 05 '19 at 19:28

JeffFerguson

2,952
19
28

answered Jun 19 '19 at 10:23

Bernhard

177
8

Please use the edit link on your question to add additional information. The Post Answer button should be used only for complete answers to the question. - [From Review](/review/low-quality-posts/23311513) – deHaar Jun 19 '19 at 12:50
1

But what is still interesting, why .Net Framework 4.7.1 does not need to do so. Any suggestions? If I force to use named pipes in 4.7.1 console app, the connect to the server takes less time. – Bernhard Jun 19 '19 at 13:09

score 0 · Answer 2 · answered Jun 25 '19 at 17:01

try to catch SQLServer timeout exceptions :

      try
        {
            // some code
        }
        catch (SqlException ex) when (ex.Number == -2)  // -2 is a sql timeout
        {
            // handle timeout
        }

this might hep timeout

score 0 · Answer 3 · answered Jun 25 '19 at 17:24

To enable remote access for SQLExpress, you have to Configure Express to accept remote connections

In ConnectionString:

need a port for SQLExpress server.
parameter database should not be empty.

If have everything well configured, the easiest way to fix timeout is:

dbConnection.ConnectionTimeout = 0;

this will let ADO.NET try/wait again and again, until it really fails.

Here is a good example:

Server=sampleServer\SQLEXPRESS,samplePort;Database=sampleDB;Persist Security Info=True;User ID=sa;Password=12345678;

Parameter can be empty. It allows your to make queries to all databases existing on the server. — Bernhard, Jun 26 '19 at 15:10

Dot net Core – How to fix: TimeOut-Error to MSSQL 2017 (which does not happen with .Net 4.7.1)

3 Answers3

Linked