LocalDb error when accessing data "The underlying provider failed on Open"

Question

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

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;
}

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" />

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

More information that I've found:

"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?

Edit:

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

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" />

Possible duplicate of [MSSQL Error 'The underlying provider failed on Open'](http://stackoverflow.com/questions/2475008/mssql-error-the-underlying-provider-failed-on-open) — Bhavik Patel, Apr 14 '16 at 02:07
I am using an ObjectContext, which I think won't work with the solution that was posted. I also read into the blog post that they had linked, and it suggested to add the 'NT AUTHORITY\NETWORK SERVICE' user within MS SQL Server Management Studio - I tried that as well, and I'm still receiving the same error. — Brandon Hyder, Apr 14 '16 at 02:38
how is your provider System.Data.SqlClient defined? have you tried re-installing it? — DevilSuichiro, Apr 14 '16 at 05:33

score 1 · Answer 1 · answered Apr 13 '16 at 23:32

You are likely enumerating GetClients() after disposing of the underlying connection.

Try either:

a) changing the return type of the method to something like List< Client>

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

or b) call .ToList() on the method immediately:

IEnumerable<Clients> clients = GetClients().ToList();

As it is, execution of .ToList() inside your method is deferred until the method's return value is enumerated, which if I had to guess, is after the db connection has been disposed.

Thank you for the response! I attempted both suggestions - but unfortunately I still get the same error; I think the issue is happening upon attempting to open the connection — Brandon Hyder, Apr 14 '16 at 00:53

score 1 · Answer 2 · answered Apr 14 '16 at 03:13

as your method returning IEnumerable<Client> you have to return also Enumerable<Client>. use as below

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

hope it will help

score 1 · Answer 3 · answered Apr 14 '16 at 16:36

1

Why is it that you added NT AUTHORITY\NETWORK SERVICE? From the first error message, it looks like whatever context you're running under is the NT AUTHORITY\SYSTEM user.

If that's the case, I think you need to add: NT AUTHORITY\SYSTEM as a login on your database and also give it the db_owner or whatever appropriate roles you need.

If that doesn't resolve it, I can take another look.

answered Apr 14 '16 at 16:36

Ayo I

7,722
5
30
40

Nice catch! I didn't notice that at all; I'm going to go try that out. – Brandon Hyder Apr 14 '16 at 17:54

score 1 · Answer 4 · edited Jun 20 '20 at 09:12

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 Agent (MSSQLSERVER)

SQL Server Browser

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)

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 error when accessing data "The underlying provider failed on Open"

4 Answers4

Linked