C# 实体框架底层提供程序在打开时失败

Entity Framework The underlying provider failed on Open

提问by User2012384

Below is my connection string:

下面是我的连接字符串：

connectionString="metadata=res://*/EDMX.Test.csdl|res://*/EDMX.Test.ssdl|res://*/EDMX.Test.msl;provider=System.Data.SqlClient;provider connection string="Data Source=home_computer;Initial Catalog=db_Test;Persist Security Info=True;User ID=testUser;Password=$1234;MultipleActiveResultSets=True""

connectionString="metadata=res://*/EDMX.Test.csdl|res://*/EDMX.Test.ssdl|res://*/EDMX.Test.msl;provider=System.Data.SqlClient;provider connection string="Data Source=home_computer;Initial Catalog=db_Test;Persist Security Info=True;User ID=testUser;Password=$1234;MultipleActiveResultSets=True""

Here is the code where the program stuck:

这是程序卡住的代码：

EDMX.TestingEntity context = new EDMX.TestingEntity();

var query = from t in context.User
            where t.UserName == _userName
            select t;

After running the above code, I check the variable query and found an exception

运行上述代码后，查看变量查询，发现异常

The underlying provider failed on Open.

底层提供程序在 Open 上失败。

I've checked:

我检查过：

1. Connection between server and computer is normal
2. I can login to the database with username testuser and with password $1234
3. I have checked the security settings in database (SQL Server) that permission has been granted to testUser

1. 服务器与电脑连接正常
2. 我可以使用用户名 testuser 和密码 $1234 登录到数据库
3. 我检查了数据库 (SQL Server) 中的安全设置，已授予 testUser 权限

Why does this exception happen? I'm using .net 4.5

为什么会发生这种异常？我正在使用 .net 4.5

---

---

Added:

添加：

I tried again, look at the inner exception and it was: 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)

我再次尝试，查看内部异常，它是：建立与 SQL Server 的连接时发生与网络相关或特定于实例的错误。服务器未找到或无法访问。验证实例名称是否正确以及 SQL Server 是否配置为允许远程连接。（提供程序：命名管道提供程序，错误：40 - 无法打开与 SQL Server 的连接）

I know it might be a network problem but I've turned off the firewall of server and also my computer and tried again but still not success..

我知道这可能是网络问题，但我已经关闭了服务器和计算机的防火墙并再次尝试但仍然没有成功..

---

---

Just now Copied the connectionstring to a program to test this connection and it was functioning well..

刚刚将连接字符串复制到程序以测试此连接并且它运行良好..

---

---

I just rollback all changes and test again and it worked

我只是回滚所有更改并再次测试，它起作用了

采纳答案by Ramesh Sivaraman

Seems like a connection issue. You can use the Data link properties to find if the connection is fine. Do the following:

好像是连接问题。您可以使用数据链接属性来确定连接是否正常。请执行下列操作：

1. Create a blank notepad and rename it to "X.UDL"
2. Double click to open it
3. Under connections tab choose the server name/enter the name use the correct credentials and DB
4. Click OK to save it.

1. 创建一个空白记事本并将其重命名为“X.UDL”
2. 双击打开
3. 在连接选项卡下选择服务器名称/输入名称使用正确的凭据和数据库
4. 单击确定以保存它。

Now open the file in Notepad and compare the connection string properties.

现在在记事本中打开文件并比较连接字符串属性。

回答by Vinay Pratap Singh

Please check the following things first.

请先检查以下事项。

While generating the Edmx you would have given a name to you connection string. that gets into the app config of the project with the Entity.

在生成 Edmx 时，您将为连接字符串命名。与实体一起进入项目的应用程序配置。

Have you copied the same connection string to your main Config file. Also the Name should be same as which you have given while generating the EDMX file.

您是否将相同的连接字符串复制到主配置文件中。此外，名称应与您在生成 EDMX 文件时提供的名称相同。

回答by Amar Pawar

Try this- Open the command prompt as administrator and type this netsh Winsock reset

试试这个 - 以管理员身份打开命令提示符并输入这个 netsh Winsock reset

Restart your system and try again.

重新启动系统并重试。

回答by user3290439

My client reported this error. I found that he was messing with *.ldf files. He copied *ldf file on one database and renamed it to match a second database (which I asked him to place in a folder).

我的客户报告了这个错误。我发现他在搞乱 *.ldf 文件。他复制了一个数据库上的 *ldf 文件并将其重命名以匹配第二个数据库（我让他将其放在一个文件夹中）。

I replicated the same scenario, and got this same error in my development system. Error got fixed after deleting the *ldf file(s).

我复制了相同的场景，并在我的开发系统中遇到了同样的错误。删除 *ldf 文件后错误得到修复。

回答by Oamsalem

