SQL Server data encryption and decryption (1), SQL server encryption and decryption

Source: Internet
Author: User

SQL Server data encryption and decryption (1), SQL server encryption and decryption

Are basic examples. For more information, see the official documentation:

1. Transact-SQL Functions
2. Database key
3. Certificate
4. Asymmetric Key
5. symmetric key


--drop table EnryptTestcreate table EnryptTest(id int not null primary key,EnryptData nvarchar(20),)insert into EnryptTestvalues(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');select * from EnryptTest;

[Transact-SQL function encryption]

/*********************************** [Transact-SQL function encryption: *******************************/-- use TRIPLE DES algorithm (128 key bit length). -- Add Test column alter table EnryptTest add PassPhrase varbinary (256) alter table EnryptTest add PassPhrase2 varbinary (256) -- used for validator verification -- encryption (EncryptByPassPhrase) -- https://technet.microsoft.com/zh-cn/library/ms190357%28v= SQL .105%29.aspx? F = 255 & MSPPError =-2147217396 update EnryptTest set PassPhrase = EncryptByPassPhrase ('Hello. kk ', EnryptData) goupdate EnryptTest set PassPhrase2 = EncryptByPassPhrase ('Hello. kk '-- a phrase used to generate a symmetric key, EnryptData -- plaintext to be encrypted, 1 -- indicates whether the validators are encrypted with plaintext. If the validators are added, 1, convert (varbinary, id) -- data used to derive the validators (such as the primary key) go -- decryption (DecryptByPassPhrase) -- https://technet.microsoft.com/zh-cn/library/ms188910%28v= SQL .105%29.aspx? F = 255 & MSPPError =-2147217396 select convert (nvarchar, DecryptByPassphrase ('Hello. kk ', PassPhrase) from EnryptTest; goselect convert (nvarchar, DecryptByPassphrase ('Hello. kk '-- generate the pass phrase for decryption key, PassPhrase2 -- encrypted text varbinary, 1 -- add validators, convert (varbinary, id) to be decrypted ))) -- The validators are the primary key from EnryptTest; go -- Note: If the validators are not used, the data is not secure. -- for example, you can change the passwords of all IDs to be the same as those of, the decryption of other passwords is the same as that of A. Other users may log on to another account. update EnryptTest set PassPhrase = (select PassPhrase from EnryptTest where id = 4) goselect id, EnryptData, convert (nvarchar, decryptByPassphrase ('Hello. kk ', PassPhrase) from EnryptTest; -- delete the test column alter table EnryptTest drop column PassPhrase alter table EnryptTest drop column PassPhrase2go

[Database master key]

/*************************************** [Database master key ]*********************************** /select * from sys. key_encryptionsselect * from sys. crypt_properties -- create a database master key -- https://technet.microsoft.com/zh-cn/library/ms174382 (v = SQL .105 ). aspxcreate master key encryption by password = n' Hello @ mymasterkey' -- must comply with Windows password Policy requirements go -- open the database master key for the current database -- https://technet.microsoft.com/zh-cn/library/ms186336 (v = SQL .105 ). aspxopen master key decryption by password = n' Hello @ mymasterkey' go -- change the attributes 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' Hello @ mymasterkey' alter master key add encryption by password = n' Hello @ kk 'alter master key drop encryption by password = n' Hello @ kk 'alter master key add encryption by service master keyalter master key drop encryption by service master 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' Hello @ mymasterkey' 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' Hello @ mymasterkey' encryption by password = n' Hello @ mymasterkey' -- New Passwordgo -- remove the master key from the current database -- https://technet.microsoft.com/zh-cn/library/ms180071 (v = SQL .105 ). aspxdrop master keygo

[Certificate]

/*************************************** ** [Certificate ]*********************************** **/-- certificates and asymmetric keys use database-level internal public keys to encrypt data, and use the database-level internal private key to decrypt data-When the database master key is used to encrypt the private key, the encryption by password option is not required. The private key uses the database master key for encryption-(this is a bit difficult to understand, and an example is provided) 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 =-2147217396 create certificate Mycertificateencryption by password = n' Hello @ Mycertificate '-- encrypt the password with subject = n' EnryptData certificate ', -- certificate description start_date = N '20140901', -- certificate validity date expiry_date = N '20160901'; -- certificate expiration date go -- encrypt data using the certificate's public key -- https://msdn.microsoft.com/zh-cn/library/ms188061 (v = SQL .105 ). aspxupdate EnryptTest set CertificateCol = EncryptByCert (CERT_ID ('mycertificate'), CONVERT (VARCHAR (MAX), EnryptData )) go -- decrypt data with the certificate's private key -- https://msdn.microsoft.com/zh-cn/library/ms178601 (v = SQL .105 ). aspxselect *, CONVERT (VARCHAR (MAX), DECRYPTBYCERT (CERT_ID ('mycertificate'), CertificateCol, n' Hello @ Mycertificate ') from EnryptTest; go -- modify private key password -- https://msdn.microsoft.com/zh-cn/library/ms189511 (v = SQL .105 ). aspxalter certificate mycertificate with private key (decryption by password = n'hello @ Mycertificate ', encryption by password = n' Hello @ Mycertificate ') go -- 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'hello @ Mycertificate ', -- encryption by password = n'hello @ Mycertificate '); -- encrypt the private key. go -- create/restore the certificate. create certificate mycertificate from file = n'd: \ mycertificate. cer 'with private key (file = n'd: \ mycertificate_saleskey.pvk', decryption by password = 'Hello @ Mycertificate '); go -- delete symmetric key -- https://msdn.microsoft.com/zh-cn/library/ms182698 (v = SQL .105 ). aspxdrop certificate Mycertificate; go -- delete the test column alter table EnryptTest drop column CertificateCol; go

[Asymmetric key]

/*************************************** [Asymmetric key ]************************************ */-- by default, the private key is protected by the database master key. select * from sys. key_encryptionsselect * from sys. crypt_propertiesselect * from sys. certificatesselect * from sys. asypolicric_keysselect * from sys. openkeysselect * from EnryptTest -- add Test column alter table EnryptTest add asyuncriccol varbinary (max) go -- create asymmetric keys -- https://msdn.microsoft.com/zh-cn/library/m S174430 (v = SQL .105 ). aspxcreate asypolicric key myasypolicric with algorithm = rsa_512 encryption by password = 'Hello @ myasypolicric '; go -- encryption (EncryptByAsymKey) -- https://msdn.microsoft.com/ZH-CN/library/ms186950 (v = SQL .105 ). aspxupdate EnryptTest set asypolicriccol = EncryptByAsymKey (asymkey_id ('myasypolicric '), convert (varchar (max), EnryptData) go -- decryption (DecryptByAsymKey) -- https://msdn.microsoft.com/ZH-CN/librar Y/ms189507 (v = SQL .105 ). aspxselect *, convert (varchar (max), DecryptByAsymKey (asymkey_id ('myasypolicric '), asyuncriccol, n' Hello @ myasyuncric ')) from EnryptTestgo -- change asymmetric key properties -- https://msdn.microsoft.com/zh-cn/library/ms187311 (v = SQL .105 ). aspx -- change the private key password alter asypolicric key myasypolicric with private key (decryption by password = n' Hello @ myasypolicric ', -- original private key password encryption by password = n' Hello @ myasypolicric '); -- new private key KEY and password go -- delete the private KEY and retain only the public KEY -- if the asymmetric KEY is mapped to the Scalable KEY Management (EKM) KEY on the EKM device and the remove provider key option is not specified, -- the key is deleted from the database but not from the device. A warning is issued. Alter asypolicric key myasypolicric remove private key; go -- delete asymmetric key -- https://msdn.microsoft.com/ZH-CN/library/ms188389 (v = SQL .105). aspxdrop asymmetric Ric key myasypolicric; go -- delete test column alter table EnryptTest drop column asypolicriccolgo

Symmetric Key]

