SQL Server Encryption Hierarchy

--------------------------------------------------------------------------------------------------------------- ------------------------------------------

First Floor:

The service master key, which is automatically generated when needed, such as when the database master key is encrypted. And do not create by hand.

Then

We can still back it up and restore it.

Backup Service Master key to File = ' E:\server_master_key.bak '
Encryption by Password = ' 123456 '; --No with
Go

-------------------------

Restore Service Master key from file = ' E:\server_master_key.bak '
Decryption by password = ' 123456 '; --No with
Go

Second floor:

The database master key, which it wants us to create manually, it is used to encrypt the certificate, the symmetric key, and the asymmetric key.

Create master key encryption by password = ' 123456 ';
Go

-----------------------------

As previously mentioned, the database master key is encrypted by the service master key! Why do I have to create a master key in

Ecryption by password this one! This is because SQL Server provides two ways to access

1, automatic type, simply said that SQL Server know you want to use will automatically open the master key, but it also has bad

Is that it is to be associated with a permission. This means that if your account does not have the proper permissions, it will not open.

2, manual type, that is to say you want to open and close manually.

--------------------------------------

A, remove service master key encryption

ALTER master key drop encryption by service master key;
Go --No with

---------------------------------------

B. Add Service Master key encryption

Open master key decryption by password = ' 1234567 '
Alter MASTER key add encryption by service master key;
Close Master key; --No with
Go

------------------------------

The database master key also supports backup and restore

Backup Master key to File = ' E:\master_key.bak '
Encryption by Password = ' 123456 '; --No with
Go

---------------------------

Restore master key from file = ' E:\master_key.bak '
Decryption by password = ' 123456 '
Encryption by Password = ' 1234567 '--no with but be sure to add encryption by password this item to run correctly.
Go

---------------------------

----------- Summary: Both service master key and master key do not have awith-----------------

Second floor:

The Asymmetric encryption section, which is used to encrypt data.

Create:

Create asymmetric key Asy_key_test
With
Algorithm = rsa_512
Encryption by Password = ' 123456 ';
Go

Delete:

Drop asymmetric key asy_key_test;

Go

Encryption:

INSERT into T (x, Y) VALUES (1,encryptbyasymkey (asymkey_id (' asy_key_test '), ' This is the Clear Text '));
Go

Decrypt:

Select X,cast (
DecryptByAsymKey (asymkey_id (' asy_key_test '), Y,n ' abcdef ') as varchar)
From T;
Go

For asymmetric keys, encryption is done with the public key, and decryption is with the private key.

That is, if we modify the private key part of the key, the decryption error will occur.

Alter asymmetric key Asy_key_test
With
private key (encryption by password = ' abcdef ', decryption by password = ' 123456 ');
Go --be careful with the parentheses behind it !

--------------------------------------------------------------------------------------------------------------- ----

Symmetric encryption section,

Create:

Create symmetric key Sym_key_test
With
Algorithm = Triple_des
Encryption by Password = ' 123456 ';

--Encryption by asymmetric key asy_key_name;
Go

Delete:

Drop symmetric key sym_key_test;

Use:

Open symmetric key sym_key_test decryption by password = ' 123456 '

INSERT into T3 (x, Y) VALUES (1,encryptbykey (Key_GUID (' sym_key_test '), ' This is the text! ');

Select CAST (DecryptByKey (Y) as varchar) fromT3;

Close symmetric key sym_key_test;
Go

--------------- encryption is still to have key_guid, decryption will not be so much --------------------------------------

Certificate section:

The certificate has its own public key and private key, and the expiration time, support backup also restore that is, the certificate is strong portability.

Create:

Create Certificate Ctf_test
Encryption by Password = ' 123456 '
With
Subject = ' Certificate name: Ctf_test ',
start_date = ' 2014-10-16 ',
expiry_date = ' 2015-10-16 '
Go

Backup:

Backup Certificate Ctf_test
To file = ' E:\ctf_public.bak '
With private key (file= ' E:\ctf_private.bak ', decryption by password= ' 123456 ', encryption by password= ' 123456 ');
Go

Restores:

Create Certificate Ctf_test
From file = ' E:\ctf_public.bak '
With private key (file= ' E:\ctf_private.bak ', decryption by password= ' 123456 ', encryption by password= ' 123456 ');
Go

Manage the private key of the certificate:

Delete

Alter certificate Ctf_test

Remove private key;

Increase

Alter certificate Ctf_test
With private key

(file= ' E:\ctf_private.bak ', decryption by password= ' 123456 ', encryption by password= ' 123456 ');
Go

Use

CREATE TABLE T4 (X int, Y varbinary (max));
Go

Encryption:

INSERT into T4 (x, Y) VALUES (1,encryptbycert (cert_id (' ctf_test '), ' This is the text '));
Go

Decrypt:

SELECT *, Cast (DecryptByCert (cert_id (' ctf_test '), Y,n ' 123456 ') as varchar) as [decrypt text]
From T4;
Go

SQL Server Encryption Hierarchy