Unable connect to SQL Server on SSMS and Got error 10054

Question

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

Answer 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:

• Scenario 1: No matching TLS protocols exist between the client and the server
• Scenario 2: Matching TLS protocols on the client and the server, but no matching TLS cipher suites
• Scenario 3: SQL Server uses a certificate signed by a weak-hash algorithm, such as MD5, SHA224, or SHA512

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.

Answer 2

In my case this error was caused by me creating the docker version of SqlServer without it being able to create the data folder.

After fixing that issue it worked just fine.