16 — Security & Encryption

-- Step 1: Create the predicate function
CREATE FUNCTION Security.fn_TenantFilter(@TenantId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS fn_result
    WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT)
       OR IS_MEMBER('db_owner') = 1
       OR IS_MEMBER('SecurityAdmin') = 1
);
GO

-- Step 2: Bind to the table
CREATE SECURITY POLICY TenantFilterPolicy
    ADD FILTER PREDICATE Security.fn_TenantFilter(TenantId) ON dbo.Orders,
    ADD BLOCK  PREDICATE Security.fn_TenantFilter(TenantId) ON dbo.Orders AFTER INSERT,
    ADD BLOCK  PREDICATE Security.fn_TenantFilter(TenantId) ON dbo.Orders AFTER UPDATE
WITH (STATE = ON, SCHEMABINDING = ON);
GO

-- Set at connection start (ADO.NET, JDBC, etc.)
EXEC sp_set_session_context @key = N'TenantId', @value = 42, @read_only = 1;
-- @read_only = 1 prevents the app from overwriting it mid-session

-- Disable without dropping
ALTER SECURITY POLICY TenantFilterPolicy WITH (STATE = OFF);

-- Add a predicate to an existing policy
ALTER SECURITY POLICY TenantFilterPolicy
    ADD FILTER PREDICATE Security.fn_TenantFilter(TenantId) ON dbo.OrderItems;

-- Drop
DROP SECURITY POLICY TenantFilterPolicy;

CREATE TABLE dbo.Customers (
    CustomerId   INT          PRIMARY KEY,
    Email        VARCHAR(200) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    Phone        VARCHAR(20)  MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)') NULL,
    CreditScore  INT          MASKED WITH (FUNCTION = 'random(300,850)') NULL,
    SSN          CHAR(11)     MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)') NULL
);

-- Add mask to existing column
ALTER TABLE dbo.Customers
    ALTER COLUMN CreditScore ADD MASKED WITH (FUNCTION = 'default()');

-- Drop mask
ALTER TABLE dbo.Customers
    ALTER COLUMN CreditScore DROP MASKED;

-- Database-scoped UNMASK (2022+)
GRANT UNMASK ON dbo.Customers(SSN) TO SupportRole;

-- Grant column-level unmask (2022+)
GRANT UNMASK ON SCHEMA::dbo TO DataEngineer;

EXECUTE AS USER = 'LowPrivUser';
SELECT CustomerId, Email, SSN FROM dbo.Customers;
REVERT;

Service Master Key (SMK)            ← protected by Windows DPAPI / machine key
  └── Database Master Key (DMK)     ← in master database
        └── Certificate (or AEK)    ← in master database
              └── Database Encryption Key (DEK) ← in user database

-- Step 1: Create DMK in master (if not exists)
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3cur3P@ssw0rd!2024';
GO

-- Step 2: Create certificate to protect the DEK
CREATE CERTIFICATE TDE_Cert
    WITH SUBJECT = 'TDE Certificate for MyDatabase',
    EXPIRY_DATE = '2030-01-01';
GO

-- Step 3: Back up the certificate IMMEDIATELY
-- (cannot restore an encrypted database without it)
BACKUP CERTIFICATE TDE_Cert
    TO FILE = '/var/opt/mssql/backup/TDE_Cert.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/backup/TDE_Cert_key.pvk',
        ENCRYPTION BY PASSWORD = 'CertBackupP@ss!'
    );
GO

-- Step 4: Create DEK in the user database
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

-- Step 5: Enable encryption
ALTER DATABASE MyDatabase SET ENCRYPTION ON;
GO

-- Encryption state: 0=None, 1=Unencrypted, 2=Encrypting, 3=Encrypted,
--                   4=Key change in progress, 5=Decrypting
SELECT d.name,
       dek.encryption_state,
       dek.encryption_state_desc,
       dek.percent_complete,
       dek.key_algorithm,
       dek.key_length
FROM sys.databases d
JOIN sys.dm_database_encryption_keys dek ON d.database_id = dek.database_id;

