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") 
        {
        }
    }
}

Answer 1

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.

Answer 2

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