A connection was successfully established with the server but then an error 233

I am having error while connecting to SQL Server:

Details in Stack Trace are:

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

Cannot connect to ServerName.

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

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

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

------------------------------
Server Name: ServerName
Error Number: 233
Severity: 20
State: 0


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   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()

NOTE I have tried

• closing, reopening Sql server Management Studio.

• closing , reopen VS & rebuild Solution

• killed worker process accessing database.

• login credentials are correct.

• able to ping server to make sure its not down.

Introduction

After the global RansomeWare attack, we updated our Windows Server 2012 R2 only to find out that our SQL Server Management Studio (SSMS) stopped working. Whenever we try to connect to SSMS locally or remotely it provides us with the following
Error:

A connection was successfully established with the server, but then an error occurred during the login process. (Provider: SSL Provider, error: 0 — No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Even after trying to log in with «Windows Authentication» and the error was same. Also tried solutions for similar SQL Error 233 issues but with no result.

The Solution

After much tinkering, the finding was that the Windows Update has modified the TLS and SSL settings in a wrong way. Specific changes had to be made to the Cipher Suits and Protocols in the registry in order to make it (SSMS) work again and now we can log
in to SSMS successfully (both locally and remote login). So the specific changes which had to be made to the registry are as follows:

Changes in Schannel:: (Inlcuding Client Side Protocols)

Protocols Enabled: Multi-Protocol Unified Hello, PCT 1.0, TLS 1.0

Protocols Disabled: SSL 2.0, SSL 3.0, TLS 1.1, TLS 1.2

Ciphers Enabled: NULL, DES 56/56, RC2 40/128, RC2 56/128, RC2 128/128, RC4 40/128, RC4 56/128, RC4 64/128, RC4 128/128, Triple DES 168, AES 128/128, AES 256/256

Hashes Enabled: MD5, SHA, SHA 256, SHA 384, SHA 512

Key Exchanges Enabled: Diffie-Hellman, PKCS, ECDH

Cipher Suite Changes:: 

Disabled: SSL_CK_RC4_128_WITH_MD5, SSL_CK_DES_192_EDE3_CBC_WITH_MD5

Enabled: Everything else realted to TLS

To achieve this easily (changes to security and modification of registry) Also used this tool (IISCrypto): https://www.nartac.com/Products/IISCrypto

Do make sure that each cipher or protocols mentioned here are specifically enabled or disabled, or else the issue won’t be resolved.

References

1. Initial Solution is provided in Stack Overflow [Link:
   https://stackoverflow.com/questions/44067698/how-to-solve-microsoft-sql-server-error-233-provider-ssl-provider]
2. Similar but different solutions for the same issue can be found at www.sqlservercentral.com [Link:

   https://www.sqlservercentral.com/Forums/Topic1810025-3411-1.aspx]

Sometimes, while accessing the MSSQL Database, users encounter “SQL server error 233” or  “SQL Server login failed error 233” or “pipe error 233”. This is a general connection error and to fix this you need to test different solutions. The error 233 come with different messages. Below we will share all the information that needs to be checked one by one.

You can get this error message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe) (Microsoft SQL Server, Error: 233)

You can see an error message like this:

Main Causes of pipe error 233

The SQL server error 233 implies that the provider name is Shared Memory Provider. The Shared Memory is a protocol used in SQL Server along with TCP/IP as well as Named pipe. 

If you check the settings and values for all protocols in SQL Server Configuration Manager. You can see there an option of  “Protocols for MSSQLSERVER”(here MSSQLSERVER is the name of SQL Server). 

In addition to this, if you have named instance installed on your machine then you will get “Protocols for <INSTANCENAME>.

Now after clicking on this option “Protocols for MSSQLSERVER”, you can view all the three protocols used in SQL Server in the right-side panel as shown in the below image.

Here you can see Shared Memory protocol is set as “Enabled” but Named Pipes is set as “Disabled” mode. So, this should be enabled to make a successful database connection. This was the main reason for getting this error.

You can also experience Error 233 in case SQL Server Instance has exceeded the total number of user connections set in server configurations.

Also Read: Free Methods to Recover Corrupted MySQL Database

Checklist to fix Server error 233/ Error 233 in SQL server 2017

These are the list to be checked when Microsoft SQL server error 233 in SQL server 2017 occurs.

1. Enable SQL server and windows authentication
2. Check default database
3. Max allowed connections
4. Shared memory, TCP/IP, Named Pipes
5. Check if SQL server browser has started
6. “Maximum server memory” to below the amount which could run the services
7. Check for remote connection

Solutions to resolve“Microsoft SQL Server Error 233- Login failed for user”

Let us discuss the best possible techniques to resolve this error.

Method 1: Enable SQL server and windows authentication

• Login to the MS SQL server with windows authentication
• Do Right-click onto the server name in object explorer and then click on properties
• Click on security, select SQL server & windows authentication
• Select OK
• Then, Close the SQL server management studio
• Click on start then run & write services.msc
• Then, search for SQL and restart all services.

Method 2: Check default database

Check the default database & if it is online. You will see these issues commonly when the default database is offline or does not exists. If your default database is other than master then, you need to revert to master.

Method 3: Max allowed connections

Open MSSMS & run the command below mentioned:

sp_configure ‘show advanced options’, 1;

go

reconfigure

go

sp_configure ‘user connections’, 0

go

reconfigure

go

You got this error and the reason will be that the user connections was reset to 1. Only one user was able to connect with SQL Server. Just a simple query & the error fixed. 

Method 4: Shared Memory, TCP/IP, Named Pipes

Typically, to troubleshoot this, You have to go SQL server configuration manager (SSCM) and:

• Check if shared memory protocol is enabled or not
• Ensure if named pipes protocol is enabled
• Confirm if TCP/IP is enabled, and s ahead of the named pipes in the settings

Method 5: “Maximum server memory” to below the amount which could run the services

SQL server -f-m-s name of your instance

Keeping CMD open, open new CMD as an administrator and navigate to your SQL server instance folder (mine is: C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBinn)

And run the following command which is given below to increase instance memory to GB:  

sqlcmd

EXEC sys.sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

EXEC sys.sp_configure ‘min server memory’, 1024;

GO

EXEC sys.sp_configure ‘max server memory’, 4096;

GO

RECONFIGURE;

GO

Now, SQL server memory is increased. Close cmd windows and check if the SQL service is running by going to SQL Server – Configuration Manager.

You can connect to your instance through SSMS.

Method 6: Check if SQL server browser is open and working

To resolve this issue, follow one of the following steps:

• Start > Run > type services.msc and press enter – find SQL browser & start it
• Use “net start” I Start > run > CMD; type net start SQLBrowser

Method 7: Check for remote connection

It is important is to enable the remote connection. If you have turn off this feature, then SQL server will work properly on your system but if the feature is on then, you can follow the following steps:

• Click on server node – choose properties.
• Select connection – Check “arrow remote connection to this server”

This is the manual method through which you can fix the SQL service login failed error 233. But this method is too tough for the non-technical guys. You should have the knowledge of Compters & technology to apply these techniques.

Alternative Method

In order to fix the SQL, Server login failed error 233 you can use MS SQL Database Recovery Tool. This tool has a user-friendly GUI that even a non-technical person can install & run the software and fix login failed for user problem. The SQL Recovery Utility runs on Windows 10/8/7/XP/Vista/2000/2010.

It also support SQL server version 2000, 2005, 2008, 2012, 2014, 2016, 2017, 2019

Conclusion

In this article, We have shared Manual, as well as the Automated method to fix Microsoft SQL server error 233.  Manual methods need technical expertise to fix this issue. But the alternative method GUI is user-friendly. You don’t need any technical information for fixing the issue. Hope this post helps you to solve this issue.

Related Post