A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)

Question

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.

Possible duplicate of [SQL Server 2008 Error 233](http://stackoverflow.com/questions/6072505/sql-server-2008-error-233) — MusicLovingIndianGirl, Dec 23 '15 at 07:19
Check whether all your SQL Services are running. If yes, still restart all once and check again. Also, check whether MIxed authentication mode enabled for your SQL server. — Paresh J, Dec 23 '15 at 07:21
I have enabled and started the browser service and the issue resolved. Please try. — user6232480, May 30 '17 at 17:49
My server is running in Mixed mode, I've stopped and started it and I'm still getting this error :-(. I'm getting this from SQL localdb specifically. — bytedev, Aug 10 '18 at 09:52
same problem happened to me and changing the DNS to the google DNS (8.8.8.8) fixed it — Hossein Narimani Rad, Mar 28 '23 at 06:51

Tyson Gibby · Answer 1 · 2023-05-05T14:16:34.850

169

Same Error with Connection String in Visual Studio dev environment

Our development database server was recently given a self-signed certificate so it automatically became untrusted. This resulted in the login error cited above. I added TrustServerCertificate=True to my connection string and it works now.

"Server=TheServerAddress; Database=TheDataBase; User Id=TheUsername; Password=ThePassword; TrustServerCertificate=True"

NOTE: This certificate configuration is not recommended for production environments.

edited May 05 '23 at 14:16

answered Feb 22 '22 at 22:19

Tyson Gibby

2,590
2
18
37

2

This fixed it for me. It appears to be a breaking change in Microsoft.Data.SqlClient version 2. See https://erikej.github.io/sqlclient/efcore/2020/06/22/sqlclient-2-breaking-changes.html – andrej351 Aug 10 '22 at 01:32
12

Thanks, the only solution worked for .NET 7 – Hashem Ahmed Nov 20 '22 at 00:18
12

Worked `TrustServerCertificate=True` – Gnyasha Dec 05 '22 at 00:26
2

This worked for my .NET 7.0 ASP.NET Core deployment to IIS 8.5. Without this, the connection string fails with 404 Response Error and a whole bunch of untrusted secure certificate errors. – Dave Stuart Jan 08 '23 at 01:19
2

Worked for me after upgrading from .NET 6 to .NET7 and latest EF Core. Cheers – EM15 Jan 30 '23 at 09:23
1

This answer is a saviour! Thank you! – Yash Gupta Feb 21 '23 at 18:27
1

Worked on my .Net 7 project, Just added "TrustServerCertificate=True". – erenozten Mar 05 '23 at 17:26
Worked for me: "Server=.; Initial Catalog=ProductDb; Integrated Security=True; TrustServerCertificate=True;" Where Server = DOT; – Rahim Mar 29 '23 at 11:45
I had to add both TrustServerSecurity=True and Integrated Security=True, even though I'm using an explicit pass and user. – tutiplain Apr 15 '23 at 20:13

score 109 · Answer 2 · edited Jul 05 '18 at 08:20

109

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.

edited Jul 05 '18 at 08:20

answered Mar 01 '17 at 18:13

user7643068

1,099
2
7
2

Worked on V17 as well. – Asad Ullah Feb 08 '18 at 20:21
Many thanks, it saved a lot of time. On first install of SSMS it is selected "Windows Authentication Mode" by default. – balron Oct 28 '19 at 18:41
1

Works also on SQL 2019, but you need to re-start the server after applying this setting – Orhan Celik Dec 16 '20 at 15:16
Just installing MSSql 2019...this was a big help – Chris Catignani Jun 04 '21 at 01:19
16

Not working with .NET 7.0. Adding `TrustServerCertificate=True` to the connection string was the solution, according to Tyson's answer. – Usama Aziz Nov 26 '22 at 12:02
Thank you @UsamaAziz adding `TrustServerCertificate=True` solved the issue from my end as well. – Ram Fattah Dec 12 '22 at 03:55
thank you @UsamaAziz , can confirm `TrustServerCertificate=True` fixed my issue. – Niklas Mar 28 '23 at 07:05

Duck Ling · Answer 3 · 2021-05-21T16:44:37.947

33

This is what helped me [src]:

File > Connect Object Explorer... > Options (bottom right) > Connection properties tab > Trust Server Certificate tickbox

edited May 21 '21 at 16:44

answered Nov 17 '20 at 16:27

Duck Ling

1,577
13
20

This also works in Azure Data Studio (including macOS) – Jedidja Oct 17 '22 at 14:40
This solved my problem, especially while scaffolding data for .NET Entity Framework. – Paul Nov 11 '22 at 16:48
I have been trying to figure this out since December of 2022. Thank you so much! – AlpacaRG Apr 29 '23 at 16:50

Rauland · Answer 4 · 2020-07-17T16:39:11.470

31

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;"

edited Jul 17 '20 at 16:39

answered Jul 17 '20 at 15:59

Rauland

2,944
5
34
44

6

+1 This seems to be the new norm for .Net Core 7. Was working fine yesterday before updating, but today failed until this was added. – EvilDr Nov 11 '22 at 14:01
5

It works fine but I think it's a better idea to add TrustServerCertificate=True instead of disabling encryption by adding Encrypt=False. – Sinan ILYAS Nov 15 '22 at 13:18
1

down voted because disabling encryption may be against many companies policies, and at the least is against best practice. – Andrew Hill Jan 02 '23 at 23:22

score 29 · Accepted Answer · edited Nov 25 '21 at 06:54

29

Turn out to be some SQL service was stopped somehow on server. Restarting SQL Server service manually was the solution.

edited Nov 25 '21 at 06:54

Ruli

2,592
12
30
40

answered Jan 21 '16 at 11:21

Pranav Singh

17,079
30
77
104

Use Sql Server Configuration Manager > SQL Server Services to find out what processes are running (C:\Windows\SysWOW64\SQLServerManager15.msc) – Denis G. Labrecque Apr 19 '22 at 15:45

score 27 · Answer 6 · answered Jan 15 '21 at 02:08

27

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

answered Jan 15 '21 at 02:08

Vojtěch Vorel

371
3
4

That was the issue on my end as well. Why can't microsoft give more useful error messages. – Eternal21 Jun 15 '23 at 19:41

score 23 · Answer 7 · answered Sep 05 '19 at 09:12

23

the following points work for me. Try:

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

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

answered Sep 05 '19 at 09:12

Joseph Wu

4,786
1
21
19

Where the heck was the first screenshot taken? – Denis G. Labrecque Apr 19 '22 at 15:41
1

it is under the Task Manager > Services tab – Joseph Wu Apr 20 '22 at 06:49

score 7 · Answer 8 · answered Nov 03 '20 at 17:33

7

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!

answered Nov 03 '20 at 17:33

Rikalous

4,514
1
40
52

score 4 · Answer 9 · edited Aug 04 '23 at 22:41

4

For MS SQL Management Studio

Options >> connection properties and check Trust Server Certificate option

Edit:
For Those wondering how to reach the above window. The options button could be found in the bottom right corner of the login dialogue Screen shot of login dialogue

edited Aug 04 '23 at 22:41

Community

1
1

answered Jun 17 '21 at 09:21

Thabet

307
1
3
10

This is the answer that should have been accepted. – Zizzipupp Jan 11 '22 at 14:23
This may be a great solution except how to find this window – Denis G. Labrecque Feb 01 '23 at 21:25

score 3 · Answer 10 · answered Dec 23 '15 at 07:20

3

From here:

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

How to fix it...!

F8 or Object Explorer

Right click on Instance --> Click Properties...

Select "Connections" on "Select a page" area at left

Chenge the value to 0 (Zero) for "Maximum number of concurrent connections(0 = Unlimited)"

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

answered Dec 23 '15 at 07:20

Rahul Tripathi

168,305
31
280
331

1

Correct me if I am wrong but if you cannot login Object Explorer will not show the instance of the server – Bmize729 Jun 28 '18 at 01:31
I'm having trouble starting SQL Server Agent to try that. It wont' start for me.' – Sam Oct 02 '20 at 23:32
This was my issue (Azure Sql Server). I was processing a queue with hundreds of messages all arriving at once. Resolved by slowing the queue down to process 5 at a time. – GreenRock Nov 23 '22 at 23:35

score 3 · Answer 11 · answered Jun 27 '18 at 18:15

3

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

answered Jun 27 '18 at 18:15

Kishor K

1,435
12
16

score 3 · Answer 12 · answered Jun 03 '23 at 22:24

3

I solved it using this parametres Trusted_Connection=true;TrustServerCertificate=True

If you have a problem, update OLEDB Driver 18.6.5

Thanks.

answered Jun 03 '23 at 22:24

aemre

2,351
2
17
20

YashTheRedDevil · Answer 13 · 2022-11-01T15:40:05.890

Steps to resolve the issue:

Goto File > Connect Object Explorer.. > Options > Connection Properties: Make sure 'Trust connection certificate' checkbox is checked.
Goto File > Connect Object Explorer.. > Options > Connection Properties: Make sure 'Encrypt connection' check box is unchecked.
Check whether 'SQL Server(MSSQLSERVER)' and 'SQL Server Agent(MSSQLSERVER)' services are running.
Add the following statements in the connection string:

Trusted_Connection=True;TrustServerCertificate=True;

This way, I was able to resolve the issue.

score 1 · Answer 14 · answered Jan 18 '19 at 16:49

1

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.

answered Jan 18 '19 at 16:49

Tawab Wakil

1,737
18
33

Mine just won't let me do it. Says I cannot use special principal dbo. – Sam Oct 02 '20 at 23:38

score 1 · Answer 15 · answered Aug 04 '20 at 16:03

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

But I'm trying to use Windows Auth. – Sam Oct 02 '20 at 23:37 — Sam, Oct 02 '20 at 23:37

score 1 · Answer 16 · edited Feb 03 '21 at 20:35

1

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.

edited Feb 03 '21 at 20:35

code11

1,986
5
29
37

answered Feb 03 '21 at 12:26

Adam Lincoln

11
1
2

score 1 · Answer 17 · answered Nov 24 '21 at 14:27

1

In my case changing <default> to master under Connection Properties in Options solved the issue.

answered Nov 24 '21 at 14:27

lnagy

23
6

score 1 · Answer 18 · answered Dec 26 '21 at 00:20

Seems like I had a unique situation - other than all the answers posted here.

I connect to the database with a restricted login (mapped to the database user) and NOT the sysadmin sa

Now I restored by database from a copy of another database on the same sql server database (same issue would occur even if you have restored form another sql server database server), but missed to delete the database user and recreated it.

So, essentially, if you are restoring your sql server database here is what you need to do:

Restore the database
Delete any roles owned by the user (that is ultimately mapped to the login which is not working)
Delete the user mapped to the login
Re-map the login to the user
Recreate the role and add the user to that role

score 1 · Answer 19 · answered Aug 19 '22 at 17:48

1

Got this error from downloading Microsoft.Data.SqlClient package

removed Microsoft.Data.SqlClient package and it worked.

answered Aug 19 '22 at 17:48

David Morrow

262
4
9

I got it working doing the reverse. By removing System.Data.SqlClient and adding the newer Microsoft.Data.SqlClient. – M.Parent May 04 '23 at 15:29

score 0 · Answer 20 · answered Dec 24 '17 at 20:15

0

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

answered Dec 24 '17 at 20:15

Alex

9
4

score 0 · Answer 21 · edited Jul 09 '19 at 12:09

0

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

edited Jul 09 '19 at 12:09

Milo

3,365
9
30
44

answered Jul 09 '19 at 11:44

Viteokntl

21
1

score 0 · Answer 22 · answered Sep 27 '19 at 21:34

0

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.

answered Sep 27 '19 at 21:34

Kellen Stuart

7,775
7
59
82

score 0 · Answer 23 · answered Aug 17 '20 at 20:36

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

score 0 · Answer 24 · answered Feb 18 '21 at 15:37

0

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.

answered Feb 18 '21 at 15:37

Pinpaho

187
2
5
15

score 0 · Answer 25 · answered May 03 '21 at 13:18

One of the solutions above gave me a clue. The sa account was generating this exact error. I was able to log in with a Windows service account with admin privileges. At some point recently a GPO had been applied to strengthen the password policy. The existing sa password didn't meet the new strengthened password policy.

Log into SQL Server Management Studio with alternative admin credentials. Instance > Security > Logins > account (sa in my case) Untick "Enforce password policy" Click OK. Disconnect and login again with the account.

Worked for me.

HAd to reboot the server to get various jobs running again but if I took time to go through them I probably could have restarted them from the SSMS without a server restart.

If there is an password policy to strengthen password, please change password according to policy and don't disable policy. It will work but will make your application susceptible to hacks & less secure. — Pranav Singh, May 04 '21 at 12:17

score 0 · Answer 26 · answered Jun 03 '21 at 13:46

I had previously limited the number of connections to my DB to 2. Clearly this was an issue. After restarting the server, login, you will get an error popup and just click OK. Right click on the server in the object explorer, go to properties, go to connection and change your limited number of connections (I went from 2 to 20) everything works fine now.

I appreciate this is an old post. But I came across it today, saw lots on MS forums which were incorrect solutions. Almost all of them on here didn't work for me either.

score 0 · Answer 27 · answered Jul 15 '21 at 18:47

0

What helped me is deleting .ldf files from \bin\Debug

answered Jul 15 '21 at 18:47

nightcoder

13,149
16
64
72

score 0 · Answer 28 · answered Dec 08 '21 at 15:21

0

One possible source of this problem is if the database login is not mapped to a user in the database. Solution is of course simple, map the login to the database user.

answered Dec 08 '21 at 15:21

jmoreno

12,752
4
60
91

score 0 · Answer 29 · answered Jan 02 '22 at 11:04

0

Create the User in Security. Default database will be set to Master After that, you can log in to SQL Server Authentication.

answered Jan 02 '22 at 11:04

grey

209
3
6

score 0 · Answer 30 · answered Aug 31 '22 at 21:38

i got the error after deleting a database. this db was the default db chosen by the mssql management tool. so, the connection was established but because the db was missing, the process resulted in an error. the solution is to change the defaut db to a db that exists. in the connection popup press the 'options' button, and then in the 'connect to database' field, choose one that exists in your sql server. that solved the problem for me. :)

score 0 · Answer 31 · answered Sep 23 '22 at 21:13

For me the following did the trick:

Open SQL Server Configuration
On the left under "SQL Server Network Configuration", click on "Protocols for MSSQLSERVER"
Then on the right make sure to set "Named Pipes" to Enabled

Restarted my pc. Then could succesfully login again with my sa account.

score 0 · Answer 32 · answered Oct 14 '22 at 15:30

0

I'm a little late to the party with this, but I found when I saw this error it was due to the login I was using not having permission to the stored procedure it was trying to execute.

answered Oct 14 '22 at 15:30

Tim

4,051
10
36
60

score 0 · Answer 33 · answered Jul 12 '23 at 14:46

SO warns me that I had to review all previous 30+ answers - I did and I am pretty sure my case is different.

My reason was that I was connecting to MSSQL 2019 using password and my password was lost and was specified as Password=; in the .net connection string.

And it really looks like a bug in MSSQL. If I specify any other invalid password the error is "Login failed for user X". And what is really strange is that if I specify blank password again (after specifying non-blank invalid password) the error is "Login failed for user X" again. Not that strange one about "connection established" and so on.

A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)

33 Answers33

Same Error with Connection String in Visual Studio dev environment

Linked