Connection to SQL Server Works Sometimes

Question

An ADO Net application is only sometimes able to connect to another server on the local network   It seems random whether a given connection attempt succeeds or fails   The connection is using a connection string in the form      Server THESERVER TheInstance Database TheDatabase User Id TheUser  Password ThePassword    the error returned is      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 42030  handshake 0     The  NET application is a small test app that executes the following code   using  SqlConnection conn   new SqlConnection cs   using  SqlCommand cmd   new SqlCommand  SELECT COUNT    FROM TheTable   conn         conn Open        int rowCount    int cmd ExecuteScalar        TheTable is small  just 78 rows   However  on the same machine where the  NET application receives this error  I am able to connect to THESERVER using SSMS and the User Id Password named in the connection string   Why might the connection fail from an ADO Net app  but succeed with identical credentials from SSMS

User · Answer

In my case  the parameter Persist Security Info true with the user and password in connection string is causing the problem  Removing the parameter or set to false solve the problem

User · Answer

For me  it turns out that the firewall in windows server was blocking the port 1433 which is the default sql server port  So adding an inbound rule to accept those connections made the trick for me

User · Answer

In our case problem occured due to availability cluster configuration  To solve this issue we had to set MultiSubnetFailover to True in the connection string   More details on MSDN

User · Answer

In my case above all options were already there   Solved it by increasing Connection Time-out   30

User · Answer

Ive had the same error just come up which aligned suspiciously with the latest round of Microsoft updates  09 02 2016   I found that SSMS connected without issue while my ASP NET application returned the  timeout period elapsed while attempting to consume the pre-login handshake acknowledgement  error  The solution for me was to add a connection timeout of 30 seconds into the connection string eg   ConnectionString  Data Source xyz Initial Catalog xyz Integrated Security True Connection Timeout 30     In my situation the only affected connection was one that was using integrated Security and I was impersonating a user before connecting  other connections to the same server using SQL Authentication worked fine   2 test systems  separate clients and Sql servers  were affected at the same time leading me to suspect a microsoft update

User · Answer

I had the same problem  trying to connect to a server in a local network  through VPN  from Visual Studio  while setting up an Entity Data Model  Managed to solve only by setting TransparentNetworkIPResolution false in the connection string   In VS Add Connection Wizard  you can find it in the Advanced tab

User · Answer

My executable that was built using  NET Framework 3 5 started reporting these connection issues in about half of the times after some Windows Updates got installed recently  week of Aug 7  2017    Connection failures were caused by  NET Framework 4 7 that got installed on target computer  Windows Updates auto-install was on  - https   support microsoft com  kbid 3186539  Uninstalling  NET Framework 4 7 solved connection issues   Apparently  there is a breaking change in  Net Framework 4 6 1 - TransparentNetworkIPResolution Updating connection string as per article also solved the issue without the need to roll back the framework version

User · Answer

I had the same problem  manage to solve it by opening enabling the port 1433 and tcp ip in SQL Server Configuration Manager and then Restarted the Server

User · Answer

Adding a response here  despite previously accepted answer  As my scenario was confirmed to be DNS  More specifically  a dns timeout during the pre-login handshake  By changing from a DNS name to an IP Address  or using Hosts file entry   you bypass the problem  Albeit at the cost of losing automatic ip resolution  For example  even with a Connection String s timeout value set to 60 for a full minute  it still would happen within a couple seconds of the attempt  Which leads one to question why would it timeout before the specified timeout period  DNS

User · Answer

I solved the problem like Eric but with some other changes   Start Sql Server Configuration Manager Open the node SQL Server Network Configuration Left-click Protocols for MYSQLINSTANCE In the right-hand pane  right-click TCP IP Click Properties Select the IP Addresses tab For each listed IP address  ensure Active and Enabled are both Yes   AND  For each listed IP address  ensure TCP Dynamic Ports is empty and TCP Port   1433  or some other port  Open windows firewall and check that the port is Opened in Incoming connections

User · Answer

To trace the  quot Connection Timeout expired quot  error  Please  make sure that   The instance of the SQL Server Database Engine is up and running  The SQL Server Browser service is running  The TCP IP is enabled  The server name was typed correctly  There are no network problems as mentioned at How to Check SQL Server Instance Connectivity from the application server to database server The TCP IP port for the Database Engine instance is not blocked by a firewall  The client and server are configured to use the same network protocol    For more details  Please check Connection Timeout Expired  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment

User · Answer

Before you lose more time solving the problem  like me  try just to restart your windows machine  Worked for me after applying all the other solutions

User · Answer

It turned out that TCP IP was enabled for the IPv4 address  but not for the IPv6 address  of THESERVER   Apparently some connection attempts ended up using IPv4 and others used IPv6   Enabling TCP IP for both IP versions resolved the issue   The fact that SSMS worked turned out to be coincidental  the first few attempts presumably used IPv4   Some later attempts to connect through SSMS resulted in the same error message   To enable TCP IP for additional IP addresses    Start Sql Server Configuration Manager Open the node SQL Server Network Configuration Left-click Protocols for MYSQLINSTANCE In the right-hand pane  right-click TCP IP Click Properties Select the IP Addresses tab For each listed IP address  ensure Active and Enabled are both Yes

User · Answer

I fixed this error on Windows Server 2012 and SQL Server 2012 by enabling IPv6 and unblocking the inbound port 1433

User · Answer

I had the exact issue  tried several soultion didnt work   lastly restarted the system   it worked fine

User · Answer

I had the same handshake issue when connection to a hosted server   I opened my Network and sharing center and enabled IPv6 on my wireless network connection

User · Answer

Try a simple SQL Server restart first before doing anything drastic  Might fix it  It did for me

User · Answer

I had this problem when I did a SharePoint 2010 to 2013 migration   I suspected that because the database server is on the other side of a firewall which does not route IP6 that it was trying then to use IP6 and failing when connecting to the database   I think the issue is now solved   The errors seem to have stopped   What I did was I simply disabled IP6  by unchecking it  for the network adapter on the SharePoint Servers

User · Answer

I had this same issue  but I was connecting to a remote db using a static IP address   So none of the above solutions solved my issue     I had failed to add the proper User Mapping for the security Login I was using  so the solution for me was simply to ensure the User Mapping setting was set to access my database

User · Answer

I had the same issue that automatically resolve after last Microsoft windows update  does anyone experience the same

User · Answer

Solved this problem by blocking  blacklisting IP address that were trying to brute force user accounts  Check your SQL access logs for large numbers of failed login attempts  usually for the  sa  account

User · Answer

Unfortunately  I had problem with Local SQL Server installed within Visual Studio and here many solutions didn t work out for me  All I have to do is to reset my Visual Studio  by going to   Control Panel   Program  amp  Features   Visual Studio Setup Launcher  and click on More button and choose Repair  After that I was able to access my Local SQL Server and work with local SQL Databases

[sql-server] Connection to SQL Server Works Sometimes

Examples related to sql-server

Examples related to ado.net