1

I have an issue with connecting to a server on SSMS SQL Server. I got this error 10054 when i tried to connect :

SQL Server error 10054

===================================
Cannot connect to <IP>.
===================================
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Framework Microsoft SqlClient Data Provider)

------------------------------
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-10054-database-engine-error
------------------------------
Server Name: <IP>
Error Number: 10054
Severity: 20
State: 0
------------------------------
Program Location:

   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(SNIHandle handle, SNIPacket packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.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've tried on other laptop, some of the laptop can connect it, some cannot.

I also have tried different SQL Server version (19 and 22) and also tried different SSMS version (19.0.1, 19.0.2, 19.1)

I already tried adding additional connections parameters too in the SSMS options on Connect to Server Menu

Additional Connection Parameters

I also tried and checked solution in this website but still nothing works

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-exist-connection-closed

I have tried to open inbound and outbound firewall for port 1433 too

I did find error on C:\Program Files\Microsoft SQL Server\MSSQL16.SQL_SERVER\MSSQL\Log when I try to restart the SQL Server service

2023-07-15 22:04:03.71 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Davin:SQL_SERVER ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2023-07-15 22:04:03.71 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Davin:51166 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Hope anyone can help me, thanks

1
  • I'm using SQL Server login. I think I did find the errors when restarting the SQL Server Service. The errors was on C:\Program Files\Microsoft SQL Server\MSSQL16.SQL_SERVER\MSSQL\Log. But I'm not sure if that the problem, because I just found it out. I've update the error detail above.
    – Davin
    Jul 15, 2023 at 15:13

1 Answer 1

1

Error Message

The cause for 10054 errors according to Microsoft is:

  • A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

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

The operating system error 10054 is raised in the Windows sockets layer. For more information, see Windows Sockets Error Codes: WSAECONNRESET 10054.

Reference: An existing connection was forcibly closed by the remote host (OS error 10054) (Microsoft | Learn)

The article then goes on to explain a couple of scenarios:

In summary, you seem to have encountered an TLS/SSL issue.

Windows Server 2012

In older versions of Windows Server (for example Windows Server 2012) TLS 1.2 wasn't activated by default. The capability to encrypt with TLS 1.2 was introduced with a service pack (I think this was Windows Server 2012 Service Pack 2), but even then encryption wasn't automatically enabled after applying the Service Pack.

Enabling TLS 1.2

In order to enable if (or check that) TLS has been activated at the Windows Server Level, have a look at the following registry settings on your server:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client] 
"DisabledByDefault"=dword:00000000 
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server] 
"DisabledByDefault"=dword:00000000 
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] 
"DisabledByDefault"=dword:00000000 
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server] 
"DisabledByDefault"=dword:00000000 
"Enabled"=dword:00000001

The upper portion is for TLS 1.1 (not recommended) and the lower portion is for TLS 1.2. (recommended).

  1. If the registry settings are there, then TLS 1.2 should be active.
  2. If you had to add the registry settings to your Windows Server's registry, then please reboot your server.
  3. After activating TLS 1.2 via the registry, you should be able to connect to your SQL Server from your client.
  4. If not, continue with the mentioned trouble-shooting scenarios (Microsoft article)

Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.