CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
Brandon Hyder
Brandon Hyder

Reputation: 123

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.

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

Upvotes: 1

Views: 2460

Answers (4)

Brandon Hyder
Brandon Hyder

Reputation: 123

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

Upvotes: 1

Ayo I
Ayo I

Reputation: 8124

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.

Upvotes: 1

Mostafiz
Mostafiz

Reputation: 7352

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

Upvotes: 1

Steve Konves
Steve Konves

Reputation: 2668

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.

Upvotes: 1

Related Questions