如何修复“命名管道提供程序error 40 -无法打开到“SQL Server”的连接?

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

命名管道提供程序，错误:40 -无法打开到SQL Server的连接

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

我尝试使用本地IP地址和公共IP地址进行连接。我试过了:

1. Yes, the site can communicate with the server
2. 是的，站点可以与服务器通信。
3. Named pipes/TCP is enabled.
4. 命名管道/启用TCP。
5. Remote connections are allowed.
6. 允许远程连接。
7. Windows Firewall is off
8. Windows防火墙是关闭的
9. Created an exception for port 1433 in Windows Firewall.
10. 在Windows防火墙中为端口1433创建了一个异常。
11. Enabled everything in SQL Server Configuration Manager.
12. 启用SQL Server配置管理器中的所有内容。

What else can I do here?

我还能做什么?

26 个解决方案

#1

Solving this problem is very easy:

解决这个问题很容易:

1. Go to control panel.
2. 去控制面板。
3. search for services.
4. 搜索服务。
5. Open Local services window from your search results
6. 从搜索结果中打开本地服务窗口
7. Restart your MSSQLSERVER service.
8. 重新启动MSSQLSERVER服务。

Screenshot of the steps:

截图的步骤:

#2

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

恐惧、羞愧……

#3

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

在安装SQL Server之后，这是一个三步骤的过程:

1. Enable Named Pipes SQL Config Manager --> SQL Server Network Consif --> Protocols --> Named Pipes --> Right-click --> Restart
2. 启用命名管道SQL配置管理器——> SQL Server网络构成——>协议——>命名管道——>右键单击——>重启

2. Restart the server SQL Config Manager --> SQL Server Services --> SQL Server (SQLEXPRESS) --> Right-click --> Restart

   重新启动服务器SQL配置管理器——> SQL server服务——> SQL server (SQLEXPRESS)——>右键单击——>重新启动

3. Use proper server and instance names (both are needed!) Typically this would be .\SQLEXPRESS, for example see the screenshot from QueryExpress connection dialog.

   使用合适的服务器和实例名(都需要!)例如，请查看QueryExpress连接对话框的屏幕截图。

There you have it.

你拥有它。

#4

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.

我刚刚安装了SQL SERVER 2012 developer。当我在创建第一个SSIS包时，我在连接管理器框中尝试在SQL Server 2012数据工具中创建数据连接任务时收到了管道错误。我在上面那篇文章的帮助下决定了。

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.

如果选择一个命名实例并调用您的命名实例SSQDatabase1，那么您的pc的名称就是PCX1。您必须输入PCX1\SSQDatabase1，而不仅仅是SSQDatabase1，否则您将收到命名管道错误。

#5

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.

MSDN Social上的一个线程，Re: Named Pipes Provider, error: 40 -无法打开到SQL Server的连接，它有一个与您的错误相关的相当不错的问题列表。你可能想看看其中是否有你正在经历的。

• Incorrect connection string, such as using SqlExpress
• 不正确的连接字符串，例如使用SqlExpress
• Named Pipes(NP) was not enabled on the SQL instance
• 在SQL实例上未启用命名管道(NP)
• 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
• 在您正在处理的两台机器之间尝试基本连接测试。

#6

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

我刚刚启用了TCP/IP，通过Sql Server配置管理器中的命名管道，我的问题得到了解决，请参考这个以获得更多信息解决命名管道错误40

#7

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

使用服务器\实例名。在我的项目中使用双反斜杠解决了我的问题。

#8

Thanks to Damian...

由于Damian……

TCP/IP Named Pipes ... both enabled

TCP / IP命名管道…都启用

Web Config....(for localhost)

网络配置....(主机)

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

#9

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

也有同样的问题。花了6个小时来迁移一些服务器。尝试了所有关于这个话题和其他话题的建议。

Solution was as simple as server restart!

解决方案就像服务器重启一样简单!

#10

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:

在我的例子中，我有一个独立的服务器，我将configuration manager中的sql server端口默认端口1433更改为某个数字，并重新启动sql server服务以生效，如果我登录到服务器，我可以通过management studio连接到sql server。但是我无法通过sql server从本地机器连接，我得到了一个错误:

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

