Authentication & Authorization

For a quick comparison of TDE, Always Encrypted, RLS, DDM, and Auditing, see the Security Controls Comparison.

The Security Architecture

Security Architecture

🔑

SQL Authentication

Username/password stored in DB. No MFA, no central revocation. Legacy method.

🛡️

Microsoft Entra ID

Centralized identity with MFA, conditional access, group-based access. Recommended.

🤖

Managed Identity

Passwordless app-to-DB auth. No secrets, no rotation. Best for all app connections.

👥

Database Roles

db_datareader, db_datawriter, db_owner — control what authenticated users can do.

🔒

Row-Level Security

Filter predicates control which rows each user sees. Multi-tenant isolation.

🎭

Dynamic Data Masking

Masks sensitive data in query results without changing stored data.

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)

🎯 Exam Focus

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).

Entra ID setup — enable order

Enable Entra auth on Azure SQL

1

Assign server identity

System-assigned MI on the SQL logical server

Required for Entra group membership reads

2

Grant Directory Readers

Server identity needs Directory Readers role

Otherwise group lookups fail

3

Set Entra admin

Portal: SQL Server → Entra ID → Set admin

Use a group, not a single user

4

Create user FROM EXTERNAL PROVIDER

Connect AS the Entra admin

CREATE USER [...] FROM EXTERNAL PROVIDER

5

Grant role / perms

ALTER ROLE db_datareader ADD MEMBER ...

Least privilege — not db_owner

Common ordering trap

CREATE USER FROM EXTERNAL PROVIDER must be run by an account that itself authenticates with Entra (the Entra admin or another Entra user). A SQL-auth login — even sa equivalent — will fail with Principal not found.

Managed Identity (App-to-Database)

The best pattern for application connections — no passwords, no secrets, no rotation:

Managed Identity Flow

📱

App Service / Function

App has system-assigned managed identity

No credentials stored in code or config

🛡️

Microsoft Entra ID

App requests access token

Entra validates identity automatically

🏷️

Access Token

Entra issues short-lived JWT token

Token scoped to Azure SQL resource

🗄️

Azure SQL Database

Token presented in connection

SQL validates token, grants access per DB user

🏢 Real-World DBA Note

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

Server & Database Role Hierarchy

🏢

Server-Level Roles

sysadmin, securityadmin, serveradmin, dbcreator — control server-wide operations and logins

🗄️

Database-Level Roles

db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin — per-database permissions

Granting access — the canonical 4-step sequence

Most "the user can't see / write to the table" exam questions test this exact order:

Login → User → Role → Object permission

1

Server principal

CREATE LOGIN (master DB) — SQL or Entra

Or skip for contained DB users

2

Database principal

CREATE USER FOR LOGIN (mapped) or

CREATE USER FROM EXTERNAL PROVIDER (Entra contained)

3

Role membership

ALTER ROLE db_datareader ADD MEMBER user

Or custom role: CREATE ROLE then GRANT

4

Object permission (optional)

GRANT SELECT ON schema::Sales TO role

Use schema-level grants over per-object

Common ordering trap

A login alone cannot query a database — it needs a user mapped in that DB. A user without a role gets permission denied. The exam loves to give you 3 steps and ask which one is missing.

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	❌	❌	❌	✅

⚠️ Watch Out

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.

Row-Level Security — Per-Tenant Filtering

📱

App Sets Context

App calls SESSION_CONTEXT with TenantId before queries

🔍

Filter Predicate

Security policy function checks SESSION_CONTEXT vs row TenantId column

✅

Filtered Results

Query returns only rows matching the tenant — transparent to the application

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.

🏢 Real-World DBA Note

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)

🎯 Exam Focus

partial(prefix, mask, suffix) exam trap — the first and third arguments are integer counts of characters left visible, not the literal text. The middle argument is the literal mask string.

-- Show first 0 chars, mask with 'XXXX-XXXX-XXXX-', show last 4 chars
ALTER TABLE Customers
  ALTER COLUMN CreditCard ADD MASKED
  WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');
-- Result: XXXX-XXXX-XXXX-1234

-- Show first 1 char, mask with 'XXX', show last 0 chars
-- 'partial(1,"XXX",0)' on 'Ehab' → 'EXXX'

If the question gives you a mask result and asks which partial() produced it, count exposed chars left and right — that's your prefix and suffix.

⚠️ Watch Out

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.

Auth/permission troubleshooting — check in this order

When a user reports "login failed" or "can't see data", walk down this list:

Troubleshooting flow

1

Login exists?

SELECT FROM sys.sql_logins / sys.server_principals

Or in Entra: account enabled?

2

Firewall passes the IP?

Server-level + DB-level firewall rules

