Artikel
09/24/2010

ENCRYPTBYKEY (Transact-SQL)

Verschlüsselt Daten mithilfe eines symmetrischen Schlüssels.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

key_GUID
Bezeichnet den GUID des zum Verschlüsseln von cleartext verwendeten Schlüssels. uniqueidentifier.
'cleartext'
Bezeichnet die mithilfe des Schlüssels zu verschlüsselnden Daten.
@cleartext
Eine Variable vom Typ nvarchar, char, varchar, binary, varbinary oder nchar, die Daten enthält, die mit dem Schlüssel verschlüsselt werden sollen.
add_authenticator
Gibt an, ob ein Authentifikator mit cleartext verschlüsselt werden soll. Muss beim Verwenden eines Authentifikators 1 sein. int.
@add_authenticator
Gibt an, ob ein Authentifikator mit cleartext verschlüsselt werden soll. 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.

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 verhindert 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 wird empfohlen, eine Spalte mit einem eindeutigen und unveränderlichen Wert als Authentifikator zu verwenden. Wenn sich der Wert des Authentifikators ändert, geht der Zugriff auf die Daten u. U. verloren.

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 AdventureWorks;
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 AdventureWorks;

-- 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