SQL Server data encryption and decryption (i)

Are basic examples, more reference official documentation:

1. Transact-SQL functions
2. Database key
3. Certificates
4. Asymmetric key
5. Symmetric key

--drop Table Enrypttestcreate table enrypttest (ID int not null primary key,enryptdata nvarchar (a),) insert INTO enrypttest VALUES (1,n ' 888888 '), (2,n ' 888888 '), (3,n ' 123456 '), (4,n ' A '); select * from Enrypttest;

"Transact-SQL function Encryption"

/*********************************** "Transact-SQL function Encryption" ********************************/--uses TRIPLE DES algorithm (128 Key bit length) to encrypt the data. --Add a Test column ALTER TABLE enrypttest add PassPhrase varbinary (a) ALTER TABLE enrypttest add PASSPHRASE2 varbinary (256)-- For authenticator verification--encryption (Encryptbypassphrase)--https://technet.microsoft.com/zh-cn/library/ms190357%28v=sql.105%29.aspx?f= 255&mspperror=-2147217396update enrypttest Set PassPhrase = Encryptbypassphrase (' Hello.kk ', enryptdata) goupdate Enrypttest Set PassPhrase2 = Encryptbypassphrase (' Hello.kk ')--the passphrase used to generate the symmetric key, enryptdata--the plaintext to be encrypted, and the "mark" indicates whether the validator is encrypted with the plaintext. If the validator is added, then 1, convert (varbinary,id)--data used to derive the authenticator (such as a primary key)) go--decryption (decryptbypassphrase)--https://technet.microsoft.com /zh-cn/library/ms188910%28v=sql.105%29.aspx?f=255&mspperror=-2147217396select convert (nvarchar, Decryptbypassphrase (' Hello.kk ', PassPhrase)) from Enrypttest;goselect convert (Nvarchar,decryptbypassphrase (' Hello.kk '--Generate a passphrase for the decryption key, passphrase2--the encrypted text to decrypt varbinary, add authenticator, convert (varbinary,id))--The authenticator is the mainKey from enrypttest;go--: Without the authenticator, the data is not secure-such as: the password of all IDs to a, like a, other password decryption and a, others may log on to other accounts update enrypttest set PassPhrase = ( Select PassPhrase from Enrypttest where id=4) goselect Id,enryptdata,convert (nvarchar,decryptbypassphrase (' Hello.kk ', PassPhrase)) from enrypttest;--Delete Test column ALTER TABLE enrypttest drop column PassPhrase ALTER TABLE enrypttest drop column PASSP Hrase2go

"Database Master Key"

/*************************************** "Database master key" ***********************************/select * from Sys.key_ Encryptionsselect * from sys.crypt_properties--Creating the Database Master key--https://technet.microsoft.com/zh-cn/library/ms174382 (v= sql.105). Aspxcreate master key encryption by password = N ' [email protected] '-- You must comply with Windows password Policy requirements go--Open the database master key--https://technet.microsoft.com/zh-cn/library/ms186336 (v=sql.105) for the current database. Aspxopen Master key decryption by password = N ' [email protected] ' go--change the properties of the database master key--https://technet.microsoft.com/zh-cn/ library/ms186937 (v=sql.105). Aspxalter master key regenerate with encryption by password = N ' [email protected] ' Alter MASTER key add encryption by Password = n ' [email protected] ' ALTER master key drop encryption by password = n ' [E Mail protected] ' ALTER master key add encryption by service Master Keyalter master key drop encryption by service mast ER key--Export Database master key--https://technet.microsoft.com/zh-cn/library/ms174387 (v=sql.105). Aspxbackup Master key to File = N' D:\XXDB_MasterKey ' encryption by password = N ' [email protected] ' go--Import the database master key from the backup file--https://    technet.microsoft.com/zh-cn/library/ms186336 (v=sql.105). Aspxrestore Master key from file = N ' D:\XXDB_MasterKey ' Decryption by Password = n ' [email protected] ' encryption by password = n ' [email protected] '--new passwordgo- -Removes the master key--https://technet.microsoft.com/zh-cn/library/ms180071 (v=sql.105) from the current database. Aspxdrop Master Keygo

Certificate

