Dogodek
Pridobite potrdilo v storitvi Microsoft Fabric – brezplačno!
19. nov., 23h - 10. dec., 23h
Za omejen čas ekipa Microsoft Fabric Community ponuja brezplačne kupone za izpit DP-600.
Pripravite se zdajTa brskalnik ni več podprt.
Izvedite nadgradnjo na Microsoft Edge, če želite izkoristiti vse prednosti najnovejših funkcij, varnostnih posodobitev in tehnične podpore.
Applies to: SQL Server Azure SQL Managed Instance
This function decrypts data with a symmetric key. That symmetric key automatically decrypts with a certificate.
Transact-SQL syntax conventions
DecryptByKeyAutoCert ( cert_ID , cert_password
, { 'ciphertext' | @ciphertext }
[ , { add_authenticator | @add_authenticator }
[ , { authenticator | @authenticator } ] ] )
cert_ID
The ID of the certificate used to protect the symmetric key. cert_ID has an int data type.
cert_password
The password used to encrypt the private key of the certificate. Can have a NULL
value if the database master key protects the private key. cert_password has an nvarchar data type.
'ciphertext'
The string of data encrypted with the key. ciphertext has a varbinary data type.
@ciphertext
A variable of type varbinary containing data encrypted with the key.
add_authenticator
Indicates whether the original encryption process included, and encrypted, an authenticator together with the plaintext. Must match the value passed to ENCRYPTBYKEY (Transact-SQL) during the data encryption process. add_authenticator has a value of 1 if the encryption process used an authenticator. add_authenticator has an int data type.
@add_authenticator
A variable indicating whether the original encryption process included, and encrypted, an authenticator together with the plaintext. Must match the value passed to ENCRYPTBYKEY (Transact-SQL) during the data encryption process. @add_authenticator has an int data type.
authenticator
The data used as the basis for the generation of the authenticator. Must match the value supplied to ENCRYPTBYKEY (Transact-SQL). authenticator has a sysname data type.
@authenticator
A variable containing data from which an authenticator generates. Must match the value supplied to ENCRYPTBYKEY (Transact-SQL). @authenticator has a sysname data type.
varbinary, with a maximum size of 8,000 bytes.
DECRYPTBYKEYAUTOCERT
combines the functionality of OPEN SYMMETRIC KEY
and DECRYPTBYKEY
. In a single operation, it first decrypts a symmetric key, and then decrypts encrypted ciphertext with that key.
Requires VIEW DEFINITION
permission on the symmetric key, and CONTROL
permission on the certificate.
This example shows how DECRYPTBYKEYAUTOCERT
can simplify decryption code. This code should run on an AdventureWorks2022
database that does not already have a database master key.
--Create the keys and certificate.
USE AdventureWorks2022;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Sammamish HR',
EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
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;
OPEN SYMMETRIC KEY (Transact-SQL)
ENCRYPTBYKEY (Transact-SQL)
DECRYPTBYKEY (Transact-SQL)
Encryption Hierarchy
Dogodek
Pridobite potrdilo v storitvi Microsoft Fabric – brezplačno!
19. nov., 23h - 10. dec., 23h
Za omejen čas ekipa Microsoft Fabric Community ponuja brezplačne kupone za izpit DP-600.
Pripravite se zdaj