访问数据时的LocalDb错误“底层提供程序在打开时失败” [英] LocalDb error when accessing data "The underlying provider failed on Open"

问题描述

我正在尝试连接到数据库的本地副本以进行测试，以便不改变实时数据。

I'm currently attempting to connect to a local copy of a database for testing purposes as to not alter live data.

我正在使用MVC与实体框架（.Net 4.5），当我尝试通过EF访问数据时，我正在遇到这个错误：

I'm using MVC with Entity Framework (.Net 4.5), and I'm currently running into this error whenever I attempt to access data through EF:

An exception of type 'System.Data.EntityException' occurred in System.Data.Entity.dll but was not handled in user code

Additional information: The underlying provider failed on Open.

这是尝试访问数据的功能 - 错误发生在第一行

This is the function that is trying to access the data - the error happens on the first line

public IEnumerable<Client> GetClients()
{
    List<Client> clients = (from c in this.repository.Clients select c).ToList();
    return clients;
}

这是我的app.config中的连接字符串：

Here is my connection string in the app.config as well:

<add name="EntitiesConnectionString" connectionString="metadata=res://*/EFName.csdl|res://*/EFName.ssdl|res://*/EFName.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=(LocalDb)\v11.0;Initial Catalog=testdb;Integrated Security=True;Pooling=False&quot;" providerName="System.Data.EntityClient" />

我使用Visual Studio 2015附带的集成SQL Server Express来管理本地数据库我已经创建了。

I'm using the integrated SQL Server Express that comes with Visual Studio 2015 to manage the local DB that I have created.

我发现的更多信息：

"Cannot open database \"testdb\" requested by the login. The login failed.\r\nLogin failed for user 'NT AUTHORITY\\SYSTEM'."

我不相信我在创建的数据库上设置了任何权限，也许是连接字符串错误？

I don't believe that I have any permissions set on the db that I created, perhaps something is wrong with the connection string?

编辑：

我添加了NT AUTHORITY\NETWORK SERVICE '用户，然后将其添加到MS SQL Server Management Studio中的'db_owner'角色，没有任何运气

I've added the 'NT AUTHORITY\NETWORK SERVICE' user, and then added it to the 'db_owner' role in MS SQL Server Management Studio without any luck

我还尝试创建一个新的登录名和用户的凭据和'db_owner'角色，我仍然收到相同的登录失败的用户'用户'。

I also attempted to create a new login and user with credentials and the 'db_owner' role, and I still receive the same "Login failed for user 'user'."

这是我使用测试的连接字符串我创建的用户：

This is the connection string that I'm using with the test user that I created:

<add name="EntitiesConnectionString" connectionString="metadata=res://*/EFName.csdl|res://*/EFName.ssdl|res://*/EFName.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=(LocalDb)\v11.0;Initial Catalog=testdb;Integrated Security=False;Persist Security Info=True;User ID=test;Password=password&quot;" providerName="System.Data.EntityClient" />

推荐答案

我找到了解决问题的办法！有一些事情需要做。

I've found the solution to my problem! There were a few things that needed to be done.

首先，以下服务没有运行，需要：

First off, the following services were not running and needed to be:

SQL Server（MSSQLSERVER）

SQL Server (MSSQLSERVER)

SQL Server代理（MSSQLSERVER）

SQL Server Agent (MSSQLSERVER)

SQL Server浏览器

SQL Server Browser

其次，我使用不同的本地服务器 not（LocalDb）\v11.0或（LocalDb）\mssqllocaldb ，并将数据库和数据重新复制到数据源（仅使用我的电脑名称作为数据源）

Second, I used a different local server not (LocalDb)\v11.0 or (LocalDb)\mssqllocaldb and re-copied the DB and data to it (using just my computer's name for the data source)

第三，通过SQL Server Management Studio连接到本地数据库，并创建具有db_owner权限的登录/密码/用户，然后使用我的连接字符串中的那些：

Third, I had to connect to the local DB through SQL Server Management Studio and create a login/password/user with db_owner permissions, and then use those in my connection string as such:

<add name="EntitiesConnectionString" connectionString="metadata=res://*/EFName.csdl|res://*/EFName.ssdl|res://*/EFName.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=POI;Initial Catalog=testdb;Integrated Security=False;Persist Security Info=True;User ID=test_user;Password=thepassword&quot;" providerName="System.Data.EntityClient" />

这篇关于访问数据时的LocalDb错误“底层提供程序在打开时失败”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！