Connection time out SQL 2017

Question

Connection time out SQL 2017

Tomys

Mr or Mrs. 500

Points: 567
More actions
May 6, 2020 at 10:42 am

#3749245

Hi Team,
I am getting this message while I try to connect to a new SQL Sever 2017 server :
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=9; handshake=29995; (.Net SqlClient Data Provider)
Any clue?
I am able to connect locally to this host from my SSMS i.e. from my machine and after I RDP to the host itself.
But after I RDP to another other host , I try connecting , I get the above message .

Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

Incorrect connection string. No open port. No open IP address. Something network related for sure. Check that the listener service is running. Gotta be something along these lines because if local works and remote doesn't, what's the difference? Network.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 2

yes something with network, port 1433 is open, telnet works, what is listener service? where can I check if running? thanks

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

the listener is the SQL Browser. Not on by default, but if you connect to 1433, and you have a local instance running on 1433, that shouldn't matter. You are sure 1433 is open in the firewall?

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 4

Hi Steve, yes firewall port for SQL 1433 is open , attaching the inbound rule from the SQL Server. This is a default instance and there are no named instances on this machine.

Full Error is like below when trying to connect from one SQL Server machine.

However , from some other SQL Servers when I try connecting the connection is success. There is no time out seen. Even when trying to connect to the SQL server from my machine SSMS no issues.

The source SQL Server is SQL server 2017 on windows 2019.

Full Error:

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=14; handshake=29997; (.Net SqlClient Data Provider)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

------------------------------

Server Name:

Error Number: -2

Severity: 11

State: 0

Program Location:

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)

at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

The wait operation timed out

dmbaker SSCertifiable Points: 5086 More actions · Answer 5

Is your new server clustered or part of an "availability group"? I think those are the right terms, I'm not an expert on that.

If this is the case, and assuming you can modify your connection string, you might try adding "MultiSubnetFailover=True" to your connection string.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery

I've got this issue where I work, generally get slow connection time and occasional timeouts without this. SSMS will usually work, but apps generally always fail with a connection timeout without this setting in the connection string (regardless of .NET version, in spite of what article says).