How do I fix the error 'Named Pipes Provider, error 40 - Could not open a connection to' SQL Server'?

Question

I can't seem to connect to my database from a site. I get this error:

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

I tried using the local IP address to connect as well as a public one. I've tried:

Yes, the site can communicate with the server
Named pipes/TCP is enabled.
Remote connections are allowed.
Windows Firewall is off
Created an exception for port 1433 in Windows Firewall.
Enabled everything in SQL Server Configuration Manager.

What else can I do here?

It may be worthwhile checking if SQL Server is actually using port 1433. It may be using something other than the default port. — Rajeev Shenoy, Mar 30 '12 at 15:08
Also make sure, while pinging, that the server name is properly set (or well written). This was the problem in my case ! — Yann Chabot, Jul 21 '17 at 15:19
@RajeevShenoy: How can you tell without a connection (which is the problem)? I can tell the port, while connected, by using netstat but this only works if there is a connection. — MSIS, Aug 23 '19 at 23:30
Related post - [Why am I getting “Cannot Connect to Server - A network-related or instance-specific error”?](https://stackoverflow.com/q/18060667/465053) — RBT, Aug 30 '19 at 08:41
I forgot to add the port number - when calling the server (slight variation of Rajeev's comment) i.e. the call needed to include: serverName, portNumber. This solved the "pipes provider error 40 " issue. — Grant Shannon, Aug 06 '20 at 15:00
Enable Named Pipes SQL Config Manager --> SQL Server Network Consif --> Protocols --> Named Pipes --> Right-click --> Restart — Ali NajafZadeh, Aug 03 '21 at 17:04
if you're working with AWS or anything like that, there's a firewall behind your local firewall, you need to open port 1433 there too — eiran, Apr 08 '23 at 14:35

score 134 · Accepted Answer · edited Jan 21 '16 at 05:40

134

Solving this problem is very easy:

Go to control panel.
search for services.
Open Local services window from your search results
Restart your MSSQLSERVER service.

Screenshot of the steps:

Screenshot of the steps

edited Jan 21 '16 at 05:40

elixenide

44,308
16
74
100

answered Jan 03 '16 at 05:45

Kazem

1,450
2
11
13

2

Wow, Management studio wouldn't start my service even though it was set to automatic Thanks – johnny 5 Feb 14 '17 at 03:17
Thanks, this helped.Will I keep having to start the service from here each time? I didn't previously have to. – naz786 Jun 05 '17 at 14:11
5

What if this services isn't here? I have installed SQL Server 17. I don't see this service.... – Ciaran Gallagher Jul 26 '17 at 18:33
You shouldn't restart services from here, but rather from SQL Server configuration manager. – Asher Aug 30 '18 at 00:03
2

Making a 170GB database backup the service stopped. Thanks to this it only took 5 minutes to restart the sql server that was being used by an important client. Thank you very much. – Shondeslitch Dec 04 '18 at 16:26
4

All actions checked, but the restart didn't work for me. Still getting the same error. – Jay Apr 23 '19 at 12:33
1

do we do this for azure sql as well? – Alex Gordon Oct 29 '21 at 02:35

score 104 · Answer 2 · edited Jan 06 '13 at 10:25

104

And the simplest solution - check if your slash is back...

I spent about an hour trying to figure out what's wrong with SERVER/INSTANCENAME when everything is configured correctly, named pipes, user access rights... and suddenly it struck me, it's not a slash, it's a backslash (\).

The horror, the shame...

edited Jan 06 '13 at 10:25

Peter Mortensen

30,738
21
105
131

answered Nov 13 '12 at 18:21

mizuki nakeshu

1,315
2
9
12

3

I had the OP's problem and it turned out I was missing the **SERVER/INSTANCENAME** info (there was a dot there instead). – LuxDie Mar 15 '16 at 03:31
1

@LuxDie Where do you put **SERVER\INSTANCENAME** at and where it is located? – tom_mai78101 Jan 20 '17 at 16:35
This one is the key, one should not forget that you have to connect to a "database" that is hosted in an insance on a server. This error is raised if you use only the server's hostname for the connection and trying to use the instancename as database or vice versa. – MichaelHuelsen Feb 26 '19 at 20:49
Solved my issue. Working connection string: `Server=MyServerName\DOLPHIN=Trusted_Connection=True;Database=DolphinPlatform` While in development (C#) I was connecting to a locally installed MSSQL 2017 server, but when I went to deploy it, the remote server installed by the software vendor was running as an `Instance`. I kept thinking it was an authentication problem because it was running as a service or something till I found this. – Alan Dec 18 '19 at 16:15
I share your horror and shame -- TWO HOURS I was failing to check this! – Ed Weatherup Mar 28 '22 at 10:12

score 70 · Answer 3 · edited May 24 '23 at 13:14

70

It's a three step process really after installing SQL Server:

Enable Named Pipes SQL Config Manager --> SQL Server Network Config --> Protocols --> Named Pipes --> Right-click --> Restart

Restart the server SQL Config Manager --> SQL Server Services --> SQL Server (SQLEXPRESS) --> Right-click --> Restart
Use proper server and instance names (both are needed!) Typically this would be .\SQLEXPRESS, for example see the screenshot from QueryExpress connection dialog.

There you have it.

edited May 24 '23 at 13:14

Ian Kemp

28,293
19
112
138

answered Oct 24 '15 at 07:34

Pompair

7,083
11
60
69

since you have relatively late high-scoring answer. What is it, when you have a service that communicates with your sql server fine, then 3 hours into its run, it start to get this error that persists for 10 minutes or so. Then, service continues to run like prior to the error. ??? – T.S. Apr 10 '19 at 19:23
This is for local connections? What is additionally necessary for remote connections through network to work? – Kissaki May 06 '20 at 10:11
Thanks! This worked for me. I was using `localhost` instead of `.\SQLEXPRESS` – Alireza S.N Aug 11 '20 at 03:20
this was my solution. I first tested with SQL Express and I connect using `.` but after I installed full SQL Server I had to change named to `.\SQLEXPRESS` also named pipes was disabled and SQL browser was disabled – zac Dec 23 '21 at 22:12

score 21 · Answer 4 · answered Mar 12 '14 at 23:44

I had just installed SQL SERVER 2012 developer. When I was creating my first SSIS package, I received this pipes error when I was trying to create a data connection task in SQL Server 2012 Data Tools in the Connection Manager box. I resolved with the help of the post above.

If choose a named instance and you call your named instance SSQDatabase1 and your pc's name is PCX1. You must enter PCX1\SSQDatabase1 not just SSQDatabase1 or you will receive the named pipes error.

score 18 · Answer 5 · edited Apr 05 '16 at 17:46

18

A thread on MSDN Social, Re: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server, has a pretty decent list of possible issues that are related to your error. You may want to see if any of them could be what you're experiencing.

Incorrect connection string, such as using SqlExpress
Named Pipes(NP) was not enabled on the SQL instance
Remote connection was not enabled
Server not started, or point to not a real server in your connection string
Other reasons such as incorrect security context
try basic connectivity tests between the two machines you are working on

edited Apr 05 '16 at 17:46

James Jenkins

1,954
1
24
43

answered Mar 30 '12 at 16:43

Khan

17,904
5
47
59

Went through them, unfortunately, that didn't help. :( – Damien Mar 30 '12 at 18:00
6

Okay, figure this out. Turns out, when i installed the server i did a named instance. You cannot connect to a named instance the same way as you would a default instance. So, data source: localhost\name of instance that works. Still couldn't get it going with an ip address, but glad to finally connect. – Damien Mar 30 '12 at 18:55
Glad to hear, but out of curiosity can you connect to it using `ip.ip.ip.ip/NamedInstance`? – Khan Mar 30 '12 at 18:57

score 12 · Answer 6 · edited May 06 '20 at 10:14

12

i Just enabled TCP/IP,VIA,Named Pipes in Sql Server Configuration manager , My problem got solved refer this for more info Resolving Named Pipes Error 40

edited May 06 '20 at 10:14

Kissaki

8,810
5
40
42

answered Sep 02 '15 at 09:03

Samanth

346
3
10

dead link. any info on what it said? – Kissaki May 06 '20 at 10:12

score 10 · Answer 7 · answered Sep 11 '13 at 11:06

10

Use SERVER\\ INSTANCE NAME .Using double backslash in my project solved my problem.

answered Sep 11 '13 at 11:06

Tamizh venthan

119
1
2

This one also worked for me but can anyone tell me why this worked? – GWR Feb 18 '16 at 17:17
I'd say string escaping, but depends on context – mizuki nakeshu Jun 30 '16 at 16:29
1

If that string is in C# code, `@"server\instance"` or `"server\\instance"` should work. If it's in a config file, then you just want the plain text server\instance. – Denise Skidmore Oct 11 '19 at 21:30

score 9 · Answer 8 · answered Dec 30 '13 at 21:48

Thanks to Damian...

TCP/IP Named Pipes ... both enabled

Web Config....(for localhost)

<add name="FooData" connectionString="Data Source=localhost\InstanceName;Initial Catalog=DatabaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />

score 6 · Answer 9 · answered Jan 16 '14 at 07:05

6

Did have the same problem. Spent like 6 hours when had to migrate some servers. Tried all suggestions available on this topic and others.

Solution was as simple as server restart!

answered Jan 16 '14 at 07:05

halloweenlv

490
6
14

1

Sadly, resetting the server fixed the problem for me as well. All the other suggestions mentioned had been attempted to no avail. – MrShmee Jun 07 '18 at 20:20

RBT · Answer 10 · 2023-03-09T01:25:02.867

TL;DR: Your SQL Server instance is using dynamic ports due to which it is not working. Force the SQL Server service to use static port # 1433 to get it working.

Detailed Explanation: Before starting with the details, let us first understand the scenario in which this issue can occur.

Whenever we run SQL Server setup on a machine then it asks us to input the name of the service instance. Why it asks for it? The reason is that the SQL Server setup provides a facility through which you can install and run multiple instances of SQL Server service on the same machine. There can be variety of reasons due to which you may want to run multiple SQL Server service instances on the same box e.g. unavailability of more physical servers in your estate, isolating SQL Server instance failures, load balancing, etc. To differentiate between multiple instances on the same machine, you must give a unique name to each service instance. Have a look at my machine (Refer screenshot). I have only one service instance and it is the default service instance of SQL Server:

For each new instance that you setup via SQL Server installer, there will be a new row in this service console window with same SQL Server prefix in the Name column. Text in parenthesis (MSSQLSERVER) is the name of the service instance. To understand the naming convention of SQL Server service instances, please refer to the corollary section in the end of the answer.

So, if you have more than one instance of SQL Server service running on a given machine then this problem can occur. When you have two or more than two SQL Server instances running on the same box then there are two possible configurations:

Combination of default instance and one or more named instances
Combination of two or more named instances only i.e. no default instance. SQL Server setup does not mandates the installation of default instance if you wish to. I had followed this configuration on my server when I faced this issue.

Key concept: Each instance of Microsoft SQL Server service installed on a machine uses a different port to listen for incoming SQL connection requests. In other words each SQL Server service is a different process which is attached to a unique port number. Default instance of SQL Server uses port # 1433. As you install named instances then they will start using dynamic ports which is decided at the time of start-up of Windows service corresponding to named SQL Server instance.

My code was failing (with error code 40) to connect to the only named SQL Server instance that I had on my VM. You can try below possible solutions:

Solution # 1: Client code trying to connect to SQL Server instance takes help from SQL Server browser service to figure out port number at which your named instance is listening for incoming connections. Make sure SQL browser service is running on your computer.

Solution # 2: Check the port # (in yellow color) your named SQL Server instance is using from SQL Server configuration manager as shown in the snapshot below:

Use that port number explicitly in your connection string or with sqlcmd shown below:

sqlcmd -s mymachinename,11380 -i deleteDB.sql -o SQLDelete.txt

Solution # 3: Force your named instance to use port # 1433 which is used by the default instance otherwise. Remember this solution will work only if you do not have any default SQL Server instance running on as machine as the port # 1433 would be already in use. In any operating system, a given port number can never be used by two processes at the same time. At the end of day, our SQL Server service instance is also a process.

Set TCP Dynamic ports field to blank and TCP Port field to 1433.

Change the port number in your connection string as shown below:

sqlcmd -s mymachinename\instanceName -i deleteDB.sql -o SQLDelete.txt

OR

sqlcmd -s mymachinename,1433 -i deleteDB.sql -o SQLDelete.txt

Note: Please restart the SQL Server service instance after saving the TCP/IP settings.

Interestingly enough after resolving the error when I went back to dynamic port setting to reproduce the same error then it didn't happen. Not sure why.

Please read below interesting threads to know more about dynamic ports of SQL Server:

How to configure SQL Server Port on multiple instances?

When is a Dynamic Port “dynamic”?

When to use a TCP dynamic port and when TCP Port?

I got leads to solution of my problem from this blog.

Corollary: Name of a SQL Server instance follows below mentioned logic

For default instance, it is same as the name of the machine hosting the SQL Server service. It is predefined. It cannot be changed by the user during installation. Don't get confused by the suffix (MSSQLSERVER) present in the service name. It is an internally managed name relevant for server side only. Client side apps will never get to know that.
For named instance, it is a concatenated string in specific format as shown here - <name of the machine hosting the SQL Server service>\<user defined instance name>

Pro Tip: Default SQL Server instance can be connected via few alias names as well which are convenient to type and intuitive to remember. But alias names can be used only while you are trying to connect to the service instance from the service host machine itself. In such a scenario, in place of host name you can also use below mentioned alias names:

.
(local)

While connecting to the SQL Server service instance from a remote machine only standard names will work.

score 4 · Answer 11 · edited Apr 10 '19 at 19:18

4

Very simple solution

use (local)\InstanceName that's it. it worked for me.

edited Apr 10 '19 at 19:18

T.S.

18,195
11
58
78

answered Nov 11 '13 at 16:20

Husrat Mehmood

2,270
1
20
22

score 3 · Answer 12 · answered Dec 19 '14 at 18:43

in my case, i had a standalone server, i changed the sql server port default port 1433 in configuration manager to some number and restarted the sql serve service to take effect,i was able to connect to the sql server through management studio if i login to the server. but i was not able to connect from my local machine through sql server, i was getting the error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and

that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

I checked and verified all the below

-Named pipes/TCP is enabled. -Remote connections are allowed. -Windows Firewall is off -Created an exception for portin Windows Firewall( this was not necessary in my case as the server is in same subnet network). -Enabled everything in SQL Server Configuration Manager.

then i chnaged back the port number to default 1433 and restarted the sql server service, and the issue got resolved and i am able to connect the sql server from my local management studio.

score 3 · Answer 13 · answered Jan 14 '18 at 19:59

3

I had the same problem. I use the MSSQL Server Management Studio 2017 and solved this problem using these steps:

Check for working fine SQL Server Services services or not.
Also check for working in good condition SQL Server (MSSQLSERVER).
Also check for working fine SQL Server Browser.
Restart SQL Server (MSSQLSERVER)

and fixed it.

answered Jan 14 '18 at 19:59

Murat

61
8

3

Thanks for this! SQL Server Browser was disabled so this fixed it for me! – Sam May 31 '18 at 11:40

score 3 · Answer 14 · answered Mar 06 '18 at 05:34

3

You will find most likely your DB name is not correct, you will see the server name in VS like "DESKTOP-0I14BKI" but if you open up SSMS you will see DESKTOP-0I14BKI\SQLBLAHBLAH , simply add "\SQLBLAHBLAH" (instance name) to your "server name" in VS connection properties.

You will see:

To Fix:

answered Mar 06 '18 at 05:34

scott

1,531
2
16
29

Thank you for the screenshots and for mentioning Visual Studio! So simple, but not obvious to someone doing this the first time ever! – Jinjinov Nov 18 '20 at 16:59

score 2 · Answer 15 · edited Dec 08 '16 at 01:32

2

Try the following steps:

Open Services window (open "run box" and type services.msc).
Looking for SQL services (with SQL prefix).
Start them (if cannot start. Goto step 4).
Right_click to each service -> Properties -> Change to tab "Log on"-> choise log on as "Local ..." -> 0K. Then start SQL services again.

Try Open SQL and connect database.

edited Dec 08 '16 at 01:32

Pang

9,564
146
81
122

answered Oct 21 '14 at 02:41

Harry Ho

39
2

score 1 · Answer 16 · answered Jun 15 '17 at 05:30

1

In my case, I opened SQL Server Management Studio and searched for SQLEXPRESS in my Database engine. It had two instances and I selected the correct one.

answered Jun 15 '17 at 05:30

DivyaMenon

311
4
19

score 1 · Answer 17 · answered Mar 20 '19 at 11:14

If you are working with Asp.net core and using appsettings.json than write server as localhost and after write sql instance name for enabled named pipe like this

  "ConnectionString": {
    "dewDB": "server=localhost\\dewelopersql;database=dewdb;User ID=sa;password=XXXXX",
  },

score 0 · Answer 18 · answered Feb 13 '14 at 20:55

After following all the steps mentioned here, if it still does not connect, try adding the DNS with the IP address in the hosts file in the etc folder. Adding an IP address instead of DNS name in the connection string should be a temporary solution to check if the connection actually works.

score 0 · Answer 19 · answered Dec 02 '14 at 02:31

I tried using the local IP address to connect as well as a public one. I've tried:

Yes, the site can communicate with the server Named pipes/TCP is enabled. Remote connections are allowed. Windows Firewall is off Created an exception for port 1433 in Windows Firewall. Enabled everything in SQL Server Configuration Manager.

i ensured and did the above as well and I just want to share that the DOUBLE BACKSLASH

oBuilder.DataSource = "SPECIFICPCNAME\SQLEXPRESS";

Using a SINGLE BACKSLASH resulted into a build error i.e.: Error 1 Unrecognized escape sequence

I hope this helps the next guy - I've sacrificed dinner, midnight snack and NBA highlights time solving this (shame)

Thanks to [Tamizh venthan] ^_^

score 0 · Answer 20 · answered Dec 20 '14 at 19:24

Enable TCP/Ip , Piped Protocol by going to Computer Management ->SQL and Services, ensure the Service is On. Enbale the port on the Firewall. Try to login through Command Prompt -> as Admin; last the User Name should be (local)\SQLEXPRESS. Hope this helps.

score 0 · Answer 21 · answered Mar 03 '15 at 19:31

0

Open SQL Server Configuration Manager

Select SQL Server Services from right.
Find your server from right and go to its properties (with right click)
Change log on method to Local System.

enter image description here

answered Mar 03 '15 at 19:31

zapoo

1,548
3
17
32

score 0 · Answer 22 · answered Dec 10 '15 at 07:33

I had the same problem and solved the problem by disabling my firewall(ESET).

The first step to solve this problem should be to try pinging your own computer from another computer. If you have firewall on, you may not be able to ping yourself. I tried pinging my own pc, then ping was failed(didnt get response from the server)

score 0 · Answer 23 · answered Jun 30 '16 at 17:01

I was trying to add a new connection in VS2015. None of the suggestions here worked. Suspecting some sort of a bug in the wizard, especially since SSMS was able to connect just fine, I decided to try and trick it. It worked!

Instead of adding the connection, use "Create new SQL Server Database". Enter your server name and a random name for the new DB, e.g. "test".
Assuming this succeeds, open Server Explorer in VS, locate the connection in Data Connections, right-click it and select Modify Connection.
Change "test" (from step 1) to the name of the existing database you want to connect to. Click "Test Connection". This time it should work!
Delete the temporary database you created in step 1.

score 0 · Answer 24 · answered Aug 23 '16 at 15:45

I have one more solution, I think. I recently had changed my computer name so, after I couldn't connect still after trying all above methods. I changed the Server name.. Server name => (browse for more) => under database engine, a new server was found same as computers new name. This worked, and life is good again.

score 0 · Answer 25 · answered Oct 11 '16 at 15:10

0

I struggled for ages on this one before I realized my error - I had used commas instead of semicolons in the connect string

answered Oct 11 '16 at 15:10

Andy

10,412
13
70
95

score 0 · Answer 26 · answered Dec 31 '16 at 23:56

I had this issue but none of the suggestions above fixed it.

I was seeing this issue when I deployed my website to IIS. The fix was to go into advanced settings against the default app pool and change the identity property from the default to Administrator.

Dave · Answer 27 · 2017-10-13T15:57:43.733

For me it was a Firewall issue.

First you have to add the port (such as 1444 and maybe 1434) but also

C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe

and

%ProgramFiles%\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Binn\SQLAGENT.EXE

The second time I got this issue is when I came back to the firewall, the paths were not correct and I needed to update form 12 to 13! Simply clicking on browse in the Programs and Services tab helped to realise this.

Finally, try running the command

EXEC xp_readerrorlog 0,1,"could not register the Service Principal Name",Null

For me, it returned the error reason

score 0 · Answer 28 · answered Jul 22 '18 at 21:17

I tried pretty much everything on this page but I had some underlying issues which were actually what needed to be resolved. I was unable to do certain things like open SQL Server Configuration Manager, which ended up being corrupt/missing WMI provider files.

There are lots of tedious ways to resolve this issues according to what I've read, but the tool from tweaking.com was able to remove and replace/repair my WMI (Windows Management Instrumentation) Provider files.

I used to do computer repair and overall the tweaking.com tool really impressed me, and it was suggested from one of the WMI error forum pages I went to.

After I fixed this issue I was able to connect to my SQL db, both locally and remotely.

Hope this helps someone.

score 0 · Answer 29 · answered Mar 13 '19 at 20:17

0

open port number 1433 on your server for sql remote connection

answered Mar 13 '19 at 20:17

Hamid Jolany

800
7
11

score 0 · Answer 30 · answered Jul 20 '19 at 19:18

If you tried restarting the MSSQLSERVER service, and it did not work, this might be a solution:

If you are using SQLExpress, your server name should be as the following ComputerName\SQLExpress. However, for SQLDeveloper, you do not have to right SQLDeveloper after your ComputerName.

estinamir · Answer 31 · 2022-09-20T15:25:20.660

0

I was getting this error on linked server which has gone offline. It was inside of a sql query, which was inside a dll, which was in another project, and in another language (VB). Copy your query into sql studio manager and run directly from there to see if this is your problem as well.

edited Sep 20 '22 at 15:25

answered Sep 20 '22 at 15:19

estinamir

435
5
11

Ian Fallon · Answer 32 · 2023-01-03T14:06:57.507

0

I had a different version of this issue when trying to connect Power BI desktop to a local developer instance of SQL Server 2019. I was using:

localhost:<port>

Changing it to:

hostname\servername

solved it for me. Possibly complicated by the fact I have 2 SQL Server installations on my machine on different ports.

edited Jan 03 '23 at 14:06

answered Jan 03 '23 at 10:19

Ian Fallon

21
2

score -1 · Answer 33 · answered May 03 '16 at 09:43

I have suggested below steps to resolve your issue How do I fix the error 'Named Pipes Provider, error 40 - Could not open a connection to' SQL Server'

Check for working fine SQL Server Services services or not.
Also check for working in good condition SQL Server (MSSQLSERVER).
Also check for working fine SQL Server Browser.
Delete all earlier Aliases, now create new aliases as per your requirements.
Now check for working of SQL Server Default Port 1433
Next click on Client Protocols in instance, then click on TCP/IP, now click on mouse right click, open the Property, here you can make assure your working fine your default port SQL 1433.
Open your SQL Server Management Studio, then right click, click on "Property" option and then click on Connections tab, then finally tick for Allow remote Connections to this server.
Check for right working or your Ping IP Host.

How do I fix the error 'Named Pipes Provider, error 40 - Could not open a connection to' SQL Server'?

33 Answers33

Linked

Related