为什么会出现“无法连接到服务器-与网络有关或与实例有关的错误”?

sql-server visual-studio azure azure-virtual-machine

1287500 观看

30回复

1830 作者的声誉

尝试连接到SQL Server时出现以下错误:

无法连接到108.163.224.173。

建立与SQL Server的连接时发生与网络相关或特定于实例的错误。

服务器未找到或无法访问。验证实例名称正确,并且已将SQL Server配置为允许远程连接。

(提供者:命名管道提供程序,错误:40-无法打开与SQL Server的连接)(Microsoft SQL Server,错误:1326)

当我尝试在Visual Studio 2010中尝试将数据库配置为gridview时,会引发此错误。我不知道如何调试此错误。

您将如何调试此错误?除了错误消息中提到的步骤之外,我还应该采取什么步骤来确定实际发生的情况?

作者: Sasa Shree 的来源 发布者: 2013 年 8 月 5 日

回应 (30)


282

5122 作者的声誉

我发现以下技术很有帮助:

  1. 确保您的数据库引擎配置为接受远程连接

    • 开始>所有程序> SQL Server 2005>配置工具> SQL Server外围应用配置器
    • 单击用于服务和连接的外围应用配置
    • 选择有问题的实例>数据库引擎>远程连接
    • 启用本地和远程连接
    • 重新启动实例
  2. 您可能需要在防火墙上为正在使用的SQL Server实例和端口创建一个例外

    • 开始>运行> Firewall.cpl
    • 单击例外选项卡
    • 添加sqlservr.exe(通常位于中C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Bin,检查安装的实际文件夹路径)和端口(默认为1433
    • 还要检查您的连接字符串
  3. 检查您的SQL Server服务是否已启动并正常运行:

    • 转到所有程序> Microsoft SQL Server 2008>配置工具> SQL Server配置管理器> SQL Server服务
    • 检查以确保SQL Server服务状态为“正在运行”。

    此外,请确保您的远程服务器在同一网络中。运行sqlcmd -L以确定您的服务器是否包含在网络列表中。

  4. 在SQL Server配置中启用TCP / IP

    当通过网络连接两个或多个SQL Server时,它们将使用TCP / IP进行所有通信。SQL Server安装的默认端口为1433。可以通过SQL Server配置管理器更改此端口。应该为要连接的SQL Server启用TCP / IP。

    • 转到所有程序>> Microsoft SQL Server 2008 >>配置工具>> SQL Server配置管理器>>选择TCP / IP
    • 右键单击TCP / IP >>单击启用

    您必须重新启动SQL Server服务,所有更改才能生效。右键单击并转到菜单属性,以选择可以更改SQL Server默认端口的位置。

作者: Teo Chuen Wei Bryan 发布者: 05.08.2013 02:33

106

2980 作者的声誉

我为我找到了解决方案:

打开“ SQL Server配置管理器”

现在,单击“ SQL Server网络配置”,然后单击“ 名称协议”

右键单击“ TCP / IP”(确保已启用),单击“属性”。

现在,选择“ IP地址”选项卡-并且-转到最后一个条目“ IP全部”

输入“ TCP端口” 1433。

现在, 重新启动 “ SQL Server .Name”。使用“ services.msc”(winKey + r)

它会工作...

作者: Irshad Khan 发布者: 08.09.2015 12:34

10

3745 作者的声誉

If none of the above solutions work (nothing worked for me) then just RESTART your computer and you will be able to connect to your sql server (localhost).

作者: Eugene Sunic 发布者: 08.10.2015 10:06

3

815 作者的声誉

I have to run SQL Server Browser service into SQL Server Configuration Manager. Installation can't discover newly created service without this.

作者: MarkosyanArtur 发布者: 27.01.2016 11:11

78

2841 作者的声誉

我通过打开“ 服务”来解决该问题,然后开始运行Sql Server(Sqlexpress)服务。

服务形象

作者: Anik Saha 发布者: 16.02.2016 08:14

11

1337 作者的声誉

I had the same error when I wanted to run my WinForms project (that includes working with a SQL Server database and that worked perfectly on my PC) on another PC. The problem was in Windows Firewall on my PC. I solved this by adding two rules. This is the whole procedure how to allow SQL Server through Windows Firewall:

  1. Open "Run" and enter services.msc
  2. Find the service for SQL Server (instance name) and SQL Server Browser. One at a time, right click, select "Properties", copy the path to exe file
  3. Then open firewall.cpl, click allow an application or add rule, add the previously copied path (there is a procedure you need to follow), check Domain and Private, uncheck Public.

This is the YouTube link where you can see this procedure: Allow SQL Server through Windows Firewall

作者: NutCracker 发布者: 27.02.2016 04:10

8

387 作者的声誉

After doing everything mentioned here:
http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/ Still did not work for me.

Steps worked for me:

Start > Run > cmd > sqlcmd -L

It will prompt you the server name. Make sure this server name is same as the one you are trying to get connected to in CONNECT TO SERVER box of SQL management studio.

I made this silly mistake I keep using MSSQLSERVER rather using this server name.

Hope this helps for the people who make silly mistake like me.

Thanks.

作者: Denn 发布者: 27.02.2016 05:47

13

1141 作者的声誉

Press window + R (Run window Open) and in run window type "services.msc" and new services open find SQL SERVER with instance name in my case it's SQL SERVER(SQLEXPRESS) then start this service and try again it works for me Hope Its Works for You also.在此处输入图片说明

作者: Hafiz Asad 发布者: 02.04.2016 08:25

1

1891 作者的声誉

当您的sql server实例为时,会发生此错误stopped

转到所有程序> SQL Server>配置工具> SQL SERVER配置管理器

然后单击“ SQL服务器服务”,将出现实例列表,选择有问题的实例,然后单击顶部工具栏上的“播放”图标,希望对您有所帮助。

这个答案很晚(但总比没有好。)

作者: Alex 发布者: 16.04.2016 12:58

3

178 作者的声誉

While the above solutions should work in 90% of the cases, but if you are still reading this answer!!! You are probably trying to connect to a different server than intended. It may be due to a configuration file pointing to a different SQL server than the actual server you think you are trying to connecting to.

Happened to me atleast.

作者: Arjmand 发布者: 28.04.2016 07:51

3

954 作者的声誉

I tried all the other answers on this question and some if not all probably played a part in getting this working for me, but I still couldn't connect to the DB remotely. I was using a SQL Server on an Azure VM.

I eventually remembered that the VM has endpoints that are controlled by the Azure account proxy, so I went on to the Azure Portal and added 1433 as an available endpoint and I could connect to my SQL instance.

Hope this helps someone who has tried all the other answers and is still having no luck!

作者: John 发布者: 26.05.2016 02:52

87

4195 作者的声誉

添加我强烈支持的评论作为屏幕截图的答案。

我花了很多时间,最后对我有用的是:

1)打开Sql Server配置管理器 -> SQL Server网络配置 -> <(INSTANCE)>的协议 -> TCP / IP(双击它)。