-- Create new certificate
USE master;
CREATE CERTIFICATE TDE_Cert_2025
    WITH SUBJECT = 'TDE Certificate Rotation 2025';

-- Re-encrypt the DEK with the new certificate
USE MyDatabase;
ALTER DATABASE ENCRYPTION KEY
    REGENERATE WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_2025;

ALTER DATABASE MyDatabase SET ENCRYPTION OFF;
-- Wait for decryption to complete (check percent_complete)
USE MyDatabase;
DROP DATABASE ENCRYPTION KEY;

-- Restore certificate on target instance
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TargetDMKPass!';
CREATE CERTIFICATE TDE_Cert
    FROM FILE = '/var/opt/mssql/backup/TDE_Cert.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/backup/TDE_Cert_key.pvk',
        DECRYPTION BY PASSWORD = 'CertBackupP@ss!'
    );
-- Now RESTORE DATABASE will succeed

Column Master Key (CMK)          ← stored outside SQL Server (cert store, Azure Key Vault, HSM)
  └── Column Encryption Key (CEK) ← encrypted copy stored in SQL Server
        └── Encrypted column value ← stored in table

-- Step 1: Create CMK metadata (actual key lives in cert store / AKV)
CREATE COLUMN MASTER KEY CMK1
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/BBF037EC4A133912893A2E7DC5C4D9D4F14ECBA3'
);
GO

-- Step 2: Create CEK (encrypted with CMK)
CREATE COLUMN ENCRYPTION KEY CEK1
WITH VALUES (
    COLUMN_MASTER_KEY = CMK1,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E...  -- generated by SSMS/PowerShell
);
GO

-- Step 3: Create encrypted columns
CREATE TABLE dbo.PatientData (
    PatientId   INT          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,
    Diagnosis   VARCHAR(500) ENCRYPTED WITH (
                                 COLUMN_ENCRYPTION_KEY = CEK1,
                                 ENCRYPTION_TYPE = RANDOMIZED,
                                 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
                             ) NULL
);

USE MyDatabase;

-- Create DMK in user database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DMKPass!2024';

-- Create certificate for symmetric key protection
CREATE CERTIFICATE ColumnEncryptCert
    WITH SUBJECT = 'Column Encryption Cert';

-- Create symmetric key
CREATE SYMMETRIC KEY ColEncKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ColumnEncryptCert;

-- Encrypt a value (key must be open in session)
OPEN SYMMETRIC KEY ColEncKey
    DECRYPTION BY CERTIFICATE ColumnEncryptCert;

UPDATE dbo.Employees
SET SSN_Encrypted = ENCRYPTBYKEY(KEY_GUID('ColEncKey'), SSN_Plaintext);

CLOSE SYMMETRIC KEY ColEncKey;

OPEN SYMMETRIC KEY ColEncKey
    DECRYPTION BY CERTIFICATE ColumnEncryptCert;

SELECT EmployeeId,
       CONVERT(CHAR(11),
           DECRYPTBYKEY(SSN_Encrypted)) AS SSN
FROM dbo.Employees;

CLOSE SYMMETRIC KEY ColEncKey;

ALTER TABLE dbo.Employees
    ADD SSN_Encrypted VARBINARY(256) NULL;
-- Store original as varbinary; ENCRYPTBYKEY returns varbinary(8000)

-- Encrypt with authenticator (EmployeeId binds the ciphertext)
ENCRYPTBYKEY(KEY_GUID('ColEncKey'), SSN_Plaintext, 1, CONVERT(VARBINARY, EmployeeId))

-- Decrypt: must pass matching authenticator or result is NULL
DECRYPTBYKEY(SSN_Encrypted, 1, CONVERT(VARBINARY, EmployeeId))

-- Database A: create cert, sign the procedure
USE DatabaseA;
CREATE CERTIFICATE ModuleSignCert
    WITH SUBJECT = 'Signs procedures that access DatabaseB';

ADD SIGNATURE TO dbo.usp_CrossDbOperation
    BY CERTIFICATE ModuleSignCert;

