Securing SQL Server Applications

Prerequisites: Basic SQL Server administration and Transact-SQL syntax

Table of Contents

1. Course Overview
2. Introduction to advanced security topics

• 2.1 SQL Server Security Overview
• 2.2 The importance of securing application data
• 2.3 Recognize security threats and vulnerabilities
• 2.4 Best practices for applications accessing SQL Server
• 2.5 Using TLS/SSL with client connections

3. Audit

• 3.1 Purpose and benefits of auditing and logging
• 3.2 Instance-level auditing configuration
• 3.3 Database-level auditing configuration
• 3.4 Reading and interpreting audit logs

4. Encryption in SQL Server

• 4.1 Understanding encryption for data security
• 4.2 Using hash functions for passwords
• 4.3 Data encryption in SQL Server — Key hierarchy
• 4.4 Columnar data encryption
• 4.5 Data encryption at rest (TDE)
• 4.6 Always Encrypted
• 4.7 Always Encrypted with Secure Enclaves

5. Fighting SQL injection attacks

• 5.1 Understanding SQL injections
• 5.2 Real-world injection threats — sqlmap
• 5.3 Protecting T-SQL code against injections
• 5.4 Correct use of Stored Procedures and Dynamic SQL

6. Regulatory Compliance Tools

• 6.1 Introduction to regulatory compliance
• 6.2 Masking sensitive data with Dynamic Data Masking
• 6.3 Preserve history with Temporal Tables
• 6.4 Introduction to Database Ledger
• 6.5 Using SQL Server Ledger — Demo

1. Course Overview

This course is aimed at developers and DBAs who work with SQL Server and want to improve the security posture of their databases. Data is an organization’s most valuable asset, and SQL Server is often poorly secured by default or neglect.

Main topics covered

Educational objective

At the end of this course, you will know how to apply a defense-in-depth solution to protect your SQL Server database, covering all data access points.

2. Introduction to advanced security topics

Module duration: 22m 56s

2.1 SQL Server Security Overview

SQL Server is one of the most popular database platforms in the world. Ensuring your safety is crucial. In today’s data-centric world, information equals power. Protecting this information goes beyond simple regulatory compliance: it is about maintaining trust, ensuring continuity of operations and protecting the reputation of the organization.

Many companies neglect their database security with a “this has never happened to us” mindset. This attitude is dangerous.

Terminologies and fundamental concepts

Authentication Process of confirming the identity of a user, application, or system. This identity is called a principal in SQL Server.

Authorization Determines the resources a principal can access and the operations it can perform. The correct term in the database world is privileges.

Encryption — two types:

• Encryption at rest: encryption of data stored on disk or storage media. Even if someone physically accesses the storage, they cannot read the data without the decryption key.
• Encryption in transit: encryption of data that passes between systems or over a network. Protects against interception and prevents the decryption key from remaining in the same location as the encrypted data.

Audit and monitoring

• Auditing: systematic process of recording and analyzing activities in the database. Provides a clear record of who performed what and when.
• Monitoring: Real-time monitoring of the database system. Alert about unusual or unauthorized activities as soon as they occur.

Compliance and Integrity

• Compliance: adherence to a set of guidelines, standards or laws designed to ensure data protection and confidentiality.
• Integrity: accuracy and consistency of data throughout its lifecycle.

Changing security features in SQL Server

Since SQL Server 2016, Microsoft has made significant advances:

Security approach: Defense-in-Depth

Security must be holistic, as attackers are constantly looking for vulnerabilities to exploit. A single weak link in the security chain can be enough for an intrusion. This is why it is important to adopt a layered security approach, called defense-in-depth:

Think of your data as treasure in a castle. You wouldn’t just rely on the castle walls to protect it — you’d have moats, guards, watchtowers. Similarly, defense-in-depth involves having multiple layers of security controls. This is not just about securing the perimeter, but also ensuring that even if one layer is breached, intruders have several more layers to penetrate.

Relevance of Azure Compliance for SQL Server Security

For cloud solutions, Azure compliance offerings help ensure that SQL Server’s security posture meets global standards:

• Azure Policy regulatory compliance controls
• Azure Purview for data governance
• Microsoft Defender for SQL for vulnerability assessment and advanced threat protection

Some of these features are available for on-premises instances through Azure Arc.

2.2 The importance of securing application data

Securing application data is not only a technical necessity; it is a business imperative, a commitment to stakeholders and a reflection of organizational values.

Types of data to protect

Customer data It’s not just about names and addresses. We may store personal information, transaction histories, preferences. These are customers who trust us to protect their personal and financial information.

Employee data Employees are the most valuable assets. Their data — personal information, salary information, performance records — represents a gold mine for bad actors and deserves the highest level of protection.

Trade secrets These are the vital elements of competitive advantage. From proprietary algorithms to unique business processes, these secrets define the company’s position in the market.

Financial data From earnings reports to transaction histories, this data is the dashboard of business health. Their integrity and confidentiality are essential.

Real-world impacts of data breaches

Financial impacts Beyond the immediate costs of dealing with a breach are potential fines, lawsuits, and loss of business. A single violation can have long-term financial repercussions.

Reputational damage Trust, once broken, is difficult to rebuild. A data breach can damage the trust of customers, partners and stakeholders.

Operational disruptions Violations can shut down operations, delay projects, and require extensive reviews, diverting resources from core business activities.

Legal and regulatory consequences Failure to comply with data protection regulations can lead to legal action and significant penalties.

2.3 Recognize security threats and vulnerabilities

Top Threats Targeting SQL Server

SQL Injection By injecting malicious code into SQL queries, attackers can manipulate these queries and gain unauthorized access or corrupt data. It’s like a burglar fooling the security system by pretending to be the owner.

Denial of Service (DDoS) attack Attackers overwhelm the system with excessive requests, slowing it down or causing it to crash. The massive 2016 DDoS attack targeting Dyn, a major DNS provider, disrupted major sites like Twitter, Reddit, and Netflix.

Important Note: Protection against DDoS attacks is typically done outside of the database, using specialized hardware like firewalls or dedicated DDoS protection services.

Unauthorized access Individuals are accessing the database without proper permissions, due to weak passwords, misconfigurations, or compromised credentials.

Data exfiltration Unauthorized transfer of data from the database to an external location. It’s like a spy who discreetly transmits secrets.

Malware and ransomware Malware that can corrupt, steal or hold data hostage, demanding a ransom for its release.

Real examples of violations

2.4 Best practices for applications accessing SQL Server

Reference Resources

Leading organizations like Microsoft and the Open Web Application Security Project (OWASP) offer comprehensive guidelines on database security.

• SQL Server security best practices
• Securing SQL Server (official web page)

OWASP is a nonprofit organization that provides guidance and resources for improving web application security.

• Database Security Cheat Sheet
• Proactive Controls: Secure Database Access
• SQL Injection Prevention Cheat Sheet

These sources cover in particular:

• Platform and network security
• Identity and Access Management
• Data protection
• Audit and compliance
• Threat detection and response

OWASP Best Practices for Relational Database Server Security

1. Isolate database backend to prevent unauthorized access
2. Configure the database for encrypted connections only
3. Always require strong authentication
4. Never store database credentials in source code
5. Assign permissions based on the Principle of Least Privilege

2.5 Using TLS/SSL with client connections

Practical demonstration

The Tabular Data Stream (TDS) protocol

SQL Server uses a network protocol at the application layer of the OSI model, named TDS (Tabular Data Stream). This protocol has no built-in encryption and the data that passes through is in the clear. It is therefore easy to sniff network packets and reconstruct the contents of a SELECT recordset using an open source tool like Wireshark.

The TDS specification for sending the SQL username and password at login uses XOR operations — which is not strong encryption. It is therefore preferable to encrypt the connection via TLS.

Server-side configuration

To enable TLS in SQL Server:

1. Open SQL Server Configuration Manager
2. Navigate to SQL Server Network Configuration
3. Go to Protocols for [instance name] → right click → Properties
4. In the Flags tab, configure the encryption options