在建立到SQL Server的连接时发生与网络相关或特定于实例的错误。没有找到或无法访问服务器。验证实例名是否正确。

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)

该SQL服务器配置为允许远程连接。(提供程序:命名管道提供程序，错误:40 -无法打开到SQL Server的连接)(Microsoft SQL Server，错误: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.

——管道/启用TCP。允许远程连接。-Windows防火墙已关闭——为portin Windows防火墙创建了一个异常(在我的例子中，这不是必需的，因为服务器位于同一个子网网络中)。-在SQL Server配置管理器中启用所有内容。

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.

然后我将端口号调回到默认的1433，并重新启动sql server服务，问题得到了解决，我可以从本地管理工作室连接sql服务器。

#11

Try the following steps:

试试下面的步骤:

1. Open Services window (open "run box" and type services.msc).

   打开服务窗口(打开“运行框”并键入Services .msc)。

2. Looking for SQL services (with SQL prefix).

   寻找SQL服务(带有SQL前缀)。

3. Start them (if cannot start. Goto step 4).

   启动它们(如果不能启动的话)。转到步骤4)。

4. Right_click to each service -> Properties -> Change to tab "Log on"-> choise log on as "Local ..." -> 0K. Then start SQL services again.

   右击每个服务->属性->更改为tab“登录”->选择登录为“本地…”- > 0 k。然后再次启动SQL服务。

Try Open SQL and connect database.

尝试打开SQL并连接数据库。

#12

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

我也有同样的问题。我使用MSSQL服务器管理Studio 2017，通过以下步骤解决了这个问题:

1. Check for working fine SQL Server Services services or not.
2. 检查SQL Server服务是否正常工作。
3. Also check for working in good condition SQL Server (MSSQLSERVER).
4. 还要检查是否在状态良好的SQL Server (MSSQLSERVER)中工作。
5. Also check for working fine SQL Server Browser.
6. 还可以检查是否运行良好的SQL Server浏览器。
7. Restart SQL Server (MSSQLSERVER)
8. 重启SQL Server(MSSQLSERVER)

and fixed it.

和固定它。

#13

Very simple solution

非常简单的解决方案

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

都使用(本地)\就是这样。它为我工作。

#14

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.

在完成这里提到的所有步骤之后，如果仍然没有连接，请尝试在etc文件夹中的主机文件中添加具有IP地址的DNS。在连接字符串中添加IP地址而不是DNS名称应该是检查连接是否真正工作的临时解决方案。

#15

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

我尝试使用本地IP地址和公共IP地址进行连接。我试过了:

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.

是的，站点可以与启用了管道/TCP的服务器进行通信。允许远程连接。Windows防火墙被关闭，在Windows防火墙中为端口1433创建了一个异常。启用SQL Server配置管理器中的所有内容。

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

我确保并完成了上面的工作，我只想分享这个双重反斜杠

oBuilder.DataSource = "SPECIFICPCNAME\SQLEXPRESS";

oBuilder。数据源= " SPECIFICPCNAME \ SQLEXPRESS”;

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

使用一个反斜杠会导致一个构建错误，例如。:错误1无法识别的转义序列

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

我希望这能帮助下一个人——我牺牲了晚餐、午夜小吃和NBA的黄金时间来解决这个问题(羞耻)

Thanks to [Tamizh venthan] ^_^

由于[Tamizh venthan]^ _ ^

#16

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.

启用TCP/Ip、管道协议，通过计算机管理->SQL和服务，确保服务继续。将防火墙上的端口打包。尝试登录通过命令提示->作为管理员;最后，用户名应该是(本地)\SQLEXPRESS。希望这个有帮助。

#17

Open SQL Server Configuration Manager

打开SQL Server配置管理器

1. Select SQL Server Services from right.
2. 从右侧选择SQL Server服务。
3. Find your server from right and go to its properties (with right click)
4. 从右找到您的服务器并进入其属性(右击)
5. Change log on method to Local System.
6. 将方法更改为本地系统。

