Comme nous l’avions vu dans le premier épisode de cette série sur le chiffrement dans SQL Server, la solution la plus rigoureuse pour le chiffrement des données reste Always Encrypted. Celle-ci peut s’appliquer en complément de TDE qui lui va s’occuper de protéger l’intégralité des données et notamment ces backups. Always Encrypted, lui, travaille sur par colonne par colonne et partage les éléments cryptographique avec le composant applicatif qui veut lire ou écrire les données, de manière à que SQL Server ne voit pas les données en clair (ou presque : nous parlerons des Secure Enclaves plus loin)
Les objets cryptographiques derrière Always Encrypted :
La technologie derrière Always Encrypted repose sur des clés de chiffrements.
La première est la Column Master Key (ou CMK). C’est une clé RSA de 2048 bits qui sert à chiffré les clés de chiffrement de données. C’est cette clé que l’on devra sécuriser et que l’on doit administrer avec précaution. C’est aussi celle-ci que le composant applicatif devra être capable de lire pour chiffrer et déchiffrer les données. Cette clé peut-être stockée soit dans le magasin de certificat Windows, soit le magasin certificat Java si on développe avec le driver JDBC, soit dans un Azure Key Vault. Aucun autre support de gestion de cette clé n’est disponible pour le moment. Si notre composant applicatif est sous Linux on peut cependant utiliser un Azure Key Vault (par exemple, avec le driver PHP : https://learn.microsoft.com/fr-fr/sql/connect/php/using-always-encrypted-php-drivers?view=sql-server-ver16 ) ou le driver JDBC (https://learn.microsoft.com/fr-fr/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver16 ).
Dans cet article, nous nous concentrerons sur l’Azure Key Vault et le magasin de certificat Windows.
La deuxième est la Column Encryption Key (CEK). Elle sert à chiffrer les données dans la colonne de notre base. Le chiffrement des données se fait par un algorithme publique et reconnu : AEAD_AES_256_CBC_HMAC_SHA_256 , qui est documenté par l’IETF. Cela veut dire qu’à partir que si le stockage de la clé est sécurisé, on peut avoir confiance en ce chiffrement. Dans le cas où la CMK est stockée dans l’Azure Key Vault, Microsoft (et donc le gouvernement américain, même si la zone Azure est en France, dans le cadre du Cloud Act ) pourrait donc déchiffrer nos données, en théorie.
La CEK va donc chiffrer les données par colonne et va affecter la taille de celles-ci. Avec l’algorithme AEAD_AES_256_CBC_HMAC_SHA_256, on obtient donc le tableau des tailles suivantes par type :
| Data Type | Longueur en octet |
| bigint | 65 |
| binary | Variable se référer à la documentation de l’algo |
| bit | 65 |
| char | Variable se référer à la documentation de l’algo |
| date | 65 |
| datetime | 65 |
| datetime2 | 65 |
| datetimeoffset | 65 |
| decimal | 81 |
| float | 65 |
| geography | non supporté |
| geometry | non supporté |
| hierarchyid | non supporté |
| image | non supporté |
| int | 65 |
| money | 65 |
| nchar | Variable se référer à la documentation de l’algo |
| ntext | non supporté |
| numeric | 81 |
| nvarchar | Variable se référer à la documentation de l’algo |
| real | 65 |
| smalldatetime | 65 |
| smallint | 65 |
| smallmoney | 65 |
| sql_variant | non supporté |
| sysname | non supporté |
| text | non supporté |
| time | 65 |
| timestamp (rowversion) | non supporté |
| tinyint | 65 |
| uniqueidentifier | 81 |
| varbinary | Variable se référer à la documentation de l’algo |
| varchar | Variable se référer à la documentation de l’algo |
| xml | non supporté |
On comprend donc que le chiffrement implique forcément de consommer plus d’espace : un integer consomme 4 octets, mais une fois chiffré il en consomme 65.
Dans la documentation disponible ici, on a la formule suivante qui nous permet de déterminer la taille en octet d’un contenu :
1 + 32 + 16 + (FLOOR(DATALENGTH(cell_data)/16) + 1) * 16
Ainsi par exemple avec une chaine de caractère de 76 caractères telle que “Par exemple du texte là qui peut être assez long finalement si on est bavard”, dans SQL Server je fais le calcul suivant :
On a doc 129 octets pour ce varchar(76). Si c’était du nvarchar(76), cela prendrait 209 octets chiffrés au lieu de 152. L’impact est donc non-négligeable dès que l’on arrive sur des grosses volumétries.
Usage et limitation :
Le chiffrement par Always Encrypted doit être considéré uniquement pour des colonnes stockant des informations très sensibles. Si les informations très sensibles n’ont pas été très bien identifiées par le métier, Microsoft propose de l’outillage et de la méthodologie afin de les identifier. cela peut se faire soit dans le Portail Azure, soit dans SSMS (https://learn.microsoft.com/en-us/sql/relational-databases/security/sql-data-discovery-and-classification?view=sql-server-ver16&tabs=t-sql ).
Une fois les colonnes identifiées, il faut également comprendre que leur chiffrement va impacteur leur usage. Dans le meilleur des cas (avec un chiffrement déterministe), on ne pourra faire que des recherches d’égalité dans nos requêtes ( SELECT = / IN / GROUP BY / DISTINCT). On ne peut pas non plus comparer les valeurs non-chiffrées et chiffrées directement. Il faut impérativement utiliser la paramétrisation.
La liste des limitations étant longue, il vaut mieux se reporter à la documentation pour vérifier si l’usage envisagé est compatible : https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16&source=docs#limitations
Séparation des rôles lors de la mise en œuvre :
Le but du chiffrement Always Encrypted est déporter les activités de chiffrements et de déchiffrements au driver SQL. Cela implique donc une responsabilité sur la détention de la paire de clés (CEK) : qui doit les posséder ? les mettre en oeuvre ? Microsoft répond à cela en considérant qu’il y a deux fonctions différentes :
Le DBA qui gère les métadonnées des clés dans la base de données : c’est à dire qu’il va voir des informations dans certaines DMV relatives à la gestion de ces clés, y compris l’emplacement de ces clés (magasin de certificat ou bien URL Azure Key Vault), mais il ne verra pas la CMK directement.
Le “Security Administrator” aura pour rôle de générer la CMK et de les placer dans le magasin de certificat ou l’Azure Key Vault, mais il n’aura pas forcément besoin d’un accès direct à la base de données. Il aura aussi potentiellement à appliquer une rotation des clés si cela fait partie de la stratégie d’entreprise ou bien si une risque de vol de la CMK a eu lieu.
Cette séparation n’est pas indispensable, mais elle représente l’aboutissement du besoin de cacher les données à des utilisateurs non-autorisés, surtout si le rôle de DBA est délégué à un prestataire ou à un opérateur de cloud par exemple.
Mise en oeuvre d’Always Encrypted :
Always Encrypted peut être mis en œuvre par deux outils :
- SQL Server Management Studio
- PowerShell
SSMS encapsule beaucoup les tâches et ne laisse pas beaucoup de marge de manœuvre pour le nommage des clés en particulier. L’utilisation de PowerShell est donc recommandée.
Par ailleurs, il y a deux cadres d’utilisation à considérer à notre époque : le composant applicatif est hébergé sur un serveur On-Prem (et dispose donc d’un magasin de certificats ) ou bien il est hébergé dans le Cloud et voudra accéder à l’Azure Key Vault.
Dans le cadre Azure (avec le Key Vault) :
Deux modules PowerShell sont nécessaire pour travailler sur ces tâches : SqlServer et Az.
Install-Module SqlServer -allowclobber Install-Module Az
On peut ensuite créer la CMK. Si on veut travailler avec Azure et son Key vault, on peut utiliser le script ci-dessous :
$subscriptionId = "xxxxxxxxxxxxxxxxxxxxxxxxx" #Id de la souscription Azure $TenantId = "xxxxxxxxxxxxxxxxxxxxxxxxx" #Id du Tenant Azure Active Directory (Entra ID désormais) $resourceGroup = "Lab1_RG" #Resource Group de l'Azure Key Vault $keyVaultName = "VDE-KeyVault" #Nom de l'Azure Key Vault $keyVaultKeyName = "AdventureWorks-CEK" Import-Module "Az" Connect-AzAccount -Tenant $TenantId # Pour se placer dans le contexte de la souscription souhaitée Set-AzContext -SubscriptionId $subscriptionId # Récupération du nom du key vault name Get-AzKeyVault-VaultName $keyVaultName-ResourceGroupName $resourceGroup # Création de la clé $keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software" $keyVaultKey
On peut désormais voir la clé dans le Key Vault Azure :
On peut voit le détail de la clé, son URL (Key Identifier) et ses permissions ensuite :
L’URL ici visible sera à renseigner dans nos applications cloud. On autorisera l’applicatif à utiliser le rôle “Key Vault Crypto Service Encryption User”.
Il nous reste à l’enregistrer dans notre base de données. On peut le faire en PowerShell ou bien en SQL. C’est ici que l’on peut dessiner la frontière entre le rôle de “dba” et celle de “security administrator”. Le “security administrator” peut juste donner l’URL de la clé au dba pour qu’il l’intègre à la base :
USE [AdventureWorks] GO CREATE COLUMN MASTER KEY [AdventureWorks-CMK-SQL-Object] WITH ( KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT', KEY_PATH = N'https://vde-keyvault.vault.azure.net/keys/AdventureWorks-CMK/9112fb34e3a84051a6ee490bb110a259' ) GO
Ou bien en PowerShell (attention c’est plus long, mais on part du principe que cela fait suite au script PowerShell précédent ) :
Import-Module SqlServer $connectionString = "Data Source=LAB1SQL1;Initial Catalog=AdventureWorks;Integrated Security=True;Connect Timeout=30;Trust Server Certificate=True;Application Name=`"Script PowerShell de déploiement AlwaysEncrypted`"" $database = Get-SqlDatabase -ConnectionString $connectionString $database# Récupération des paramètres de la CMK stockée dans le Key Vault : $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid # Déclaration de la CMK dans SQL Server $cmkName = "AdventureWorks-CMK-SQL-Object" New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
On peut ensuite vérifier que notre CMK est bien déclarée dans SQL Server avec la requête suivante :
On peut ensuite crée la CEK qui nous servira à chiffrer les données dans les colonnes :
$cekName = "AdventureWorks-Person-emailaddress-CEK" New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
Là aussi, on peut vérifier la création de la CEK en SQL :
Une fois la CEK présente, on peut l’utiliser pour chiffrer nos colonnes. Imaginons que l’on souhaite chiffrer la colonne EmailAddress de la table Person.EmailAddress : il faudra choisir entre deux type de chiffrement déterministe (Deterministic) ou aléatoire (Randomized). Déterministe signifie que deux chaines de caractères identiques renverront la même valeur une fois chiffrée, là où aléatoire renverra deux valeurs différentes. L’avantage de déterministe est ainsi de pouvoir faire des indexations et des jointures, contrairement au chiffrement aléatoire. Par contre, pour un attaquant, le fait d’avoir du déterministe (des valeurs qui se répètent dans la table) est une information déjà intéressante.
# Création des instructions de chiffrements : elles peuvent être multiples. $ces = @() $ces += New-SqlColumnEncryptionSettings -ColumnName "Person.EmailAddress.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
Désormais, la lecture des données sans les privilèges d’accès à la clé dans le Key Vault présentera les données ainsi :
SSMS permet d’accéder à des données chiffrées avec Always Encrypted, même si la clé est dans un Azure Key Vault (on aura une pop-up d’authentification, comme avec la commande PowerShell AzConnect), pour cela il suffit juste de cliquer sur “options” dans la fenêtre d’ouverture de session et d’aller cocher “enable Always Encrypted (column encryption)” :
Après cela, les données sont lisibles.
Dans le cadre On-Prem (avec le magasin de certificat Windows) :
Imaginons désormais que notre composant applicatif soit hébergé sur un serveur Windows et ne souhaite pas utiliser l’Azure Key Vault. La seule solution aujourd’hui possible est le magasin de certificat Windows. On peut créer un certificat auto-signer
$cert = New-SelfSignedCertificate -Subject "VDE-CMK-WindowsStore" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange
On pourra voir le certificat dans la branche “personal” de CurrentUser (Start/run/mmc.exe , on ajoute le composant “certificats” pour l’utilisateur courant).
Attention à bien générer le certificat sur une machine autre que celle SQL Server, afin de bien séparer les rôles : la machine SQL Server ne doit pas disposer de la CMK.
Après, de la même manière qu’avec l’Azure Key Vault, on peut créer la CEK :
$cekName = "AdventureWorks-Person-emailaddress2-CEK" $cmkName = "AdventureWorks-CMK-SQL-Object2" $connectionString = "Data Source=LAB1SQL1;Initial Catalog=AdventureWorks;Integrated Security=True;Connect Timeout=30;Trust Server Certificate=True;Application Name=`"Script PowerShell de déploiement AlwaysEncrypted`""</div><div>$database = Get-SqlDatabase -ConnectionString $connectionString New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
$ces = @() $ces += New-SqlColumnEncryptionSettings -ColumnName "Person.EmailAddress2.EmailAddress" -EncryptionType deterministic -EncryptionKey $cekName Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
Si jamais on voulait retirer le chiffrement sur une colonne :
$ces = @() $ces += New-SqlColumnEncryptionSettings -ColumnName "Person.EmailAddress2.EmailAddress" -EncryptionType PlainText Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
Il est à noter qu’il est possible d’exporter le certificat dans la MMC des certificats Windows pour ensuite l’importer dans son Azure Key Vault. L’inverse n’est pas possible.
Considérations applicatives :
Comme on l’a expliqué lors de ces deux épisodes, le bon fonctionnement applicatif repose sur deux points :
- La chaine de connexion spécifie “
Column Encryption Setting=enabled“ - Le compte d’exécution de l’applicatif dispose d’un accès à la Column Master Key.
Dans le cas de l’utilisation d’un applicatif déployé nativement dans le cloud, les stratégies dites RBAC (Role-Based Access Control) sont à privilégier. La documentation Microsoft donne détaille davantage que cet article les éléments à considérer.
Si notre applicatif est plus classique, et est déployé sur un serveur Windows, on s’intéressera à son compte d’exécution pour utiliser le magasin de certificat approprié. Dans le cas de Linux, cela pourra être avec le driver JDBC et le Java KeyStore (https://learn.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver16 )
Les dernières versions de SQL Server (que ça soit dans Azure en PaaS ou bien On-Prem / IaaS) proposent cependant ce qu’ils appellent les Secure Enclaves, pour lever certaines limitations de développement d’Always Encrypted ! Peut-être l’objet d’un nouvel épisode ?
Continuez votre lecture sur le blog :
- Le chiffrement et SQL Server – Episode 1 : Transparent Data Encryption (TDE) vs Always Encrypted (Capdata team) [SQL Server]
- Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
- Le chiffrement Oracle : Transparent Data Encryption sur Oracle 19c (Emmanuel RAMI) [Oracle]
- Le chiffrement Oracle : native network encryption (Emmanuel RAMI) [Oracle]
- Sauvegardes SQL Server dans un Azure Blob Storage (Capdata team) [AzureSQL Server]