在此处输入图片说明

2)选择-> IP地址(Tab)

3)转到最后一个条目IP All,并提及TCP端口1433

在此处输入图片说明

4)按,Win+R然后输入services.msc

5)现在重新启动SQL Server <(INSTANCE)>

在此处输入图片说明

在此之后,问题得到解决!

作者: user1336087 发布者: 28.07.2016 03:46

13

895 作者的声誉

如果您可能正在使用Express Edition

"\SQLEXPRESS"在您的服务器名称后添加

例如 "MY-SERVER\SQLEXPRESS"

在此处输入图片说明

作者: Abdulrahman Bres 发布者: 26.09.2016 01:08

4

1133 作者的声誉

I was experiencing the same problem and the problem was that I hade several projects in the solution (Weband Droid) and even though Default project was choosen in the Package Manager Console it used the connection string from the Droid project:

PM> update-database -Verbose
Using StartUp project 'Droid'. <-- DROID
Using NuGet project 'Web'. <-- WEB
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
<!-- BAD TARGET DATABASE -->
Target database is: 'DefaultConnection' (DataSource: .\SQLEXPRESS, Provider: System.Data.SqlClient, Origin: Convention).
System.Data.SqlClient.SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
   [REMOVED TEXT]
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:-1,State:0,Class:20
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

After setting the Startup Project to Web and the Default Project in Package Manger Console I got it to work.

作者: Jefecito 发布者: 15.10.2016 06:22

1

158 作者的声誉

我通过将使用Entity Framework的项目设置为启动项目来解决此问题,然后运行“ update-database”命令。

