Authentication & Authorization
For a quick comparison of TDE, Always Encrypted, RLS, DDM, and Auditing, see the Security Controls Comparison.
The Security Architecture
Authentication Methods
SQL Authentication
Traditional username/password stored in the database.
- Server level:
CREATE LOGIN→ creates a login on the logical server - Database level:
CREATE USER FOR LOGIN→ maps the login to a database user - Weakness: passwords stored in the DB, no MFA, no central revocation
Microsoft Entra ID (Recommended)
Centralized identity with MFA, conditional access, and no password management.
| Feature | SQL Auth | Entra ID |
|---|---|---|
| MFA | ❌ | ✅ |
| Conditional Access | ❌ | ✅ (require compliant device, location-based) |
| Central revocation | ❌ (must alter each server) | ✅ (disable in Entra → blocked everywhere) |
| Group-based access | ❌ | ✅ (add users to Entra groups) |
| Managed Identity | ❌ | ✅ (passwordless app auth) |
Microsoft Entra authentication is the recommended method. For the exam, know that you must configure an Entra admin on the SQL Server before creating Entra users. Only ONE Entra admin can be set per logical server, but it can be a group (best practice: always use a group).
Managed Identity (App-to-Database)
The best pattern for application connections — no passwords, no secrets, no rotation:
Oracle DBA parallel: This is like Oracle External Authentication + Kerberos, but simpler. The app gets a token from Entra ID automatically — no keytab, no wallet, no password rotation ever. Use this for ALL app-to-DB connections in Azure.
Authorization — Role Hierarchy
Fixed Database Roles — Quick Reference
| Role | Can SELECT | Can INSERT/UPDATE/DELETE | Can DDL | Can Manage Security |
|---|---|---|---|---|
db_owner | ✅ | ✅ | ✅ | ✅ |
db_datareader | ✅ | ❌ | ❌ | ❌ |
db_datawriter | ❌ | ✅ | ❌ | ❌ |
db_ddladmin | ❌ | ❌ | ✅ | ❌ |
db_securityadmin | ❌ | ❌ | ❌ | ✅ |
Common exam trap: db_datareader does NOT grant INSERT/UPDATE/DELETE. db_datawriter does NOT grant SELECT. To read AND write, a user needs BOTH roles.
Row-Level Security (RLS)
Controls which rows a user can see. Perfect for multi-tenant applications.
How it works: A filter predicate function checks SESSION_CONTEXT (set by the app) against the row's tenant column. Azure applies this transparently — queries return only matching rows.
Oracle DBA parallel: RLS = Oracle VPD (Virtual Private Database). VPD uses policy functions on tables. RLS uses security predicate functions + security policies. Same concept, different syntax. If you've built multi-tenant Oracle apps with VPD, RLS will feel natural.
Dynamic Data Masking
Masks sensitive data in query results without changing stored data:
| Mask Function | What It Does | Example |
|---|---|---|
default() | Full mask | XXXX |
email() | Masks email | aXX@XXXX.com |
partial(prefix, mask, suffix) | Custom mask | XXXX-XXXX-XXXX-1234 |
random(start, end) | Random number | 42 (within range) |
Dynamic Data Masking does NOT encrypt data at rest — it only masks the display. Users with db_owner or UNMASK permission see unmasked data. It's a presentation-layer security feature, not encryption. For true encryption → use Always Encrypted.
Security Feature Comparison (Exam Favorite!)
| Feature | Protects | From Whom | Data Changed? |
|---|---|---|---|
| TDE | Data at rest (files, backups) | Physical disk theft | No (transparent) |
| Always Encrypted | Specific columns (in use) | Even DBAs | No (client-side encrypt/decrypt) |
| Dynamic Data Masking | Query output display | Non-privileged users | No (display only) |
| Row-Level Security | Row access | Wrong tenants/users | No (filter predicate) |
| TLS | Data in transit | Network sniffing | No (transport encryption) |