Now Let’s Talk Databases - Azure SQL and Our Web API

Following Up on Our Azure Deployment Journey

Introduction#

In the previous post, we deployed a .NET Web API inside a Docker container and hosted it on Azure Web Apps.

That was step one—getting the app running.

Now, it’s time for step two—connecting to a database so we can persist data. This means:

Creating an Azure SQL Database
Configuring firewall rules so our Web App and local machine can talk to it
Setting up the database schema with EF Core migrations
Seeding the database with some initial data
Verifying the connection & exposing API endpoints

If you just want the step-by-step commands to set up Azure SQL and connect it to your API, jump to the bottom for the TL;DR Deployment Guide.

Why This POC?#

If you’ve been following along, you know I’m using A Cloud Guru’s Azure sandbox, which means every 4 hours my entire setup gets wiped.

That means every time I start a new session, I have to rebuild everything from scratch. At first, this was annoying. But now? It’s actually a good thing—because it forces me to truly understand the process instead of getting lucky once.

But one thing that kept breaking in my redeployments was the database connection. And every time, I had to debug the same issues over and over. This post exists so future-me (and you) don’t have to go through that again.

Step 1: Setting Up the Azure SQL Database#

First, we need to create the database and allow connections.

Create the Azure SQL Server & Database#

az sql server create --name $SQL_SERVER_NAME \
--resource-group $RESOURCE_GROUP \
--location $LOCATION \
--admin-user sqladmin \
--admin-password "YourStrongls #Password"

az sql db create \
--resource-group $RESOURCE_GROUP \
--server $SQL_SERVER_NAME \
--name $SQL_DATABASE_NAME \
--service-objective Basic

Add Firewall Rules#

By default, Azure SQL blocks all incoming connections. We need to:

Allow Azure Web Apps to access the database
Allow our local machine to connect (so we can debug with Azure Data Studio)

# Allow Azure services to connect
az sql server firewall-rule create \
    --resource-group $RESOURCE_GROUP \
    --server $SQL_SERVER_NAME \
    --name AllowAzureServices \
    --start-ip-address 0.0.0.0 \
    --end-ip-address 0.0.0.0

# Allow our personal IP
MY_IP=$(curl -s ifconfig.me)
az sql server firewall-rule create \
    --resource-group $RESOURCE_GROUP \
    --server $SQL_SERVER_NAME \
    --name MyPersonalIP \
    --start-ip-address $MY_IP \
    --end-ip-address $MY_IP

Step 2: Connecting the Web App to the Database#

Now that the database is ready, we need to store the connection string in our Web App’s settings.

az webapp config appsettings set --name $APP_NAME \
--resource-group $RESOURCE_GROUP \
--settings ConnectionStrings__DefaultConnection="Server=tcp:$SQL_SERVER_NAME.database.windows.net,1433;Initial Catalog=$SQL_DATABASE_NAME;Persist Security Info=False;User ID=sqladmin;Password=YourStrongls #Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Step 3: Running the Database Migration#

Next, we run EF Core migrations to create the necessary tables.

dotnet ef database update --connection "Server=tcp:$SQL_SERVER_NAME.database.windows.net,1433;Initial Catalog=$SQL_DATABASE_NAME;Persist Security Info=False;User ID=sqladmin;Password=YourStrongls #Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Step 4: Seeding the Database#

We’ll seed the database inside Program.cs.

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

    if (!db.Users.Any())
    {
        db.Users.Add(new User { Name = "John Doe" });
        db.SaveChanges();
    }
    
    if (!db.Products.Any())
    {
        db.Products.Add(new Product { Name = "The 100", Price = 100 });
        db.SaveChanges();
    }
}

Step 5: Adding API Endpoints#

Now that the database is seeded, let’s expose some endpoints to read data.

// Get All Users
app.MapGet("/api/users", async ([FromServices] AppDbContext db) =>
{
    var users = await db.Users.ToListAsync();
    return Results.Ok(users);
});

// Get All Products
app.MapGet("/api/products", async ([FromServices] AppDbContext db) =>
{
    var products = await db.Products.ToListAsync();
    return Results.Ok(products);
});

Debugging Issues – The Real Struggles & Fixes#

Here’s a breakdown of real-world debugging issues I faced and how I fixed them.

Problem: Web App Can’t Connect to Database
Fix:

Verify database connectivity first via Azure Data Studio
Double-check firewall rules are set
Store the connection string in Web App settings

Problem: EF Migrations Failed (Wrong Password or Firewall Issue)
Fix:

Confirm DB exists with:
```
SELECT name FROM sys.databases;
```
Run EF migrations with correct credentials

Problem: Web App Runs, But No Data Shows Up
Fix:

Manually check if DB is seeded
Restart the Web App
Check logs for missing connection string errors

Repo Structure – Organizing for Future POCs#

The AZ204-Learning repo is where I’m storing all my Azure POCs.

AZ204-Learning/
│── MyWebApiApp/        # .NET Web API deployed on Azure with ACR
│── SomeOtherProject/   # Event Grid, Functions, etc.
│── README.md           # Documentation & future project list

TL;DR Deployment Guide#

1️⃣ Create SQL Server & Database#

az sql server create --name $SQL_SERVER_NAME \
--resource-group $RESOURCE_GROUP \
--location $LOCATION \
--admin-user sqladmin \
--admin-password "YourStrongls #Password"
az sql db create --resource-group $RESOURCE_GROUP \
--server $SQL_SERVER_NAME \
--name $SQL_DATABASE_NAME \
--service-objective Basic

2️⃣ Add Firewall Rules#

az sql server firewall-rule create --resource-group $RESOURCE_GROUP \
--server $SQL_SERVER_NAME --name AllowAzureServices \
--start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
MY_IP=$(curl -s ifconfig.me)
az sql server firewall-rule create --resource-group $RESOURCE_GROUP \
--server $SQL_SERVER_NAME --name MyPersonalIP \
--start-ip-address $MY_IP --end-ip-address $MY_IP

3️⃣ Run EF Migrations#

dotnet ef database update --connection "Server=tcp:$SQL_SERVER_NAME.database.windows.net,1433;Initial Catalog=$SQL_DATABASE_NAME;Persist Security Info=False;User ID=sqladmin;Password=YourStrongls #Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

4️⃣ Restart Web App & Test API#

az webapp restart --name $APP_NAME --resource-group $RESOURCE_GROUP
curl -X GET https://$APP_NAME.azurewebsites.net/api/users

Final Thoughts#

The database setup wasn’t hard, but the small details made debugging a nightmare.

If future-me is reading this:

Follow the Debugging Checklist
Always set the firewall rules
Double-check the connection string

Next up: Securing secrets with Azure Key Vault & automating deployments! 🚀