作者: user1796440 发布者: 22.10.2016 08:34

5

421 作者的声誉

I am using SQL Server 2016 and Window 10.

在此处输入图片说明

First thing is to allow remote connection to SQL Server. What I did is to type sqlservermanager13.msc at start menu in order to open the SQL Server Configuration Manager. Make sure the TCP/IP status is enabled. 在此处输入图片说明

Check your TCP port number by double click TCP/IP protocol name. Usually it is 1433 by default.

在此处输入图片说明

The following procedures configure the Windows Firewall by using the Windows Firewall with Advanced Security Microsoft Management Console (MMC) snap-in. The Windows Firewall with Advanced Security only configures the current profile.

To open a port in the Windows firewall for TCP access

  1. On the Start menu, click Run, type WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
  3. In the Rule Type dialog box, select Port, and then click Next.
  4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
  5. In the Action dialog box, select Allow the connection, and then click Next.
  6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
  7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Another thing to configure.

To open access to SQL Server when using dynamic ports

  1. On the Start menu, click Run, type WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
  3. In the Rule Type dialog box, select Program, and then click Next.
  4. In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Click Next.
  5. In the Action dialog box, select Allow the connection, and then click Next.
  6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
  7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Have a look at Microsoft doucmentation Configure a Windows Firewall for Database Engine Access

作者: Fred 发布者: 11.11.2016 07:06

1

682 作者的声誉

如果在Visual Studio中调试时遇到此问题,请确保项目构建路径指向本地驱动器,或按照以下步骤将权限授予网络文件夹。

作者: Kevin D. 发布者: 16.11.2016 12:20

3

477 作者的声誉

I moved from a work laptop on Windows 7 to a work laptop on Windows 10. I had been successfully using SSMS2016 on Windows 7.

The same issue applied using SSMS2012 or SSMS2016. My access to the 10 sql servers using windows authentication was still the same. I could test this from another server. However, 2 of the 10 servers would not connect from my laptop. Both were ms sql server 9 but I could connect to other sql server 9 databases.

The solution was to add a firewall rule (using Windows Firewall with Advanced Security).

Create an Incoming rule for each SSMS eg C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe

I'm not a network expert so I've not included the details but hopefully it will point you in the right direction.


Error msg (pre firewall rule) "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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)"

作者: johnc 发布者: 17.01.2017 09:55

2

316 作者的声誉

My issue started when I tried to change the server from IIS Express to Local IIS (while using LocalDB).

在此处输入图片说明

I was using LocalDB (for dev purposes), and when I went to revert from Local IIS to IIS Express, Visual Studio had switched my data source from Data Source=(LocalDb)\MSSQLLocalDB to Data Source=.\SQLEXPRESS

Incorrect connection string

<add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SurveyTestsDB.mdf;Initial Catalog=SurveyTestsDB;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />

Correct connection string

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\SurveyTestsDB.mdf;Initial Catalog=SurveyTestsDB;Integrated Security=True" providerName="System.Data.SqlClient" />

Hope this helps someone out there.

作者: megamaiku 发布者: 20.02.2017 10:29

3

1055 作者的声誉

This solution also solves both network error and the service behind sql server

I answered a similar question here, you need to stat the other open Run type-> services.msc - under services -> sort by stopped you will see a bunch of stopped SQL services Right click and start

To begin - there are 4 issues that could be causing the common LocalDb SqlExpress Sql Server connectivity errors SQL Network Interfaces, error: 50 - Local Database Runtime error occurred, before you begin you need to rename the v11 or v12 to (localdb)\mssqllocaldb

I found that the simplest is to do the below - I have attached the pics and steps for help.

First verify which instance you have installed, you can do this by checking the registry and by running cmd

  1. cmd> Sqllocaldb.exe i
  2. cmd> Sqllocaldb.exe s "whicheverVersionYouWantFromListBefore" if this step fails, you can delete with option d cmd> Sqllocaldb.exe d "someDb"
  3. cmd> Sqllocaldb.exe c "createSomeNewDbIfyouWantDb"
  4. cmd> Sqllocaldb.exe start "createSomeNewDbIfyouWantDb"

SqlLOCALDb_edited.png

作者: transformer 发布者: 01.03.2017 05:17

2

391 作者的声誉