-- Export cert (without private key — only the public key is needed)
BACKUP CERTIFICATE ModuleSignCert
    TO FILE = '/tmp/ModuleSignCert.cer';

-- Database B: import cert, create user from cert, grant permissions
USE DatabaseB;
CREATE CERTIFICATE ModuleSignCert
    FROM FILE = '/tmp/ModuleSignCert.cer';

CREATE USER CertUser FROM CERTIFICATE ModuleSignCert;
GRANT SELECT ON dbo.SensitiveTable TO CertUser;

USE master;
CREATE CERTIFICATE AutomationCert
    WITH SUBJECT = 'Automation Login Cert',
    EXPIRY_DATE = '2030-01-01';

CREATE LOGIN AutomationLogin FROM CERTIFICATE AutomationCert;
GRANT VIEW SERVER STATE TO AutomationLogin;

CREATE ASYMMETRIC KEY AsymKey1
    WITH ALGORITHM = RSA_2048;

-- Encrypt (public key)
SELECT ENCRYPTBYASYMKEY(ASYMKEY_ID('AsymKey1'), 'SecretData');

-- Decrypt (private key — requires password if key is password-protected)
SELECT CONVERT(VARCHAR(100), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey1'), ciphertext));

-- Requires a certificate or asymmetric key in master
USE master;
CREATE CERTIFICATE BackupCert
    WITH SUBJECT = 'Backup Encryption Certificate',
    EXPIRY_DATE = '2030-01-01';

BACKUP CERTIFICATE BackupCert
    TO FILE = '/var/opt/mssql/backup/BackupCert.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/backup/BackupCert.pvk',
        ENCRYPTION BY PASSWORD = 'BackupCertPass!'
    );

-- Encrypted backup
BACKUP DATABASE MyDatabase
    TO DISK = '/var/opt/mssql/backup/MyDatabase_enc.bak'
    WITH ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = BackupCert
    ),
    COMPRESSION, STATS = 10;

-- S3 backup with encryption (2022+)
BACKUP DATABASE MyDatabase
    TO URL = 's3://mybucket/MyDatabase.bak'
    WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert),
    CREDENTIAL = 'S3Credential';

Windows DPAPI / Machine Key
  └── Service Master Key (SMK)          -- auto-created; protects DMK in master
        └── Database Master Key (DMK)   -- per-database; opened explicitly or auto by SMK
              ├── Certificates
              ├── Symmetric Keys
              └── Asymmetric Keys
                    └── Column Encryption Keys (CEKs)  -- Always Encrypted

-- Regenerate SMK (careful: requires all DMKs to be re-encrypted)
ALTER SERVICE MASTER KEY REGENERATE;

-- Back up SMK
BACKUP SERVICE MASTER KEY
    TO FILE = '/var/opt/mssql/backup/SMK.bak'
    ENCRYPTION BY PASSWORD = 'SMKBackupPass!';

-- Restore SMK
RESTORE SERVICE MASTER KEY
    FROM FILE = '/var/opt/mssql/backup/SMK.bak'
    DECRYPTION BY PASSWORD = 'SMKBackupPass!';

USE MyDatabase;
-- Back up DMK
BACKUP MASTER KEY
    TO FILE = '/var/opt/mssql/backup/MyDB_DMK.bak'
    ENCRYPTION BY PASSWORD = 'DMKBackupPass!';

-- Open DMK manually (when auto-open by SMK is disabled)
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DMKPass!2024';

-- Symmetric key (column encryption)
CREATE SYMMETRIC KEY ColEncKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ColumnEncryptCert;

-- TDE database encryption key
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

-- Backup encryption
BACKUP DATABASE MyDatabase
    TO DISK = '/var/opt/mssql/backup/MyDatabase_enc.bak'
    WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert);

-- Asymmetric key
CREATE ASYMMETRIC KEY AsymKey_RSA4096
    WITH ALGORITHM = RSA_4096;

-- CMK backed by a local certificate (thumbprint from cert store)
CREATE COLUMN MASTER KEY CMK_WinCert
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/BBF037EC4A133912893A2E7DC5C4D9D4F14ECBA3'
);

