89

I am trying to deploy my website on windows server 2003. Am i missing something or what is wrong from the following error message, how can I correct it? Thank

I am having the 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.) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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.)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): 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.)]
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1019
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +108
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +126
System.Data.SqlClient.SqlConnection.Open() +125
NHibernate.Connection.DriverConnectionProvider.GetConnection() +104
NHibernate.Tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.Prepare() +15 NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(Dialect dialect, IConnectionHelper connectionHelper) +89
NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.Update(ISessionFactory sessionFactory) +80
NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners) +599
NHibernate.Cfg.Configuration.BuildSessionFactory() +104
MyProject.API.Data.SessionManager..cctor() in C:\Dev\Code\API\Data\SessionManager.cs:27

learning
  • 11,415
  • 35
  • 87
  • 154
  • 2
    I got this when the user I was trying to connect with didn't have access to the database. – Peter Jun 15 '16 at 11:26
  • 1
    I got this error because the **password** of the user in the connection string was **expired**. – Uwe Keim Aug 28 '19 at 08:19
  • 1
    @UweKeim thanks! this was the clue I needed as I was getting the error on Appveyor build VM, I think their documented user/password has expired. Ended up just swapping to integrated security instead – Keith Nicholas Aug 19 '21 at 11:27

27 Answers27

87

Typically, to troubleshoot this, you go to SQL Server Configuration Manager (SSCM) and:

  1. ensure Shared Memory protocol is enabled
  2. ensure Named Pipes protocol is enabled
  3. ensure TCP/IP is enabled, and is ahead of the Named Pipes in the settings

Maybe it can help: Could not open a connection to SQL Server

Note : If this is a new instance of SQL Server be sure SQL Server and Windows Authentication is enabled

  1. Right Click the Server in SSMS and pull up server properties
  2. Go to Security--> Select 'SQL Server and Windows Authentication Mode'
  3. Restart the Server and Login with the credentials
Bakudan
  • 19,134
  • 9
  • 53
  • 73
DimonZa
  • 904
  • 8
  • 3
  • 6
    I needed mixed mode authentication enabled AND to restart the SQL Server Agent service after the user login was complete for SQL Server authentication to work. Make sure to restart that service if the settings don't seem to be "taking." Hope that helps someone. Shared Memory/Named Pipes/ TCP/IP was enabled by default. – Rachael Mar 04 '15 at 17:23
  • 1
    @Magier Right-click on any of the protocols and select the "Order" option. You can rearrange the preferred connection order there. – CauselessEffect Nov 08 '18 at 21:48
  • IDK how I missed this before, but you have to restart SQL Server. I was simply exiting SMSS and coming back in -- which never restarted the actual SQL Server. In case anyone else is doing the same thing... – MarkJoel60 Aug 25 '21 at 18:43
39

Check if your connection string has "Trusted_Connection=true" added.

Rukshala Weerasinghe
  • 659
  • 7
  • 7
  • 1
    Worked for me. Thanks! – WillC Dec 12 '20 at 16:58
  • What does this setting mean? I would like to know before I put it in. Thanks – mgPePe Feb 05 '21 at 16:27
  • @mgPePe trusted connection is used if you're connecting to the database without providing a user name and password. For example, if you are connecting via a Data Source Name, and the DSN contains the user ID you're using to connect, then your trusted connection is true. If you are providing a user name and password as part of your connection string, then trusted connection is false. – Rukshala Weerasinghe Mar 01 '21 at 06:29
  • Trusted connection means that the identity associated with the connecting process is used as the identity that sql server will authenticate. If you are launching your process, then the identity associated with the process is your windows login credentials. If your connecting process is running as a service, check the service properties to see what identity it is using. – Elroy Flynn May 17 '23 at 01:27
35

I had this same error message, turns out it was because I didn't have mixed mode auth enabled. I was on Windows Auth only. This is common in default MSSQL deployments for vSphere, and becomes an issue when upgrading to vSphere 5.1.

To change to mixed mode auth you can follow the instructions at http://support.webecs.com/kb/a374/how-do-i-configure-sql-server-express-to-enable-mixed-mode-authentication.aspx.

Sachin Joseph
  • 18,928
  • 4
  • 42
  • 62
LowWalker
  • 451
  • 4
  • 2
29