VNet rules / Private Endpoint config

3

User exists in target DB?

SELECT FROM sys.database_principals

Login without user = master-only access

4

Role membership / GRANT?

IS_ROLEMEMBER / sys.database_role_members

sys.database_permissions for explicit GRANT/DENY

5

Object-level DENY overrides?

DENY beats GRANT — always

Check ownership chains for views/sprocs

Security Feature Comparison

Full side-by-side matrix lives on the Security Controls Comparison page — don't duplicate, just learn it once there.

---

Anti-Patterns

• "Two Entra admins for redundancy." Only one Entra admin per logical server is allowed — but it can (and should) be a group. Setting an individual user creates a single point of human failure.
• "Use the SQL sa admin in cloud too." Azure SQL DB has no sa. The closest equivalent is the server admin login set at provisioning + the Entra admin. SQL VM still has sa — disable it after creating a least-privileged sysadmin.
• "Server-level firewall rule = enough." Server FW is permissive across all DBs on that logical server. Layer with DB-level FW rules (or, better, Private Endpoint + deny public network).
• "GRANT SELECT to public." public includes every login. Always GRANT to specific roles or groups. The most common cause of lateral data leakage on shared servers.
• "Contained DB user with password — we'll rotate later." Contained user passwords don't sync to a central directory. They drift. Prefer Entra-mapped contained users (CREATE USER [user@tenant.com] FROM EXTERNAL PROVIDER).
• "Service principal authenticates with a client secret." Secrets expire and end up in source control. Use managed identity (Connect-AzAccount -Identity) wherever the consumer runs in Azure.

⚠️ Watch Out

DENY beats GRANT — always. A user in a group GRANTed SELECT and individually DENYed SELECT cannot read. Audit DENYs separately from GRANTs; they're the silent troubleshooting trap.

---

Migration Between Auth Models

From → To	Path	Cost
SQL auth only → Mixed (SQL + Entra)	Set Entra admin at logical server; create Entra users in DB	Online; new auth path coexists
SQL auth → Entra-only	Add Entra admin, migrate apps, then Set-AzSqlServerActiveDirectoryOnlyAuthentication -Enabled $true	Cuts SQL auth completely; verify all clients first
Local SQL logins → Contained DB users	CREATE USER ... WITH PASSWORD (SQL) or FROM EXTERNAL PROVIDER (Entra)	DB now portable across servers
Per-user GRANTs → Role-based (DB roles)	Create custom DB roles, GRANT to roles, ADD users to roles	Cleaner audit; reduces sprawl
Per-user GRANTs → Entra group + role	Same as above but ADD an Entra group instead of users	Lifecycle managed in Entra, not SQL
Server FW → Private Endpoint	Create PE in app VNet; flip publicNetworkAccess = Disabled	Eliminates public IP attack surface; DNS rework
Service principal + secret → Managed identity	Replace Connect-AzAccount -ServicePrincipal with -Identity	Removes secret rotation entirely
Standalone SQL logins on MI → Entra logins	CREATE LOGIN ... FROM EXTERNAL PROVIDER at MI level	Centralized auth; legacy logins can coexist during cutover

Most expensive move: server FW → Private Endpoint (DNS + on-prem hub-spoke planning).

---

Real Scenarios

1. Net-new Azure SQL DB for a regulated app → Entra-only auth, Entra group as admin, Private Endpoint, public network disabled. Driver: zero-secret + zero-public-IP posture. Trade-off: client connectivity needs hybrid DNS resolution.
2. Lift-and-shift legacy app, hardcoded SQL connection string → Mixed mode auth on Azure SQL DB, plan to migrate to managed identity quarter-on-quarter. Driver: app-side change isn't immediate. Trade-off: temporarily larger attack surface — mitigate with KV-stored secret rotated quarterly.
3. Multi-tenant SaaS, separate DB per tenant → Contained DB users (Entra-mapped) in each DB. Driver: DB portability + per-tenant identity. Trade-off: no cross-DB queries from a single connection.
4. CI/CD pipeline deploys schema → Pipeline managed identity, granted db_ddladmin only. Driver: least privilege. Trade-off: requires pipeline auth wiring (OIDC).
5. DBA team needs break-glass access → PIM-eligible Entra group + Entra admin = that group. Driver: Just-in-time elevation, audit trail in Entra. Trade-off: requires Entra ID P2 for PIM.

---

Flashcards

🧠 Smart order (weak cards first)

What is the recommended authentication method for Azure SQL?

Click to reveal answer

Microsoft Entra ID (Azure AD) authentication — supports MFA, conditional access, centralized identity

1 / 6

---

Quiz

Q1/4

0 correct

Which authentication method is recommended for Azure SQL Database?