Entity Framework Code First ignoring connection string, using IIS instead

Question

I have a web app that I've created using Entity Framework Code First. In setting it up I have managed to match my DB connection string to my DBContext by specifying the full namespace and class of the DBContext as the name of the connection string.

<add name="MyClassProject.EfDbContext"  connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=true;User Id=MyUsername;Password=MyPassword;" providerName="System.Data.SqlClient"/>

Initially when I set up the project, I just had it created in c:\inetpub\wwwroot, and just ran it through Visual Studio. Everything worked fine.

Now I'm trying to have the code build to a separate website folder, and have the website run as it's own website and app pool in IIS. I've set up the website, and my hosts file but when I went to run it I received the following error.

Cannot open database "MyDatabase" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\MyAppPool'.

I'm wondering why this is happening, as I seem to be specifying the security username and password to use for the DB in my connection string....so why is it trying to connect as the app pool that my website is running in?

Also, how can I fix this, without having to give MyAppPool (or Network Service if I changed it to that) DB permissions in SQL Server?

Update: I should've mentioned that I initialise my DBContext class using:

namespace MyClassProject 
{ 
    public class EfDbContext : DbContext 
    { 
        public EfDbContext() : base ("MyDatabase") 
        {
        }
    }
}

Daniel A. White · Accepted Answer · 2013-03-04 22:30:14Z

4

Remove Integrated Security=true;. That is the setting that passes the current user off.

answered Mar 4, 2013 at 22:30

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sean Holmesby Over a year ago

I've removed that attribute from the connection string, but am still having the same issue. I've even tried explicitly setting Integrated Security=false; but that didn't work either.

Daniel A. White Over a year ago

@SeanHolmesby are you sure its using that connection string?

Sean Holmesby Over a year ago

When I run through Visual Studio (when it works) it definitely adds data to the database that I'm trying to reference. I have:- namespace MyClassProject { public class EfDbContext : DbContext { public EfDbContext() : base ("MyDatabase") { } } } ......And then...... <connectionStrings> <add name="MyClassProject.EfDbContext" connectionString="user id=MyUsername;password=MyPassword;Data Source=.\SQLEXPRESS;Database=MyDatabase;Integrated Security=false;" providerName="System.Data.SqlClient"/> </connectionStrings>

Sean Holmesby Over a year ago

My seed data is using CreateDatabaseIfNotExists... however the DB already exists from when I ran through Visual Studio. I then went in, added the user to the security for the DB, and tried running through IIS.

Sean Holmesby · Accepted Answer · 2013-03-05 04:43:40Z

4

I found the issue. When I initialise my DBContext class with : base("MyDatabase"), it overrides the connection string specified in the web.config.

Removing that from my DBContext class, with the database already existing, the site now works in IIS. However, if I don't have the database created already, (or if I have my database initialiser use DropCreateDatabaseWhenModelChanges or DropCreateDatabaseAlways so that it'll needs to recreate the DB), the initialiser will fail, as it'll try to use an SQL user that doesn't have permissions to create the DB.

My way around it is to use the : base("MyDatabase") and run from Visual Studio initially so the database is created. Then remove it from code, add the specified user to the DB security in SQL Server, and it'll allow my site to run in IIS thereafter.

answered Mar 5, 2013 at 4:43

Sean Holmesby

2,1753 gold badges24 silver badges34 bronze badges

1 Comment

abatishchev Over a year ago

base("MyDatabase") means connection string name from Web.config. So you just need to have different connection string for different deployment points. Or set different name base on condition.

Greg · Accepted Answer · 2017-12-27 16:26:32Z

When using Integrated Security, the DB is given a token from the user who is currently running the process. In all likelihood, you run Visual Studio from your user account, which likely has Admin permissions on your SQL Server instance.

When IIS runs your app, it uses something called an Application Pool (or App pool). You can have multiple apps in a single pool to be managed together. The app pool also runs under a special user account named for the pool. App pool users exist under a container called "IIS AppPool", so the local user for the DefaultAppPool is IIS AppPool\DefaultAppPool. If you want to grant access to a resource on your local system (including file permissions), you can also grant it to the app pool user or local group IIS_IUSRS to grant it to all app pools.

Remember that these are local accounts, so they will not cross network boundaries. To grant permissions on a different server, you'll need to either use a domain user (or even better, a domain Managed Service Account) or you can set the app pool user to NETWORK SERVICE and then you can grant permissions to MyDomain\MyWebServer$ (the dollar sign is important).

abatishchev · Accepted Answer · 2013-03-05 04:58:25Z

0

You can use Web.config Transform to have Local connection stirng different from Remote (say in Release mode). To start using it you need to publish your Web App using One-Click Publish from Visual Studio. That's really very handy way to publish web apps!

Looks like that's what you're looking for.

Or set connection string name base on a condition:

public EfDbContext() : base (GetConnectionStringName())
{
}

private static GetConnectionStringName()
{
    return RunLocally() : "LocalDatabase" : "RemoteDatabase";
}

private static bool RunLocally()
{
   // implement some how
}

answered Mar 5, 2013 at 4:58

abatishchev

101k88 gold badges303 silver badges443 bronze badges

Collectives™ on Stack Overflow

Entity Framework Code First ignoring connection string, using IIS instead

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related