A connection was successfully established with the server, but then an error occurred during the pre-login handshake

Question

I am getting following error when i am trying to connect Production DB from Local Environment.

I was able to connect Production DB before, but suddenly i am getting following error, any idea?

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The handle is invalid.)

I was trying to run asp.net website in local PC, which has connection string of Production DB, following is stack trace for error I am getting in local environment.

>    at MyWebsiteDAL.clsForumQuestion.SelectAll(Int32 CurrentPageIndex, Int32 PageSize) in D:\EDrive\My WebSites\MyWebsite\MyWebsite\MyWebsiteDAL\clsForumQuestion.cs:line 821
       at CodeConnect.Default.Page_Load(Object sender, EventArgs e) in D:\EDrive\My WebSites\MyWebsite\MyWebsite\MyWebsite\Default.aspx.cs:line 100
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Any idea what might have gone wrong here?

Are you able to connect from the local PC with SSMS? This question would be better asked on serverfault.com — Will A, Jul 17 '10 at 04:15
Yes i can able to connect. What I observer was suddenly that starts working. — Jordon Willis, Jul 17 '10 at 04:18
I believe this question does belong on StackOverflow (and not ServerFault) because the underlying problem causing the error was Asp.net Cassini in Visual Studio. Stopping all existing Cassini (WebDev.WebServer.exe) instances and re-running the app worked for me. — MikeTeeVee, Sep 13 '11 at 17:51
@ MikeTeeVee I got the same problem with a simple WinForm application. No cassini involved. — Sylvain Rodrigue, Aug 31 '16 at 12:43
For Ubuntu 22.04, please follow https://stackoverflow.com/a/72137669/5772425 — Sayyed Dawood, Jun 28 '22 at 09:01

score 176 · Accepted Answer · answered Jul 17 '10 at 04:21

176

Solution

1) Clean your VS.Net Solution

2) Rebuild Project.

3) Reset IIS

4) Run the project again.

Basically that solved my problem, but in my case i was not getting this error and suddenly my local environment starts giving me above error, so may be that trick work for me.

answered Jul 17 '10 at 04:21

Jordon Willis

4,851
7
27
34

28

I had a similar problem (localhost too). The key point seems maybe to have been point 3. All I seemed to need to do was kill the instance of the VS WebServer (Cassini) and rebuild & hit F5. That solved it for me. Thanks for your answer. (You should consider marking it as the solution) – Amadiere Mar 07 '11 at 11:34
1

This solution also worked for me. I too was using VS Web Server (Cassini) as opposed to IIS just the same as Amadiere. – Simon Lomax Apr 07 '11 at 11:01
11

This sounds more like a workaround than a fix. I wonder if the real problem has to do with connection pooling. – jrummell Jun 29 '11 at 18:55
Thank you! It suddenly started giving me this error too. I was editing an aspx page and debugging when it started throwing this error from one build to the next. (FYI: I was connecting to SQL Server 2008 Express on Arvixe). I opened my system-tray and stopped all the "Asp.net Development Server"'s (Cassini). Re-ran and everything is working again. Thought I was losing it for a sec. – MikeTeeVee Sep 13 '11 at 17:43
I tried all of the above without success. It took a reboot to get things to work. – Matthew Fotzler Jul 07 '13 at 15:25
It was worked for me just with 1 & 2 levels. Thanks. – QMaster Feb 26 '14 at 01:59
By "reset IIS" I presume you meant "Restart IIS"? This was the solution for me, no need to do anything in my solution. – Ian Kemp Jul 03 '14 at 13:38
This solution also worked for the following error message: System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The handle is invalid.) ---> System.ComponentModel.Win32Exception (0x80004005): The handle is invalid Thank you! – Bjørn Otto Vasbotten Aug 05 '14 at 08:28
2

Had this again today. All you need to do is run iisreset from an admin command prompt. – Ian Kemp Oct 02 '14 at 10:02
I also put my focus on step 3. In my case I was using IIS Express. I first discovered two instances of it running (taskbar system tray). I exited out of both of these but still got the error. I then used task manager to kill the iisexpress.exe *32 worker process. This resolved the issue. – Terence Golla Oct 30 '14 at 13:56
7

This answer could be improved by providing some possible directions for root cause analysis. Work-arounds have value, but it's more useful to know why this occurs and why the work-around works. – laindir Aug 08 '16 at 18:23
1

