SQL Server encryption and SQL Server encryption

SQL Server encryption and SQL Server encryption

Encryption in SQL Server is hierarchical, and each upper layer provides lower protection.

Instance:
/**
SMK (Service Master Key) is generated when SQL Server is installed. It is protected by Windows DPAPI (Data Protection API ).
**/

/** Create a Database-level DMK (Database Master Key), protected by SMK **/
Create master key encryption by password = n' Passw0rd'
Go

/** The 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
Avoid using RC and DESX algorithms. This function will be deleted 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 a certificate. The certificate can be protected by other methods.

create certificate cert_ENC with subject='certificate for ENC',expiry_date='20990101' go 

-- 4. symmetric keys can be encrypted in the preceding three methods.

-- 4.1 Encryption by asymmetric keys

create symmetric key symc_key_enc_byAsyc with algorithm=AES_128 encryption by asymmetric key asyc_key_enc go 

-- 4.2 Encryption By symmetric keys

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 encrypted by certificate

create symmetric key symc_key_enc_byCert with algorithm =AES_128 encryption by certificate cert_ENC go

/** Column-level data encryption and decryption. MSSQL provides the following 4 pairs of encryption/decryption functions to encrypt column data
EncryptByCert () and DecryptByCert ()-encrypt and decrypt data using certificates
EncryptByAsymKey () and DecryptByAsymKey ()-use asymmetric keys to encrypt and decrypt data
EncryptByKey () and DecryptByKey ()-use symmetric keys to encrypt and decrypt data
EncryptByPassphrase () and DecryptByPassphrase ()-use the password field to generate a symmetric key for data encryption and decryption
Note: The data to be encrypted and decrypted must be of the varbinary type.
**/

-- Take ENCRYPTBYKEY as an example.

-- Encrypt and decrypt the *** ID IDN

create table tb(IDN int,Name varchar(20)); insert into tb values (123456789,'BigBrother'),(090807001,'SpiderMan'),(336655789,'SuperMan') go

-- Added the column Ency_IDN to store encrypted data, and used the symmetric key symc_key_enc_byAsyc encrypted by the asymmetric key to encrypt the data.

Alter table tb add Ency_IDN varbinary (128); go open encryption Ric key symc_key_enc_byAsyc decryption by asy1_ric key asyc_key_enc with password = n'pass @ word '; update tb set Ency_IDN = ENCRYPTBYKEY (KEY_GUID ('symc _ key_enc_byAsyc '), CONVERT (Varbinary, IDN )); -- convert to varbinary close symmetric key symc_key_enc_byAsyc before encryption -- explicitly disable symmetric key go

-- Decrypt encrypted column data

Open encryption Ric key Cipher decryption by asypolicric key asyc_key_enc with password = n' Pass @ word'; select IDN, Ency_IDN, convert (int, DECRYPTBYKEY (Ency_IDN) as Decr_IDN from tb; close your Ric key symc_key_enc_byAsyc -- explicitly disable symmetric key go1 <br>

The above is all the content of this article, hoping to help you learn.