[sql-server] A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 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.

In my case I had the following by mistake in my connection string:

Encrypt=True

Changing to

Encrypt=False

Solved the problem

"Server=***;Initial Catalog=***;Persist Security Info=False;User  ID=***;Password=***;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;"

To add on top of @Pranav Singh and @Rahul Tripathi answer. After doing all the mentioned by those 2 users, my .net app still wasnt connecting to the database. My solution was.

Open Sql Server Configuration Manager, go to Network configuration of SQL SERVER, click on protocols, right click on TCP/IP and select enabled. I also right clicked on it opened properties, Ip directions, and scrolled to the bottom (IPAII , and there in TCP Port, I did setup a port (1433 is supposed to be default))

This is what helped me [src]:

SQL 2016 solution/workaround here (could also work in earlier versions). This may not work or be appropriate in every situation, but I resolved the error by granting my database user read/write schema ownership as follows in SSMS:

Database > Security > Users > User > Properties > Owned Schemas > check db_datareader and db_datawriter.

Sometimes specifying the database (instead of default) solves this error.

You can also get this error, somewhat unhelpfully, if the database name specified in the connection string doesn't exist. Check your Db Name carefully!

I've gotten this error because the user trying to login was lacking permissions.

I don't recommend doing this, but I fixed it by granting the user db_owner globally. It was a local instance, so not a huge security concern.

From here:

Root Cause: Maximum connection has been exceeded on your SQL Server Instance.

How to fix it...!

1. F8 or Object Explorer
2. Right click on Instance --> Click Properties...
3. Select "Connections" on "Select a page" area at left
4. Chenge the value to 0 (Zero) for "Maximum number of concurrent connections(0 = Unlimited)"
5. Restart the SQL Server Instance once.

Apart from that also ensure that below are enabled:

• Shared Memory protocol is enabled
• Named Pipes protocol is enabled
• TCP/IP is enabled

the following points work for me. Try:

1. start SSMS as administrator
2. make sure SQL services are running. Change startup type to 'Automatic'

3. In SSMS, in service instance property table, enable below:

For me, adding Trusted_Connection=True to the connection string helped.

For SQL2008,

• open Management Studio
• Rt click on instance
• go to properties
• select Security
• Under Server Authentication, check SQL Server and Windows Authentication Mode
• hit OK

Restart the server using configuration manager.

I had to do 2 things: Do what Joseph Wu said and change the authentication to be SQL and Windows. But I also had to go to Server Properties > Advanced and change "Enable Contained Databases" to True.

I got that error message in code line containing SqlConnection.Open() running my .NET code as x64 application. Running as x86 causing no errors.

Solution was to deactivate the Force protocol encryption option for TCP/IP in %windir%\System32\cliconfg.exe

Force protocol encryption

When you starting application 'Run as administrator'.This way I avoided this error.

Here is the solution I found:

Go to SQL Server Management, Disable Memory Shared, TCP/IP and Pipes named.

Restart the server and try again.

My problem resolved by this what changes i have done in .net core Do this changes in Appsetting.json

Server=***;Database=***;Persist Security Info=False;User ID=***; Password=***;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30

and Do following changes in Package console manager

Scaffold-DbContext "Server=***;Database=***;Persist Security Info=False;User ID=***; Password=***;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context DatabaseContext -f

Happy coding