Available options:

For Force Strict Encryption:

• Requires SQL Server 2022 minimum and TDS version 8
• Client side: ODBC driver for SQL Server version 18.1.2.1 or OLEDB driver version 19.2.0 or higher
• Replaces Force Encryption; if enabled, Force Encryption is ignored
• Compatible with TLS 1.3
• Certificate is still validated (self-signed certificates can no longer be forced as trusted)

Certificate tab:

• Allows you to import a certificate or use a certificate present in the Windows trust store
• SQL Server creates its own self-signed certificate on default installation

Advanced tab — Extended Protection:

• When enabled (Allowed or Required) with Kerberos, uses service binding and channel binding to prevent man-in-the-middle attacks (authentication relay)
• A man-in-the-middle attack: an attacker secretly intercepts and relays (and potentially modifies) communications between two parties who believe they are communicating directly

Client Side Options — Login in SSMS

In SSMS, via the Options button → connection parameters:

• Enable Encrypt connection
• Check Trust Server Certificate if a self-signed certificate is used on the server side (otherwise error: “The certificate chain was issued by an authority that is not trusted”)

Verifying that the connection is encrypted:

SELECT encrypt_option
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Configuration in a .NET application

// Chaîne de connexion avec chiffrement TLS standard
"Server=myServer;Database=myDB;Encrypt=true;TrustServerCertificate=false;"

// Avec TDS 8 (Strict Encryption - SQL Server 2022+)
"Server=myServer;Database=myDB;Encrypt=Strict;"
// Note : TrustServerCertificate est ignoré avec Strict, un certificat valide est requis

For Microsoft JDBC Driver, similar options exist (encrypt, trustServerCertificate, hostNameInCertificate).

Reminder: TLS secures the connection between all clients and the server — an essential part of in-depth protection.

3. Audit

Module duration: 24m 49s

3.1 Purpose and benefits of auditing and logging

Difference between Auditing and Logging

Database Auditing A comprehensive or focused process, implemented proactively for a specific purpose. It is the process of collecting and analyzing user activities and transactions in a database to detect and investigate security violations, compliance violations, and other suspicious activities.

Audit logs typically contain:

• The user who performed the action
• The date and time of the action
• The type of action performed
• Data affected

Logging More focused on recording a chosen activity as a preventative measure. It is a log of system events, errors and messages. Auditing is always focused on security, while logging can be general purpose.

Impact: Audit logs are generally smaller and more focused than log files, and are often limited to specific users or actions.

The SQL Server ERRORLOG — Security use

The ERRORLOG is the SQL Server system log. It contains useful information for general diagnostics, but also for basic security protection. In particular, the ERRORLOG contains information about failed login attempts.

Configuration in SSMS:

1. SQL Server instance properties → Security page
2. Login auditing section — available options:

• Failed logins only (default value)
• Successful logins only
• Both failed and successful logins

Important: If you change the connection logging mode, you must restart SQL Server for the option to take effect.

Usefulness for detecting a brute-force attack: Two critical situations where this logging is useful:

1. An application has a bad login/password and is constantly trying to connect
2. A brute-force attack tries to guess the password of an SQL login from a dictionary

Detection of a brute-force attack on the SA account:

A brute-force attack iterates over a list of potential passwords (a dictionary) to try to find the correct password for a login. For a simulation:

# Script PowerShell de simulation d'attaque brute-force
# (Usage : test de pénétration uniquement, sur sa propre instance)
$passwords = Get-Content "dictionary.txt"
foreach ($password in $passwords) {
    try {
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = "Server=myServer;User Id=sa;Password=$password;"
        $conn.Open()
        Write-Host "SUCCESS: Password found: $password"
        break
    } catch {
        Write-Host "FAILED: $password"
    }
}

Consultation of ERRORLOG via T-SQL:

-- Lecture du ERRORLOG courant, filtré sur les tentatives SA
EXEC xp_readerrorlog 
    0,      -- @LogNumber : 0 = ERRORLOG courant
    1,      -- @LogType : 1 = SQL Server ERRORLOG (2 = SQL Agent)
    N'Login failed for user ''sa'''  -- @SearchItem1

Consulting ERRORLOG via PowerShell:

# Via le module SQL Server de Microsoft
Get-SqlErrorLog -ServerInstance "myServer"

# Via DBATools (module communautaire très complet)
Get-DbaErrorLog -SqlInstance "myServer"

Configuring a SQL Server Agent alert:

• Message number: 18456 (Login failed)
• Filter on: Login failed for user ‘sa’

Note: The ERRORLOG is a pure text file accessible by anyone with access to the server. Third-party tools can even block the source IP automatically.

3.2 Configuring instance-level auditing

SQL Server Integrated Auditing is based on the Extended Events feature — a lightweight performance monitoring system that uses very few resources. Auditing is therefore a lightweight feature that does not impact server performance.

Availability:

• All editions of SQL Server support instance/server level auditing
• All editions support database level auditing since SQL Server 2016 (previously it was limited to Enterprise and Developer editions)

SQL Server Audit Architecture

SQL Server Instance
└── Audit (container : définit la cible de sortie)
    └── Server Audit Specification (liste des événements à auditer)
        └── Action Groups (groupes d'actions prédéfinis)

It is possible to have multiple audits per instance and several specifications per audit.

Creating an instance-level audit

Step 1: Create Audit (target)

Via SSMS: Security → Audits → right click → New Audit

Configurable parameters:

• Audit Name
• Destination: file, Windows Security Event Log or Windows Application Event Log
• Maximum size of the file before creating a new file
• Number of files to keep before deleting old logs
• Reserving disk space in advance for audit files
• On Audit Log Failure: behavior in case of audit failure
• Continues: the server continues to run
• Fail operation: stops the operation that triggered the audit with an exception
• Shut down: stops the SQL Server engine

Important: The audit is automatically created in a disabled state. It does not audit any action until activated.

T-SQL script generated:

CREATE SERVER AUDIT [Audit1]
TO FILE 
(
    FILEPATH = N'C:\SQLAudit\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 5,
    RESERVE_DISK_SPACE = OFF
)
WITH
(
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE,
    AUDIT_GUID = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'  -- IMPORTANT pour Availability Groups
);
GO

Availability Groups case: The audit GUID must be identical on both servers (primary and secondary). Use sys.server_audits to get the GUID and recreate the audit with that same GUID on the secondary. This guarantees the continuity of the audit during a failover.

Step 2: Create the Server Audit Specification

Server-level specifications collect action groups (predefined action groups). These groups come from the DDL triggers features of SQL Server 2005. At this level, you can only add groups of actions, not individual actions.

Identify actions in a group:

-- Voir les actions contenues dans des groupes spécifiques
SELECT 
    name AS action_name,
    containing_group_name,
    class_desc
FROM sys.dm_audit_actions
WHERE containing_group_name IN ('SERVER_PRINCIPAL_CHANGE_GROUP', 'LOGIN_CHANGE_PASSWORD_GROUP')
  AND audit_action_type = 'G'  -- G = Server level
ORDER BY containing_group_name, name;

Create and activate specification:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec1]
FOR SERVER AUDIT [Audit1]
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),   -- Création/modification de logins
ADD (LOGIN_CHANGE_PASSWORD_GROUP)      -- Changements de mot de passe
WITH (STATE = ON);
GO

-- Activer l'audit
ALTER SERVER AUDIT [Audit1] WITH (STATE = ON);
GO

View audit logs:

• In SSMS: right click on the audit → View Audit Logs
• Displayed events include: CREATE LOGIN, DROP LOGIN, ALTER LOGIN (reset password), etc.

3.3 Configuring Database Level Auditing

At the database level, specific actions on database objects can be audited, with the ability to filter by object or user.

Identify appropriate action groups

-- Trouver les groupes d'actions disponibles pour les objets (class = DATABASE_OBJECT)
SELECT 
    name,
    containing_group_name,
    class_desc,
    audit_action_type
