locked
SQL Server Pre-Login Handshake RRS feed

  • Question

  • I'm connecting to MSSQL database through my ASP .NET application, but sometimes I got this error while opening connection.

    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=3; handshake=14996;

    To solve it temporarily I've to restart IIS. I'm using this code snippet to connect to MSSQL:

    	using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
    
                /* my commands here */
    
                connection.Close();
                connection.Dispose();
                SqlConnection.ClearPool(connection);
            }

    I did some investigations and I did:

    • I allowed the port 1433 in the inbound and outbound rules in the database server, but no changes.
    • I allowed IPv6 in the database server.
    • All MSSQL services is running in the database server, and there are other apps connected to the same server with other databases but the error appears with this site only.

    but nothing changed




    • Edited by Ahmed.Negm Monday, October 13, 2014 7:08 PM
    Monday, October 13, 2014 7:06 PM

Answers

  • Ahmed,

    this is something related to your network, you can be almost 100% sure of that.

    See if you can connect through the IP address instead of the DNS name or try to add an alias in the native client of the client machine, that points to the server IP and has the same name.

    Try to troubleshoot your network. Does the database server have access to the AD DS? Is the DNS server address set up correctly for the server network interface used by the instance?

    Tuesday, October 14, 2014 1:52 PM
  • Ahmed,

    I didn't mean installing AD DS in the same server as the SQL one, but rather monitor the connectivity between them, but since you said you switched clients and the problem didnt happen anymore, it appears there was something wrong with your client.

    I'm not a network specialist so I dont believe I have any further advice in this case.

    Tuesday, October 14, 2014 3:16 PM

All replies

  • It could be that your instance is under resource pressure, and is unable to process the login request. This "handshake" occurs while authenticating. Are you able to connect to the instance through SSMS? Are you connecting locally or remotely? How is resource usage in your server? Can you monitor conectivity between the application and database servers? Is there any package loss? Intermittent connection? How many connections are active in your SQL Server instance?
    Monday, October 13, 2014 7:46 PM
  • Dear Samir,

    I can connect to my database through SSMS normally without any problems, and I have a lot of apps and a lot of databases and this error doesn't appear there but this database and this app.

    I connect locally from my app server to my database server with the default connection time-out 600.

    The connection between servers is stable. The count of active connections for all databases is 160 connection.


    Best Regards, Ahmed Negm

    Tuesday, October 14, 2014 11:04 AM
  • Hello Ahmed,

    What about the Connection string, something different to the Connection string of your other applications?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 14, 2014 11:29 AM
  • No, the same one.

    Data Source=xxx.xxx.xxx.xxx;Initial Catalog=dbName;Persist Security Info=True;User ID=user;Password=pwd


    Best Regards, Ahmed Negm

    Tuesday, October 14, 2014 11:32 AM
  • I did all of those configurations, but nothing changed!

    Best Regards, Ahmed Negm

    Tuesday, October 14, 2014 1:34 PM
  • Ahmed,

    this is something related to your network, you can be almost 100% sure of that.

    See if you can connect through the IP address instead of the DNS name or try to add an alias in the native client of the client machine, that points to the server IP and has the same name.

    Try to troubleshoot your network. Does the database server have access to the AD DS? Is the DNS server address set up correctly for the server network interface used by the instance?

    Tuesday, October 14, 2014 1:52 PM
  • Samir,

    I moved the application to another app server, and I got positive results for now, so I agree with you in the point of network problem, but please could you advise how to check it, or guide me to the points which I should check.

    According to the your other point, my database server has no AD DS because it's no recommended to install AD on the DB server as you know, and the same to my  app server. On the other hand, the DNS is configured successfully like other network server.

    I'm looking forward for your troubleshooting guide, thank you in advance


    Best Regards, Ahmed Negm

    Tuesday, October 14, 2014 2:36 PM
  • Ahmed,

    I didn't mean installing AD DS in the same server as the SQL one, but rather monitor the connectivity between them, but since you said you switched clients and the problem didnt happen anymore, it appears there was something wrong with your client.

    I'm not a network specialist so I dont believe I have any further advice in this case.

    Tuesday, October 14, 2014 3:16 PM