SQL Server encryption Feature instance

In SQL Server, encryption is hierarchical, and each upper layer provides protection.

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/70/99/wkiol1w583ojd3bxaagj5fpwxlo470.jpg "title=" Enc.png "alt=" Wkiol1w583ojd3bxaagj5fpwxlo470.jpg "/>

Instance:

/** 
SMK (Service Master Key) is generated at SQL Server installation and is created by the Windows DPAPI (Data Protection API) provides protection  
**/ 
< BR style= "Background-color:inherit;" >/** CREATE Database-level DMK (DB Master key), protected by SMK **/ 
create Master Key Encryption by Password=n ' Passw0rd '  
go 

/** database is protected by the DMK  
symmetric encryption algorithm supported :D ES | Triple_des | Triple_des_3key | RC2 | RC4 | rc4_128| DESX | aes_128 | aes_192 | AES_256&NBSP;
Asymmetric encryption algorithm: RSA_512 | rsa_1024 | RSA_2048&NBSP;
take care to avoid using the Rc,desx class algorithm and remove this feature after 2014 &NBSP;
**/&NBSP;
--1. Create an asymmetric key. &NBSP;

Create asymmetric key Asyc_key_enc with algorithm=rsa_1024 encryption by Password=n ' [email protected] ' go

--2. Create a symmetric key.

Create symmetric key Symc_key_enc with algorithm=triple_des encryption by Password=n ' [email protected] ' go

--3. Create a certificate. 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 the three ways above
--4.1 is encrypted by an asymmetric key

Create symmetric key SYMC_KEY_ENC_BYASYC with algorithm=aes_128 encryption by asymmetric key Asyc_key_enc go

--4.2 Encrypted by symmetric key

Open symmetric key Symc_key_enc decryption by password=n ' [email protected] '; 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 encryption/decryption functions to encrypt column data
EncryptByCert () and DecryptByCert ()-use certificates to encrypt and decrypt data
Encryptbyasymkey () and DecryptByAsymKey ()-Encrypt and decrypt data using an asymmetric key
EncryptByKey () and DecryptByKey ()-Encrypt and decrypt data using a symmetric key
Encryptbypassphrase () and Decryptbypassphrase ()-Encrypt and decrypt data using a password field to generate a symmetric key
Note: Data that is encrypted and decrypted must be of type varbinary
**/

--Take EncryptByKey as an example, other similar
--Encryption and decryption of * * * IDN

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

--Add column ENCY_IDN to 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 ' [email protected] '; Update TB Set Ency_idn=encryptbykey (Key_GUID (' symc_key_enc_byasyc '), convert (VARBINARY,IDN));--turn to Varbinary before encrypting Close symmetric key symc_key_enc_byasyc--explicitly close symmetric key go

--Decrypt the encrypted column data

Open symmetric key symc_key_enc_byasyc decryption by asymmetric key asyc_key_enc with password=n ' [email protected] '; Select Idn,ency_idn,convert (Int,decryptbykey (ENCY_IDN)) as decr_idn from TB; Close symmetric key symc_key_enc_byasyc--explicitly close symmetric key go

This article is from "Joe TJ" blog, be sure to keep this source http://joetang.blog.51cto.com/2296191/1680238

SQL Server encryption Feature instance