FROM sys.dm_audit_actions
WHERE class_desc = 'OBJECT'
ORDER BY containing_group_name, name;

Important results:

Creation of a Database Audit Specification

Approach 1: Use an Action Group (all tables, all users)

-- Auditer tous les accès à tous les objets de la base
USE [PachadataTraining];
GO

CREATE DATABASE AUDIT SPECIFICATION [DbAuditSpec_AllAccess]
FOR SERVER AUDIT [Audit1]
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON);
GO

Limitation: With an action group, it is impossible to filter by object name or by user.

Approach 2: Use specific actions (filtered by table and user)

-- Auditer uniquement les opérations CRUD sur la table Contacts, par tous les utilisateurs
USE [PachadataTraining];
GO

CREATE DATABASE AUDIT SPECIFICATION [DbAuditSpec_ContactCRUD]
FOR SERVER AUDIT [Audit1]
ADD (SELECT ON OBJECT::[dbo].[Contacts] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[Contacts] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[Contacts] BY [public]),
ADD (DELETE ON OBJECT::[dbo].[Contacts] BY [public])
WITH (STATE = ON);
GO

Note: The syntax with OBJECT:: is similar to the syntax of GRANT/REVOKE. The BY [public] clause audits the access of all users (everyone is a member of the public role).

To audit a specific user: BY [username]

Audit test:

-- Effectuer un SELECT pour tester l'audit
SELECT TOP 10 * FROM [dbo].[Contacts];
-- Aller dans SSMS → clic droit sur l'audit → View Audit Logs
-- L'action SELECT et l'utilisateur de base de données apparaissent

3.4 Reading and interpreting audit logs

New permissions in SQL Server 2022

Before SQL Server 2022, only logins with CONTROL SERVER permission could access audit results. Since SQL Server 2022, new security permissions allow finer delegation:

Grant permissions:

-- Permettre la consultation graphique dans SSMS
GRANT VIEW ANY SECURITY DEFINITION TO [auditor_login];

-- Permettre la lecture des journaux via fn_get_audit_file
GRANT VIEW SERVER SECURITY AUDIT TO [auditor_login];

Before SQL Server 2022, alternatives existed: copy the audit files somewhere and provide access to a SQL Server Express instance, or provide direct access to the audit binaries.

Catalog views for audit metadata

-- Informations sur les audits de l'instance
SELECT * FROM sys.server_audits;            -- Audits (nom, GUID, dates création/modification, état)
SELECT * FROM sys.server_file_audits;       -- Infos supplémentaires sur les fichiers cibles

-- État d'exécution des audits (requiert VIEW SERVER SECURITY STATE - SQL Server 2022+)
SELECT * FROM sys.dm_server_audit_status;   -- Chemin et taille du fichier en cours

-- Spécifications d'audit au niveau serveur
SELECT * FROM sys.server_audit_specifications;        -- Nom de la spécification
SELECT * FROM sys.server_audit_specification_details; -- Détail des groupes d'actions

-- Joindre les deux vues pour une information complète
SELECT sas.name AS spec_name, sasd.audit_action_name, sasd.audited_result
FROM sys.server_audit_specifications sas
JOIN sys.server_audit_specification_details sasd 
    ON sas.server_specification_id = sasd.server_specification_id;

-- Au niveau base de données
SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.database_audit_specification_details;

Reading logs with fn_get_audit_file

-- Lire tous les fichiers d'audit (pattern GLOB)
SELECT *
FROM fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT);

-- Filtrer par type d'action (par exemple, uniquement les SELECT)
-- D'abord trouver l'action_id
SELECT name, action_id 
FROM sys.dm_audit_actions 
WHERE name = 'SELECT';  -- action_id = 'SL'

-- Lire uniquement les SELECT réussis
SELECT 
    event_time,
    action_id,
    succeeded,
    server_principal_name,
    database_name,
    object_name,
    statement
FROM fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'SL'
  AND succeeded = 1;

Parameters of fn_get_audit_file:

1. Audit file pattern: path with GLOB pattern to read all files in a rollover
2. Initial file name: path of a specific file to start playback from a specific point
3. Audit record offset: starting position in the file (generally NULL or DEFAULT)

Note: The SCHEMA_OBJECT_ACCESS_GROUP action is triggered when SQL Server checks if the user has access to the object. The logs therefore also contain refused access attempts. The succeeded = 1 filter allows you to keep only successful accesses.

4. Encryption in SQL Server

Module duration: 41m 11s

4.1 Understanding encryption for data security

Fundamental concepts

Encryption is the process of converting clear text into cipher text to protect data confidentiality. In reversible encryption, the cipher text can be converted back to clear text, but only by someone who has the decryption key.

Encryption quality depends on:

• The quality of the algorithm (the lock)
• The quality of the key (the key to close/open)

Uses of encryption in SQL Server:

• Selectively encrypt sensitive data in columns (e.g. credit card number)
• Encryption keys can sign code objects (stored procedures) to grant permissions without explicitly granting permissions to a user

Encryption Types

Symmetric encryption

• Uses a single key to encrypt and decrypt data
• Analogue: the key to your apartment in the real world
• Faster, but less secure because the same key must be shared
• Key distribution issue: how to transmit the encryption key securely?

Asymmetric encryption

• Uses two keys: a public key to encrypt, a private key to decrypt
• Solves the distribution problem: only the public key circulates, the private key remains secret
• More secure, but slower and more resource intensive

In SQL Server: We generally use symmetric encryption for columns (performance), protected by an asymmetric key (security). Both types of keys can be created in SQL Server.

4.2 Using hash functions for passwords

Hash vs. Encryption

Hashing uses irreversible algorithms — there is no way to convert a hash to its original value. Passwords do not need to be known in plain text; just compare the hash values.

The HASHBYTES function

SQL Server has a cryptographic hash function: HASHBYTES.

-- Tester la taille du résultat selon l'algorithme
SELECT DATALENGTH(HASHBYTES('SHA2_256', 'test'));  -- 32 bytes (256 bits)
SELECT DATALENGTH(HASHBYTES('SHA2_512', 'test'));  -- 64 bytes (512 bits)

Available algorithms:

Collision: When two different inputs produce the same hash. The more bits the hash has, the less likely collisions are.

Example of storing hashed passwords:

-- Création de la table de logins
CREATE TABLE [Contact].[Login] (
    Username    NVARCHAR(50)    NOT NULL PRIMARY KEY,
    PasswordHash VARBINARY(64) NOT NULL  -- SHA2_512 = 64 bytes
);

-- Insertion d'un mot de passe haché
INSERT INTO [Contact].[Login] (Username, PasswordHash)
VALUES ('DrWho', HASHBYTES('SHA2_512', 'mySecretPassword'));

-- Vérification lors d'une connexion
SELECT COUNT(1) AS IsValid
FROM [Contact].[Login]
WHERE Username = 'DrWho'
  AND PasswordHash = HASHBYTES('SHA2_512', 'mySecretPassword');

Issue: Inherent weakness of deterministic hashing

Hash functions are deterministic in nature (same input → same output). This allows an attacker (e.g. Dalek) to perform the following attack:

1. Dalek knows its own password → it knows its hash
2. Dalek updates the hash of Dr. Who with his own hash
3. Dalek connects like Dr. Who
4. Dalek restores old hash to erase traces

Solution: Salt

The salt is a random string of characters added to the password before hashing. Since HASHBYTES does not have a built-in salt argument, we concatenate it manually.

-- Utiliser le Username comme salt
INSERT INTO [Contact].[Login] (Username, PasswordHash)
VALUES ('DrWho', HASHBYTES('SHA2_512', 'DrWho' + 'mySecretPassword'));
--                          ^^^Username utilisé comme salt

-- Vérification avec salt
SELECT COUNT(1) AS IsValid
FROM [Contact].[Login]
WHERE Username = 'DrWho'
  AND PasswordHash = HASHBYTES('SHA2_512', 'DrWho' + 'mySecretPassword');

Important: The use of salt is a required option to ensure the security of hashed values.

