How to provision an EF database using custom provi

Question

We are using EF 6 Code First Migrations in a relatively new project (i.e. there isn't a lot of clutter to contend with). Also, as this is an "enterprise-y" application, we have some specific deployment rules for our target database:

• all application level data access must be done through a specific DB user (app-user)
• this app-user does not have permissions to create new databases

Therefore, in order to correctly provision a new target database for this application, we need to:

• CREATE DATABASE [database_name] CONTAINMENT = PARTIAL
• CREATE USER [app-user] WITH PASSWORD=N'p@ssw0rd'
• (plus assign specific DB roles to this new user)

I was hoping to carry this out by writing a custom IDatabaseInitializer<TContext>, but it seems that I cannot hook into the database initialization at the correct point.

Conceptually, I want to do this:

• have one connection string that is used for read/write access to the DB, using the "controller" app-user user
• have a separate connection string that is solely used for provisioning the DB, using more privileged credentials

The code I have tried to use looks a bit like this:

internal class ProvisionThenMigrateInitializer<TContext, TConfiguration>
    : MigrateDatabaseToLatestVersion<TContext, TConfiguration>, IDatabaseInitializer<TContext>
    where TContext : DbContext
    where TConfiguration : DbMigrationsConfiguration<TContext>, new()
{
    private readonly DbMigrationsConfiguration _readWriteConfiguration;
    private readonly string _provisioningConnectionName;

    public ProvisionThenMigrateInitializer(string readWriteConnectionName, string provisioningConnectionName)
    {
        _provisioningConnectionName = provisioningConnectionName; 

        _readWriteConfiguration = new TConfiguration
        {
            TargetDatabase = new DbConnectionInfo(readWriteConnectionName)
        };
    }

    void IDatabaseInitializer<TContext>.InitializeDatabase(TContext context)
    {
        if (context.Database.Exists())
        {
            if (!context.Database.CompatibleWithModel(false))
            {
                DbMigrator migrator = new DbMigrator(_readWriteConfiguration);
                migrator.Update();
            }
        }
        else
        {
            // TODO - Create the DB and user here...

            string[] sqlStatements =
            {
                "CREATE DATABASE [database_name] CONTAINMENT = PARTIAL ",
                "USE [database_name]",
                "CREATE USER [app_user] WITH PASSWORD=N'p@ssw0rd'",
                "USE [database_name]",
                "ALTER ROLE [db_datareader] ADD MEMBER [app_user]",
                "ALTER ROLE [db_datawriter] ADD MEMBER [app_user]",
            };

            string connectionString = ConfigurationManager.ConnectionStrings[_provisioningConnectionName].ConnectionString;

            SqlConnection sqlConnection = new SqlConnection(connectionString);

            foreach (SqlCommand command in sqlStatements.Select(sqlStatement => new SqlCommand(sqlStatement, sqlConnection)))
            {
                command.ExecuteNonQuery();
            }

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

I set the initializer to use in the static constructor of my DbContext derived class:

Database.SetInitializer(new ProvisionThenMigrateInitializer<Context, Configuration>(
    DOMAIN_MODEL_CONNECTION_STRING_NAME,
    DOMAIN_MODEL_PROVISIONING_CONNECTION_STRING_NAME));

However, when I attempt to use my fancy new custom database initializer, in the following manner, it just plain doesn't work:

using (Context c = new Context())
{
    try
    {
        c.Database.Initialize(true);
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }
}

I think that by the time I attempt to call c.Database.Initialize(true) EF has already attempted to connect to the database (using the app_user credentials, not the "provisioning credentials), the connection attempt fails, and we bomb out.

Is it actually possible to use EF 6, Code First and Migrations in a way that will allow this provisioning of my database? If so, what am I doing wrong?

Many thanks.

Answer 1

You should be able to do what you want. Key to the matter is making sure context is accessed / renewed with right connection details.

Call the Migrate Method when it suits you in your code.

Change MigrateDatabaseToLatestVersion to match you migration strategy.

EDIT: I will try and summarise the idea and show a snippet sample.

Essentially I use a LUW class that defaults to DONT TOUCH DB. The Luw needs the DBServer and DBName in constructor I have a tool that gets the DBConnection for SQL Server

From an Admin ui I have a button. Migrate. I can then trigger automatic migration when it suits. I currently use Automatic. But this concept woudl apply fine to managed migrations.

public class Luw{ public Luw(string dataSource, string dbName ){ // constructor Context = GetContext(dataSource, dbName ); }

public override void MigrateDb() { 
      // i put this method in my UoW class, I trigger Migrations when I want them to start.
    Database.SetInitializer(new MigrateDatabaseToLatestVersion<MYDbContext, MYSECIALMigrationConfiguration>());
   // Context = GetDefaultContext(); //HERE GET THE CONTEXT WITH CORRECT CONNECTION INFO 
    Context.Database.Initialize(true);
 }


  public static MyDbContext GetContext(string dataSource, string dbName)
   {
       Database.SetInitializer(new ContextInitializerNone<MyDbContext>());
       return new MyDbContext((MYTOOLS.GetSQLConn4DBName(dataSource,dbName )),true);
   }

 public class MYSPECIALMigrationConfiguration : MYBaseMigrationConfiguration<MYDbContext>{  }


 public abstract class MYBaseMigrationConfiguration<TContext> : DbMigrationsConfiguration<TContext> 
where TContext  : DbContext{

protected  MYBaseMigrationConfiguration() {
    AutomaticMigrationsEnabled = true;  // you can still chnage this later if you do so before triggering Update
    AutomaticMigrationDataLossAllowed = true; // you can still chnage this later if you do so before triggering Update

}

public clas SQLTOOLS{
//    ..... for SQL server....
public DbConnection GetSqlConn4DbName(string dataSource, string dbName) {
        var sqlConnStringBuilder = new SqlConnectionStringBuilder();
        sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource;
        sqlConnStringBuilder.IntegratedSecurity = true;
        sqlConnStringBuilder.MultipleActiveResultSets = true;

        var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString);
        var sqlConn = sqlConnFact.CreateConnection(dbName);
        return sqlConn;
    }        

Answer 2

Here's how I did it:

I have an 'admin' SQL login that is a member of the 'dbcreator' and 'securityadmin' fixed server roles.

I have two connection strings: one specifying the 'admin' sql login, and the other specifying the sql login name I have reserved for use by tenant connections to the db. The 'tenant' login gets created via the initial migration and is granted only reader and writer access to the domain model database.

I have a domain model. I have my DbContext class. I have a parameterless constructor on my DbContext class which specifies the ADMIN connection string and is intended to be used to run migrations; and I have another constructor which specifies the TENANT connection string and is the constructor used through the code for all db access made in the context of a logged on tenant user. public Context() : base(ADMIN_CONNECTION_STRING_NAME) { //etc and public Context(int tenantOrgId) : base(TENANT_CONNECTION_STRING_NAME) {

Prior to enabling migrations, I used the DbContext in a unit test, which resulted in EF Code First creating the DB catalog. I have enabled migrations which has produced an inital DbMigration.

I then edited the initial DbMigration "Up" method to provision the tenant sql login and grant it membership to the reader and writer roles:

    public override void Up()
    {
        SqlConnectionStringBuilder domainModelConnectionStringBuilder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings[Context.TENANT_CONNECTION_STRING_NAME].ConnectionString);
        string domainModelDatabaseName = domainModelConnectionStringBuilder.InitialCatalog;

        Sql(string.Format("IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'gsp_domainmodel_tenant') CREATE LOGIN [gsp_domainmodel_tenant] WITH PASSWORD=N'ge0sp@tia!', DEFAULT_DATABASE=[{0}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF", domainModelDatabaseName));
        Sql(string.Format("USE [{0}]", domainModelDatabaseName));
        Sql(string.Format("IF NOT EXISTS (SELECT * FROM [{0}].sys.database_principals WHERE name = 'gsp_domainmodel_tenant') CREATE USER [gsp_domainmodel_tenant] FOR LOGIN [gsp_domainmodel_tenant] WITH DEFAULT_SCHEMA=[gsp]", domainModelDatabaseName));
        Sql(string.Format("USE [{0}]", domainModelDatabaseName));
        Sql(string.Format("ALTER ROLE [db_datareader] ADD MEMBER [gsp_domainmodel_tenant]", domainModelDatabaseName));
        Sql(string.Format("USE [{0}]", domainModelDatabaseName));
        Sql(string.Format("ALTER ROLE [db_datawriter] ADD MEMBER [gsp_domainmodel_tenant]", domainModelDatabaseName));

        CreateTable(  //etc

That is all you need to do if you are happy with the team using Update-Database to apply migrations to their local DBs, and you are happy with exec'ing Migrate.exe on the command line to deploy the db on your build machine, and you are happy with deploying the db changes to production using your own wits.

You can go one step further and specify the MigrateDatabaseToLatestVersion initializer to automate deploying the migrations, both on local dev workstations and on environments that you deploy to. The trick is that you need to make the MigrateDatabaseToLatestVersion initializer run using the parameterless DbContext constructor so that the migrations are applied in the context of the ADMIN sql login (not the TENANT). That is achieved thusly: static Context() { Database.SetInitializer(new MigrateDatabaseToLatestVersion());

        // Make the initializer run now, with the parameterless constructor, such that the migrations are run using the admin connection string.
        using(var initializerCtx = new Context())
        {
            initializerCtx.Database.Initialize(true);
        }
    }