CREATE DATABASE permission denied in database 'master' Entity Framework Migration

Question

I am trying to deploy an ASP.NET MVC 4 application with Entity Framework 4.4 to a shared web hosting (GoDaddy-4GH platform). In GoDaddy I can't create databases using the application code I have to create it via their control panel, which I did.

I want to use the migration feature to allow my database to evolve without manually modify the schema.

I've use a combination of IDatabaseInitializer and DbMigrationsConfiguration. The db initializer simply migrates to the latest version.

The problem is that during the update process EF checks whether the database exists using the EnsureDatabaseExists method, and if for some reason it decides that is does not, then it goes ahead and tries to create a new database which of course fails.

1. How can I debug why the EnsureDatabaseExists returns false?
2. Is it possible to override this behavior? (from looking at the code with reflection it does not seem that way)

DBMigration implementation

public class DBMigrationInitializaer : IDatabaseInitializer<AppDbContext> {

public void InitializeDatabase(AppDbContext context) {
  bool dbExists;
  var mig = new DbMigrator(new MigrationConfiguration());
  mig.Update();

  Seed(context);
  context.SaveChanges();
}

protected virtual void Seed(AppDbContext context) {
  // TODO: put here your seed creation
}

Exception stack trace

    [SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2072894
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5061932
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +228
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +326
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   System.Data.SqlClient.<>c__DisplayClassa.<DbCreateDatabase>b__7(SqlConnection conn) +38
   System.Data.SqlClient.SqlProviderServices.UsingConnection(SqlConnection sqlConnection, Action`1 act) +98
   System.Data.SqlClient.SqlProviderServices.UsingMasterConnection(SqlConnection sqlConnection, Action`1 act) +349
   System.Data.SqlClient.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection) +315
   System.Data.Objects.ObjectContext.CreateDatabase() +84
   System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) +73
   System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists() +76
   System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +44
   System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update() +12
   MvcApplication1.Models.MyDBInitializaer.InitializeDatabase(AppContext context) in MyDBInitializaer.cs:31
   System.Data.Entity.<>c__DisplayClass2`1.<SetInitializerInternal>b__0(DbContext c) +75
   System.Data.Entity.Internal.<>c__DisplayClass8.<PerformDatabaseInitialization>b__6() +19
   System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +72
   System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +186
   System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c) +7
   System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input) +118
   System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action) +190
   System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() +73
   System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +28
   System.Data.Entity.Internal.Linq.InternalSet`1.Initialize() +56
   System.Data.Entity.Internal.Linq.InternalSet`1.GetEnumerator() +15
   System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator() +40
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +315
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   MvcApplication1.Controllers.EmployeeController.Index() in EmployeeController.cs:21

Thank you, Ido

Answer 1

I have the same problem with my webhost (amen.fr). During SEVERAL days I looked and I realized that there's something in the implementation of the class DBMigrator and sql server configuring at the host which causes this dysfunction. "System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists" unable to check the correct information. That why all transactions with the database are made with my data context. Here is the code that allows me to make semi-automatic migration :

public class Migrator
  {
    public static void RunMigrations()
    {
      //Configuration configuration = new Configuration();

      //configuration.ContextType = typeof(BOContext);//TODO Change to your DbContext
      //configuration.MigrationsAssembly = configuration.ContextType.Assembly;
      //configuration.MigrationsNamespace = "BO.Domain.Migrations";//TODO Namespace that contains your migrations classes
      //configuration.TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString, "System.Data.SqlClient");

      //DbSeederMigrator<BOContext> migrator = new DbSeederMigrator<BOContext>(configuration);
      //migrator.MigrateToLatestVersion();

      using (BOContext context = new BOContext())
      {
        Configuration configuration = new Configuration();

        configuration.ContextType = typeof(BOContext);//TODO Change to your DbContext
        configuration.MigrationsAssembly = configuration.ContextType.Assembly;
        configuration.MigrationsNamespace = "CodeFirstMembershipSharp.Migrations";//TODO Namespace that contains your migrations classes
        configuration.TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString, "System.Data.SqlClient");

        DbMigrator migrator = new DbMigrator(configuration);
        MigratorScriptingDecorator scriptor = new MigratorScriptingDecorator(migrator);

        var lm = migrator.GetLocalMigrations();// get local migration
        var dm = context.Database.SqlQuery<Migration>("select MigrationId from dbo.__MigrationHistory").Select(o => o.MigrationId); // get database migration
        List<string> pm = lm.Except(dm).ToList();// buil and set pending migration

        if (pm.Any())// Peding migration exists
        {
          string source = dm.Any() ? dm.OrderBy(o => o).Last() : DbMigrator.InitialDatabase;// get last to set source migration
          string target = pm.OrderBy(o => o).Last(); /// gest last to set target migration

          string sql = scriptor.ScriptUpdate(source, target); // buit sql script migration

          try { context.Database.ExecuteSqlCommand(sql); } // execute sql script migration
          catch (Exception e)
          {
            string spm = "Pending migrations : " + String.Join(";", pm.ToArray());
            string sdm = "Database migrations : " + String.Join(";", pm.ToArray());
            string[] tmp = { e.Message, spm, sdm, "Source : " + source, "Target : " + target, sql };

            throw new Exception(String.Join("\n-----------------\n", tmp));
          }
        }
      }

      //// TODO : code seed here
      //Migrator.Seed();
    }

    protected static void Seed()
    {
      //using (BOContext context = new BOContext())
      //{
      //  if (!context.Users.Any())
      //  {
      //    MembershipCreateStatus Status;
      //    Membership.CreateUser("Demo", "123456", "[email protected]", null, null, true, out Status);

      //    if (!context.Roles.Any(o => o.RoleName == "Admin"))
      //    {
      //      Roles.CreateRole("Admin");
      //      Roles.AddUserToRole("Demo", "Admin");
      //    }
      //  }
      //}
    }laguna-veneta
  }