4.3 Data encryption in SQL Server — Key hierarchy

The key hierarchy

To provide the best balance of speed and security, SQL Server uses a key hierarchy — encryption keys that protect other encryption keys:

Windows DPAPI (Data Protection API)
    └── Service Master Key (SMK)
        │   Créé automatiquement à l'installation de SQL Server
        └── Database Master Key (DMK) — dans chaque base de données
            └── Asymmetric Key / Certificate (KEK - Key Encryption Key)
                └── Symmetric Key (chiffre les données)
                    └── Données en colonnes (cipher text)

The Database Master Key (DMK)

-- Vérifier si le DMK existe
SELECT * 
FROM sys.symmetric_keys 
WHERE name = '##MS_DatabaseMasterKey##';

-- Créer le DMK (si absent)
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'StrongPassword123!';

-- Sauvegarder le DMK (IMPÉRATIF)
BACKUP MASTER KEY 
TO FILE = 'C:\Backup\PachadataTraining_MasterKey.mk'
ENCRYPTION BY PASSWORD = 'BackupPassword123!';

-- Vérifier si le DMK est protégé par le SMK
SELECT name, is_master_key_encrypted_by_server
FROM sys.databases
WHERE name = DB_NAME();

Advanced DMK management:

-- Supprimer la copie chiffrée par le SMK (pour forcer l'ouverture manuelle)
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

-- Ouvrir manuellement le DMK (nécessaire si la copie SMK a été supprimée)
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword123!';

-- Sauvegarder le Service Master Key
BACKUP SERVICE MASTER KEY 
TO FILE = 'C:\Backup\ServiceMasterKey.smk'
ENCRYPTION BY PASSWORD = 'BackupPassword123!';
-- Depuis SQL Server 2022 : peut aussi pointer vers Azure Blob Storage

Creating encryption keys

-- 1. Créer une clé asymétrique (paire de clés publique/privée)
CREATE ASYMMETRIC KEY [MyAsymKey]
WITH ALGORITHM = RSA_2048;
-- Algorithmes disponibles : RSA_1024 (déprécié), RSA_2048, RSA_3072

-- 2. Créer un certificat (clé asymétrique avec métadonnées X.509)
CREATE CERTIFICATE [MyCertificate]
WITH SUBJECT = 'My Encryption Certificate',
EXPIRY_DATE = '2034-12-31';
-- Attention : l'expiration est vérifiée pour Service Broker et le chiffrement des backups
-- Elle n'est PAS vérifiée pour les commandes de chiffrement de base de données

-- 3. Créer une clé symétrique protégée par certificat
CREATE SYMMETRIC KEY [MySymKey]
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE [MyCertificate];

Considerations during upgrades

Encryption algorithms evolve over time. When upgrading SQL Server, verify that the algorithms used are still supported at the target compatibility level.

-- Exemple : algorithme RSA_512 déprécié, base de données en compat level 120 (SQL Server 2014)
-- Après mise à niveau du niveau de compatibilité : erreur lors de l'utilisation de la clé

-- Solution : changer le Key Encryption Key sans déchiffrer/rechiffrer les données
-- (fonctionne si la clé symétrique a un algorithme supporté mais est protégée
-- par une clé asymétrique avec un algorithme obsolète)

OPEN SYMMETRIC KEY [MySymKey] DECRYPTION BY ASYMMETRIC KEY [OldAsymKey];
CREATE ASYMMETRIC KEY [NewAsymKey] WITH ALGORITHM = RSA_2048;
ALTER SYMMETRIC KEY [MySymKey] ADD ENCRYPTION BY ASYMMETRIC KEY [NewAsymKey];
ALTER SYMMETRIC KEY [MySymKey] DROP ENCRYPTION BY ASYMMETRIC KEY [OldAsymKey];
CLOSE SYMMETRIC KEY [MySymKey];
-- Cela évite de devoir déchiffrer toutes les données et les rechiffrer

4.4 Column data encryption

Encryption/decryption functions

-- Ouvrir la clé symétrique pour la session
OPEN SYMMETRIC KEY [DataSymKey] 
DECRYPTION BY ASYMMETRIC KEY [KEK];

-- Insérer des données chiffrées (sans salt)
INSERT INTO [dbo].[Customer] (ContactId, CreditCard_Encrypted)
VALUES (1, ENCRYPTBYKEY(KEY_GUID('DataSymKey'), '4111-1111-1111-1111'));

-- Insérer avec salt (authenticator) pour éviter la copie de données chiffrées
INSERT INTO [dbo].[Customer] (ContactId, CreditCard_Encrypted)
VALUES (2, ENCRYPTBYKEY(
    KEY_GUID('DataSymKey'),       -- GUID de la clé
    '4222-2222-2222-2222',        -- Texte en clair
    1,                             -- add_authenticator = 1 (activer le salt)
    ContactId                      -- authenticator (valeur de salt, ex. : ContactId ou email)
));

-- Déchiffrer (sans salt)
SELECT 
    ContactId,
    CAST(DECRYPTBYKEY(CreditCard_Encrypted) AS NVARCHAR(20)) AS CreditCard
FROM [dbo].[Customer];

-- Déchiffrer (avec salt)
SELECT 
    ContactId,
    CAST(DECRYPTBYKEY(CreditCard_Encrypted, 1, ContactId) AS NVARCHAR(20)) AS CreditCard
FROM [dbo].[Customer];

-- Fermer la clé après usage
CLOSE SYMMETRIC KEY [DataSymKey];

Encryption functions available depending on key type:

Creating the complete structure for columnar encryption

USE [PachadataTraining];
GO

-- 1. Créer la clé asymétrique (KEK)
CREATE ASYMMETRIC KEY [KEK]
WITH ALGORITHM = RSA_2048;

-- 2. Créer la clé symétrique protégée par le KEK
CREATE SYMMETRIC KEY [DataSymKey]
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY [KEK];

-- 3. Créer la table avec la colonne chiffrée
CREATE TABLE [dbo].[Customer] (
    CustomerId      INT             PRIMARY KEY,
    ContactId       INT             NOT NULL REFERENCES [dbo].[Contact](ContactId),
    CreditCard_Encrypted VARBINARY(MAX) NULL
    -- Taille VARBINARY : proportionnelle au texte clair, prévoir de la marge
);

Note: The size of VARBINARY for encrypted data is proportional to the size of the plaintext. Use VARBINARY(MAX) to avoid truncations.

Deterministic encryption problem

Encryption by ENCRYPTBYKEY is deterministic — the same text always produces the same cipher text. This allows an attacker to carry out a copy attack:

• Copy cipher text from one line to another to spoof a credit card number

Solution: Use the authenticator (salt) parameter with a unique value per line (like an email or a ContactId).

4.5 Data encryption at rest (TDE)

What is TDE (Transparent Data Encryption)?

TDE encrypts data files, transaction log files, and backup files at the SQL Server storage engine. It is transparent to the application — no modification of the application code is necessary. Encryption and decryption are performed by the SQL Server storage engine.

Files protected by TDE:

• Data files (.mdf, .ndf)
• Transaction log files (.ldf)
• Backup files (.bak)

Important points:

• TDE protects data at rest only. For data in transit, TLS is required. TDE is a complement to TLS, not a replacement.
• Enable TDE on a database also active TDE on tempdb (because SQL Server can copy data to temporary tables)
• Available in Standard edition since SQL Server 2019 (before: Enterprise only)

Key hierarchy for TDE

Service Master Key (SMK)
    └── Database Master Key (dans la base master)
        └── Certificate (dans la base master)
            └── Database Encryption Key (DEK) — dans la base de données cible
                └── Fichiers chiffrés (data, log, backup)

Configuring TDE — Complete Steps

-- Étape 1 : Vérifier le DMK dans la base master
USE master;
GO
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

-- Créer le DMK si absent
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

-- Étape 2 : Créer le certificat dans master (pour protéger le DEK)
USE master;
GO
CREATE CERTIFICATE [TDE_Certificate]
WITH SUBJECT = 'TDE Protection Certificate',
EXPIRY_DATE = '20340101';  -- Syntaxe courte : YYYYMMDD
-- Note : EXPIRY_DATE est sensible pour le chiffrement des backups

-- Étape 3 : Sauvegarder immédiatement le certificat (IMPÉRATIF)
BACKUP CERTIFICATE [TDE_Certificate]
TO FILE = 'C:\Backup\TDE_Certificate.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backup\TDE_Certificate_PrivKey.pvk',
    ENCRYPTION BY PASSWORD = 'CertBackupPassword123!'
);
-- Depuis SQL Server 2022 : format PFX disponible
-- BACKUP CERTIFICATE [TDE_Certificate] TO FILE = 'C:\Backup\TDE_Certificate.pfx'
-- WITH FORMAT = 'PFX', ENCRYPTION BY PASSWORD = 'PFXPassword123!';

