DECRYPTBYKEY (Transact-SQL)

Artikel
04/26/2023

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

Diese Funktion verwendet einen symmetrischen Schlüssel zum Entschlüsseln von Daten.

Hinweis

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

Für dedizierte SQL-Pools in Azure Synapse Analytics sollte die Zwischenspeicherung von Resultsets nicht in Verbindung mit DECRYPTBYKEY verwendet werden. Wenn diese kryptografische Funktion erforderlich ist, stellen Sie sicher, dass Sie die Zwischenspeicherung des Resultsets zum Zeitpunkt der Ausführung (entweder auf Sitzungsebene oder Datenbankebene) deaktiviert haben.

Syntax

  
DecryptByKey ( { 'ciphertext' | @ciphertext }   
    [ , 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

ciphertext
Eine Variable vom Typ varbinary, die Daten enthält, die mit dem Schlüssel verschlüsselt wurden.

@ciphertext
Eine Variable vom Typ varbinary, die Daten enthält, die mit dem Schlüssel verschlüsselt wurden.

add_authenticator
Gibt an, ob durch den ursprünglichen Verschlüsselungsprozess ein Authentifikator zusammen mit dem Klartext einbezogen und verschlüsselt wurde. Muss mit dem Wert übereinstimmen, der während der Datenverschlüsselung an ENCRYPTBYKEY (Transact-SQL) übergeben wurde. add_authenticator verfügt über einen int-Datentyp.

authenticator
Die Daten, die als Grundlage für die Generierung des Authentifikators verwendet werden. Diese müssen mit dem Wert übereinstimmen, der für ENCRYPTBYKEY (Transact-SQL) bereitgestellt wurde. authenticator verfügt über einen sysname-Datentyp.

@authenticator
Eine Variable, die Daten für die Generierung durch den Authentifikator enthält. Diese müssen mit dem Wert übereinstimmen, der für ENCRYPTBYKEY (Transact-SQL) bereitgestellt wurde. @authenticator weist den Datentyp sysname auf.

Rückgabetypen

varbinary mit einer maximalen Größe von 8.000 Byte. DECRYPTBYKEY gibt NULL zurück, wenn der für die Datenverschlüsselung verwendete symmetrische Schlüssel nicht geöffnet ist oder wenn ciphertext den Wert NULL aufweist.

Hinweise

DECRYPTBYKEY verwendet einen symmetrischen Schlüssel. Dieser symmetrische Schlüssel muss in der Datenbank bereits geöffnet sein. DECRYPTBYKEY lässt zu, dass mehrere Schlüssel gleichzeitig geöffnet sind. Der Schlüssel muss nicht unmittelbar vor dem Entschlüsseln von verschlüsseltem Text geöffnet werden.

Die symmetrische Ver- und Entschlüsselung erfolgt in der Regel relativ schnell und eignet sich gut für Vorgänge, die große Datenmengen umfassen.

Der DECRYPTBYKEY-Aufruf muss im Kontext der Datenbank mit dem Verschlüsselungsschlüssel erfolgen. Stellen Sie dies sicher, indem Sie DECRYPTBYKEY aus einem Objekt (z.B. einer Sicht, einer gespeicherten Prozedur oder einer Funktion) aufrufen, das sich in der Datenbank befindet.

Berechtigungen

Dieser symmetrische Schlüssel muss in der aktuellen Sitzung bereits geöffnet sein. Weitere Informationen finden Sie unter OPEN SYMMETRIC KEY (Transact-SQL).

Beispiele

A. Entschlüsseln mit einem symmetrischen Schlüssel

In diesem Beispiel wird verschlüsselter Text mit einem symmetrischen Schlüssel entschlüsselt.

-- 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, EncryptedNationalID   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

B. Entschlüsseln mit einem symmetrischen Schlüssel und einem Authentifizierungshash

In diesem Beispiel werden Daten entschlüsselt, die ursprünglich zusammen mit einem Authentifikator verschlüsselt wurden.

-- 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('SHA1', CONVERT(varbinary, CreditCardID))))   
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

C. Fehler beim Entschlüsseln, wenn nicht im Kontext der Datenbank mit dem Schlüssel

Das folgende Beispiel zeigt, dass DECRYPTBYKEY im Kontext der Datenbank mit dem Schlüssel ausgeführt werden muss. Die Zeile wird nicht entschlüsselt, wenn DECRYPTBYKEY in der Master-Datenbank ausgeführt wird. Das Ergebnis ist NULL.

-- Create the database
CREATE DATABASE TestingDecryptByKey
GO

USE [TestingDecryptByKey]

-- Create the table and view
CREATE TABLE TestingDecryptByKey.dbo.Test(val VARBINARY(8000) NOT NULL);
GO
CREATE VIEW dbo.TestView AS SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Create the key, and certificate
USE TestingDecryptByKey;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ItIsreallyLong1AndSecured!Passsword#';
CREATE CERTIFICATE TestEncryptionCertificate WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmmetricKey WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'It is place for test',
KEY_SOURCE = 'It is source for test' ENCRYPTION BY CERTIFICATE TestEncryptionCertificate;

-- Insert rows into the table
DECLARE @var VARBINARY(8000), @Val VARCHAR(30);
SELECT @Val = '000-123-4567';
OPEN SYMMETRIC KEY TestEncryptSymmmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmmetricKey'), @Val);
SELECT CAST(DecryptByKey(@var) AS VARCHAR(30)), @Val;
INSERT INTO dbo.Test VALUES(@var);
GO

-- Switch to master
USE [Master];
GO

-- Results show the date inserted
SELECT DecryptedVal FROM TestingDecryptByKey.dbo.TestView;

-- Results are NULL because we are not in the context of the TestingDecryptByKey Database
SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Clean up resources
USE TestingDecryptByKey;

DROP SYMMETRIC KEY TestEncryptSymmmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

Use [Master]
DROP DATABASE TestingDecryptByKey;
GO

Siehe auch

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Verschlüsselungshierarchie
Auswählen eines Verschlüsselungsalgorithmus