Freigeben über

DECRYPTBYKEY (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-Datenbank in Microsoft Fabric

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

Transact-SQL-Syntaxkonventionen

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 mit DECRYPTBYKEYverwendet 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 } ] )

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 dem wert entsprechen, der während des Datenverschlüsselungsprozesses an ENCRYPTBYKEY übergeben wird. add_authenticator verfügt über einen int-Datentyp.

authenticator

Die Daten, die als Grundlage für die Generierung des Authentifikators verwendet werden. Muss mit dem wert übereinstimmen, der für ENCRYPTBYKEY bereitgestellt wird. Authentifikator ist sysname.

@authenticator

Eine Variable, die Daten für die Generierung durch den Authentifikator enthält. Muss mit dem wert übereinstimmen, der für ENCRYPTBYKEY bereitgestellt wird. @authenticator ist "sysname".

Rückgabetypen

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

Hinweise

DECRYPTBYKEY verwendet einen symmetrischen Schlüssel. Dieser symmetrische Schlüssel muss in der Datenbank bereits geöffnet sein. DECRYPTBYKEY ermöglicht gleichzeitiges Öffnen mehrerer Tasten. Sie müssen den Schlüssel nicht unmittelbar vor der Verschlüsselung der Textentschlüsselung öffnen.

Symmetrische Verschlüsselung und Entschlüsselung funktionieren in der Regel schnell und funktionieren gut für Vorgänge mit großen Datenvolumes.

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.

Beispiele

A. Entschlüsseln mithilfe eines symmetrischen Schlüssels

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 mithilfe eines symmetrischen Schlüssels und eines Authentifizierungshashs

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;

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 sie in der master Datenbank ausgeführt wird; das Ergebnis lautet 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!Password#';

CREATE CERTIFICATE TestEncryptionCertificate
    WITH SUBJECT = 'TestEncryption';

CREATE SYMMETRIC KEY TestEncryptSymmetricKey
    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 AS VARBINARY (8000), @Val AS VARCHAR (30);
SELECT @Val = '000-123-4567';

OPEN SYMMETRIC KEY TestEncryptSymmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;

SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmetricKey'), @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 TestEncryptSymmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

USE [master];

DROP DATABASE TestingDecryptByKey;
GO

Verwandte Inhalte

  • Last updated on