/*************************************** [Symmetric key ]************************************ */-- also known as single-key encryption, A single-key encryption method can be used to encrypt and decrypt information at the same time. -- asymmetric keys consume more system performance. Generally, symmetric keys are used to encrypt data and asymmetric keys are used to protect symmetric keys. select * from sys. key_encryptionsselect * from sys. crypt_propertiesselect * from sys. certificatesselect * from sys. asypolicric_keysselect * from sys. openkeysselect * from sys. into ric_keysselect * from EnryptTest -- add the test column alter table EnryptTest add symmetric riccol varbinary (max) go -- create symmetric key -- https://msdn.microsoft.com/zh-cn/library/ms188357 (v = SQL .105 ). aspxcreate encryption Ric key mypolicric -- symmetric key encrypted with a password with algorithm = aes_128 encryption by password = 'Hello @ mypolicric '; gocreate encryption Ric key mypolicric -- symmetric key encrypted with asymmetric keys with algorithm = aes_128 encryption by asypolicric key myasypolicricgo -- open symmetric keys (open to effectively use encryption and decryption functions) -- https://msdn.microsoft.com/zh -Cn/library/ms0000499 (v = SQL .105 ). aspxopen encryption Ric key myshortric decryption by password = 'Hello @ myshortric '; goopen encryption Ric key myshortric decryption by asyshortric key myasyshortric with password = 'Hello @ myasyshortric'; go -- encrypted data -- https://technet.microsoft.com/zh-cn/library/ms174361%28v= SQL .105%29.aspx? F = 255 & MSPPError =-2147217396 update EnryptTest set encryption riccol = encryptbykey (key_guid ('mypolicric '), convert (varchar (max), EnryptData )) go -- decrypt data -- https://technet.microsoft.com/zh-cn/library/ms181860 (v = SQL .105 ). aspxselect *, convert (varchar (max), convert (varchar (max), decryptbykey (SymmetricCol) from EnryptTestgo -- disable symmetric keys, or close all symmetric keys opened in the current session -- https://msdn.microsoft.com/zh-cn/library/ms177938%28v= SQL .1 05% 29. aspx? F = 255 & MSPPError =-2147217396 -- close all your Ric keys; close your Ric key mypolicric; go -- alter your Ric to add or delete new encryption methods (such as adding multiple passwords, any password is available) -- https://msdn.microsoft.com/zh-cn/library/ms189440 (v = SQL .105 ). aspxopen encryption Ric key myshortric decryption by password = 'Hello @ myshortric '; alter your Ric key myshortric add encryption by password = 'Hello @ kk' -- New another Passwordclose your Ric key myshortric; open encryption Ric key my‑ric decryption by password = 'Hello @ kk '; -- Use New Passwordselect convert (varchar (max), convert (varchar (max), decryptbykey (SymmetricCol ))) from EnryptTestalter foreign Ric key myencrypted Ric drop encryption by password = 'Hello @ kk '-- Drop the new Passwordclose foreign Ric key myencrypted Ric; go -- delete symmetric key -- https://msdn.microsoft.com/zh-cn/library/ms182698 (v = SQL .105 ). aspxdrop shortric key mypolicric; go -- delete the test column alter table EnryptTest drop column named riccolgo

