0

I have SQL Server 2019 and SSMS installed on my local system. I want to connect to remotely connect to another SQL Server with SSMS using these credentials:

  • Server Name: 194.37.76.314,88756
  • Authentication: SQL Server Authentication
  • Login: myusername
  • Server Name: mypassword

I got this error:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (.Net SqlClient Data Provider)

Server Name: 194.37.76.314,88756 Error Number: 10054
Severity: 20 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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 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()

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

An existing connection was forcibly closed by the remote host

I have been connecting to the SQL Server before, but this error just started suddenly.

I tried to implement:

  • 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.

from

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

Yet unresolved.

How do I resolve this?

Thank

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
midowu
  • 937
  • 5
  • 19
  • 40
  • Check the log on the remote server. See if it has any additional error messages related to this message or if there is any non-zero state codes reported. – RBarryYoung Mar 17 '22 at 13:09
  • 3
    Since TCP/IP port numbers cannot go above 65535, there's no way you can successfully connect using that string. Double-check what you're doing. If you're mangling the string for confidentiality purposes, the best anyone can guess is that there's some sort of network issue going on. Named pipes are a red herring in any case, you're only getting that because the TCP/IP connection is failing. Consider using the `tcp:` prefix to avoid this useless fallback. – Jeroen Mostert Mar 17 '22 at 13:16
  • @JeroenMostert Yeah, that's what I thought at first too, but if the port number was invalid, they shouldn't have gotten as far as the pre-login handshake, should they? – RBarryYoung Mar 17 '22 at 13:20
  • @RBarryYoung: you could see this exchange if something other than SQL Server was listening on that port (of course not with a completely invalid port number like this). If a SQL Server is known to be listening, some kind of network issue is still the most likely (as opposed to SQL Server actually terminating the connection in that phase, especially since SQL auth is being used so there's no Kerberos getting in the way). – Jeroen Mostert Mar 17 '22 at 13:23
  • @JeroenMostert some other users can access it from their systems with the same credentials – midowu Mar 17 '22 at 13:28
  • That's good -- it means you can compare situations and narrow down the issue. As in, is your system on the same subnetwork, is there a firewall difference, is your Ethernet cable on the fritz, is your SSMS and/or SQL client driver possibly different... the things that are not different are the remote SQL Server and the credentials, so that's most likely *not* the problem. As a question, though, this should then probably be closed as "not reproducible", since whatever's going wrong is going wrong on your machine only. – Jeroen Mostert Mar 17 '22 at 13:29
  • 1
    Since it's failing during TLS setup, and the error message is blaming the remote server closing the connection, I'd be checking what TLS protocol versions and ciphersuites are supported on your client computer - versus the other ones that are connecting successfully. In PowerShell you can use `Get-TlsCipherSuite | FT Name` to show the enabled ciphersuites. Examine the registry via `HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols` for `TLS 1.0\Client`, `TLS 1.1\Client` and `TLS 1.2\Client` protocols to test the `DisabledByDefault` and `Enabled` flags. – AlwaysLearning Mar 17 '22 at 20:48
  • For Ubuntu 22.04, please follow https://stackoverflow.com/a/72137669/5772425 – Sayyed Dawood Jun 28 '22 at 09:01

0 Answers0