適用対象:
SQL ServerAzure SQL Managed Instance
この関数は、対称キーでデータを復号します。 その対称キーは、証明書で自動的に復号されます。
構文
DECRYPTBYKEYAUTOCERT ( cert_ID , cert_password
, { 'ciphertext' | @ciphertext }
[ , { add_authenticator | @add_authenticator }
[ , { authenticator | @authenticator } ] ] )
引数
cert_ID
対称キーの保護に使用されている証明書の ID。 cert_ID には、int データ型が与えられます。
cert_password
証明書の秘密キーの復号に使用されるパスワード。 データベース マスター キー (DMK) が秘密キーを保護している場合は、 NULL 値を持つことができます。
cert_password には、nvarchar データ型が与えられます。
'ciphertext'
キーで暗号化されたデータの文字列。 ciphertext には、varbinary データ型が与えられます。
@ciphertext
キーで暗号化されたデータを含む、型 varbinary の変数。
add_authenticator
元の暗号化プロセスにプレーンテキストと共に認証子が含まれ、認証子が暗号化されたかどうかを示します。 データ暗号化プロセス中に ENCRYPTBYKEY に渡される値と一致する必要があります。 add_authenticator には、暗号化プロセスで認証子が使用された場合、値 1 が与えられます。 add_authenticator には、int データ型が与えられます。
@add_authenticator
元の暗号化プロセスにプレーンテキストと共に認証子が含まれ、認証子が暗号化されたかどうかを示す変数。 データ暗号化プロセス中に ENCRYPTBYKEY に渡される値と一致する必要があります。 @add_authenticator には、int データ型が与えられます。
authenticator
認証子の生成の基礎として使用されるデータ。 ENCRYPTBYKEY に指定された値と一致する必要があります。 authenticator には、sysname データ型が与えられます。
@authenticator
認証子の生成元となるデータを含む変数。 ENCRYPTBYKEY に指定された値と一致する必要があります。 @authenticator には、sysname データ型が与えられます。
戻り値の型
最大サイズが 8,000 バイトの varbinary。
解説
DECRYPTBYKEYAUTOCERT には、OPEN SYMMETRIC KEY と DECRYPTBYKEY の機能が組み合わされています。 1 つの操作で、最初に非対称キーが復号され、そのキーで暗号化テキストが復号されます。
アクセス許可
対称キーに対する VIEW DEFINITION 権限と、証明書に対する CONTROL 権限が必要です。
例
この例では、DECRYPTBYKEYAUTOCERT が復号コードを簡略化するしくみを確認できます。 このコードは、まだ DMK を持っていない AdventureWorks2025 データベースで実行する必要があります。
<password>を強力なパスワードに置き換えます。
--Create the keys and certificate.
USE AdventureWorks2022;
CREATE MASTER KEY ENCRYPTION BY PASSWORD= '<password>';
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>';
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Sammamish HR', EXPIRY_DATE = '10/31/2035';
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber VARBINARY (128);
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--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_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber,
EncryptedNationalIDNumber AS 'Encrypted ID Number',
CONVERT (NVARCHAR, DecryptByKey(EncryptedNationalIDNumber)) AS 'Decrypted ID Number'
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DECRYPTBYKEYAUTOCERT()
SELECT NationalIDNumber,
EncryptedNationalIDNumber AS 'Encrypted ID Number',
CONVERT (NVARCHAR, DECRYPTBYKEYAUTOCERT(cert_ID('HumanResources037'), NULL, EncryptedNationalIDNumber)) AS 'Decrypted ID Number'
FROM HumanResources.Employee;