-- Étape 4 : Créer le Database Encryption Key (DEK) dans la base cible
USE [PachadataTraining];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE [TDE_Certificate];

-- Vérifier l'état du DEK
SELECT 
    DB_NAME(d.database_id) AS database_name,
    d.encryption_state,
    d.percent_complete,
    d.key_algorithm,
    d.key_length,
    d.encryptor_type
FROM sys.dm_database_encryption_keys d;
-- encryption_state : 0=Aucun, 1=Non chiffré, 2=En cours, 3=Chiffré, 4=Changement de clé, etc.

-- Étape 5 : Activer TDE
ALTER DATABASE [PachadataTraining]
SET ENCRYPTION ON;
-- Le chiffrement démarre en arrière-plan
-- Vérifier la progression avec sys.dm_database_encryption_keys

-- Depuis SQL Server 2019 : possibilité de suspendre le chiffrement
-- ALTER DATABASE [PachadataTraining] SET ENCRYPTION SUSPEND;
-- ALTER DATABASE [PachadataTraining] SET ENCRYPTION RESUME;

Additional columns in sys.dm_database_encryption_keys since SQL Server 2019:

• encryption_scan_state: encryption scan state
• encryption_scan_modify_date: last scan modification date

Restore a TDE database to another instance

-- Sur la nouvelle instance : restaurer d'abord le certificat dans master
USE master;
GO
CREATE CERTIFICATE [TDE_Certificate]
FROM FILE = 'C:\Backup\TDE_Certificate.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backup\TDE_Certificate_PrivKey.pvk',
    DECRYPTION BY PASSWORD = 'CertBackupPassword123!'
);
-- Une fois le certificat restauré, la restauration ou l'attachement de la base est transparente

Critical Warning: If the certificate is no longer available, it is impossible to recover TDE encrypted data. Always maintain backups of the certificate.

Note: For additional security, Extensible Key Management (EKM) allows you to use an external certificate via an API to retrieve keys from an external provider (HSM module or cloud key store like Azure Key Vault).

4.6 Always Encrypted

Issue that Always Encrypted resolves

Classic columnar encryption stores the key in the database. It’s comparable to having a safe but leaving the key in the hallway: the encryption is done on the server side, so the data travels unencrypted over the network and a sysadmin can access it.

Advantages of client-side encryption:

• The key is only accessible to the customer
• Guarantees segregation of tasks: the application can see the data, the database managers cannot

Always Encrypted encrypts data client-side using the SQL Server library (ADO.NET, JDBC, ODBC). The database engine never knows the decryption key — it only knows the identity (thumbprint) of the key.

Always Encrypted Architecture

Client Application
    └── SQL Server Library (ADO.NET, JDBC, ODBC)
        │   Gère le chiffrement/déchiffrement automatiquement
        ├── Column Master Key (CMK) — stockée HORS de la base de données
        │   Localisation : Azure Key Vault, Windows Certificate Store, HSM
        │   (La base contient uniquement la LOCALISATION de la CMK)
        └── Column Encryption Key (CEK) — stockée DANS la base de données (chiffrée)
                Chiffrée par la CMK
                Déchiffrée par le client → utilisée pour chiffrer/déchiffrer les données de colonne

Encryption Types Always Encrypted

Configuring Always Encrypted

Step 1: Create the Column Master Key (CMK)

Via SSMS: Security → Always Encrypted Keys → Column Master Keys → right click → New Column Master Key

Parameters:

• Name (convention: include “CMK” or “cert” in the name)
• Key store: Windows Certificate Store (local or LSAM) or Azure Key Vault

Important: The account running the client application must have access to the key store containing the CMK.

Step 2: Create the Column Encryption Key (CEK)

Via SSMS: Security → Always Encrypted Keys → Column Encryption Keys → right click → New Column Encryption Key

Parameters:

• Name
• CMK used to protect this CEK

Catalog Views:

-- Voir les Column Master Keys
SELECT * FROM sys.column_master_keys;

-- Voir les Column Encryption Keys
SELECT * FROM sys.column_encryption_keys;

-- Voir quelle CEK est chiffrée par quelle CMK
SELECT 
    cek.name AS cek_name,
    cmk.name AS cmk_name,
    cekv.encryption_algorithm_name
FROM sys.column_encryption_key_values cekv
JOIN sys.column_encryption_keys cek ON cekv.column_encryption_key_id = cek.column_encryption_key_id
JOIN sys.column_master_keys cmk ON cekv.column_master_key_id = cmk.column_master_key_id;

Step 3: Create a table with an encrypted column

CREATE TABLE [dbo].[Customer_AE] (
    CustomerId      INT         PRIMARY KEY,
    ContactId       INT         NOT NULL,
    CreditCard      NVARCHAR(20) COLLATE Latin1_General_BIN2  -- Collation BIN2 obligatoire
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = [MyCEK],
            ENCRYPTION_TYPE = DETERMINISTIC,  -- ou RANDOMIZED
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        )
);
-- Note : le type de données est VARCHAR/NVARCHAR car le cipher text est géré en interne
-- La collation BIN2 est OBLIGATOIRE pour les colonnes Always Encrypted

Step 4: Use Always Encrypted in SSMS

1. Reconnect with the Column Encryption Setting = Enabled option enabled in the SSMS connection (Always Encrypted tab)
2. Use variables declared and initialized in a single statement:

-- CORRECT : déclaration et initialisation dans la même instruction
DECLARE @CreditCard NVARCHAR(20) = '4111-1111-1111-1111';
INSERT INTO [dbo].[Customer_AE] (CustomerId, ContactId, CreditCard) VALUES (1, 1, @CreditCard);

-- INCORRECT : ne fonctionne pas avec Always Encrypted
DECLARE @CreditCard NVARCHAR(20);
SET @CreditCard = '4111-1111-1111-1111';  -- Cette syntaxe ne fonctionnera pas

4.7 Always Encrypted with Secure Enclaves

Classic Always Encrypted limitation

Always Encrypted is limited in its server-side processing capability because the server can never decrypt the data. This means that search predicates like > (greater than), LIKE, sorts, and joins are only possible with deterministic encryption and only for ties.

The Secure Enclave

The secure enclave is an isolated region of memory on the server, invisible to anyone — including the process that contains it and the DBA. It provides a server-side Trusted Execution Environment (TEE) where encrypted data can be securely decrypted for rich calculations.

Available technologies:

Additional capabilities with the secure enclave:

• Search predicates with inequality operators (>, <, BETWEEN, LIKE)
• In-place encryption: initial encryption of data without moving it
• Key rotation (key rotation)

Attestation Protocol

To let the client know that the enclave is trusted, there is an attestation protocol between the client and server.

• On Azure: Microsoft Azure Attestation (evaluates enclaves against policies)
• On-premises with VBS: attestation without service (none mode) because VBS on-premises does not yet support an attestation service

Execution flow:

1. Client app obtains encryption metadata from server
2. SQL Server requests attestation from the attestation service
3. The attestation service returns an attestation token → the server sends it to the client
4. The client, satisfied that the enclave is reliable, sends the encryption key to the enclave
5. The enclave decrypts the data in the protected space and performs the calculations

