Example to explain SQL Server encryption features _mssql

Source: Internet
Author: User
Tags decrypt idn create database asymmetric encryption

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.