just resetting iis did it for me – toddmo Oct 11 '16 at 21:35

gratinierer · Answer 2 · 2020-09-02T08:13:24.980

145

In my case this error occurred with dot net core and Microsoft.Data.SqlClient. The solution was to add ;TrustServerCertificate=true to the end of the connection string.

edited Sep 02 '20 at 08:13

answered Jul 14 '20 at 13:38

gratinierer

1,748
1
10
10

3

This was it when using self-signed certificates – Geoff Gunter Aug 07 '20 at 19:58
Yeah thanks I had to do that with the last preview of Visual Studio for Mac 17.3 – G Clovs Jun 15 '22 at 11:46
2

Tags: dotnet7 net7 Microsoft.EntityFrameworkCore.SqlServer – dani herrera Nov 10 '22 at 09:22
Effectively, the error occurs because my SQL Server is not set to a valid certificate, forcing me to add this parameter in my connection string. – Kolodziey Nov 22 '22 at 20:59
It worked for me. The app is built on .NET 6, we started experiencing the issue in one fine morning on a test server. Although https://stackoverflow.com/questions/1642483/when-using-trusted-connection-true-and-sql-server-authentication-will-this-affe says "THEN (and only then) you have Windows Authentication happening. Any user id= setting in the connection string will be ignored.", the user specified in the connection string is not being ignored! – Mujib Khan Dec 15 '22 at 09:25
This solved my problem in .net 7 – Born to code Mar 28 '23 at 10:13
Indeed only happens with .NET7. Using other projects on .NET6 with identical configurations without issues. – Seb Mar 28 '23 at 19:35

score 28 · Answer 3 · edited Sep 16 '20 at 16:00

28

Save your work,
Close Visual Studio, then
Re-open your project

This worked for me.

edited Sep 16 '20 at 16:00

Matej J

615
1
9
33

answered Feb 06 '14 at 10:07

Dumisani

2,988
1
29
40

60

This worked for me too, but I often find making a coffee while it restarts makes all the difference. – ED-209 Sep 19 '14 at 09:18
I had a similar problem, but with a node server instead. I was running a node server on ubuntu VM guest and accessing from Windows 10 VM host. Pausing and saving the VM, then resetting the Windows 10 host, then opening the VM back up worked, so I assumed it was a problem with Windows, not the local server. But then, I just now tried resetting the local node server, and that fixed it as well...so who knows, ha – RyanQuey Mar 23 '20 at 01:26

score 20 · Answer 4 · edited Apr 13 '17 at 12:13

20

Running the following command worked for me:

netsh Winsock reset

Seen at https://serverfault.com/a/487139/250527

edited Apr 13 '17 at 12:13

Community

1
1

answered Oct 23 '14 at 15:51

Ricardo Stuven

4,704
2
34
36

5

This resolved my issue. I owe the network guy lunch cause I had thrown him under the bus. – greg Apr 17 '15 at 15:55
3

FYI, this `reset` needs a system restart. – wqw Mar 22 '18 at 07:44
1

After 2.5 hours, this saved my bacon. Thank you so much! – Peter Harrison Dec 18 '20 at 23:55
It resolved my issue. thanks – Neeraj Singh Chouhan Jul 05 '23 at 03:29

score 17 · Answer 5 · edited Jun 13 '16 at 11:21

17

I experienced this error when running some very memory-expensive processes. When the system started to run short of memory, I begun to notice this kind of error. I had to change the algorithm in order to make a better use of RAM.

To be noted that while some threads threw this exception, some other threw:

System.Data.SqlClient.SqlException (0x80131904): 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=43606; handshake=560; ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Both problems disappeared after the system was changed so that it could run using less RAM.

edited Jun 13 '16 at 11:21

marc_s

732,580
175
1,330
1,459

answered Aug 19 '15 at 09:18

Starnuto di topo

3,215
5
32
66

2

Confirmed. I ran intensive processing on SQL Server from C# using parallel.foreach on my laptop. My memory usage was at its max. Computer was barely usable for about 15 minutes before i got this error. – TheLegendaryCopyCoder Sep 28 '16 at 09:15
1