[Example]

-- Test Data/* drop certificate Mycertificate; godrop master keygodrop table EnryptTestgo */create table EnryptTest (id int not null primary key, EnryptData nvarchar (20),) goinsert into EnryptTestvalues (1, N '000000'), (2, N '000000'), (3, N '000000'), (4, N 'A'); goselect * from EnryptTest; alter table EnryptTest add CertificateCol varbinary (max) go -- create master key encryption by password = n' Hello @ mymasterkey' go/* k Ey_idthumbprintcrypt_typecrypt_type_desccrypt_property created 1010x01ESKMENCRYPTION by master created BY certificate */-- create a certificate because primary key is used BY default. Key Encryption. No Password is required here. create certificate Mycertificatewith subject = n'enryptdata certificate ', start_date = n'000000', expiry_date = n'000000 '; go -- encryption and decryption are automatically encrypted using the service master key. Even if you use "close master key", update EnryptTest set CertificateCol = EncryptByCert (CERT_ID ('mycertificate'), CONVERT (VARCHAR (MAX), EnryptData) goselect *, CONVERT (VARCHAR (MAX), DECRYPTBYCERT (CERT_ID ('mycertificate'), CertificateCol) from EnryptTest; go -- Delete "service master key" alter master key drop encryption by service master keygo -- query the data and decrypt the data. Select *, CONVERT (VARCHAR (MAX), DECRYPTBYCERT (CERT_ID ('mycertificate'), CertificateCol) from EnryptTest is not automatically decrypted using CMK encryption; go -- You need to enable the CMK explicitly and use the CMK password to encrypt and decrypt the open master key decryption by password = n' Hello @ mymasterkey' go -- query the data and decrypt it. Select *, CONVERT (VARCHAR (MAX), DECRYPTBYCERT (CERT_ID ('mycertificate'), CertificateCol) from EnryptTest; go -- close master key go -- view master key, if "encryption by master key" is missing, the master key is not used for ENCRYPTION. Instead, the select * from sys is encrypted BY the password. key_encryptions/* cipher keys 101 nulleskpencryption by comment */-- 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

Two illustrations:




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.