Example to explain SQL Server encryption features _mssql

Encryption in SQL Server is hierarchical, and each upper level provides protection under. As shown in figure:

Instance:
/**
SMK (Service Master Key) is generated during SQL Server installation and is protected by Windows DPAPI (Data Protection API)
**/

/** CREATE DATABASE level DMK (db Master Key), protected by SMK **/
Create master key encryption by Password=n ' Passw0rd '
Go

Encrypted objects in the/** database are protected by DMK
Supported symmetric encryption algorithms: DES | Triple_des | Triple_des_3key | RC2 | RC4 | rc4_128| DESX | aes_128 | aes_192 | aes_256
Asymmetric encryption algorithm: RSA_512 | rsa_1024 | rsa_2048
Note Avoid using the Rc,desx class algorithm and remove this feature after 2014
**/

--1. Create an asymmetric key.

Create asymmetric key Asyc_key_enc with 
algorithm=rsa_1024 
encryption by Password=n ' Pass@word ' 
go

--2. Create a symmetric key.

Create symmetric key Symc_key_enc with 
algorithm=triple_des 
encryption by Password=n ' Pass@word ' 
go

--3. Create certificates. Certificates can also be protected by other means

Create certificate Cert_enc 
with subject= "certificate for ENC", expiry_date= ' 20990101 ' 
go
 

--4. Symmetric keys can provide encryption protection in three ways above

--4.1 is encrypted by asymmetric key

Create symmetric key symc_key_enc_byasyc with 
algorithm=aes_128 
encryption by asymmetric key Asyc_key_enc Go 

 

--4.2 is encrypted by symmetric key

Open symmetric key Symc_key_enc 
decryption by password=n '
Pass@word '; 
Create symmetric key SYMC_KEY_ENC_BYSYMC with 
algorithm = DES 
encryption by symmetric key Symc_key_enc 
go

--4.3 is encrypted by a certificate

Create symmetric key Symc_key_enc_bycert with 
algorithm =aes_128 
encryption from certificate go 

/** column-level data encryption and decryption. MSSQL provides the following 4 encryption/decryption function to encrypt the column data
EncryptByCert () and DecryptByCert ()-use certificates to encrypt and decrypt data
Encryptbyasymkey () and DecryptByAsymKey ()-Encrypt and decrypt data using asymmetric keys
EncryptByKey () and DecryptByKey ()-Encrypt and decrypt data using symmetric keys
Encryptbypassphrase () and Decryptbypassphrase ()-Creates a symmetric key using the password field to encrypt and decrypt data
Note: Data that is encrypted and decrypted must be of type varbinary
**/

--Take EncryptByKey as an example, others are very similar

--encryption and decryption of the * * # IDN

CREATE TABLE TB (IDN int,name varchar); 
INSERT into TB 
values (123456789, ' BigBrother '), (090807001, ' Spiderman '), (336655789, ' Superman ') 

--New column ENCY_IDN store encrypted data, use symmetric key symc_key_enc_byasyc previously encrypted by asymmetric key to encrypt data

ALTER TABLE TB add ENCY_IDN varbinary (128); 
Go 
open symmetric key symc_key_enc_byasyc 
decryption by asymmetric key Asyc_key_enc with 
password=n '
Pass@word '; 
Update TB 
Set Ency_idn=encryptbykey (Key_GUID (' symc_key_enc_byasyc '), CONVERT (VARBINARY,IDN));-- Before encrypting, turn to varbinary 
close symmetric key symc_key_enc_byasyc--explicitly turn off symmetric key go 

 

--Decrypting the encrypted column data

Open symmetric key symc_key_enc_byasyc 
decryption by asymmetric key Asyc_key_enc with 
password=n '
Pass@word '; 
Select Idn,ency_idn,convert (Int,decryptbykey (ENCY_IDN)) as decr_idn from 
TB; 
Close symmetric key SYMC_KEY_ENC_BYASYC--explicit closing of symmetric keys go
1
<br>

The above is the entire content of this article, I hope to help you learn.