Cloud Database Project | Azure SQL & Security

You will deploy a production-ready Azure SQL data tier with private networking, encryption, automated backups, and least-privilege logins. By the end the database has no public endpoint and a tested recovery path.

Learning Objectives

Deploy Azure SQL with a private endpoint (no public access).
Enable encryption (TDE) and automated backups.
Create least-privilege application logins.
Time: ~12 hours · Difficulty: Advanced · Prereqs: a VNet and the Azure CLI.

Architecture Overview

graph LR App[App subnet] -->|Private Endpoint| SQL[(Azure SQL)] SQL -->|TDE| Enc[Encrypted at rest] SQL --> BK[Automated backups] Net((Internet)) -. denied .- SQL

Environment Setup

An Azure VNet with an app subnet.
The Azure CLI authenticated.
A private DNS zone for the SQL private endpoint.

Step-by-Step Execution

Create the server and database

az sql server create -n tyf-sql --admin-user adm --admin-password '****' && az sql db create -s tyf-sql -n appdb --service-objective S0

Add a private endpoint and disable public access

az sql server update -n tyf-sql --set publicNetworkAccess=Disabled

Create a least-privilege login

SQL> CREATE USER app WITH PASSWORD='****'; ALTER ROLE db_datareader ADD MEMBER app;

Commands completed successfully.

Progress So Far

graph LR A[01 Deploy] -->|done| B[02 Private + no public] B -->|done| C[03 Least-priv login] style A fill:#1a4a1a,stroke:#00ff00,color:#fff style B fill:#1a4a1a,stroke:#00ff00,color:#fff style C fill:#1a4a1a,stroke:#00ff00,color:#fff

Testing & Validation

az sql server show -n tyf-sql --query publicNetworkAccess -o tsv

This should return Disabled. Confirm the app subnet can connect via the private endpoint while public connections fail.

Troubleshooting

App cannot connect: the private DNS zone must resolve the SQL FQDN to the private IP.
Still reachable publicly: confirm publicNetworkAccess is Disabled and no firewall allow-all rule exists.
Over-privileged login: grant only the roles the app needs.

Extension Ideas

Codify it with Terraform.
Add auditing and threat detection (Microsoft Defender for SQL).
Test point-in-time restore as in Backup & Recovery.

Key Results

Deployed Azure SQL with zero public network access.
Enabled encryption at rest and automated backups.
Granted the app a read-only least-privilege login.
Verified private-only connectivity end to end.

Cloud Database Project: Secure Azure SQL Data Tier