Azure SQL Database Deployment with the az CLI
I deployed a managed Azure SQL Database via the az CLI with server-level firewall rules and least-privilege contained users. Scripting the provisioning made a secured PaaS database repeatable and reviewable instead of click-driven.
Objective & Context
Azure SQL is a managed PaaS database; security depends on firewall scoping and login least privilege, not host hardening. This lab provisions a logical server and database, restricts network access, and creates non-admin application users, aligning to CIS Azure data recommendations.
Learning Objectives
- Provision a logical server and database with the az CLI.
- Restrict network access with server firewall rules.
- Create contained, least-privilege database users.
- Choose a service tier appropriate to the workload.
- Script provisioning so it is repeatable and reviewable.
Environment & Prerequisites
Prerequisites
- An Azure subscription with the
azCLI authenticated (az login) - A resource group and a naming convention decided upfront
- Your client public IP for the firewall allow rule
- Assumed skill: basic CLI usage and SQL GRANT concepts
Step-by-Step Execution
1. Create the logical server and database
az sql server create -g rg -l canadacentral -n tyf-sql --admin-user adm --admin-password '****' && az sql db create -g rg -s tyf-sql -n appdb --service-objective S0Expected output:
Database appdb provisioned (S0).What this teaches: Scripted provisioning makes the database repeatable and reviewable; the S0 tier is a sane starting point you scale after measuring. --location is a required parameter; canadacentral keeps the data in Canada for residency requirements. The command also fails without an authenticated CLI, so run az login first on a fresh machine.
2. Restrict network access
az sql server firewall-rule create -g rg -s tyf-sql -n client --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10Expected output:
(firewall rule created)What this teaches: A single-IP rule is the minimal viable network posture; 'allow Azure services' and 0.0.0.0/0 are conveniences with real attack surface.
3. Create a least-privilege user
CREATE USER app WITH PASSWORD='****'; ALTER ROLE db_datareader ADD MEMBER app;Command(s) completed successfully.Expected output:
(user app created and added to db_datareader)What this teaches: Contained users live in the database, not the server, so credentials move with the database and the app gets read-only by default. Run this T-SQL connected to appdb (for example sqlcmd -S tyf-sql.database.windows.net -d appdb), not to master; a contained user created in master does not exist in appdb.
4. Test connectivity as the app user
sqlcmd -S tyf-sql.database.windows.net,1433 -d appdb -U app -Q "SELECT TOP (1) name FROM sys.tables;"Expected output:
name
----
orders
(1 row affected)What this teaches: Azure SQL listens on TCP 1433, and the comma syntax passes the port to sqlcmd. sqlcmd prompts for the password interactively, so the secret never appears in shell history. A successful query from the allowed IP proves the firewall rule and the contained login both work; run the same command from a non-allowed IP to watch it time out.
How It Works
How It Works
Azure SQL Database is PaaS: Microsoft runs the engine, patching, and HA, while you manage schema, users, and firewall rules. The logical server is an administrative container; the database is the billable, tunable unit inside it.
Network security is layered: server firewall rules filter by IP before authentication is even attempted. Deny-by-default with explicit allow rules shrinks the reachable surface to exactly your clients, and private endpoints remove public reachability entirely.
Contained database users authenticate at the database level rather than the server level. That decouples credentials from the logical server, so the database (and its users) can move between servers without recreating logins, and each app gets only the roles it needs.
+ db create"] --> FW["firewall rule
single client IP"] FW --> U["contained user
db_datareader"] U --> APP["app connects
least privilege"]
Validation & Testing
Connect from the allowed IP with the app login and confirm read access works while writes are denied; connect from a non-allowed IP and confirm it is blocked. Pass criteria: firewall enforces source IP and the app user holds only granted roles.
Advanced: Troubleshooting
- Cannot connect: add the client IP firewall rule and confirm "Allow Azure services" only if intended.
- Over-privileged login: avoid using the server admin for apps; create scoped users.
- Throttling: the service objective (DTU/vCore) may be undersized for the workload.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| connection timeout from the client | the client IP is not in any firewall rule | add the current public IP; verify with az sql server firewall-rule list |
| login failed for user | contained user created in the wrong database or password mismatch | connect to the target database (not master) and recreate the user carefully |
| az sql commands not found | the sql extension or an old CLI version | run az upgrade and ensure the sql command group is available |
| S0 throttles under load | DTU limit hit during a burst | check sys.dm_db_resource_stats, then scale to S1/S2 or vCore based on the bottleneck |
| 'allow Azure services' questioned in review | it opens the server to all of Azure, not just your resources | replace with private endpoint or explicit IPs and document the exception |
Key Results
- Provisioned a secured Azure SQL database from a repeatable CLI script.
- Restricted network access to a single explicit source IP.
- Granted the app login read-only, removing admin over-privilege.
- Made the deployment reviewable and reproducible versus portal clicks.
Key Takeaways
- Script provisioning with the az CLI: repeatable beats click-ops.
- Firewall rules default-deny; allow only known client IPs.
- Contained users decouple credentials from the logical server.
- Grant least privilege: db_datareader first, expand deliberately.
- Pick the tier from measured resource stats, not guesses.
Practice Challenges
Challenge 1: Private endpoint
Goal: Replace the public firewall rule with a private endpoint in your VNet and prove public connectivity fails.
Hint: Create the private endpoint, approve it, then test connection from outside the VNet.
Challenge 2: Failover drill
Goal: Configure an auto-failover group and simulate a failover, measuring the reconnection behaviour.
Hint: Use a secondary in another region; watch the app reconnect to the new primary.