Running EF Core migrations with a separate SQL Server identity

Entity Framework (EF) Core migrations are useful, but the application identity does not always need permission to change the database schema.

In many production environments, the normal application connection string should only allow the application to read and write data. Schema changes should be done by a separate deployment or maintenance identity. This avoids giving the web application more SQL Server permissions than it needs during normal runtime.

One practical approach is to keep two connection strings:

• one for normal application use
• one for applying database migrations.

For example, your appsettings.json could contain:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=MyApp;Integrated Security=True;TrustServerCertificate=True;",
    "MigrationConnection": "Server=.;Database=MyApp;Integrated Security=True;TrustServerCertificate=True;"
  }
}

In a real environment, these would usually point to the same database but use different Windows identities or different SQL credentials. The normal application identity might have permissions to select, insert, update, delete, and execute stored procedures. The migration identity can additionally change the schema.

In Program.cs, check for a command-line switch:

bool runMigrations = args.Contains("--migrate-database");
string connectionStringName = runMigrations ?           "MigrationConnection" : "DefaultConnection";

Then use the selected connection string when registering the DbContext:

builder.Services.AddDbContext<AppDbContext>(options =>
{
    string connectionString =
        builder.Configuration.GetConnectionString(connectionStringName)
        ?? throw new InvalidOperationException(
            $"Connection string '{connectionStringName}' was not found.");

    options.UseSqlServer(connectionString);
});

After building the application, run migrations only when the switch is present:

WebApplication app = builder.Build();

if (runMigrations)
{
    using IServiceScope scope = app.Services.CreateScope();
    AppDbContext db =
        scope.ServiceProvider.GetRequiredService<AppDbContext>();

    await db.Database.MigrateAsync();
    return;
}

Then continue with the normal application startup:

app.MapControllers();
...
app.Run();

Now migrations can be applied explicitly:

MyApp.exe --migrate-database

Or when running with dotnet:

dotnet MyApp.dll --migrate-database

The important detail is that the application exits after applying migrations. It does not continue running as the normal web application in migration mode.

Tip: do not casually store a privileged SQL username and password in appsettings.json. For real deployments, use a secure configuration source such as environment variables, pipeline secrets, Azure Key Vault, or Windows authentication with a deployment account.

You can also override only the migration connection string during deployment:

set ConnectionStrings__MigrationConnection=Server=sql01;Database=MyApp;Integrated Security=True;TrustServerCertificate=True;
dotnet MyApp.dll --migrate-database

This keeps the privileged connection string out of the checked-in settings file. Here are some good rules of thumb I've found useful:

• use a low-privilege connection string for normal application runtime
• use a separate identity for schema changes
• run migrations only through an explicit deployment or maintenance step
• avoid running automatic migrations from every application instance
• keep privileged connection strings out of source control.

For environments where DBAs review changes, generate a SQL script:

dotnet ef migrations script --idempotent

For deployment pipelines, EF Core migration bundles can also be useful:

dotnet ef migrations bundle

The best option depends on the team and environment. The main point is the same: applying schema changes is a deployment action, not something the normal runtime identity should always be able to do.

Tip 2: if your application runs on multiple servers or multiple container instances, be very careful with startup-time migrations. Two instances trying to migrate the same database at the same time is not a deployment strategy.

Keeping read/write access and schema-change access separate is a small security improvement, but it also makes deployments more intentional.

Hope this helps!