Pour l’un de nos clients, nous avons développé une application de « synchronisation » de données clients entre une base de données locale et leur outil de CRM. Ceci afin de répondre aux réglementations du pays qui impose d’avoir une copie locale d’une base de données de clients sur le territoire national. L’autre contrainte imposée était de chiffrer les données sensibles relatives aux clients. Le but de ce billet est donc de présenter comment mettre en place facilement le chiffrement de données dans une base de données SQL Server avec SQL Server Always Encrypted. Et également, à la suite de mon expérience sur sa mise en place sur deux serveurs web, de donner certains conseils pratiques et points à vérifier ou configurer.
1) Qu’est-ce que SQL Server Always Encrypted?
Il s’agit d’une technologie de chiffrement côté client introduite par Microsoft avec SQL Server 2016. Il est aussi bien disponible pour les versions sur site que cloud avec Azure SQL Database. Le chiffrement est transparent pour les applications. Il est réalisé par un driver Always Encrypted installé sur le client (ou le serveur dans le cas d’une application web) qui chiffre ou déchiffre automatiquement les données dans l’application cliente. Lors de la rédaction de ce billet, les seuls drivers disponibles sont .NET Framework Data Provider for SQL Server (qui requiert l’installation du Framework .NET 4.6 ou supérieur) et JDBC 6.0.
2) Comment fonctionne Always Encrypted?
Always Encrypted permet de définir quelles colonnes nous souhaitons chiffrer de manière individuelle. Pour chaque colonne, il faut définir l’algorithme d’encryptage et les clés utilisées pour protéger les données stockées. Pour fonctionner, Always Encrypted a besoin de deux clés :
- Une clé principale de colonne est une clé de protection de clé qui chiffre ou déchiffre une ou plusieurs clés de chiffrement de colonne
- Une clé de chiffrement de colonne permet de chiffrer les données d’une colonne chiffrée
Pour en savoir plus sur le fonctionnement détaillé d’Always Encrypted, vous pouvez consulter la documentation Microsoft : https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15#how-it-works
3) Comment configurer et utiliser Always Encrypted?
Sa configuration nécessite peu de préparation sur le serveur de bases de données qui héberge les tables à chiffrées. Cela se réalise en deux étapes :
- Création de la clé principale de colonne
- Création de clé de chiffrement de colonne
3.1) Clé principale de colonne
Il s’agit d’un certificat qui peut être stocké dans le magasin de certificat de Windows, un module de sécurité tiers ou Azure Key Vault.
Il faut savoir que Always Encrypted ne vérifie pas explicitement la validité du certificat. Il ne l’utilise que comme paire de clés. Ainsi, on peut très bien utilisé un certificat expiré sans problème mais la bonne pratique reste de renouveler la CMK (Column Master Key) régulièrement.
Pour créer la CMK, nous pouvons utiliser SQL Server Management Studio (SSMS) ou T-SQL.
3.1.1) Création de clés principales de colonnes
Dans l’explorateur d’objets de SSMS, il faut naviguer dans la base de données qui contient les tables à chiffrer puis dans Sécurité et enfin étendre le dossier Clés Always Encrypted afin d’afficher ses deux sous dossiers comme visible sur la figure ci-dessous :
Pour créer une clé principale de colonne, effectuez un clic droit sur le dossier Clés principales de colonne (Column Master Keys) et sélectionnez Nouvelle clé principale de colonne (New Column Master Key). Dans la boîte de dialogue Nouvelle clé principale de colonne, indiquez un nom et choisissez un magasin de certificat puis le certificat à utiliser. Le bouton « Générer un certificat » permet de générer un certificat auto-signé et le place dans le magasin sélectionné.
On peut vérifier dans le magasin de certificat que ce dernier existe bien.
Note : l’ensemble de ces opérations ne doit pas être réalisé sur le serveur hébergeant l’instance de SQL Server mais sur une machine de confiance. Ainsi les données restent protégées dans SQL Server si l’hôte venait à être compromis.
Enfin, la dernière étape consiste à exporter ce certificat afin de pouvoir l’installer sur tous les postes clients ou le serveur web dans le cas d’une application web.
3.2) Clé de chiffrement de colonne
Le driver SQL Server ADO.NET utilise la clé de chiffrement de colonne pour chiffrer les données avant de les envoyer vers SQL Server et pour les déchiffrer après les avoir récupérées depuis SQL Server. Comme pour la clé principale de colonne, la création de la clé de chiffrement de colonne peut se faire via T-SQL ou SSMS. Leur création est néanmoins plus simple avec SSMS.
Dans l’explorateur d’objets de SSMS, il faut naviguer dans la base de données qui contient les tables à chiffrer puis dans Sécurité et enfin étendre le dossier Clés Always Encrypted afin d’afficher ses deux sous dossiers. Effectuez un clic-droit sur Clés de chiffrement de colonne (Column encryption keys) et sélectionnez Nouvelle clé de chiffrement de colonne (New column encryption key).
Dans la boîte de dialogue Nouvelle clé de chiffrement de colonne, indiquez un nom et choisissez un magasin de certificat puis le certificat à utiliser.
3.3) Chiffrer les données dans une table
Maintenant que nous avons créé une clé principale de colonne et une (ou plusieurs) clé(s) de chiffrement de colonne, nous pouvons créer une table pour stocker des données chiffrées. Il est également possible de chiffrer des données dans une table existante.
Mais avant de se lancer, il faut tout d’abord définir quelles colonnes doivent être chiffrées ou non.
Il faut ensuite connaître quelques règles relatives aux types de données et que l’on souhaite faire comme requêtes sur la base de données. Cela permet de déterminer le type de chiffrement à utiliser.
Si vous devez effectuer une recherche sur la colonne, elle doit utiliser un type de chiffrement déterministe. En revanche, vous ne pourrez effectuer que des recherches strictes (égal à, différent et des jointures). Une recherche avec la clause LIKE n’est pas possible. La clause GROUP BY sur la colonne est également possible.
Le tri sur une colonne chiffrée doit s’effectuer dans l’application car SQL Server va effectuer le tri sur la valeur chiffrée et non la valeur non chiffrée.
Si la colonne ne sera pas utilisée pour chercher des enregistrements, il faut configurer le type de chiffrement sur Aléatoire (Randomized). Il est plus sécurisé mais ne permet pas d’effectuer des recherches, jointures et regroupement de données.
Pour en savoir plus sur les restrictions, c’est par ici :
3.3.1) Création d’une table avec des colonnes chiffrées
Pour créer une table avec des colonnes chiffrées, la syntaxe CREATE TABLE fonctionne parfaitement. Il suffit de lui transmettre quelques paramètres additionnels avec le mot clé ENCRYPTED WITH :
ENCRYPTION_TYPE:RANDOMIZEDouDETERMINISTICALGORITHM: accepte uniquement la valeurAEAD_AES_256_CBC_HMAC_SHA_256COLUMN_ENCRYPTION_KEY: clé utilisée pour encrypter la valeur
Exemple :
CREATE TABLE [dbo].[customers](
[lastname] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK1) NULL,
[firstname] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK1) NULL,
[address1] [nvarchar](50) NULL,
[address2] [nvarchar](50) NULL,
[address3] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[postalcode] [nvarchar](5) NULL,
[birthdate] [datetime2] ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) NULL,
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
3.3.2) Chiffrer les données dans une table existante
Il est également possible de chiffrer les données d’une table existante y compris si elle contient des données. Cela peut se faire soit via SSMS, soit via un script PowerShell. Je présente ici la méthode que j’ai utilisé à savoir le chiffrement via SSMS.
Le chiffrement peut se faire au niveau de la base de données, au niveau d’une seule table ou encore une colonne unique d’une table. Dans tous les cas, l’assistant de SSMS et la configuration sera identique.
Pour configurer le chiffrement des colonnes sur une table, effectuez un clic droit sur le nom de la table puis sélectionnez Chiffrer les colonnes (Encrypt columns). La boite de dialogue s’affiche :
Il suffit alors, pour chaque colonne que l’on souhaite chiffrer, de sélectionner le type de chiffrement et la clé de chiffrement. Puis, de finaliser l’opération avec le bouton « Suivant » jusqu’à arriver à la fin de l’assistant et de cliquer sur « Terminer ».
Les données contenues dans la table vont être chiffrées suivant la configuration définie via l’assistant.
Aucune autre opération n’est nécessaire.
Note : l’assistant propose aussi de générer un script PowerShell pour une exécution ultérieure.
Pour le chiffrement de colonnes via PowerShell, je vous reporte à la documentation Microsoft :
4) Mise en place au sein d’une application
Comme nous l’avons vu précédemment, le chiffrement/déchiffrement des données est réalisé par un driver. Ce qui implique très peu de changement dans le code de votre application.
Aucune modification du code de l’application n’est donc nécessaire si votre application utilise des procédures stockées, des ORMs ou des commandes paramétrées T-SQL. continuera à fonctionner. Sauf si celle-ci utilise des opérations SQL non supportées par Always Encrypted.
Si votre application construit des requêtes SQL de manière dynamique, elles doivent être modifiées pour utiliser les paramètres de requêtes. Ce qui devrait être systématiquement le cas car il s’agit d’une bonne pratique en terme de sécurité pour toute application.
Le dernier changement qu’il faut effectuer est la modification de la chaine de connexion à la base de données. Il convient d’ajouter Column Encryption Setting=enabled. Exemple de la chaine de connexion que j’utilise en production sur le projet en question :
metadata=res://*/Core.CustomersModel.csdl|res://*/Core.CustomersModel.ssdl|res://*/Core.CustomersModel.msl;provider=System.Data.SqlClient;provider connection string="data source=XXX;column encryption setting=enabled;initial catalog=ma_bdd;User Id=XXX;Password=XXX;MultipleActiveResultSets=True;App=EntityFramework"
il s’agit ici d’une chaine de connexion spécifique à Entity Framework, l’ORM du Framework .Net. Mais Always Encrypted fonctionne aussi avec JDBC.
Column Encryption Setting peut également prendre les valeurs suivantes :
Disabled: il n’y a pas de colonnes chiffrées avec Always Encrypted ou de paramètres dans vos requêtes SQLResultSet: il n’y a pas de paramètres dans vos requêtes SQL mais les requêtes retournent des données dont les colonnes sont chiffrées avec Always Encrypted
5) Quelques conseils / remarques
Je vais ici détailler quelques petits points à vérifier et configurer sur un serveur web IIS. Ainsi que détailler quelques messages d’erreurs qui peuvent survenir et donc les messages ne sont parfois pas très explicites.
5.1) Configuration sur un serveur web IIS
Pour qu’une application web utilisant Always Encrypted fonctionne, il faut que votre pool d’application IIS utilisé par votre application ait le droit d’accéder au certificat Always Encrypted. Si ce n’est pas le cas, vous pourrez rencontrer deux erreurs. Soit un time out lors de l’exécution de votre application lorsque celle-ci exécutera une requête SQL qui renvoie des données chiffrées. Soit un message vous indiquant que le certificat n’a pas été trouvé dans le magasin de certificat Windows.
Il faut donc donner accès au pool d’applications IIS à votre certificat :
- Ouvrez le magasin de certificat Windows
- Faites un clic droit sur votre certificat, sélectionnez Toutes les tâches > Gérer les clés privées
- Cliquez sur Ajouter
- Définir l’emplacement sur votre ordinateur
- Saisir IIS AppPool\nom_de_votre_pool_iis dans nom de l’objet
- Cliquez deux fois sur OK
5.2) Messages d’erreurs potentiels
5.2.1) SQL Exception : Operand type clash
Cette erreur survient si le type de votre ou vos colonnes n’est pas compatible avec Always Encrypted ou mal configuré. Par exemple, la collation définie pour votre colonne n’est pas correcte.
Pour ma part, j’ai eu cette erreur lorsque je n’avais pas encore complété la chaine de connexion à la base de données avec Column Encryption Setting=enabled.
5.2.2) Time out
J’ai rencontré cette erreur lorsque le certificat n’était pas installé sur le serveur ou que le pool d’application IIS n’était pas autorisé à accéder au certificat Always Encrypted.
5.2.3) Certificat non trouvé
Le message peut paraître clair mais il peut induire en erreur. Il se peut que votre certificat ne soit pas du tout présent sur le serveur mais la cause peut se trouver également au niveau des droits d’accès du pool d’application IIS sur le certificat Always Encrypted.
Conclusion
Always Encrypted est donc un outil de chiffrement de données, fourni avec SQL Server, très simple et rapide à mettre en place lorsque l’on a toutes les bonnes informations. Il convient, je pense, parfaitement pour des applications sans grande complexité dans les requêtes SQL. En revanche, en raison de ces limitations sur les types de données ou l’utilisation qui sont faites des données, son utilisation n’est pas recommandée pour des applications avec un modèle de données complexe ou si vous devez chiffrer une base de données dans son intégralité.
Pour ce scénario d’utilisation, il convient donc d’utiliser des solutions personnalisées ou le chiffrement transparent des données (TDE) fourni par SQL Server depuis sa version 2008. Ce type de chiffrement pourra faire l’objet d’un second billet.