Along with trying everything as suggested by Teo Chuen Wei Bryan, make sure you are also referring to the correct Server/Instance name in the connection string.

If you are using the short form of host name/Instance on the database server or in the web.config file, make sure you use the fully qualified domain name(FQDN)/Instance

Also, to test connectivity from a server where SQL server client is NOT present,

--> create a text file and change its file extension to .udl

--> Right click the file and you can see connection tab.

--> Input server name and log on information to test connection to the database server.

Hope this helps.

作者: SanthoshM 发布者: 15.03.2017 09:35

4

778 作者的声誉

You can test the following methods.

  • a

    1. Check the connection string of the project.
  • b

    1. Go to services and restart SQLServer Instance.
  • c

    1. Open 'SQLServer Configuration Manager'
    2. In the left panel select 'SQLServer Network Configuration' and expanding it
    3. Select 'Protocols for MSSQLServer'
    4. In the right panel dbl click on 'TCP/IP'
    5. In the 'Protocol' tab set the 'Enabled' to 'Yes'
    6. In the 'IP Addresses' tab scroll to down
    7. In the 'IPAll' set 'TCP Port' to 1433
  • d
    1. Open 'Firewall with advanced security'
    2. In the right tab select 'Inbound Rules'

In the middle tab find the record that 'local Port' is 1433, If you can't found it try to created it with following levels

  • In the Start menu, click Run, type 'WF.msc', and then click OK
  • In the left panel click the 'Windows Firewall with Advanced Security'
  • In the right panel right-click 'Inbound Rules', and then click 'New Rule'
  • In the Rule Type dialog box, select 'Port', and then click Next
  • In the Protocol and Ports dialog box, select 'TCP', and select 'Specific Local Ports', and then type the port number 1433, Click Next
  • In the Action dialog box, select Allow the connection, and then click Next
  • In the 'Profile' dialog box, checking the Domain, Private and Public, Then click Next
  • In the 'Name' dialog box, type 'SQL 1433 Port' and for a description write description for own. Then click Finish
  1. Then in the middle tab double click the found item(Instance) or created item of 'SQL 1433 Port' name by you.
  2. Select 'Scope' tab in opened dialog box(SQL Server Properties)
  3. In the Local PC Go to google.com in your Browser and search 'My IP'.
  4. then copy of your 'IP'
  5. Go to the remote server and in the 'SQL Server Properties' dialog box of 'Scope' tab, in the 'Remote IP Address' select the 'These IP Addresses' option and click 'Add' button
  6. In the opened dialog box(IP Address) select 'This IP Address or Subnet' option and paste your 'IP', click OK button.
作者: MohammadSoori 发布者: 30.03.2017 07:15

2

3742 作者的声誉

If you suddenly encounter this error say in a production environment and nothing has changed, try the following 4 items in the order below to see if it gets fixed.

  1. restart the sql server service.
  2. restart the service (say IIS) that is calling into sql server. (the problem is probably here if the time between the start of the service call to SQL server and the time you end up getting the response error is super short (about one or two second).
  3. restart the server sql server is on.
  4. restart the server the calling service is on.
作者: Barka 发布者: 24.04.2017 03:33

2

638 作者的声誉

When I experienced this error in Visual Studio,

“建立与SQL Server的连接时发生了与网络相关或特定于实例的错误。服务器未找到或无法访问。验证实例名称正确,并且已将SQL Server配置为允许远程连接。(提供者:命名管道提供者,错误:40-无法打开与SQL Server的连接)”

...这是在执行以下C#代码期间,该代码试图获取我的SQL Server数据以在网格中显示它。中断恰好发生在显示connect.Open()的行上:

        using (var connect = Connections.mySqlConnection)
        {
            const string query = "SELECT Name, Birthdate, Narrative FROM Friends";
            using (var command = new SqlCommand(query, connect))
            {
                connect.Open();
                using (var dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        // blah
                    }
                }
            }
        }

这是无法解释的,因为SQL查询非常简单,我拥有正确的连接字符串,并且数据库服务器可用。我决定自己在SQL Management Studio中手动运行实际的SQL查询,它运行得很好,并产生了几条记录。但是查询结果中有一件事情引人注目:Friends表中的varchar(max)类型字段中有一些编码不正确的HTML文本(特别是某些编码的注释符号<!--属于“ Narrative”列的数据)。可疑数据行如下所示:

Name    Birthdate    Narrative
====    =========    ============== 
Fred    21-Oct-79    &lt;!--HTML Comment -->Once upon a time...

请注意,编码的HTML符号“ &lt;”代表“ <”字符。不知何故,它进入了数据库,而我的C#代码无法识别它!它每次都在connect.Open()行失败!在我手动编辑数据库表Friends中的那一行数据并放入已解码的“ <”字符后,一切正常!该行应该是这样的:

Name    Birthdate    Narrative
====    =========    ============== 
Fred    21-Oct-79    <!--HTML Comment -->Once upon a time...

我使用下面的这个简单的UPDATE语句编辑了一个坏行。但是,如果您有几行令人讨厌的HTML编码行,则可能需要使用REPLACE函数的更精细的UPDATE语句:

UPDATE Friends SET Narrative = '<!--HTML Comment -->Once upon a time...' WHERE Narrative LIKE '&lt%'

因此,这个故事的寓意是(至少在我看来),在将HTML内容存储在数据库中之前先对其进行清理,这样一来,您就不会再收到这种神秘的SQL Server错误!(嗯,如果您需要更多信息,那么对HTML内容进行正确的清理/解码是另一个讨论的主题,值得单独进行StackOverflow搜索!)

作者: ShieldOfSalvation 发布者: 22.05.2017 08:50

3

119 作者的声誉

You may check service status of MS SQL Server 2014. In Windows 7 you can do that by:

  1. Go to search and Type "SQL Server 2014 Configuration Manager
  2. Then click on "SQL Server Service" on left menu
  3. Check the instance of SQL Server service status if it is stopped or running
  4. If it has stopped, please change the status to running and log in to SQL Server Management Studio 2014
作者: Chamara 发布者: 16.07.2017 07:36

3

155 作者的声誉

Summary

To fix this issue encountered while running local app vs remote database, use SQL Server Configuration Manager to add an alias for the remote database.

Details

I had run into this problem recently when transitioning from a Windows 7 to a Windows 10 laptop. I was running a local development and runtime environment accessing our Dev database on a remote server. We access the Dev database through a server alias setup through SQL Server Client Network Utility (cliconfg.exe). After confirming that the alias was correctly setup in both the 64 and 32 bit versions of the utility and that the database server was accessible from the new laptop via SSMS, I still got the error seen by the OP (not the OP's IP address, of course).

It was necessary to use SQL Server Configuration Manager to add an alias for the remote Dev database server. Fixed things right up.

在此处输入图片说明

作者: Bill Needels 发布者: 02.11.2017 02:00

19

2681 作者的声誉

此错误主要是在SQL服务停止时发生的。您需要重新启动服务。要转到此窗口,您必须像这样搜索服务- 在此处输入图片说明

然后搜索SQLSERVER(MSSQLSERVER)并重新启动服务。

在此处输入图片说明

希望这会起作用。

作者: Debendra Dash 发布者: 08.11.2017 04:19

3

398 作者的声誉

Why this error is so boresome and noisy, just because it can occur in varied situation.

I have done all approchs above here, and still being sucked. So make sure u have done the same as me before browsing downward.

Maybe I am not able to fix ur situation instantly, but I can point out a direction or thinking to u(The one who finally slide down here). I have started to ponder the error of my running program occurring after I made sure that the instance name is clearly right and set my database to allow remote control following the methods above. After then, I suspected something wrong happening in my code snippet of SQL connection.

Solution of my problem:

  • Check my sqlconnection function

  • Click to see its configuration

  • New a connection

在此处输入图片说明

  • Select ur server name

在此处输入图片说明

It works for me with pondering what exactly happen in the process of connection.Hope my thinking will lead u to kill ur error.

作者: Chase07 发布者: 14.11.2017 10:57

2

394 作者的声誉

Web.config中的XML标记排列很重要

第一

<configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>

<connectionStrings>
  <add name="SqlConnectionString" connectionString="Data Source=.; Initial Catalog=TestDB; Trusted_Connection=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
作者: user7339584 发布者: 25.12.2017 08:52

2

51 作者的声誉

尝试,,1433连接字符串的末尾添加和端口号(如中的)。

作者: Kalyan Hurkat 发布者: 07.06.2018 01:44
32x32