ENCRYPTBYKEY (Transact-SQL)

Artikel
05/30/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics

Verschlüsselt Daten mithilfe eines symmetrischen Schlüssels.

Hinweis

Diese Syntax wird vom serverlosen SQL-Pool in Azure Synapse Analytics nicht unterstützt.

Syntax

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

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) und früher finden Sie unter Dokumentation zu früheren Versionen.

Argumente

key_GUID
Bezeichnet die GUID des zum Verschlüsseln von cleartext verwendeten Schlüssels. uniqueidentifier.

'Klartext'
Bezeichnet die mithilfe des Schlüssels zu verschlüsselnden Daten.

@cleartext
Eine Variable vom Typ nvarchar, char, varchar, binary, varbinary oder nchar mit Daten, die mit dem Schlüssel verschlüsselt werden sollen.

add_authenticator
Gibt an, ob ein Authentifikator zusammen mit dem Klartext verschlüsselt wird. Muss beim Verwenden eines Authentifikators 1 sein. int.

@add_authenticator
Gibt an, ob ein Authentifikator zusammen mit dem Klartext verschlüsselt wird. Muss beim Verwenden eines Authentifikators 1 sein. int.

authenticator
Bezeichnet die Daten, aus denen ein Authentifikator abgeleitet wird. sysname.

@authenticator
Eine Variable, die Daten enthält, von denen ein Authentifikator abgeleitet werden soll.

Rückgabetypen

varbinary mit einer maximalen Größe von 8.000 Bytes.

Gibt NULL zurück, wenn der Schlüssel nicht geöffnet, nicht vorhanden oder ein veralteter RC4-Schlüssel ist und die Datenbank nicht mindestens den Kompatibilitätsgrad 110 aufweist.

Gibt NULL zurück, wenn der Wert cleartext NULL entspricht.

Hinweise

EncryptByKey verwendet einen symmetrischen Schlüssel. Dieser Schlüssel muss geöffnet sein. Wenn der symmetrische Schlüssel in der aktuellen Sitzung bereits geöffnet ist, ist es nicht notwendig, ihn im Kontext der Abfrage erneut zu öffnen.

Mit dem Authentifikator kann verhindert werden, dass ganze Werte in verschlüsselten Feldern ersetzt werden. Betrachten Sie dazu beispielsweise die folgende Tabelle mit Daten zu Gehältern.

Employee_ID	Standard_Title	Base_Pay
345	Kopierraumhilfe	Fskj%7^edhn00
697	Leiter der Finanzabteilung	M0x8900f56543
694	EDV-Leiter	Cvc97824%^34f

Ohne die Verschlüsselung aufzubrechen, kann ein böswilliger Benutzer wichtige Informationen aus dem Kontext folgern, in dem der verschlüsselte Text gespeichert wird. Da ein Leiter der Finanzabteilung besser als eine Kopierraumhilfe verdient, folgt daraus, dass der als M0x8900f56543 verschlüsselte Wert größer als der als Fskj%7^edhn00 verschlüsselte Wert sein muss. Wenn dies der Fall ist, kann jeder beliebige Benutzer mit der ALTER-Berechtigung für die Tabelle der Kopierraumhilfe eine Gehaltserhöhung verschaffen, indem die Daten in seinem Base_Pay-Feld durch eine Kopie der im Base_Pay-Feld gespeicherten Daten des Leiters der Finanzabteilung ersetzt werden. Dieser ganzwertige Ersetzungsangriff umgeht die Verschlüsselung insgesamt.

Ganzwertige Ersetzungsangriffe können durch Hinzufügen von Kontextinformationen zum Klartext vereitelt werden, bevor dieser verschlüsselt wird. Diese Kontextinformationen werden zum Überprüfen verwendet, ob die Klartextdaten verschoben wurden.

Wenn ein authenticator-Parameter beim Verschlüsseln der Daten angegeben wird, wird der gleiche Authentifikator zum Entschlüsseln der Daten mithilfe von DecryptByKey benötigt. Zur Verschlüsselungszeit wird ein Hash des Authentifikators zusammen mit dem Klartext verschlüsselt. Zur Entschlüsselungszeit muss der gleiche Authentifikator an DecryptByKey übergeben werden. Stimmen beide nicht überein, schlägt die Entschlüsselung fehl. Dies zeigt an, dass der Wert seit seiner Verschlüsselung verschoben wurde. Es empfiehlt sich, eine Spalte zu verwenden, die einen eindeutigen und unveränderlichen Wert als Authentifikator enthält. Wenn sich der Authentifikatorwert ändert, verlieren Sie möglicherweise den Zugriff auf die Daten.

Die symmetrische Ver- und Entschlüsselung erfolgt relativ schnell und kann auch bei großen Datenmengen verwendet werden.

Wichtig

Das Verwenden der Veschlüsselungsfunktionen von SQL Server zusammen mit der ANSI_PADDING OFF-Einstellung kann aufgrund implizierter Konvertierungen zu Datenverlust führen. Weitere Informationen zu ANSI_PADDING finden Sie unter SET ANSI_PADDING (Transact-SQL).

Beispiele

Die in den folgenden Beispielen veranschaulichte Funktionalität beruht auf Schlüsseln und Zertifikaten, die unter Vorgehensweise: Verschlüsseln einer Spalte mit Daten erstellt werden.

A. Verschlüsseln einer Zeichenfolge mithilfe eines symmetrischen Schlüssels

Im folgenden Beispiel wird der Employee-Tabelle eine Spalte hinzugefügt und dann der Wert der Sozialversicherungsnummer in der NationalIDNumber-Spalte verschlüsselt.

USE AdventureWorks2022;  
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. Verschlüsseln eines Datensatzes zusammen mit einem Authentifizierungswert

USE AdventureWorks2022;  
  
-- 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

Siehe auch

DECRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Verschlüsselungshierarchie
HASHBYTES (Transact-SQL)