I ran MsSql as container, and had restricted it to 1GB of memory. It worked when I upped the limit to 2GB. Thank you sir ‍♂️ – jonas Oct 12 '21 at 08:20
I agree, this is due to lack of RAM – Nikita Khromov Dec 30 '21 at 11:22
More generally, this might occur if your process isn't getting any CPU time. Perhaps because the OS is busy swapping memory as in your example. Perhaps because another CPU heavy program has a higher priority. For example the default priority for scheduled tasks is below normal. – Jeremy Lakeman Sep 07 '22 at 02:16

score 11 · Answer 6 · answered Nov 14 '12 at 12:27

11

I had the same problem, I was storing session data in the database, the connection string had Encrypt=True in it, which I assume told the sql client to connect to the server in secure (SSL) mode, removing this helped!

answered Nov 14 '12 at 12:27

Terry Kernan

746
5
12

1

This flag will be added to your connection string if you are going to be using a Windows Azure SQL Database. I am pretty certain it will be used for other PaaS/IaaS/Server providers also. – Parth Shah Nov 05 '14 at 04:02
AzureSQL requiring encrypt true https://blogs.msdn.microsoft.com/buckwoody/2012/03/06/sql-azure-requiring-encrypttrue/ – Jeson Martajaya Sep 12 '18 at 17:46

score 7 · Answer 7 · answered Jun 25 '13 at 17:29

7

In my case it was:

Persist Security Info=True;

in my connection string that needed to be removed. Once I did that I no longer had issues.

answered Jun 25 '13 at 17:29

delliottg

3,950
3
38
52

score 6 · Answer 8 · edited Dec 05 '10 at 05:04

6

You might want to check a few things:

You production server allows remote connections. (possible that someone turned this off, especially if you have a DBA)
Check your connection string. Sometimes if you are using an ip address or server name this will cause this error. Try both.

edited Dec 05 '10 at 05:04

user229044

232,980
40
330
338

answered Dec 05 '10 at 03:45

Richard Fantozzi

279
2
6

score 3 · Answer 9 · edited May 23 '17 at 12:26

3

As described in the answer of Ricardo ,

netsh Winsock reset

has worked for me ,

P.S. if you have Internet download manager or such programs which changes you IP Setting is installed then after running this command when you reboot your computer IDM will ask to change setting , Set NO in this case and then run your application it will work correctly.

Hope it

edited May 23 '17 at 12:26

Community

1
1

answered Jul 25 '15 at 08:35

Vishal Sharma

2,773
2
24
36

score 3 · Answer 10 · edited Jan 21 '22 at 14:40

3

I was getting the exact same problem with no changes to the code base or servers. It turned out to be that the DB server was running at 100% CPU and SQL Server was being starved of any CPU time, which caused the timeout.

edited Jan 21 '22 at 14:40

Leniel Maccaferri

100,159
46
371
480

answered Feb 03 '16 at 03:21

TheLukeMcCarthy

2,253
2
25
34

SQL Server is the DB server, right? Or is the DB server just the system in general on which SQL Server is running? – binki Dec 17 '21 at 06:35
Is this case the Db server is the physical hardware and SQL Server is the software – TheLukeMcCarthy Mar 16 '22 at 04:59

score 3 · Answer 11 · edited Mar 10 '16 at 15:05

I had a similar issue where I couldn't connect to a database and tried the recommendations here.

At the end of the day this is what worked for me:

Used the SQL Server Configuration Manager tool to enable the TCP/IP and/or the Named Pipes protocols on the SQL Server client computer.

Click Start, point to All Programs, and click SQL Server Configuration Manager.
Click to expand SQL Server Network Configuration and then click Client Protocols.
Right-click the TCP/IP protocol and then click Enable.
Right-click the Named Pipes protocol and then click Enable.
Restart the SQL server service if prompted to do so.

I am still not sure why or when this was disabled.

Paul Evans · Answer 12 · 2020-03-06T19:56:40.683

3

Tried most of the above and nothing worked. Then turned off the VPN software (NordVPN) and everything was fine.

edited Mar 06 '20 at 19:56

answered Mar 06 '20 at 15:20

Paul Evans

1,436
2
10
13

score 3 · Answer 13 · answered Oct 03 '22 at 04:57

3

If you're connecting to a database in an availability group, then you may need to add multisubnetfailover=true to the connection string.

answered Oct 03 '22 at 04:57

geofftnz

9,954
2
42
50

score 2 · Answer 14 · answered Aug 26 '13 at 08:44

2

I restarted SQL Server (Sharepoint) service and it solved the issue.

answered Aug 26 '13 at 08:44

user2087459

21
1

