Azure for .NET Developers — Part 3: Azure SQL, EF Core, and the Firewall Gotchas

SQL Server is home turf for most .NET teams, which makes Azure SQL feel deceptively familiar: same T-SQL, same tooling, same EF Core provider. The differences hide in the operational layer — firewalls that block us by default, transient faults that are normal rather than exceptional, and a pricing model where the wrong checkbox costs hundreds a month. In this part we provision a serverless Azure SQL database, wire it to EF Core properly, and walk through the gotchas in the order they will actually hit us.

Picking the right flavor of SQL on Azure

Azure offers three ways to run SQL Server, and the decision matters because migrating between them later is real work:

For a new ASP.NET Core application the answer is almost always Azure SQL Database. Managed Instance exists for migrations that depend on instance-level features (SQL Agent jobs, cross-database queries, Service Broker); a VM is the escape hatch when we need something the managed offerings refuse to do. Everything below assumes Azure SQL Database.

Provisioning serverless with auto-pause

The serverless tier is the budget-friendly star of Azure SQL: compute scales between a minimum and maximum vCore count, is billed per second, and can pause entirely when idle — leaving us paying only for storage (roughly a dollar or two a month for a small dev database).

RG=rg-myapp-prod
SERVER=sql-myapp-prod        # globally unique
DB=sqldb-myapp

az sql server create \
  --name $SERVER --resource-group $RG --location westeurope \
  --admin-user sqladmin --admin-password '<strong-password-here>'

az sql db create \
  --name $DB --server $SERVER --resource-group $RG \
  --edition GeneralPurpose --family Gen5 \
  --compute-model Serverless \
  --min-capacity 0.5 --capacity 2 \
  --auto-pause-delay 60 \
  --backup-storage-redundancy Local

That is a database that scales between 0.5 and 2 vCores and pauses after 60 idle minutes. Two honest caveats. First, resuming from pause takes up to a minute, and connections during resume fail — fine for dev/test and low-traffic internal apps, wrong for anything latency-sensitive (set --auto-pause-delay -1 to disable pausing while keeping per-second billing). Second, the logical "server" here is not a machine; it is a free administrative boundary that holds databases, logins, and firewall rules. The database is what costs money.

The firewall gotcha, before it gets you

Here is the part that generates the support tickets: by default, nothing can connect to that server. Not our laptop, not our App Service — the server-level firewall denies all public traffic until we add rules. The first symptom is error 40615, "Cannot open server ... requested by the login. Client with IP address 'x.x.x.x' is not allowed to access the server."

# Allow our current public IP for local development
MYIP=$(curl -s https://api.ipify.org)
az sql server firewall-rule create \
  --server $SERVER --resource-group $RG \
  --name dev-laptop --start-ip-address $MYIP --end-ip-address $MYIP

# Allow Azure services (App Service outbound) to reach the server
az sql server firewall-rule create \
  --server $SERVER --resource-group $RG \
  --name allow-azure-services \
  --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

The magic 0.0.0.0 rule means "traffic from Azure datacenters," which is how a basic App Service reaches the database. Be aware of what it actually allows: any Azure customer's outbound traffic, not just ours — acceptable for getting started (the connection still needs valid credentials), but production setups should graduate to virtual network integration or private endpoints. Also remember home IPs change; when local connections suddenly fail weeks later with 40615, the firewall rule is stale, not the code.

How do I connect ASP.NET Core with Entity Framework Core to Azure SQL?

Install Microsoft.EntityFrameworkCore.SqlServer, put the Azure SQL connection string in configuration (App Service settings or a Key Vault reference, never in source), and register the context with UseSqlServer, passing EnableRetryOnFailure so EF Core retries the transient faults that are routine in cloud databases. From there, migrations, LINQ, and change tracking work exactly as they do against local SQL Server.

EF Core setup that survives the cloud

Grab the ADO.NET connection string template:

az sql db show-connection-string --server $SERVER --name $DB --client ado.net

It goes in configuration — per part 2, that means an App Service setting or Key Vault reference named ConnectionStrings__Default. Registration in Program.cs:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("Default"),
        sql =>
        {
            sql.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
            sql.CommandTimeout(30);
        }));

EnableRetryOnFailure is the line that separates cloud-ready code from code that merely works in the demo. Azure SQL performs maintenance, failovers, and reconfigurations that briefly kill connections — these are transient faults, they are normal, and the SqlClient error numbers they surface (4060, 40197, 40501, 10928...) are on the strategy's built-in retry list. Without the strategy, each one becomes a user-facing 500; with it, a retried query nobody notices. It matters double with serverless auto-pause, since the first connection to a paused database routinely fails while the database resumes.

One consequence to know: with a retrying execution strategy, user-initiated transactions need to be wrapped in strategy.ExecuteAsync(...) so the whole unit retries together — EF throws a descriptive exception if we forget, so we will not miss it silently.

Migrations: in CI, not at startup

The tempting shortcut is await db.Database.MigrateAsync() in Program.cs. It works — until we scale to two instances and both race to migrate, or a slow migration blows the startup health-check window, or a bad migration takes production down with no human watching. Startup migration couples schema changes to process starts, and those happen at surprising times (slot swaps, platform maintenance, scale-out).

The robust pattern is applying migrations as an explicit CI/CD step, before the new code deploys:

dotnet ef migrations bundle --self-contained -r linux-x64 -o ./efbundle
./efbundle --connection "$SQL_CONNECTION_STRING"

Migration bundles (available since EF Core 6) compile our migrations into a standalone executable — no SDK on the runner, no dotnet ef at deploy time. In the GitHub Actions workflow from part 2, the bundle step slots in between azure/login and webapps-deploy; the CI identity's IP can be allowed via a temporary firewall rule or the runner can use the Azure-services rule. Startup migration remains defensible for single-instance internal tools; for everything else, migrate deliberately. The full workflow is in the 03-azuresql folder of the companion repo.

Dropping passwords entirely with Entra authentication

Everything above used SQL authentication because it is the shortest path, but Azure SQL supports Microsoft Entra authentication, and it composes with the managed identity we gave our App Service in part 2. Enable an Entra admin on the server (az sql server ad-admin create), create a contained user for the app's identity (CREATE USER [app-myapp-prod] FROM EXTERNAL PROVIDER; plus role grants), and change one connection-string clause:

Server=tcp:sql-myapp-prod.database.windows.net,1433;Database=sqldb-myapp;Authentication=Active Directory Default;Encrypt=True;

Authentication=Active Directory Default tells Microsoft.Data.SqlClient to use the same credential chain as DefaultAzureCredential — managed identity in Azure, az login locally. No password exists, so no password can leak or expire at 2 a.m. We will lean on this pattern more later in the series; for now, know that SQL auth is the on-ramp, not the destination.

What's next

We now have the classic three-tier setup running entirely on Azure: an ASP.NET Core app on App Service talking to Azure SQL through EF Core, with retries, sane migrations, and a path off passwords. In part 4 we add Azure Blob Storage for files and static assets — uploading from ASP.NET Core, SAS tokens versus managed identity access, and why we should stop storing uploads on the web server's disk.