/***************************************** "Certificates" *************************************/--certificates and Asymmetric keys encrypt data using the database-level internal public key, and decrypts the data using the database-level internal private key--the encryption by PASSWORD option is not required when the private key is encrypted with the database master key. The private key is encrypted using the database master key-(a bit difficult to understand, and finally given an example) SELECT * FROM Sys.key_encryptionsselect * from Sys.crypt_propertiesselect * FROM Sys.certificatesselect * from enrypttest--Add Test column ALTER TABLE enrypttest add Certificatecol varbinary (max) go--Create certificate--https ://technet.microsoft.com/zh-cn/library/ms187798%28v=sql.105%29.aspx?f=255&mspperror=-2147217396create Certificate mycertificateencryption by Password = n ' [email protected] '--encrypted password with subject = N ' enryptdata Certificate ',--certificate Description start_date = N ' 20150401 ',--certificate Effective Date Expiry_date = n ' 20160401 ';--Certificate expiry date go--encrypt data using the public key of the certificate--https:// msdn.microsoft.com/zh-cn/library/ms188061 (v=sql.105). Aspxupdate enrypttest Set certificatecol = EncryptByCert (CERT_ ID (' mycertificate '), CONVERT (VARCHAR (MAX), enryptdata)) go--decrypts the data with the private key of the certificate--https://msdn.microsoft.com/zh-cn/library/ ms178601 (v=sql.105). Aspxselect *,convert (VARCHAR (MAX), DecryptByCert (cert_id (' mycertificate '), Certificatecol,n ' [email protected] ')) from Enrypttest; go--Modify the private key password--https://msdn.microsoft.com/zh-cn/library/ms189511 (v=sql.105). Aspxalter Certificate mycertificate with Private key (decryption by password = n ' [email protected] ', encryption by password = n ' [email protected] ') go--from Remove the private key from the certificate alter certificate mycertificate remove private keygo--Backup certificate--https://msdn.microsoft.com/zh-cn/library/ ms178578 (v=sql.105). aspxbackup certificate mycertificate to File = N ' D:\mycertificate.cer '--Certificate backup path for encryption with private Key (file = N ' D:\mycertificate_saleskey.pvk ',--used to decrypt the certificate private key file path decryption by password = n ' [email protected] ',--the private key Decrypted password encryption by password = N ' [email protected] ');--Password to encrypt the private key go--Create/Restore certificate creates certificate Mycertificate from  FILE = N ' D:\mycertificate.cer ' with private key (file = N ' D:\mycertificate_saleskey.pvk ', decryption by password = ' [email protected] '); go--Delete the symmetric key--https://msdn.microsoft.com/zh-cn/library/ms182698 (v=sql.105). Aspxdrop Certificate mycertificate;go--delete Test column ALTER TABLE enrypttest drop column Certificatecol;go

"Asymmetric Key"

/*************************************** "Asymmetric key" *************************************/--by default, the private key is protected by the master key of the database select * FROM Sys.key_encryptionsselect * from Sys.crypt_propertiesselect * to Sys.certificatesselect * from Sys.asymmetric_ke Ysselect * from Sys.openkeysselect * from enrypttest--Add test columns ALTER TABLE enrypttest add Asymmetriccol varbinary (max) go--create non- Symmetric key--https://msdn.microsoft.com/zh-cn/library/ms174430 (v=sql.105). Aspxcreate Asymmetric key MyAsymmetric with algorithm=rsa_512 encryption by password= ' [email protected] '; go--Encryption (Encryptbyasymkey)--https:// msdn.microsoft.com/zh-cn/library/ms186950 (v=sql.105). Aspxupdate enrypttest Set asymmetriccol = EncryptByAsymKey ( asymkey_id (' Myasymmetric '), CONVERT (varchar (max), enryptdata)) go--decryption (DecryptByAsymKey)--https:// msdn.microsoft.com/zh-cn/library/ms189507 (v=sql.105). Aspxselect *,convert (varchar (max), DecryptByAsymKey (Asymkey _id (' Myasymmetric '), Asymmetriccol,n ' [email protected] ')) from enrypttestgo--change asymmetric key properties--https:// Msdn.microsoft. com/zh-cn/library/ms187311 (v=sql.105). aspx--Change the private key password alter asymmetric key myasymmetric with private key (decryption by password = n ' [email protected] ',--original private key password encryption by password = n ' [email protected] ');--New private key password go--Delete private key, only Leave public key--if an asymmetric key is mapped to an extensible Key Management (EKM) key on an EKM device and the Remove PROVIDER key option is not specified, the key is removed from the database, but it is not removed from the device. A warning is issued. Alter asymmetric key Myasymmetric remove private key;go--Remove asymmetric key--https://msdn.microsoft.com/zh-cn/library/ms188389 ( v=sql.105). Aspxdrop symmetric key myasymmetric; go--delete test column ALTER TABLE enrypttest drop column Asymmetriccolgo

"Symmetric key"