Configuring the Secure Enclave

Windows prerequisites: Verify that VBS is enabled

Ouvrir System Information (msinfo32.exe)
→ System Summary → "Virtualization-based security" : Running
Si désactivé : modifier la clé de registre Windows (voir documentation) et redémarrer

Configure SQL Server to use VBS:

-- Vérifier la valeur actuelle
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = 'column encryption enclave type';

-- Activer VBS (valeur = 1)
EXEC sp_configure 'column encryption enclave type', 1;
RECONFIGURE;
-- Un redémarrage du service SQL Server est nécessaire (RECONFIGURE seul ne suffit pas)

-- Après redémarrage, vérifier dans le ERRORLOG :
-- "DIM loaded VBS enclave for Always Encrypted"

Create a CMK compatible with the enclave:

SSMS → Security → Always Encrypted Keys → Column Master Keys
→ New Column Master Key
→ Cocher : "Allow enclave computations"

Key rotation to activate the enclave on an existing CEK:

SSMS → Security → Always Encrypted Keys → Column Master Keys
→ clic droit sur l'ancien CMK → Rotate
→ Choisir le nouveau CMK enclave-enabled comme cible

(Rotation changes KEK to CEK without decrypting/re-encrypting column data)

Enable Always Encrypted with enclave in SSMS: Reconnect with:

• Column Encryption Setting = Enabled
• Secure Enclave = VBS (or other technology)
• Attestation URL = (empty for VBS without on-premises attestation service)

-- Test : une requête avec inégalité sur une colonne Always Encrypted
-- (fonctionne uniquement avec l'enclave activée)
DECLARE @minAmount NVARCHAR(20) = '4000-0000-0000-0000';
SELECT * FROM [dbo].[Customer_AE]
WHERE CreditCard > @minAmount;
-- Calcul effectué dans l'enclave sécurisée côté serveur

Performance note: Performance will be slower than on an unencrypted column. It is recommended to index the column for better performance.

5. Fight against SQL injection attacks

Module duration: 16m 32s

5.1 Understanding SQL injections

Definition

A SQL injection is the action of sending arbitrary SQL commands to the server by adding characters to an SQL query to modify its action and perform an exploit: obtaining more information, modifying data or structures, or even accessing the server’s underlying operating system.

Concrete example

An ASP.NET web application converts a product search into an SQL query:

-- Requête générée dynamiquement
SELECT * FROM Products WHERE ProductName LIKE '%[userInput]%'

DROP TABLE attack: An attacker enters: '; DROP TABLE Products; --

The request becomes:

SELECT * FROM Products WHERE ProductName LIKE '%'; DROP TABLE Products; --'%'

→ The Products table is deleted.

Tautology attack: An attacker enters: ' OR 1=1; --

The request becomes:

SELECT * FROM Products WHERE ProductName LIKE '%' OR 1=1; --%'

→ All products are returned (1=1 is always true).

SQL Injection Techniques

5.2 Real-world injection threats — sqlmap

Practical demonstration

sqlmap — Open source SQL injection tool

sqlmap is a Python script available on GitHub. It is a SQL injection framework that automates vulnerability detection and exploitation.

⚠️ Legal warning: It is probably prohibited to perform injection tests on public sites. Only use sqlmap on your own applications, in a test environment.

Installation:

git clone https://github.com/sqlmapproject/sqlmap.git
cd sqlmap
python sqlmap.py --help

Basic usage:

# Test sur une URL avec paramètre GET
python sqlmap.py -u "http://localhost/search?q=test" --level=2

# Crawl du site pour trouver les formulaires (profondeur 2)
python sqlmap.py -u "http://localhost/" --crawl=2 --threads=1

How ​​sqlmap identifies the DBMS:

In the demonstration, sqlmap uses the Time-Based Blind technique by injecting WAITFOR DELAY:

-- Injection injectée par sqlmap pour tester SQL Server
'; WAITFOR DELAY '0:0:5'; --

The presence of a delay indicates that the underlying engine is SQL Server. Sqlmap can thus deduce the DBMS and adapt its injection techniques.

Trace Extended Events in SQL Server to monitor sqlmap:

-- Créer une session d'événements étendus pour tracer les requêtes d'une application
-- (filtrer sur l'ApplicationName 'BadApp' par exemple)
CREATE EVENT SESSION [TraceInjection] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    WHERE sqlserver.application_name = N'BadApp'
)
ADD TARGET package0.ring_buffer;
ALTER EVENT SESSION [TraceInjection] ON SERVER STATE = START;

5.3 Protection of T-SQL code against injections

OWASP Resources

• OWASP Code Review Guide: PDF document of more than 200 pages with a code review checklist including web application security (data management, injection, etc.)
• OWASP Query Parameterization Cheat Sheet
• OWASP SQL Injection Prevention Cheat Sheet

Technique 1: Quote escaping (first level of protection)

// Côté application C#
string searchValue = userInput.Replace("'", "''");  // Doubler les apostrophes
// Ou utiliser une regex pour une validation plus stricte

Why exhaust helps: By doubling apostrophes, an attacker can no longer terminate a SQL string and start a new statement. This protects against the majority of simple injection attempts.

Test with sqlmap: After adding escaping, sqlmap reports: “all tested parameters do not appear to be injectable”.

Technique 2: Parameterized queries (robust protection)

// Approche NON SÉCURISÉE (concaténation de chaîne)
string sql = "SELECT * FROM Contacts WHERE LastName LIKE '%" + userInput + "%'";

// Approche SÉCURISÉE (requête paramétrée avec SqlCommand)
using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var cmd = new SqlCommand(
        "SELECT * FROM Contacts WHERE LastName LIKE @searchParam", conn);
    cmd.Parameters.AddWithValue("@searchParam", "%" + userInput + "%");
    var reader = cmd.ExecuteReader();
    // ...
}

ORM and parameterized queries: Entity Framework uses prepared statements internally — it automatically protects against SQL injections. This is one of the advantages of using an ORM.

5.4 Correct use of Stored Procedures and Dynamic SQL

SQL Injection Prevention Methods — Summary

Rule of thumb: Two levels of verification are better than one. Client-side verification is complementary to server-side verification.

Stored Procedure without Dynamic SQL (secure)

-- Stored Procedure paramétrée - aucune injection possible
CREATE OR ALTER PROCEDURE [dbo].[SearchContacts_Safe]
    @LastName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ContactId, LastName, FirstName, Email
    FROM [dbo].[Contact]
    WHERE LastName LIKE '%' + @LastName + '%';
END;

// Appel côté application C# avec SqlCommand de type StoredProcedure
using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var cmd = new SqlCommand("dbo.SearchContacts_Safe", conn)
    {
        CommandType = CommandType.StoredProcedure
    };
    cmd.Parameters.AddWithValue("@LastName", userInput);
    var reader = cmd.ExecuteReader();
}

Stored Procedure with Dynamic SQL (attention required)

-- VERSION VULNÉRABLE : Dynamic SQL sans protection
CREATE OR ALTER PROCEDURE [dbo].[SearchContacts_DynamicSQL_Vulnerable]
    @LastName   NVARCHAR(100) = NULL,
    @FirstName  NVARCHAR(100) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM [dbo].[Contact] WHERE 1=1';
    
    IF @LastName IS NOT NULL
        SET @SQL = @SQL + ' AND LastName LIKE ''%' + @LastName + '%''';
    IF @FirstName IS NOT NULL
        SET @SQL = @SQL + ' AND FirstName LIKE ''%' + @FirstName + '%''';
    
    EXEC (@SQL);
END;
-- PROBLÈME : l'injection ' OR 1=1; -- fonctionne ici