-- CMK backed by Azure Key Vault (key URL from AKV portal)
CREATE COLUMN MASTER KEY CMK_AKV
WITH (
    KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
    KEY_PATH = N'https://myvault.vault.azure.net/keys/AlwaysEncryptedKey/abc123def456'
);

-- CMK backed by an HSM using the CNG (Cryptography Next Generation) store
CREATE COLUMN MASTER KEY CMK_HSM
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CNG_STORE',
    KEY_PATH = N'My HSM CNG Provider/MyHSMKeyName'
);

-- Step 1: Create the Column Master Key metadata in SQL Server
--         (actual key material lives in the provider specified above)
CREATE COLUMN MASTER KEY CMK1
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/BBF037EC4A133912893A2E7DC5C4D9D4F14ECBA3'
);
GO

-- Step 2: Create the Column Encryption Key, encrypted by the CMK
--         The ENCRYPTED_VALUE is the CEK bytes encrypted with the CMK's public key.
--         Generate ENCRYPTED_VALUE via SSMS Always Encrypted wizard or:
--         New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings
CREATE COLUMN ENCRYPTION KEY CEK1
WITH VALUES (
    COLUMN_MASTER_KEY = CMK1,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E...  -- generated client-side
);
GO

-- Step 3a: Use CEK in a new table definition
CREATE TABLE dbo.PatientRecords (
    PatientId   INT  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,
    Notes       VARCHAR(2000)
                ENCRYPTED WITH (
                    COLUMN_ENCRYPTION_KEY = CEK1,
                    ENCRYPTION_TYPE = RANDOMIZED,
                    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
                ) NULL
);
GO

-- Step 3b: Encrypt an existing column (requires data migration via SSMS or SqlServer module)
--          SQL Server cannot re-encrypt data server-side; use SSMS > Always Encrypted wizard
--          or PowerShell Set-SqlColumnEncryption to handle the client-side re-encryption.
--
-- After client-side re-encryption, the column definition will show:
ALTER TABLE dbo.Employees
    ALTER COLUMN 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;
GO

-- Regenerate the SMK; SQL Server re-encrypts all DMKs automatically
-- Run during a maintenance window — brief lock on master
ALTER SERVICE MASTER KEY REGENERATE;

-- Back up immediately after
BACKUP SERVICE MASTER KEY
    TO FILE = '/var/opt/mssql/backup/SMK_new.bak'
    ENCRYPTION BY PASSWORD = 'SMKBackupPass!New';

USE MyDatabase;

-- Regenerate DMK; SQL Server re-encrypts all dependent symmetric keys and certs
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'DMKNewPass!2025';

-- Back up immediately
BACKUP MASTER KEY
    TO FILE = '/var/opt/mssql/backup/MyDB_DMK_new.bak'
    ENCRYPTION BY PASSWORD = 'DMKBackupPass!New';

USE master;

-- Step 1: Create new certificate
CREATE CERTIFICATE TDE_Cert_2025
    WITH SUBJECT = 'TDE Certificate Rotation 2025',
    EXPIRY_DATE = '2031-01-01';

-- Step 2: Back up new certificate BEFORE switching
BACKUP CERTIFICATE TDE_Cert_2025
    TO FILE = '/var/opt/mssql/backup/TDE_Cert_2025.cer'
    WITH PRIVATE KEY (
        FILE   = '/var/opt/mssql/backup/TDE_Cert_2025.pvk',
        ENCRYPTION BY PASSWORD = 'CertBackupP@ss2025!'
    );

-- Step 3: Re-encrypt the DEK with the new certificate (online, no downtime)
USE MyDatabase;
ALTER DATABASE ENCRYPTION KEY
    REGENERATE WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_2025;

-- Monitor progress
SELECT percent_complete, encryption_state_desc
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID('MyDatabase');

-- Phase 1: Add new CMK metadata (new key must already exist in the key store)
CREATE COLUMN MASTER KEY CMK2
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/NEWTHUMBPRINTHERE'
);
GO