/*************************************** "Symmetric key" *************************************/--, also known as single-key encryption, uses the encryption method of single-key cipher system, The same key can be used for both encryption and decryption of information.--asymmetric key consumes more system performance, generally uses symmetric key to encrypt data, use asymmetric key to protect symmetric key SELECT * FROM Sys.key_encryptionsselect * Sys.crypt_propertiesselect * from Sys.certificatesselect * from Sys.asymmetric_keysselect * from Sys.openkeysselect * fro M Sys.symmetric_keysselect * from enrypttest--Add Test column ALTER TABLE enrypttest add Symmetriccol varbinary (max) go--Create a symmetric key--https://msdn.microsoft.com/zh-cn/library/ms188357 (v=sql.105). Aspxcreate symmetric key mysymmetric--symmetric key with password encryption with algorithm=aes_128 encryption by password= ' [email protected] '; gocreate symmetric key mysymmetric--symmetric key encrypted with asymmetric key with algorithm=aes_128 encryption by asymmetric key myasymmetricgo--open symmetric key (open to effectively use cryptographic decryption function) --https://msdn.microsoft.com/zh-cn/library/ms190499 (v=sql.105). Aspxopen symmetric key MySymmetric decryption by Password= ' [email protected] '; Goopen symmetric key mysymmetric decryption by asymmetric key MyAsymmetric with password= ' [email protected]ic '; go--encrypted data--https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105 %29.aspx?f=255&mspperror=-2147217396update enrypttest Set symmetriccol = EncryptByKey (Key_GUID (' MySymmetric '), CONVERT (varchar (max), enryptdata)) go--decrypts the data--https://technet.microsoft.com/zh-cn/library/ms181860 (v=sql.105). Aspxselect *,convert (varchar (max), convert (varchar (max), DecryptByKey (Symmetriccol))) from enrypttestgo--Close the symmetric key, Or close all symmetric keys that are open in the current session--https://msdn.microsoft.com/zh-cn/library/ms177938%28v=sql.105%29.aspx?f=255&mspperror= -2147217396--close all symmetric keys; Close symmetric key mysymmetric;go--alter symmetric add or remove new encryption methods (such as adding more than one password, any one is available)--https://msdn.microsoft.com/ zh-cn/library/ms189440 (v=sql.105). Aspxopen symmetric key Mysymmetric decryption by password= ' [email protected] ' Alter symmetric key mysymmetric add encryption by password = ' [email protected] '--new another passwordclose symmetr IC Key Mysymmetric;open symmetric key Mysymmetric DECRYption by password= ' [email protected] '; --use New passwordselect convert (varchar (max), convert (varchar (max), DecryptByKey (Symmetriccol))) from Enrypttestalter symmetric key mysymmetric drop encryption by password = ' [email protected] '--drop the new Passwordclo SE symmetric key mysymmetric;go--remove the symmetric key--https://msdn.microsoft.com/zh-cn/library/ms182698 (v=sql.105). Aspxdrop Symmetric key mysymmetric;go--delete test column ALTER TABLE enrypttest drop column Symmetriccolgo

Example

--Test Data/*drop Certificate Mycertificate;godrop Master keygodrop table Enrypttestgo*/create table enrypttest (id int not nul L primary key,enryptdata nvarchar (), goinsert into Enrypttestvalues (1,n ' 888888 '), (2,n ' 888888 '), (3,n ' 123456 '), (4, N ' A '); Goselect * from Enrypttest;alter table enrypttest add certificatecol varbinary (max) go--creating master key create Master key Encry ption by Password = N ' [email protected] ' Go/*key_idthumbprintcrypt_typecrypt_type_desccrypt_ Property-----------------------------------------------------------------1010x01eskmencryption by MASTER Key0x7a2feda8139f1de8f3377424c120dbdb8e1f7eaaec1bbd73e72ac04f5ceecbfac15fc7e130ca1756281ea0d8e6997f44101nulleskpencryptio N by password0x61d8f28f12ce4a1247f91e0ed828f2e937206e5d69b0754ee76756567ab428cbd5b54b76bcd1fc15e5e12202dba4e187* /--Create the certificate, because the master key is encrypted by default, no password is required here Create certificate Mycertificatewith subject = N ' Enryptdata certificate ', start_date = N ' 20150401 ', expiry_date = n ' 20160401 '; go--encryption and decryption are automatically encrypted with the service master key. Even using "Close master key" does not work with update ENrypttest Set certificatecol = EncryptByCert (cert_id (' mycertificate '), CONVERT (VARCHAR (MAX), enryptdata)) goselect *, CONVERT (VARCHAR (MAX), DecryptByCert (cert_id (' mycertificate '), Certificatecol)) from enrypttest;go--now delete "Service Master key" ALTER master key drop encryption by service master keygo--again querying data, not decrypted. The Select *,convert (VARCHAR (MAX), DecryptByCert (cert_id (' mycertificate '), Certificatecol)) are not automatically decrypted with the master key encryption from enrypttest;go--you need to explicitly open the master key, use the master key password encryption to decrypt open master key decryption by password = N ' [email protected] ' go--and then query the data, It's decrypted. Select *,convert (VARCHAR (MAX), DecryptByCert (cert_id (' mycertificate '), Certificatecol)) from Enrypttest; go--finally closes the master key, close master key go--View master key, less "encryption by master key", without the master key to encrypt, but use the password to encrypt the select * from Sys.key_ Encryptions/*key_idthumbprintcrypt_typecrypt_type_desccrypt_ Property-----------------------------------------------------------------101NULLESKPENCRYPTION by password0xfb7d746c3cf0471147ecd710b1173b69a966421089fbb813bcf108e1ed90574f5c5f0998ba44f48869e05e9093bc59e6*/--Delete test Data drop certificate Mycertificate;godrop Master Keygodrop table Enrypttestgo Reference: Service Master key: https://msdn.microsoft.com/ zh-cn/library/ms189060 (v=sql.90). aspx

Illustration of 2 photos:

SQL Server data encryption and decryption (i)