Implement a Secure Environment
Identity and authorization
Authentication options are SQL logins (server admin or contained users), Microsoft Entra ID (supports MFA and centralized group membership), and Windows auth on VMs and Managed Instance. Prefer contained database users mapped to Entra groups - they travel with the database through geo-failover. Authorization is layered: fixed server roles (dbmanager, loginmanager), fixed database roles (db_owner … db_datareader), and custom roles. Apply least privilege.
Network surface
Defense in depth means server firewall rules, database-level firewall rules, private endpoints (recommended; can disable public access), VNet service endpoints, and Managed Instance's delegated subnet. The "Allow Azure services and resources to access this server" toggle creates a 0.0.0.0 rule that admits resources in any Azure tenant - a classic wrong answer when private connectivity is the goal.
Protecting the data
- Transparent Data Encryption - at rest, including backups; on by default for new databases; service-managed or customer-managed key in Key Vault. No application change.
- Always Encrypted - client-side column encryption; the column master key never reaches the server; deterministic permits equality searches, randomized permits none, enclaves add pattern and range operations.
- Dynamic Data Masking - presentation only; it neither encrypts nor blocks access, and
db_owneror any principal withUNMASKsees plaintext. - Row-level security - filter and block predicates enforced by the engine.
- Defender for SQL - auditing, classification, vulnerability assessment, threat alerts.
Trap
Questions swap TDE and Always Encrypted. TDE protects the file and backups; Always Encrypted protects individual values from the DBA and the host.
Rule of thumb
At rest → TDE. From privileged insiders → Always Encrypted. Cosmetic masking in an app screen → Dynamic Data Masking.