score 2 · Answer 15 · answered Feb 18 '15 at 03:30

2

For me the solution is to kill zombie IIS express worker processes.

e.g. locate in Task Manager and end task.

enter image description here

answered Feb 18 '15 at 03:30

Daniel de Zwaan

3,064
25
24

score 2 · Answer 16 · answered May 07 '20 at 11:18

2

I tried many solutions in the past month, none has worked. The problem is the Production DB is within a VPN and somehow the ISP provider thinks the HTTP connection is not secure. However, I was able to connect to the same Production DB from SSMS (uses TCP).

My solution: use the mobile phone as a hotspot and use mobile data instead of my office/home Wi-Fi.

answered May 07 '20 at 11:18

mihai_omega

121
1
5

2

Same in my case. Thanks! – DLO Jun 06 '20 at 11:51

score 2 · Answer 17 · answered Jul 13 '21 at 20:30

In my case, the connection from my API to the DB worked fine when running on my local machine, this error only occurred when running the .NET Core 5 API application in a docker container utilizing base image: mcr.microsoft.com/dotnet/aspnet:5.0. The problem was a TLS version inconsistency between SQL Server and one of our Dependencies. The fix was to add the following line to the API Dockerfile, downgrading security level of TLS:

RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf

This was taken from this article wherein the aforementioned RUN command was suggested, which fixed my problem. Direct link to the suggested fix is here.

score 1 · Answer 18 · answered Jan 09 '13 at 21:19

I had this same problem and was having no luck with the suggested fixes. I then came across this article and saw the comment from Mirrh regarding a program called Sendori blocking the LSP. No idea how it got on my computer but there it was and removing it fixed the issue.

If the article doesn't work just check your Programs and uninstall Sendori if you see it.

score 1 · Answer 19 · answered Jun 22 '17 at 15:27

Same problem here and no answers listed here worked, nor any solutions I could find online. The issue started shortly after Windows 10 anniversary update got applied on my dev PC and only affected my old SQL Server 2005 instance. I was not able to connect to the instance via my Web Applications or even using Sql Management Studio.

For what it's worth, this is what resolved it for me:

Open SQL Server Configuration Manager (depending on what version of SQL Server you're running):
- C:\Windows\SysWOW64\SQLServerManager.msc
- C:\Windows\SysWOW64\SQLServerManager10.msc OR
- C:\Windows\SysWOW64\SQLServerManager12.msc OR
- etc
Select SQL Server Services
Locate the troubled service and view Properties
- eg SQL Server (SQL2005) in my case
In the Log On tab, change the "Built-in account" to "Network Service"

Which is almost what this random solution said: http://www.kutayzorlu.com/operating-systems/linux-unix-redhat-debian-ubuntu-opensuse-centos/general-server-administrating/error-fixed-a-connection-was-successfully-established-with-the-server-but-then-an-error-occurred-during-the-pre-login-handshake-12405.html

I chose Network Service for no reason at all. Mine was already configured to use Local System. This security doesn't matter to me as it was only problematic on my Local development machine, only accessed locally. I can't advise why this works, but it did.

score 1 · Answer 20 · answered Nov 08 '17 at 06:30

1

In my case, i was getting the error when i wanted to access a remote database. However, i solved it by starting SQL Server Browser service.

answered Nov 08 '17 at 06:30

Ifesinachi Bryan

2,240
1
19
20

Phantom · Answer 21 · 2019-09-30T19:11:10.023

1

Had the same problem from many days. I had to explicitly add TLS1.2 support in my core project to address this error and it worked fine. ( ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;)

See below link for more details (thanks to author Usman Khurshid) https://www.itechtics.com/connection-successfully-established-error-occured-pre-login-handshake/

edited Sep 30 '19 at 19:11

answered Sep 30 '19 at 19:02

Phantom

101
6

Dominic Isaia · Answer 22 · 2019-12-19T18:44:25.077

1

If you are connecting to an old SQL Server:

Switch from System.Data.SqlClient.SqlConnection to System.Data.OleDb.OleDbConnection

Use an OleDb connection string:

<connectionStrings>
<add name="Northwind" connectionString="Provider=SQLOLEDB.1; Data Source=MyServer; Initial Catalog=Northwind;  Persist Security Info=True; User ID=abc; Password=xyz;" providerName="System.Data.OleDb" />
</connectionStrings>

edited Dec 19 '19 at 18:44

answered Dec 19 '19 at 00:44

Dominic Isaia

127
1
9

score 1 · Answer 23 · answered Nov 09 '22 at 04:13

1

In my case, I was using .Net 6 but updated Entity Framework Core Packages to version 7.0. The issue was gone after downgrading the Nuget packages.

answered Nov 09 '22 at 04:13

inVoKer

49
1
7

having the same issue moving to EF7 ( with .net6 ). Adding TrustServerCertificate=true has solved it on local machine. But deployment to Aws k8s with the same connection string - is still giving the error. Still searching for a solution. – Jason Cragun Nov 30 '22 at 16:10

score 1 · Answer 24 · answered Feb 17 '23 at 21:30

1

In my opinion, the source of this issue is an inaccurate error message. I'm getting the same message like this, but my mistake was the incorrect port.

answered Feb 17 '23 at 21:30

Mohammad Fazeli

648
10
13

Thanks.. Changed to the right port and it worked. – mpora Mar 27 '23 at 16:14

score 0 · Answer 25 · answered Oct 03 '12 at 17:04

Had the same issue, the reason for it was BCrypt.Net library, compiled using .NET 2.0 framework, while the whole project, which used it, was compiling with .NET 4.0. If symptoms are the same, try download BCrypt source code and rebuild it in release configuration within .NET 4.0. After I'd done it "pre-login handshake" worked fine. Hope it helps anyone.

score 0 · Answer 26 · edited Oct 24 '14 at 07:07

0

I experienced this error and did all the suggestions from you guys here but none had any effect for my error.

I caught the culprit: if you are using *.ini file for your system, you might want to check what server name was input there and make sure it is the same as the one in your web.config connection string.

edited Oct 24 '14 at 07:07

Ruskin

5,721
4
45
62

answered Oct 24 '14 at 06:38

user3619399

1

score 0 · Answer 27 · answered Jan 07 '20 at 13:30

0

In my case, Re-enabling TLS 1.0 in the DB server resolved this issue.

answered Jan 07 '20 at 13:30

Chinthaka Fernando

744
8
12

2

This is not really a solution. I will not downvote it, because in development environments it may help someone, but this exposes serious security threats. – Francesco de Guytenaere Jan 21 '21 at 10:45

score 0 · Answer 28 · answered Oct 12 '20 at 06:37

In my case, it was a TLS protocol mismatch between a Raspberry Pi hosting a .Net Core 3.1 app requesting to a Windows Server 2012 with MSSQL. I tried many changes of TLS protocol version on the Windows Server side without any effect. I finally found this post https://github.com/dotnet/SqlClient/issues/126, indicating modify the client side, ie to update the /etc/ssl/openssl.cnf on the RPi side, and that worked great for me.

score 0 · Answer 29 · answered Dec 14 '20 at 00:58

For me I had this happen on a HyperV virtual machine that was accessing my database on my local (host) machine. Basically it all works normally, but after a reboot there is some weird condition that this error starts coming up from the virtual machine applications. There is something in the network layer that is getting horribly confused. However, I have found if I ping the virtual server from the host server, it seems to fix the problem (not sure why exactly). But this might help someone.

score 0 · Answer 30 · answered Nov 12 '21 at 15:40

0

Another thing to check is if you have your SQL Server Network Configuration set to 'Force Encryption'. If you do, you'll get this error unless you do the requisite client incantations for an encrypted connection.

answered Nov 12 '21 at 15:40

Alan B

4,086
24
33

score 0 · Answer 31 · answered Jan 20 '22 at 08:56

#macusers

I have found new solution without closing the Visual Studio.

Go to the terminal.
lsof -i:<PORT_Number> i.e. 1433 in my case.
Outout will be process using that port number
Kill those processes using kill -9 <PID> i.e. process Id

Please refer Screenshot attached.

score 0 · Answer 32 · answered May 31 '22 at 18:33

In my case it was a wrong port. I connected to the on-prem DB from an Azure App Service via a Hybrid connection and the connection used one port, but the SQL Server instance used a different port. Make sure all of the ports are matching: connection string, VPN/Proxy/Hybrid connections, SQL Server allowed ports for each protocol.

sqlcmd and ping/tcpping are great tools to test your connectivity before you try changing any code in your app. You can run them both locally and in the cloud.

A connection was successfully established with the server, but then an error occurred during the pre-login handshake

32 Answers32

Linked