-- VERSION SÉCURISÉE : Dynamic SQL avec protection contre l'injection
CREATE OR ALTER PROCEDURE [dbo].[SearchContacts_DynamicSQL_Safe]
    @LastName   NVARCHAR(100) = NULL,
    @FirstName  NVARCHAR(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. Validation : rejeter les paramètres vides ou null
    IF @LastName IS NULL OR LEN(TRIM(@LastName)) = 0
    BEGIN
        THROW 50001, 'Le paramètre LastName ne peut pas être vide.', 1;
        RETURN;
    END;
    
    -- 2. Échapper les apostrophes (doubler les quotes)
    SET @LastName  = REPLACE(@LastName,  '''', '''''');
    SET @FirstName = REPLACE(@FirstName, '''', '''''');
    
    -- 3. Construire et exécuter le Dynamic SQL
    DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM [dbo].[Contact] WHERE 1=1';
    
    IF @LastName IS NOT NULL
        SET @SQL = @SQL + ' AND LastName LIKE ''%' + @LastName + '%''';
    IF @FirstName IS NOT NULL
        SET @SQL = @SQL + ' AND FirstName LIKE ''%' + @FirstName + '%''';
    
    EXEC (@SQL);
END;

Best practice: sp_executesql with parameters

-- VERSION OPTIMALE : sp_executesql avec paramètres (protège entièrement même en Dynamic SQL)
CREATE OR ALTER PROCEDURE [dbo].[SearchContacts_SpExecuteSql]
    @LastName   NVARCHAR(100) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = 
        N'SELECT * FROM [dbo].[Contact] WHERE LastName LIKE ''%'' + @p_LastName + ''%''';
    
    EXEC sp_executesql 
        @SQL,
        N'@p_LastName NVARCHAR(100)',
        @p_LastName = @LastName;
END;
-- sp_executesql avec paramètres = immunité complète contre les injections

Double protection (client + server):

// Côté client C# : validation + paramétrage
if (string.IsNullOrWhiteSpace(searchValue))
    throw new ArgumentException("La valeur de recherche ne peut pas être vide.");

searchValue = searchValue.Replace("'", "''");  // Protection client

var cmd = new SqlCommand("dbo.SearchContacts_DynamicSQL_Safe", conn)
{
    CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@LastName", searchValue);

6. Tools for Regulatory Compliance

Module duration: 19m 37s

6.1 Introduction to Regulatory Compliance

The security and protection of data in a database is not only a personal or natural obligation of a company, but can also be an external obligation imposed by a governmental or professional authority: this is compliance (regulatory conformity).

Main regulatory frameworks

HIPAA (Health Insurance Portability and Accountability Act)

• US legislation establishing requirements to protect sensitive patient health information
• Imposes several technical safeguard measures: encryption, access controls
• Ensures data security in transit and at rest

GDPR (General Data Protection Regulation)

• European Union Regulation
• Requires organizations to ensure the accuracy, confidentiality and availability of data processing services
• Highlights the protection of personal data and the rights of individuals over their data
• Maximum penalty for a data breach: 2% of company turnover

Other regulations:

• SOX (Sarbanes-Oxley Act): financial controls and data integrity
• PCI DSS (Payment Card Industry Data Security Standard): protection of payment card data

SQL Server Tools for Compliance

6.2 Masking sensitive data with Dynamic Data Masking

Practical demonstration

What is Dynamic Data Masking?

Dynamic Data Masking is a feature available since SQL Server 2016 that does not encrypt data, but masks it for regular users. The data is stored unencrypted in the database. Only the presentation is modified according to the permissions.

Use case: A customer service representative can see the last 4 digits of a social security number, while a manager can see the full number.

Available masking functions

Configuring Dynamic Data Masking

USE [PachadataTraining];
GO

-- 1. Créer un utilisateur sans login pour la démonstration (impersonation)
CREATE USER [Maria] WITHOUT LOGIN;

-- 2. Accorder la permission de lire la table à Maria
GRANT SELECT ON [dbo].[Contact] TO [Maria];

-- 3. Appliquer un masque sur la colonne Email
ALTER TABLE [dbo].[Contact]
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- Note : ALTER TABLE ne modifie pas le contenu de la colonne,
-- uniquement la présentation lors d'une requête

-- 4. Test en se faisant passer pour Maria (impersonation)
EXECUTE AS USER = 'Maria';
SELECT TOP 5 * FROM [dbo].[Contact];
-- Email apparaît masqué : aXXX@XXXX.com

-- Test via une fonction
SELECT TOP 5 LEFT(Email, 3) FROM [dbo].[Contact];
-- Le résultat de la fonction est aussi masqué (pas de contournement possible)

REVERT;  -- Revenir à sysadmin

-- 5. Accorder la permission de voir les données non masquées à Maria
-- Pour SQL Server 2016-2019 : permission globale sur toute la base
GRANT UNMASK TO [Maria];

-- Pour SQL Server 2022+ : permission granulaire sur une table spécifique
GRANT UNMASK ON [dbo].[Contact] TO [Maria];

-- 6. Test avec la permission UNMASK
EXECUTE AS USER = 'Maria';
SELECT TOP 5 * FROM [dbo].[Contact];  -- Emails en clair maintenant
REVERT;

Other examples of masks:

-- Masquage partiel d'un numéro de carte de crédit (afficher 4 derniers chiffres)
ALTER TABLE [dbo].[Customer]
ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

-- Masquage par défaut d'un numéro de téléphone
ALTER TABLE [dbo].[Contact]
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'default()');

-- Masquage aléatoire d'un âge
ALTER TABLE [dbo].[Employee]
ALTER COLUMN Age ADD MASKED WITH (FUNCTION = 'random(18, 65)');

Delete a mask:

ALTER TABLE [dbo].[Contact]
ALTER COLUMN Email DROP MASKED;

SQL Server 2022 Evolution: Before 2022, the only option was to grant UNMASK for the entire database. The ability to grant UNMASK on a specific table in 2022 is a major improvement for permission granularity.

6.3 Preserve history with Temporal Tables

What is a Temporal Table?

temporal tables (temporal tables) are a feature of SQL Server 2016 based on the ISO SQL 2011 standard (system versioned temporal tables). They allow data changes to be automatically tracked over time, maintaining a complete history without additional code.

Principle:

• Each temporal table has an associated history table that stores all row versions
• Two columns DATETIME2 (SysStartTime and SysEndTime) define the validity period of each version
• SQL Server automatically manages these columns and the history table

Use case:

• Regulatory compliance: modification history required by certain regulations
• Data audit: who modified what and when (but not who — for this, use SQL Server audit)
• Data recovery: restore a row to its previous state after accidental modification or deletion
• Trend analysis: analyze the evolution of data over time

Creating a Temporal Table

USE [PachadataTraining];
GO

CREATE TABLE [Contact].[Employee] (
    EmployeeId      INT             NOT NULL PRIMARY KEY IDENTITY(1,1),
    LastName        NVARCHAR(50)    NOT NULL,
    FirstName       NVARCHAR(50)    NOT NULL,
    Department      NVARCHAR(100),
    Salary          DECIMAL(10, 2),
    -- Colonnes de versionnement système (DATETIME2, autogénérées)
    SysStartTime    DATETIME2(3)    GENERATED ALWAYS AS ROW START,
    SysEndTime      DATETIME2(3)    GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [Contact].[EmployeeHistory]
        -- Si HISTORY_TABLE non spécifié, SQL Server génère un nom automatiquement
    )
);

History table properties:

• Natively compressed page compression (storage optimization for often duplicated data)
• Ability to create additional indexes
• Ability to add triggers
• Content not editable via normal interface (requires special operations for archiving)
• The contents of the current line have SysEndTime = 9999-12-31 23:59:59.9999999 (maximum value DATETIME2)

Check via catalog views:

SELECT 
    t.name AS table_name,
    t.temporal_type,           -- 2 = SYSTEM_VERSIONED_TEMPORAL_TABLE
    t.temporal_type_desc,
    ht.name AS history_table_name,
    t.is_memory_optimized
FROM sys.tables t
LEFT JOIN sys.tables ht ON t.history_table_id = ht.object_id
WHERE t.schema_id = SCHEMA_ID('Contact');

Query historical data with FOR SYSTEM_TIME

-- Voir TOUTES les versions (courantes + historiques) d'un employé
SELECT EmployeeId, LastName, FirstName, Department, SysStartTime, SysEndTime
FROM [Contact].[Employee]
FOR SYSTEM_TIME ALL
WHERE EmployeeId = 2
ORDER BY SysStartTime;

-- Point dans le temps (AS OF) : état de la table à un instant précis
SELECT *
FROM [Contact].[Employee]
FOR SYSTEM_TIME AS OF '2024-03-15 10:00:00'
WHERE EmployeeId = 2;

-- Plage de temps — FROM...TO
-- Retourne les lignes actives à N'IMPORTE QUEL MOMENT dans la plage
-- EXCLUT la borne supérieure (fin)
SELECT *
FROM [Contact].[Employee]
FOR SYSTEM_TIME FROM '2024-01-01' TO '2024-06-30';

-- Plage de temps — BETWEEN...AND
-- INCLUT la borne supérieure
SELECT *
FROM [Contact].[Employee]
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-30';

-- CONTAINED IN (start, end) : retourne uniquement les lignes dont
-- la PÉRIODE ENTIÈRE est contenue dans la plage spécifiée
-- (aucune ligne ne déborde sur l'extérieur de la plage)
SELECT *
FROM [Contact].[Employee]
FOR SYSTEM_TIME CONTAINED IN ('2024-01-01', '2024-06-30');

Syntax differences:

Data recovery with Temporal Tables

-- Restaurer un employé à son état d'il y a une semaine
UPDATE [Contact].[Employee]
SET LastName = h.LastName, FirstName = h.FirstName, Department = h.Department
FROM [Contact].[Employee] e
JOIN [Contact].[Employee]
    FOR SYSTEM_TIME AS OF DATEADD(WEEK, -1, GETUTCDATE()) h 
    ON h.EmployeeId = e.EmployeeId
WHERE e.EmployeeId = 2;

6.4 Introduction to Database Ledger

Problem resolved by the Database Ledger

The Database Ledger is a feature available since SQL Server 2022 (SQL Server 2020 mentioned in the course — this is SQL Server 2022). It answers a question that SQL Server did not have a native answer to: how to verify data integrity? Did someone tamper with my tables?

A sophisticated attacker can directly modify a data page (via undocumented commands like DBCC WRITEPAGE) without going through the SQL Server engine, without leaving a trace in the transaction logs. The ledger detects this type of manipulation.

Database Ledger Mechanisms

Change history Like temporal tables, the ledger maintains a history of all schema and data modifications.

Cryptographic checksums (hashes) The ledger calculates cryptographic checksums (SHA-256 hashes) of each transaction. These hashes can be validated over time.

Blockchain Hashes are chained using a blockchain-like structure — each block contains the hash of the previous block. Any modification of a block invalidates all subsequent blocks.

Merkle Tree For large volumes of data, SQL Server can calculate a Merkle tree (hashes of hashes): – 100 data blocks → 100 hashes

• Hash of these 100 hashes → digest (root value)
• This digest is a single value representing the state of the database

The digest must be stored outside the database in secure storage (the principle is to never store verification data in the same place as the data to be verified).

Ledger table types

6.5 Using SQL Server Ledger — Demonstration

Practical demonstration

Create a Ledger database

-- Option 1 : Créer une base de données entièrement Ledger
-- Toutes les tables créées dans cette base seront automatiquement des tables Ledger
CREATE DATABASE [StockPurchaseDB] WITH LEDGER = ON;
GO
-- Dans SSMS Object Explorer : l'icône de la base de données indique qu'elle est une Ledger DB

-- Option 2 : Créer des tables Ledger dans une base normale
CREATE TABLE [dbo].[StockPurchase] (
    PurchaseId  INT         PRIMARY KEY IDENTITY(1,1),
    Stock       NVARCHAR(10) NOT NULL,
    Quantity    INT          NOT NULL,
    Price       DECIMAL(10, 2) NOT NULL
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);
-- LEDGER = ON crée automatiquement la table d'historique et la vue Ledger

Structure of an Updateable Ledger Table

-- Voir les métadonnées
SELECT 
    name,
    ledger_type,            -- 1 = Updatable, 2 = Append-only
    ledger_type_desc,
    ledger_view_name,       -- Nom de la vue Ledger (table_Ledger)
    history_table_id
FROM sys.tables
WHERE name = 'StockPurchase';

Interact with the Ledger table

USE [StockPurchaseDB];
GO

-- Insérer des données
INSERT INTO [dbo].[StockPurchase] (Stock, Quantity, Price)
VALUES ('MSFT', 10, 350.00);

INSERT INTO [dbo].[StockPurchase] (Stock, Quantity, Price)
VALUES ('AAPL', 5, 175.00);

-- Mettre à jour (pour démonstration)
UPDATE [dbo].[StockPurchase]
SET Price = 360.00
WHERE PurchaseId = 1;

-- Tenter de supprimer de la table d'historique → Erreur !
-- DELETE FROM [dbo].[StockPurchaseHistory]; -- Impossible : accès bloqué

Consult the Ledger view

The Ledger view is automatically named [table_name]_Ledger and aggregates the main table and the history table:

-- Voir tout l'historique des modifications (via la vue Ledger)
SELECT 
    ledger_transaction_id,
    ledger_sequence_number,
    ledger_operation_type_desc,
    PurchaseId, Stock, Quantity, Price
FROM [dbo].[StockPurchase_Ledger]
WHERE PurchaseId = 1
ORDER BY ledger_transaction_id, ledger_sequence_number;
-- Résultat : INSERT (opération originale), puis DELETE + INSERT (l'UPDATE = DELETE ancien + INSERT nouveau)

-- Voir les modifications de schéma de la table
SELECT * FROM sys.ledger_table_history
WHERE table_name = 'StockPurchase';

-- Voir les détails des transactions Ledger (qui, quand, hash)
SELECT 
    transaction_id,
    commit_time,
    principal_name,
    table_hashes,
    block_id
FROM sys.database_ledger_transactions
ORDER BY commit_time;

Generate and verify the Digest

-- 1. Générer le digest (résumé cryptographique de l'état de la base)
EXEC sp_generate_database_ledger_digest;
-- Retourne un document JSON contenant le digest
-- Ce JSON doit être stocké dans un stockage sécurisé HORS de la base de données

-- 2. Vérifier l'intégrité de la base en passant le digest précédemment sauvegardé
-- Prérequis : activer SNAPSHOT ISOLATION
ALTER DATABASE [StockPurchaseDB] SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Vérification avec digest manuellement obtenu
EXEC sp_verify_database_ledger
    @digest = N'{"digest_version":1,"database_name":"StockPurchaseDB",...}',
    @table_name = N'dbo.StockPurchase'  -- Optionnel : vérifier une table spécifique
    -- Si omis : vérification de toute la base de données
;
-- Retourne : block_id vérifié. Aucune falsification = sp_verify réussit sans erreur
-- Si falsification détectée : la procédure retourne une erreur avec l'identifiant du bloc corrompu

Automation on Azure SQL Database

-- Sur Azure SQL Database : génération et stockage automatique des digests
-- sp_verify_database_ledger_from_digest_storage vérifie directement depuis le stockage
EXEC sp_verify_database_ledger_from_digest_storage
    @locations = N'[{"path":"https://myStorageAccount.blob.core.windows.net/ledger","sas_token":"..."}]';

Ledger Catalog View Summary

7. Summary — Defense in Depth for SQL Server

This course covered a comprehensive approach to SQL Server security in several layers:

Fundamental Principle: Security is not just a technical issue. It is a business imperative, a commitment to stakeholders and a reflection of organizational values. A defense-in-depth approach — multiple layers of security controls — is the only effective way to protect your SQL Server data.

Search Terms

securing · sql · server · applications · microsoft · databases · data · encryption · database · ledger · security · always · encrypted · audit · configuring · dynamic · injection · tde · auditing · compliance · masking · secure · temporal · types