Een kolom met gegevens versleutelen

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-database in Microsoft Fabric

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics-

In dit artikel wordt beschreven hoe u een kolom met gegevens versleutelt met behulp van symmetrische versleuteling in SQL Server met behulp van Transact-SQL. Dit wordt ook wel versleuteling op kolomniveau of versleuteling op celniveau genoemd.

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2025 of AdventureWorksDW2025 voorbeelddatabase, die je kunt downloaden van de Azure Data SQL Samples Repository GitHub-repository.

Security

Permissions

De volgende machtigingen zijn nodig om de onderstaande stappen uit te voeren:

  • CONTROL machtiging voor de database.
  • CREATE CERTIFICATE machtiging voor de database. Alleen Windows-aanmeldingen, SQL Server-aanmeldingen en toepassingsrollen kunnen eigenaar zijn van certificaten. Groepen en rollen kunnen geen eigen certificaten hebben.
  • ALTER toestemming op de tabel.
  • Bepaalde machtigingen voor de sleutel en de VIEW DEFINITION machtiging mogen niet zijn geweigerd.

Databasehoofdsleutel maken

Als u de volgende voorbeelden wilt gebruiken, moet u een databasehoofdsleutel hebben. Als uw database nog geen databasehoofdsleutel heeft, maakt u er een. Als u er een wilt maken, maakt u verbinding met uw database en voert u het volgende script uit. Vervang <password> door een complex wachtwoord.

Kopieer en plak het volgende voorbeeld in het queryvenster dat is verbonden met de AdventureWorks voorbeelddatabase. Selecteer Uitvoeren.

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

Maak altijd een back-up van uw databasehoofdsleutel. Zie (Transact-SQL) voor meer informatie over databasehoofdsleutelsCREATE MASTER KEY.

Voorbeeld: Versleutelen met symmetrische versleuteling en verificator

  1. Maak in Objectverkennerverbinding met een exemplaar van de Database Engine.

  2. Selecteer op de Standaard-balk Nieuwe query.

  3. Kopieer en plak het volgende voorbeeld in het queryvenster dat is verbonden met de AdventureWorks voorbeelddatabase. Selecteer Uitvoeren.

    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

Versleutelen met eenvoudige symmetrische versleuteling

  1. Maak in Objectverkennerverbinding met een exemplaar van de Database Engine.

  2. Selecteer op de Standaard-balk Nieuwe query.

  3. Kopieer en plak het volgende voorbeeld in het queryvenster dat is verbonden met de AdventureWorks voorbeelddatabase. Selecteer Uitvoeren.

     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

Volgende stappen

  • Last updated on