Verschlüsseln einer Datenspalte

  • Artikel

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

In diesem Artikel wird beschrieben, wie Sie eine Datenspalte mithilfe der symmetrischen Verschlüsselung in SQL Server mithilfe von Transact-SQL verschlüsseln können. Dies wird manchmal als Verschlüsselung auf Spaltenebene oder Verschlüsselung auf Zellenebene bezeichnet.

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

Sicherheit

Berechtigungen

Die folgenden Berechtigungen sind notwendig, um die Schritte unten auszuführen:

  • CONTROL-Berechtigung für die Datenbank
  • CREATE CERTIFICATE-Berechtigung für die Datenbank Nur Windows-Anmeldenamen, SQL Server-Anmeldenamen und Anwendungsrollen können Zertifikate besitzen. Gruppen und Rollen können keine Zertifikate besitzen.
  • ALTER-Berechtigung für die Tabelle
  • Einige Berechtigungen für den Schlüssel und die VIEW DEFINITION-Berechtigung dürfen nicht verweigert worden sein.

Erstellen des Datenbank-Hauptschlüssels

Um die folgenden Beispiele verwenden zu können, müssen Sie über einen Datenbankhauptschlüssel verfügen. Wenn Ihre Datenbank nicht bereits über einen Datenbankhauptschlüssel verfügt, erstellen Sie einen. Stellen Sie eine Verbindung mit Ihrer Datenbank her, und führen Sie das folgende Skript aus, um einen zu erstellen. Achten Sie darauf, ein komplexes Kennwort zu verwenden.

Kopieren Sie das folgende Beispiel, und fügen Sie es in das Abfragefenster ein, das mit der AdventureWorks-Beispieldatenbank verbunden ist. Klicken Sie auf Ausführen.

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<complex password>';

Erstellen Sie immer eine Sicherung Ihres Datenbankhauptschlüssels. Weitere Informationen zum Erstellen von Datenbank-Hauptschlüsseln finden Sie unter CREATE MASTER KEY (Transact-SQL).

Beispiel: Verschlüsseln mit symmetrischer Verschlüsselung und Authentifikator

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel, und fügen Sie es in das Abfragefenster ein, das mit der AdventureWorks-Beispieldatenbank verbunden ist. Klicken Sie auf Ausführen.

    CREATE CERTIFICATE Sales09  
   WITH SUBJECT = 'Customer Credit Card Numbers';  
GO  

CREATE SYMMETRIC KEY CreditCards_Key11  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Sales09;  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(160);   
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 using the  
-- symmetric key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
    , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary  
    , CreditCardID)));  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  
GO  

-- Now list the original card number, the encrypted card number,  
-- and the decrypted ciphertext. If the decryption worked,  
-- the original number will match the decrypted number.  

SELECT CardNumber, CardNumber_Encrypted   
    AS 'Encrypted card number', CONVERT(nvarchar,  
    DecryptByKey(CardNumber_Encrypted, 1 ,   
    HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID))))  
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

Verschlüsseln mit einfacher symmetrischer Verschlüsselung

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel, und fügen Sie es in das Abfragefenster ein, das mit der AdventureWorks-Beispieldatenbank verbunden ist. Klicken Sie auf Ausführen.

     CREATE CERTIFICATE HumanResources037  
   WITH SUBJECT = 'Employee Social Security Numbers';  
GO  

CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE HumanResources037;  
GO  

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  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
GO  

-- Now list the original ID, the encrypted ID, and the   
-- decrypted ciphertext. If the decryption worked, the original  
-- and the decrypted ID will match.  
SELECT NationalIDNumber, EncryptedNationalIDNumber   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

Nächste Schritte