Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 39 ยท Administration and Security

Encryption, Masking, Row-Level Security and Auditing

Protect SQL Server data with TDE, Always Encrypted, dynamic data masking, row-level security and auditing. Learn what each feature defends against and where it falls short.

Permissions decide who may ask. They don't help when someone steals a backup file, when a support analyst sees credit card numbers they don't need, or when one tenant's rows must never appear for another tenant. SQL Server has built-in features for those jobs, and each protects against a different threat. Choosing the right one starts with asking: who am I defending against?

This lesson tours the main features. They are SQL Server-specific and can't run in the browser, so the T-SQL is read-only and the stand-ins model the behavior with views. It follows logins, users and permissions and complements SQL injection and encryption.

Feature Protects against Who still sees the data
TDE (Transparent Data Encryption) Stolen files and backups Everyone who can query
Always Encrypted Even the DBA reading values Only the app holding the key
Dynamic Data Masking Casual exposure in results Users with UNMASK; not a strong barrier
Row-Level Security Users seeing other users' rows Per-policy
Auditing Not knowing who did what n/a (records access)

TDE: encryption at rest

Transparent Data Encryption encrypts the data and log files and backups on disk with a database encryption key, protected by a certificate in master. The application and queries don't change; pages are decrypted as they are read into memory.

SQL
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'strong-passphrase';
CREATE CERTIFICATE TdeCert WITH SUBJECT = N'TDE certificate';

USE Shop;
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TdeCert;
ALTER DATABASE Shop SET ENCRYPTION ON;

Back up the certificate and its private key immediately. Without them an encrypted backup can't be restored anywhere, ever. TDE does nothing against a user with a valid login who runs SELECT.

In Azure SQL Database, TDE is on by default.

Always Encrypted: the DBA can't read it

With Always Encrypted, the client driver encrypts the column value before sending it, and SQL Server only ever stores ciphertext. The key lives in the client's key store (a certificate store or Azure Key Vault), never on the server.

SQL
CREATE TABLE dbo.Patients
(
    PatientId int IDENTITY PRIMARY KEY,
    Ssn char(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK1,
                        ENCRYPTION_TYPE = DETERMINISTIC,
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
);
  • Deterministic encryption gives the same ciphertext for the same value, so equality searches and joins work, but patterns can leak.
  • Randomized encryption is stronger but allows no comparisons.
  • Secure enclaves (2019 and later) add richer operations such as range comparisons inside protected memory.
  • Applications must use the encrypted connection setting (Column Encryption Setting=Enabled) and the queries must use parameters, not literals.

Dynamic Data Masking

Masking hides part of a value in query results for users without the UNMASK permission. The stored data is unchanged.

SQL
ALTER TABLE dbo.Customers
    ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Customers
    ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');

GRANT UNMASK TO Supervisor;

A masked user sees [email protected]. Masking is a convenience, not security: a determined user can still guess values by filtering (WHERE Email = '[email protected]' returns a row) or inferring from WHERE Salary > 100000. Use it to reduce accidental exposure alongside permissions, not instead of them.

Masking is easy to model with a view that shows the full value only to some people. The stand-in uses a "who is asking" table:

SQL A masking view (stand-in for dynamic data masking)

Press Run (or Ctrl + Enter) to see the result here.

Row-Level Security

Row-Level Security (RLS) filters which rows a user can see or change, enforced by the engine for every query, so the application can't forget a WHERE. You write an inline table-valued function as the predicate and attach it with a security policy:

SQL
CREATE FUNCTION Security.fn_TenantFilter (@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS allowed
       WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);

CREATE SECURITY POLICY Security.TenantPolicy
    ADD FILTER PREDICATE Security.fn_TenantFilter(TenantId) ON dbo.Invoices,
    ADD BLOCK  PREDICATE Security.fn_TenantFilter(TenantId) ON dbo.Invoices AFTER INSERT
    WITH (STATE = ON);

The application connects, runs EXEC sp_set_session_context 'TenantId', 7;, and from then on SELECT * FROM dbo.Invoices returns only tenant 7's rows. A filter predicate hides rows from reads, updates and deletes; a block predicate stops writes that would violate the rule. It is the engine-level answer to multi-tenant design.

The effect is the same as a view that always applies the current tenant:

SQL A tenant filter (stand-in for a security policy)

Press Run (or Ctrl + Enter) to see the result here.

Change the tenant id in session_context to 8 and the result changes with it, without touching the query.

RLS caveats: users with db_owner or ALTER ANY SECURITY POLICY can bypass it, and clever predicates can leak data through side channels, so keep predicate functions simple and index the filter column.

Auditing

Know who did what. SQL Server Audit writes events (logins, SELECT on a table, permission changes, schema changes) to a file, the Windows event log or Azure storage.

SQL
USE master;
CREATE SERVER AUDIT ShopAudit TO FILE (FILEPATH = N'D:\Audit\');
ALTER SERVER AUDIT ShopAudit WITH (STATE = ON);

USE Shop;
CREATE DATABASE AUDIT SPECIFICATION CustomerAccess
FOR SERVER AUDIT ShopAudit
    ADD (SELECT, UPDATE ON dbo.Customers BY public)
WITH (STATE = ON);

SELECT event_time, server_principal_name, statement
FROM   sys.fn_get_audit_file(N'D:\Audit\*.sqlaudit', DEFAULT, DEFAULT);

Also useful: Ledger tables (2022) give tamper-evident history, temporal tables keep row history, and Vulnerability Assessment in SSMS scans for risky settings.

flowchart LR
  U["User or app"] --> A["Authentication: login"]
  A --> P["Permissions and roles"]
  P --> R["Row-level security"]
  R --> M["Masking in results"]
  M --> D[("Data: Always Encrypted columns, TDE files")]
  D -. "events" .-> Au["Audit log"]

Defense in depth: several layers, each stopping a different kind of leak.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which feature protects data if someone steals a backup file?

    TDE encrypts data files and backups at rest. It doesn't stop authorized users from querying.
  2. 2. What is the key difference of Always Encrypted from TDE?

    Always Encrypted encrypts and decrypts in the client driver; the server never sees the key or plaintext.
  3. 3. Why is Dynamic Data Masking not a strong security boundary?

    Masking only alters what is displayed. Predicates still run on real values.
  4. 4. What does a block predicate in a Row-Level Security policy do?

    Filter predicates limit what you read; block predicates limit what you may write.

Summary

  • TDE protects files at rest; Always Encrypted protects values from the server and DBAs.
  • Masking reduces accidental exposure but is not a barrier; RLS enforces row visibility in the engine.
  • Audit who accesses what, and layer the defenses.
  • Back up certificates and keys: losing them means losing the data.

Next: SQL Server Agent and maintenance