Configurer le chiffrement de colonne sur place avec Transact-SQL

  • Article

S’applique à : SQL Server 2019 (15.x) et versions ultérieures - Windows uniquement Azure SQL Database

Cet article explique comment effectuer des opérations de chiffrement sur place sur des colonnes en utilisant Always Encrypted avec enclaves sécurisées, avec l’instruction ALTER TABLE/ALTER COLUMN. Pour obtenir des informations de base sur le chiffrement sur place et les prérequis généraux, consultez Configurer le chiffrement de colonne sur place en utilisant Always Encrypted avec enclaves sécurisées.

Avec l’instruction ALTER TABLE ou ALTER COLUMN, vous pouvez définir la configuration de chiffrement cible pour une colonne. Lorsque vous exécutez l’instruction, l’enclave sécurisée côté serveur chiffre, rechiffre ou déchiffre les données stockées dans la colonne, en fonction de la configuration actuelle et du chiffrement cible spécifiée dans la définition de colonne dans l’instruction.

  • Si la colonne n’est pas chiffrée actuellement, elle est chiffrée si vous spécifiez la clause ENCRYPTED WITH dans la définition de colonne.
  • Si la colonne est actuellement chiffrée, elle est déchiffrée (convertie en colonne de texte en clair) si vous ne spécifiez pas la clause ENCRYPTED WITH dans la définition de colonne.
  • Si la colonne est actuellement chiffrée, elle est rechiffrée si vous spécifiez la clause ENCRYPTED WITH, et si le type de chiffrement de colonne spécifié ou la clé de chiffrement de colonne spécifiée sont différents du type de chiffrement actuellement utilisé ou de la clé de chiffrement de colonne.

Note

Vous ne pouvez pas combiner des opérations de chiffrement avec d’autres modifications dans une même instruction ALTER TABLE/ALTER COLUMN, à l’exception du changement de la colonne en NULL ou en NOT NULL, ou du changement d’un classement. Par exemple, vous ne pouvez pas chiffrer une colonne ET changer le type de données de la colonne dans une même instruction Transact-SQL ALTER TABLE/ALTER COLUMN. Utilisez deux instructions distinctes.

Comme toute requête utilisant une enclave sécurisée côté serveur, une instruction ALTER TABLE/ALTER COLUMN qui déclenche un chiffrement sur place doit être envoyée sur une connexion avec Always Encrypted et les calculs d’enclave activés.

Le reste de cet article décrit comment déclencher un chiffrement sur place avec l’instruction ALTER TABLE/ALTER COLUMN depuis SQL Server Management Studio. Vous pouvez également émettre l’instruction ALTER TABLE/ALTER COLUMN à partir d’Azure Data Studio ou de votre application.

Note

L’applet de commande Invoke-Sqlcmd du module SqlServer PowerShell et sqlcmd ne prennent pas en charge l’utilisation de ALTER TABLE/ALTER COLUMN pour les opérations de chiffrement sur place.

Effectuer un chiffrement sur place avec Transact-SQL dans SSMS

Conditions préalables

Étapes

  1. Ouvrez une fenêtre de requête avec Always Encrypted et les calculs d’enclave activés dans la connexion de base de données. Pour plus d’informations, consultez Activation et désactivation d’Always Encrypted pour une connexion de base de données.

  2. Dans la fenêtre de requête, émettez l’instruction ALTER TABLE/ALTER COLUMN, en spécifiant la configuration de chiffrement cible pour la colonne que vous souhaitez chiffrer, déchiffrer ou rechiffrer. Si vous chiffrez ou rechiffrer la colonne, utilisez la ENCRYPTED WITH clause. Si votre colonne est de type chaîne (par exemple char, varchar, nchar, nvarchar), il peut également être nécessaire de changer le classement en BIN2.

    Note

    Si votre clé principale de colonne est stockée dans Azure Key Vault, vous serez peut-être invité à vous connecter à Azure.

  3. Effacez le cache du plan pour l’ensemble des lots et des procédures stockées qui accèdent à la table, afin d’actualiser les informations de chiffrement des paramètres.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Note

    Si vous ne supprimez pas le plan de la requête affectée à partir du cache, la première exécution de la requête après le chiffrement peut échouer.

    Utilisez ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ou DBCC FREEPROCCACHE pour effacer soigneusement le cache du plan, car cela peut entraîner une dégradation temporaire des performances de requête. Pour limiter l’impact négatif de l’effacement du cache, vous pouvez supprimer uniquement les plans sélectionnés des requêtes concernées.

  4. Appelez sp_refresh_parameter_encryption afin de mettre à jour les métadonnées pour les paramètres de chaque module (procédure stockée, fonction, affichage, déclencheur) persistants dans sys.parameters et qui peuvent avoir été invalidés lors du chiffrement des colonnes.

Examples

Chiffrement d’une colonne sur place

L’exemple ci-dessous suppose que :

  • CEK1 est une clé de chiffrement de colonne activée pour les enclaves.
  • La colonne SSN est en texte clair et elle utilise le classement de base de données par défaut Latin1 non-BIN2 (par exemple Latin1_General_CI_AI_KS_WS).

L’instruction chiffre la colonne à l’aide SSN du chiffrement déterministe et de la clé de chiffrement de colonne prenant en charge les enclaves. Elle remplace également le classement de base de données par défaut avec le classement BIN2 correspondant (dans la même page de codes).

L’opération est effectuée en ligne (ONLINE = ON). Notez également l’appel à ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, qui recrée les plans des requêtes affectées par la modification de schéma de table.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Rechiffrer une colonne sur place pour changer le type de chiffrement

L’exemple ci-dessous suppose que :

  • La colonne SSN est chiffrée avec un chiffrement déterministe et une clé de chiffrement de colonne activée pour les enclaves, CEK1.
  • Le classement actuel, défini au niveau de la colonne, est Latin1_General_BIN2.

L’instruction ci-dessous rechiffre la colonne avec un chiffrement aléatoire et la même clé (CEK1)

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Rechiffrer une colonne sur place pour effectuer la rotation d’une clé de chiffrement de colonne

L’exemple ci-dessous suppose que :

  • La colonne SSN est chiffrée avec un chiffrement aléatoire et une clé de chiffrement de colonne activée pour les enclaves, CEK1.
  • CEK2 est une clé de chiffrement de colonne activée pour les enclaves (à la différence de CEK1).
  • Le classement actuel, défini au niveau de la colonne, est Latin1_General_BIN2.

L’instruction ci-dessous rechiffre la colonne avec CEK2.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Déchiffrer une colonne sur place

L’exemple ci-dessous suppose que :

  • La colonne SSN est chiffrée avec une clé de chiffrement de colonne activée pour les enclaves.
  • Le classement actuel, défini au niveau de la colonne, est Latin1_General_BIN2.

L’instruction ci-dessous déchiffre la colonne et laisse le classement inchangé. Vous pouvez également choisir de changer le classement. Par exemple, remplacez le classement par un classement non BIN2 dans la même instruction.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Étapes suivantes

Voir aussi