#18

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

我遇到了同样的问题，通过禁用防火墙(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)

解决这个问题的第一步应该是试着从另一台电脑上下载你自己的电脑。如果您有防火墙，您可能无法ping自己。我试了一下自己的pc，然后ping失败了(没有得到服务器的响应)

#19

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'

我建议下面的步骤来解决您的问题:如何修复错误命名的管道提供程序，错误40—无法打开到“SQL Server”的连接

1. Check for working fine SQL Server Services services or not.
2. 检查SQL Server服务是否正常工作。
3. Also check for working in good condition SQL Server (MSSQLSERVER).
4. 还要检查是否在状态良好的SQL Server (MSSQLSERVER)中工作。
5. Also check for working fine SQL Server Browser.
6. 还可以检查是否运行良好的SQL Server浏览器。
7. Delete all earlier Aliases, now create new aliases as per your requirements.
8. 删除所有以前的别名，现在根据您的需求创建新的别名。
9. Now check for working of SQL Server Default Port 1433
10. 现在检查SQL Server默认端口1433的工作。
11. 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.
12. 例如，下一步单击客户端协议，然后单击TCP/IP，现在单击鼠标右键，打开属性，这里您可以确保您的缺省端口SQL 1433正常工作。
13. 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.
14. 打开SQL Server Management Studio，然后右击，单击“Property”选项，然后单击Connections选项卡，最后单击允许远程连接到此服务器。
15. Check for right working or your Ping IP Host.
16. 检查是否正常工作或您的Ping IP主机。

#20

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!

我想在VS2015中添加一个新的连接。这里的建议没有一个奏效。由于怀疑向导中存在某种bug，尤其是SSMS能够很好地连接，我决定尝试并欺骗它。它工作!

1. 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".

   不要添加连接，使用“创建新的SQL Server数据库”。输入您的服务器名称和新数据库的随机名称。“测试”。

2. Assuming this succeeds, open Server Explorer in VS, locate the connection in Data Connections, right-click it and select Modify Connection.

   假设成功，在VS中打开服务器资源管理器，在数据连接中找到连接，右键单击它并选择修改连接。

3. 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!

   将“test”(从步骤1)更改为要连接的现有数据库的名称。点击“测试连接”。这次应该可以了!

4. Delete the temporary database you created in step 1.

   删除步骤1中创建的临时数据库。

#21

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.

我想我还有一个办法。最近我改变了我的电脑名，所以在尝试了以上所有的方法后，我仍然无法连接。我改了服务器名…服务器名=>(浏览更多)=>在数据库引擎下，发现一个新服务器与计算机新名称相同。这奏效了，生活又好起来了。

#22

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

我花了很长时间才意识到我的错误——我在连接字符串中使用了逗号而不是分号

#23

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.

当我将我的网站部署到IIS时，我看到了这个问题。解决方案是进入针对默认应用程序池的高级设置，并将标识属性从默认设置更改为管理员。

#24

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.

在我的例子中，我打开SQL Server Management Studio，在数据库引擎中搜索SQLEXPRESS。它有两个实例，我选择了正确的一个。

#25

For me it was a Firewall issue.

对我来说，这是一个防火墙问题。

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

首先需要添加端口(比如1444或1434)，但也要添加

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.

我第二次遇到这个问题是当我回到防火墙时，路径不正确，我需要更新表单12到13!只需点击“程序和服务”选项卡中的“浏览”，就可以实现这一点。

Finally, try running the command

最后，尝试运行命令

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

EXEC xp_readerrorlog 0,1，“无法注册服务主体名”，Null

For me, it returned the error reason

对我来说，它返回了错误原因

#26

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.

你会发现你的DB名称很可能是不正确的，你会在VS中看到服务器名称，比如“DESKTOP-0I14BKI”，但是如果你打开SSMS，你会看到DESKTOP-0I14BKI\SQLBLAHBLAH，你只需要在VS连接属性中添加“服务器名”(实例名)。

You will see:

你会看到:

To Fix:

修复: