ENCRYPTBYKEY (Transact-SQL)ENCRYPTBYKEY (Transact-SQL)

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Chiffre les données à l'aide d'une clé symétrique.Encrypts data by using a symmetric key.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

  
EncryptByKey ( key_GUID , { 'cleartext' | @cleartext }  
    [, { add_authenticator | @add_authenticator }  
     , { authenticator | @authenticator } ] )  

ArgumentsArguments

key_GUIDkey_GUID
GUID de la clé à utiliser pour chiffrer cleartext.Is the GUID of the key to be used to encrypt the cleartext. uniqueidentifier.uniqueidentifier.

'cleartext''cleartext'
Données à chiffrer à l'aide de la clé.Is the data that is to be encrypted with the key.

@cleartext
Variable de type nvarchar, char, varchar, binary, varbinary ou nchar qui contient des données à chiffrer avec la clé.Is a variable of type nvarchar, char, varchar, binary, varbinary, or nchar that contains data that is to be encrypted with the key.

add_authenticatoradd_authenticator
Indique si un authentificateur sera chiffré avec cleartext.Indicates whether an authenticator will be encrypted together with the cleartext. Sa valeur doit être égale à 1 si vous utilisez un authentificateur.Must be 1 when using an authenticator. int.int.

@add_authenticator
Indique si un authentificateur sera chiffré avec cleartext.Indicates whether an authenticator will be encrypted together with the cleartext. Sa valeur doit être égale à 1 si vous utilisez un authentificateur.Must be 1 when using an authenticator. int.int.

authenticatorauthenticator
Données à partir desquelles un authentificateur peut être dérivé.Is the data from which to derive an authenticator. sysname.sysname.

@authenticator
Variable contenant les données à partir desquelles l'authentificateur sera dérivé.Is a variable that contains data from which to derive an authenticator.

Types de retourReturn Types

varbinary d’une taille maximale de 8 000 octets.varbinary with a maximum size of 8,000 bytes.

Retourne NULL si la clé n'est pas ouverte, si elle n'existe pas ou s'il s'agit d'une clé RC4 déconseillée et que la base de données n'est pas au niveau de compatibilité 110 ou supérieur.Returns NULL if the key is not open, if the key does not exist, or if the key is a deprecated RC4 key and the database is not in compatibility level 110 or higher.

Retourne NULL si la valeur cleartext est NULL.Returns NULL if the cleartext value is NULL.

NotesRemarks

EncryptByKey utilise une clé symétrique.EncryptByKey uses a symmetric key. Cette clé doit être ouverte.This key must be open. Si la clé symétrique est déjà ouverte dans la session en cours, il n'est pas nécessaire de l'ouvrir à nouveau dans le contexte de la requête.If the symmetric key is already open in the current session, you do not have to open it again in the context of the query.

L'authentificateur vous permet d'empêcher la substitution en valeurs entières des champs chiffrés.The authenticator helps you deter whole-value substitution of encrypted fields. Par exemple, considérons la table suivante des données de salaires du personnel :For example, consider the following table of payroll data.

Employee_IDEmployee_ID Standard_TitleStandard_Title Base_PayBase_Pay
345345 Copy Room AssistantCopy Room Assistant Fskj%7^edhn00Fskj%7^edhn00
697697 Chief Financial OfficerChief Financial Officer M0x8900f56543M0x8900f56543
694694 Data Entry SupervisorData Entry Supervisor Cvc97824%^34fCvc97824%^34f

Sans même casser le code de chiffrement, un utilisateur malveillant peut déduire des informations importantes à partir du contexte dans lequel le texte chiffré est stocké.Without breaking the encryption, a malicious user can infer significant information from the context in which the ciphertext is stored. Comme à la fonction de Chief Financial Officer correspond un salaire plus élevé qu'à celle de Copy Room Assistant, la valeur chiffrée en M0x8900f56543 doit être supérieure à la valeur chiffrée en Fskj%7^edhn00.Because a Chief Financial Officer is paid more than a Copy Room Assistant, it follows that the value encrypted as M0x8900f56543 must be greater than the value encrypted as Fskj%7^edhn00. S'il en est ainsi, tout utilisateur disposant de l'autorisation ALTER sur la table peut donner une augmentation à la fonction Copy Room Assistant en remplaçant les données du champ Base_Pay par une copie des données stockées dans le champ Base_Pay de la fonction Chief Financial Officer.If so, any user with ALTER permission on the table can give the Copy Room Assistant a raise by replacing the data in his Base_Pay field with a copy of the data stored in the Base_Pay field of the Chief Financial Officer. Cette attaque par substitution complète de valeur outrepasse complètement le chiffrement.This whole-value substitution attack bypasses encryption altogether.

Vous pouvez déjouer les attaques par substitution de ce type en ajoutant des informations contextuelles au texte brut avant de le chiffrer.Whole-value substitution attacks can be thwarted by adding contextual information to the plaintext before encrypting it. Ces informations contextuelles sont utilisées pour vérifier que les données en texte brut n'ont pas été déplacées.This contextual information is used to verify that the plaintext data has not been moved.

Si un authentificateur est précisé lors du chiffrement des données, il faut utiliser le même pour déchiffrer les données à l'aide de DecryptByKey.If an authenticator parameter is specified when data is encrypted, the same authenticator is required to decrypt the data by using DecryptByKey. Au moment du chiffrement, un hachage de l'authentificateur est chiffré avec le texte brut.At encryption time, a hash of the authenticator is encrypted together with the plaintext. Au moment du déchiffrement, vous devez passer le même authentificateur à DecryptByKey.At decryption time, the same authenticator must be passed to DecryptByKey. Si les deux ne correspondent pas, le déchiffrement échoue.If the two do not match, the decryption will fail. Cela indique que la valeur a été déplacée depuis qu'elle a été chiffrée.This indicates that the value has been moved since it was encrypted. Nous recommandons l'utilisation d'une colonne contenant une valeur unique et constante comme authentificateur.We recommend using a column containing a unique and unchanging value as the authenticator. Si la valeur de l'authentificateur change, vous risquez de perdre l'accès aux données.If the authenticator value changes, you might lose access to the data.

Le chiffrement et le déchiffrement symétriques sont relativement rapides et adaptés à la manipulation de grandes quantités de données.Symmetric encryption and decryption is relatively fast, and is suitable for working with large amounts of data.

Important

L'utilisation des fonctions de chiffrement SQL ServerSQL Server conjointement au paramètre ANSI_PADDING OFF peut entraîner la perte de données en raison de conversions implicites.Using the SQL ServerSQL Server encryption functions together with the ANSI_PADDING OFF setting could cause data loss because of implicit conversions. Pour plus d’informations sur ANSI_PADDING, consultez SET ANSI_PADDING (Transact-SQL).For more information about ANSI_PADDING, see SET ANSI_PADDING (Transact-SQL).

ExemplesExamples

La fonctionnalité illustrée dans les exemples suivants utilise les clés et les certificats créés dans Procédure : Chiffrer une colonne de données.The functionality illustrated in the following examples relies on keys and certificates created in How To: Encrypt a Column of Data.

A.A. Chiffrer une chaîne à l'aide d'une clé symétriqueEncrypting a string with a symmetric key

L'exemple de code suivant ajoute une colonne à la table Employee, puis chiffre la valeur du numéro de sécurité sociale stockée dans la colonne NationalIDNumber.The following example adds a column to the Employee table, and then encrypts the value of the Social Security number that is stored in column NationalIDNumber.

USE AdventureWorks2012;  
GO  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
  
-- Encrypt the value in column NationalIDNumber with symmetric key  
-- SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber  
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO  

B.B. Chiffrer un enregistrement avec une valeur d'authentificationEncrypting a record together with an authentication value

USE AdventureWorks2012;  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
    DECRYPTION BY CERTIFICATE Sales09;  
  
-- Encrypt the value in column CardNumber with symmetric   
-- key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'),   
    CardNumber, 1, CONVERT( varbinary, CreditCardID) );  
GO  

Voir aussiSee Also

DECRYPTBYKEY (Transact-SQL) DECRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL) CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL) ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL) DROP SYMMETRIC KEY (Transact-SQL)
Hiérarchie de chiffrement Encryption Hierarchy
HASHBYTES (Transact-SQL)HASHBYTES (Transact-SQL)