Connecting an ASP.NET Core Application to Azure SQL with Entity Framework Core

Moving an ASP.NET Core application to Azure often means moving its database too. Azure SQL Database works well with Entity Framework Core, but the production setup involves more than changing a connection string. Authentication, permissions, retries, schema changes, and connection usage all affect how reliably the application runs.

Use managed identity instead of a stored password

For an application hosted in Azure App Service, a managed identity lets the app authenticate to Azure SQL without keeping a database password in configuration. Enable a system-assigned or user-assigned identity for the app, then configure an administrator for Microsoft Entra authentication on the Azure SQL server.

After the identity has been granted access to the database, configure the application’s connection string to use managed identity authentication. For a system-assigned identity, it can look like this:

Server=tcp:myserver.database.windows.net,1433;Database=OrdersDb;Authentication=Active Directory Managed Identity;Encrypt=True;TrustServerCertificate=False;

Store the value in the App Service connection-string settings, not in source control. ASP.NET Core can read it using the key ConnectionStrings__Orders. If you use a user-assigned identity, the connection string also needs that identity’s client ID.

Grant only the database permissions the app needs

In the target database, create a database user for the app’s managed identity. The following is a simple starting point for an application that reads and writes data:

-- Create a contained database user for the App Service managed identity.
CREATE USER [my-orders-app] FROM EXTERNAL PROVIDER;

-- These built-in roles are convenient, but broader than some production apps need.
ALTER ROLE db_datareader ADD MEMBER [my-orders-app];
ALTER ROLE db_datawriter ADD MEMBER [my-orders-app];

For tighter control, define database roles that grant access only to the required tables or stored procedures. A working connection does not mean the identity should automatically receive broad permissions, especially if the application has administrative or reporting functions.

Configure EF Core for Azure SQL

Install the SQL Server EF Core provider that matches the major version of your application’s EF Core packages. Then register the context and enable retries for transient database faults:

var connectionString = builder.Configuration.GetConnectionString("Orders")
    ?? throw new InvalidOperationException("The Orders connection string is missing.");

builder.Services.AddDbContextPool<OrdersDbContext>(options =>
{
    // Retry a small number of times when Azure SQL reports a transient fault.
    options.UseSqlServer(connectionString, sql =>
        sql.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(10),
            errorNumbersToAdd: null));
});

AddDbContextPool can reduce the overhead of creating contexts in a busy service. Use it only when your context does not retain request-specific state in fields or other mutable properties: pooled contexts are reused. If that restriction does not fit your application, use AddDbContext instead.

Transient fault retries help with short-lived connectivity problems, but they do not make every operation safe to repeat. If an operation writes data and then loses its connection before receiving the response, the client may not know whether the write succeeded. Design important operations to be idempotent, or use a stable operation identifier so a repeated request cannot create duplicate work.

Think about connections and transactions under load

EF Core and Microsoft.Data.SqlClient use connection pooling, so an application normally opens a logical connection for a database operation and returns it to the pool when finished. Avoid holding a context or an open transaction while waiting on unrelated work, such as a slow external HTTP request. Under load, long-held connections can exhaust the available pool and make otherwise quick queries wait.

Retries also affect transactions. If you create an explicit transaction while retry-on-failure is enabled, the transaction needs to run within EF Core’s execution strategy so the whole unit of work can be retried consistently. Do not retry only the last query in a multi-step transaction.

Keep database schema changes out of app startup

It may be tempting to call Database.Migrate() every time the web app starts. That gets risky when several instances start together, when the app’s database permissions are intentionally limited, or when a migration takes longer than the deployment window.

A safer approach is to review migrations and apply them as a separate deployment step, using a migration bundle or a controlled pipeline task. That keeps schema changes visible and lets the deployment identity have migration permissions without granting those permissions to the running web app.

Make database behavior observable

When a request is slow, check both the application and the database. Application Insights or Azure Monitor can help correlate request durations with dependency calls, while Azure SQL metrics and Query Store can show resource pressure and expensive query plans. Log enough context to identify the operation, but do not log credentials, access tokens, or sensitive parameter values.

A good first deployment check is to verify that the app can authenticate with its managed identity, execute a small query, and report database failures clearly. Once that path is sound, load-test the actual queries and watch connection use, retry counts, and database resource consumption before increasing the app’s instance count.