I had the same error by in SQL Server Management Studio.

I found that to look at the more specific error, look at the log file created by the SQL Server. When I opened the log file, I found this error

Could not connect because the maximum number of ’2′ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed

I spend quite some time figuring this out. Finally running the following code fixed my problem.

sp_configure 'show advanced options', 1;
go

reconfigure
go

sp_configure 'user connections', 0
go

reconfigure
go

More on here and here

Edit

To view logs search for "logs" on windows startup button, click "view events logs". From there go to Applications under "Windows Logs". You can also choose "System" logs to see system wise errors. You can use filter on current logs by clicking "Filter Current Logs" on right side and then select "Error checkbox".

TheTechGuy
  • 16,560
  • 16
  • 115
  • 136
  • 4
    Looking at the logs instantly pointed out the error in my case too. I'd say this is should be the actual answer here, since it could be a number of different things. In my case it was simply an incorrect user/password in the connection string. – julealgon Mar 07 '14 at 15:09
  • 3
    You guys should consider posting here the location of that log file – Nick Jan 09 '18 at 16:19
  • 4
    The sql error error log was at %Program-Files%\Microsoft SQL Server\MSSQL13.SQL\MSSQL\LOG\ERRORLOG for me. – Jared Phelps Feb 15 '18 at 01:28
  • 4
    @Nick `C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\log\ERRORLOG` – Kellen Stuart Sep 05 '19 at 21:37
10

Just another possibility. I had to restart the sql server service to fix this issue for me.

khr055
  • 28,690
  • 16
  • 36
  • 48
7

The "real" error was in the SQL error log:

C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\log\ERRORLOG

Path will depend on your version of SQL Server

Kellen Stuart
  • 7,775
  • 7
  • 59
  • 82
  • 1
    Thanks for sharing this really usefull hint. I've been able to solve my problem after checking the log! – Carl Verret Jul 27 '21 at 20:57
  • 1
    So funny: even one year later, I came to the exact same problem on a new computer and this solved my problem :) (there was a missing database on my new local server) – Carl Verret Nov 07 '22 at 23:53
6

Adding this to my connection string worked for me:

Trusted_Connection=true
Shoaib Khalil
  • 1,874
  • 17
  • 10
5

You should enable the Server authentication mode to mixed mode as following: In SQL Studio, select YourServer -> Property -> Security -> Select SqlServer and Window Authentication mode.

DupDup
  • 189
  • 2
  • 6
5

Goto to SQL server using windows Credentials - > Logins - > Select the Login - > in the Properties -> Check if the Log in is enabled/disabled. If Disabled, make it enable, this solution worked for me.

user3226665
  • 51
  • 1
  • 2
4

In C# and SQL SERVER, we can fix the error by adding Integrated Security = true to the connection string.

Please find the full connection string:

constr = @"Data Source=<Data-Source-Server-Name>;Initial Catalog=<DB-Name>;Integrated Security=true";
Janardhan Reddy
  • 156
  • 1
  • 8
3

I had the same error, Fixed it by ensuring that SQL server had SQL authentication mode enabled.

see images below.

  1. To enable, go to server properties. enter image description here

  2. Click on the Security tab and select the SQL server and window server auth mode and press ok

enter image description here

  1. You will need to restart the server for the changes to reflect, should be fine now.

enter image description here

Ssebbaale Paul
  • 131
  • 1
  • 4
2

By looking into SQL SERVER log file in "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG", it says "Login failed for user 'XXXXX'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]"

The fixing method is to open "Microsoft SQL Server Management Studio" -> Right click the SQL server and then select "Properties" -> Security -> Change the authentication to mixed mode. -> Restart SQL server.

Will Yang
  • 21
  • 2
1

I was getting this error today. In my case, looking at the ERRORLOG file on the SQL server gave me this error:

Login failed for user ''. Reason: Failed to open the database '' specified in the login properties.

This was because I had deleted the "Default database" of this user a few days ago. Setting the default database to my new database fixed the problem.

Hope this helps someone else.

Scotty
  • 1,127
  • 1
  • 7
  • 17
1

I forgot to add the "Password=xxx;" in the connection string in my case.

Charles HETIER
  • 1,934
  • 16
  • 28
  • I have used Visual Studio Connect tool to generate the connection string but it was not working. I have added the Password and it is working. – Hamza Anis Mar 03 '18 at 16:38
