DECRYPTBYKEYAUTOASYMKEY (Transact-SQL)
SQL Server 2008
Entschlüsselt Text mithilfe eines symmetrischen Schlüssels, der automatisch mithilfe eines asymmetrischen Schlüssels entschlüsselt wird.
Im folgenden Beispiel wird gezeigt, wie mit DecryptByKeyAutoAsymKey Code vereinfacht werden kann, mit dem eine Entschlüsselung ausgeführt wird. Der Code sollte für eine neu installierte Kopie der AdventureWorks-Datenbank ausgeführt werden.
--Create the keys and certificate.
USE AdventureWorks;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
CREATE ASYMMETRIC KEY SSN_AKey
WITH ALGORITHM = RSA_2048 ;
GO
CREATE SYMMETRIC KEY SSN_Key_02 WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY SSN_AKey;
GO
--
--Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber2 varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_02
DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber2
= EncryptByKey(Key_GUID('SSN_Key_02'), NationalIDNumber);
GO
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key.
--2. Decrypt the data.
--3. Close the symmetric key.
OPEN SYMMETRIC KEY SSN_Key_02
DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
SELECT NationalIDNumber, EncryptedNationalIDNumber2
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber2))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--OPTION TWO, using DecryptByKeyAutoAsymKey()
SELECT NationalIDNumber, EncryptedNationalIDNumber2
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey') , NULL ,EncryptedNationalIDNumber2))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
GO