If you are using a local .mdf file, probably a sync software such Dropbox attempted to sync two log files (.ldf) in two different computers you can delete the log files from the bin Directory and make sure the .mdf properties->Copy to Output Directory ->Copy if newer that will copy the selected DB file and it's log to the bin Directory. !Alert- if your DB file has only changed in the bin Directory all the changes ill be discarded!

如果您使用的是本地 .mdf 文件，可能是 Dropbox 等同步软件尝试同步两台不同计算机中的两个日志文件 (.ldf)，您可以从 bin 目录中删除日志文件，并确保 .mdf 属性->复制到输出目录 -> 如果较新，则复制将复制选定的 DB 文件及其日志到 bin 目录。!Alert- 如果您的数据库文件仅在 bin 目录中更改，则所有更改都将被丢弃！

回答by Vishal Saini

1. Search "Component Services" in Programs and Files
2. Go to Services
3. Find "Distributed Transaction Coordinator" Service
4. Right click and Restart the Service

1. 在程序和文件中搜索“组件服务”
2. 前往服务
3. 查找“分布式事务协调器”服务
4. 右键单击并重新启动服务

You've just done a restart of the service and the code should run without errors

您刚刚重新启动了服务，代码应该可以正常运行

回答by Abdul Khaliq

Possible solution is described in this Code Project tip:

此代码项目提示中描述了可能的解决方案：

As folks mentioned IIS user network service user credentials while trying to log in sql server. So just change the Application pool settings in your IIS:

1. Open Internet Information Service Manager
2. Click on Application Pools in left navigation tree.
3. Select your version Pool. In my case, I am using ASP .Net v4.0. If you dont have this version, select DefaultAppPool.
4. Right click on step 3, and select advanced settings.
5. Select Identity in properties window and click the button to change the value.
6. Select Local System in Built-in accounts combo box and click ok. That's it. Now run your application. Everything works well.

正如人们在尝试登录 sql server 时提到的 IIS 用户网络服务用户凭据。因此，只需更改 IIS 中的应用程序池设置：

1. 打开 Internet 信息服务管理器
2. 单击左侧导航树中的应用程序池。
3. 选择您的版本池。就我而言，我使用的是 ASP .Net v4.0。如果您没有此版本，请选择 DefaultAppPool。
4. 右键单击第 3 步，然后选择高级设置。
5. 在属性窗口中选择 Identity 并单击按钮更改值。
6. 在内置帐户组合框中选择本地系统，然后单击确定。就是这样。现在运行您的应用程序。一切正常。

回答by Rustem Mustafin

We had connection string in web.configwith Data Source=localhost, and there was this error (MSSQL was on the same machine). Changing it to actual `DOMAIN\MACHINE' helped, somewhy.

我们在web.configwith 中有连接字符串Data Source=localhost，并且出现了这个错误（MSSQL 在同一台机器上）。不知何故，将其更改为实际的“DOMAIN\MACHINE”有所帮助。

回答by Kevin B Burns

For me when that usually starts happening, I have to remote desktop into the service and at the minimum restart IIS. It usually starts popping up right after I deploy code. On a few rare occasions I have had to restart the SQL services and IIS. I wrote a batch script to take a param (1 or 2) and have it setup to either do a restart of IIS ( i.e. 1), or go full nuclear (i.e. 2).

对我来说，当这通常开始发生时，我必须远程桌面进入服务并至少重新启动 IIS。它通常在我部署代码后立即开始弹出。在少数情况下，我不得不重新启动 SQL 服务和 IIS。我编写了一个批处理脚本来获取参数（1 或 2），并将其设置为重新启动 IIS（即 1）或全核（即 2）。

回答by Boney

Always check for Inner Exception if any. In my case Inner Exception turned out to be really helpful in figuring out the issue.

如果有，请始终检查内部异常。就我而言，内部异常对解决问题非常有帮助。

My site was working fine in Dev Environment. But after i deployed to production, it started giving out this exception, but the Inner Exception was saying that Login failed for the particular user.
So i figured out it was something to do with the connection itself. Hence tried logging in using SSMS and even that failed.

我的网站在开发环境中运行良好。但是在我部署到生产环境后，它开始发出这个异常，但内部异常表示特定用户的登录失败。
所以我发现这与连接本身有关。因此尝试使用 SSMS 登录，甚至失败了。

Eventually figured out that exception showed up for the simple reason that the SQL server had only Windows Authentication enabled and SQL Authentication was failing which was what i was using for Authentication.

最终发现出现异常的原因很简单，即 SQL 服务器仅启用了 Windows 身份验证，而 SQL 身份验证失败，这正是我用于身份验证的原因。

In short, changing Authentication to Mixed(SQL and Windows), fixed the issue for me. :)

简而言之，将身份验证更改为混合（SQL 和 Windows），为我解决了这个问题。:)