0

Enable Mixed authentication mode while installing MSSQL server. Also provide password for sa user.

Sakib
  • 85
  • 2
  • 11
0

This is old but I had the problem in the connect dialog that it was still defaulting to a database I had removed. And by running those commands the default database in the prompt wasn't changing. I read somewhere I can't find now, that if you open the "Connect to Server" dialog and then select "Options" and select "Connection Properties" tab by typing the default database (no by selecting from the drop down) the database will then stay on that new value entered. This sounds like a flaw to me but in case someone was wondering about that, that should fix the issue, at least on SQL Server 2012

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
user3223834
  • 59
  • 1
  • 1
  • 9
0

Hi Just enable both for server authentication as per screen shot attached below.

enter image description here

alexander.polomodov
  • 5,396
  • 14
  • 39
  • 46
Deepak Bajaj
  • 21
  • 3
0

All good and valid courses of investigation especially the logs for more info.

For those hitting this it might be a simple gotcha where when you have created the DB User you may have enforced a password policy and left the user to change the password on first login (i.e. left the checkboxes around the password field at their default values).

Very easily done in SQL Management Studio and can of course cause authentication issues off the bat that are masked unless you look into the logs.

Cueball 6118
  • 517
  • 4
  • 16
0

Check that the server name you're logging into with SQL Management Studio matches your connection string.

I was getting this error today. It turned out that I hadn't realised the machine with SQL Server installed had multiples servers running. I had in fact put my database in a totally different server to the one I thought I was using. (So my connection string was pointing to a server with no database)

Hence, When .net tried to access the database it couldn't find anything and gave only a misleading error message about pipes.

I opened the correct server in SQL Management Studio, added my database to and then all worked fine. (If the correct server isn't available in the dropdown, try browsing for it.)

Image showing the connection string matching the correct server name

Ian Gibblet
  • 552
  • 3
  • 18
0

In my case it was a spelling mistake in the database name in connection string.

Wieslaw Olborski
  • 187
  • 1
  • 7
0

I know i am probably the only one that will have this problem in this way. but if you deleted the mdf files in the C:/{user}/ directory, you will get this error too. restore it and you are golden

syter
  • 135
  • 2
  • 10
0

I ran across this in a code-first application which expected the database to be there:

Make sure the database is created / the name in the connection string is correct.

combatc2
  • 1,215
  • 10
  • 10
0

I had the same problem. I tried all the suggested answers in this page but to no avail! Finally, I tried the steps below and it worked for me:

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

  1. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  2. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
  3. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

Then try this in your Package Manager Console:

Scaffold-DbContext "Server=YourServer;Database=YourDB;Persist Security Info=False;User=YourUserName; Password=YourPassword; MultipleActiveResultSets=False;Encrypt=False; TrustServerCertificate=False; Connection Timeout=30;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context DatabaseContext -f
Ali Safari
  • 1,535
  • 10
  • 19
0

In my case, my situation was a little different.

1. My Mistake: I was missing a ";" in stringConnection. I know this is a newbie bug, but I am new to C # and SQL Server. I have one day :)

private string connectionString = "Data Source=localhost;Initial Catalog=FundamentalsCSharp" 
       + "User=sa;Password=123456";

2. My Solution: Put a ";" it was missing after the phrase "FundamentalsCSharp" and it worked.

private string connectionString = "Data Source=localhost;Initial Catalog=FundamentalsCSharp;" 
       + "User=sa;Password=123456";

3. Note: Change "FundamentalsCSharp" for your owned Initial Catalog.

I hope this can be of use to someone. Thanks!

modavidc
  • 56
  • 4
0

In my case, my project is Asp.net Core 3.1 and this is my connection string

"ConnectionString": "Data Source=.; Initial Catalog=WebDB; user Id=sa; Password=123"

And database name in sql server webdb (lower case in sql server). After many time change database name webdb in connecion string and worked fine.

topcool
  • 2,498
  • 7
  • 28
  • 52
0

For those doing entityframework code first, also check to enture you have done your migrations. The best place to look for the root cause is the sql server error log

snnpro
  • 193
  • 2
0

In my case My disk was full 99%, It worked after I freed up to 80% of the disk.

henrry
  • 486
  • 6
  • 25