-- Phase 2 (client-side): Use SSMS or PowerShell to re-wrap the CEK with CMK2
-- Invoke-SqlColumnMasterKeyRotation -InputObject $db -SourceColumnMasterKeyName 'CMK1' -TargetColumnMasterKeyName 'CMK2'

-- Phase 3: Refresh stored procedure parameter encryption metadata
EXEC sp_refresh_parameter_encryption @procedure_name = N'dbo.usp_GetPatient';

-- Phase 4 (client-side): Complete rotation — removes old CMK1 copy of CEK value
-- Complete-SqlColumnMasterKeyRotation -InputObject $db -SourceColumnMasterKeyName 'CMK1'

-- Phase 5: Drop old CMK metadata after all clients are updated
DROP COLUMN MASTER KEY CMK1;

-- Step 1: Create new key
CREATE SYMMETRIC KEY ColEncKey_v2
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ColumnEncryptCert;

-- Step 2: Open both keys and re-encrypt all rows
OPEN SYMMETRIC KEY ColEncKey   DECRYPTION BY CERTIFICATE ColumnEncryptCert;
OPEN SYMMETRIC KEY ColEncKey_v2 DECRYPTION BY CERTIFICATE ColumnEncryptCert;

UPDATE dbo.Employees
SET SSN_Encrypted_v2 = ENCRYPTBYKEY(
    KEY_GUID('ColEncKey_v2'),
    CONVERT(NVARCHAR(50), DECRYPTBYKEY(SSN_Encrypted))
);

CLOSE ALL SYMMETRIC KEYS;

-- Step 3: Validate, then drop old key and rename new column
-- (do in a transaction or with application downtime to avoid partial state)
DROP SYMMETRIC KEY ColEncKey;

SELECT name, subject, expiry_date, thumbprint, pvt_key_encryption_type_desc
FROM sys.certificates
ORDER BY expiry_date;

SELECT name, key_algorithm, key_length, create_date, modify_date
FROM sys.symmetric_keys;

SELECT d.name,
       CASE dek.encryption_state
           WHEN 0 THEN 'No DEK'
           WHEN 1 THEN 'Unencrypted'
           WHEN 3 THEN 'Encrypted'
           WHEN 2 THEN 'Encrypting...'
           WHEN 5 THEN 'Decrypting...'
           ELSE CAST(dek.encryption_state AS VARCHAR(10))
       END AS tde_state,
       dek.key_algorithm,
       dek.key_length,
       c.name AS protecting_certificate
FROM sys.databases d
LEFT JOIN sys.dm_database_encryption_keys dek ON d.database_id = dek.database_id
LEFT JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
ORDER BY d.name;

SELECT pol.name AS policy_name,
       pol.is_enabled,
       pred.predicate_type_desc,
       OBJECT_SCHEMA_NAME(pred.target_object_id) + '.' +
           OBJECT_NAME(pred.target_object_id) AS target_table,
       OBJECT_SCHEMA_NAME(pred.predicate_object_id) + '.' +
           OBJECT_NAME(pred.predicate_object_id) AS predicate_function
FROM sys.security_policies pol
JOIN sys.security_predicates pred ON pol.object_id = pred.object_id;

SELECT OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name,
       c.masking_function
FROM sys.masked_columns c
WHERE c.is_masked = 1;

-- Column master keys
SELECT name, key_store_provider_name, key_path, create_date
FROM sys.column_master_keys;

-- Column encryption keys and their values
SELECT cek.name, cev.encryption_algorithm_name,
       cmk.name AS master_key_name, cmk.key_store_provider_name
FROM sys.column_encryption_keys cek
JOIN sys.column_encryption_key_values cev ON cek.column_encryption_key_id = cev.column_encryption_key_id
JOIN sys.column_master_keys cmk ON cev.column_master_key_id = cmk.column_master_key_id;

-- Encrypted columns
SELECT OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name, c.encryption_type_desc, cek.name AS cek_name
FROM sys.columns c
JOIN sys.column_encryption_keys cek ON c.column_encryption_key_id = cek.column_encryption_key_id
WHERE c.column_encryption_key_id IS NOT NULL;