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.