Cloud Database Security

Advanced Databases
TLSencryption at restAzure SQL AADrow-level securityaudit logs

Objective

Secure a cloud database: enforce encrypted connections, implement row-level security, configure auditing, and manage secrets.

Tools & Technologies

  • TLS
  • encryption at rest
  • Azure SQL AAD
  • row-level security
  • audit logs

Key Commands

az sql server tde-key set
ALTER DATABASE ENCRYPTION ON
CREATE SECURITY POLICY
az sql db audit-policy update

Architecture Overview

graph TD subgraph Security Layers NETWORK[Network\nFirewall / Private Endpoint] AUTH[Authentication\nAAD / SQL Auth / MFA] AUTHZ[Authorization\nRBAC / Row-Level Security] ENCRYPT[Encryption\nTLS in transit + TDE at rest] AUDIT[Auditing\nAll access logged] end NETWORK --> AUTH --> AUTHZ --> ENCRYPT --> AUDIT style NETWORK fill:#1a1a2e,stroke:#ff4444,color:#ff4444 style AUDIT fill:#1a1a2e,stroke:#00ff88,color:#e0e0e0

Step-by-Step Process

01
Transparent Data Encryption

Enable TDE for encryption at rest.

-- SQL Server / Azure SQL
ALTER DATABASE mydb SET ENCRYPTION ON;
SELECT * FROM sys.dm_database_encryption_keys;

-- Azure CLI: TDE is on by default
az sql db show --query 'transparentDataEncryption'
02
Row-Level Security

Restrict data access per user at the row level.

-- Allow users to only see their own rows
CREATE SCHEMA security;

CREATE FUNCTION security.fn_filter (@user_id INT)
RETURNS TABLE
AS RETURN SELECT 1 WHERE @user_id = CAST(SESSION_CONTEXT(N'user_id') AS INT);

CREATE SECURITY POLICY UserFilter
ADD FILTER PREDICATE security.fn_filter(user_id) ON dbo.orders
WITH (STATE = ON);

Challenges & Solutions

  • Disabling TLS on connection string exposes credentials and data in transit
  • Row-level security bypass via EXECUTE AS — test thoroughly

Key Takeaways

  • Store connection strings in Azure Key Vault, not in application config files
  • Enable Advanced Threat Protection — it detects SQL injection attempts automatically