About symmetric keys and asymmetric keys for SQL (Basic)

First of all, let's talk about our own understanding, the key we all know is the one thing we know to encrypt. First of all, the symmetric key, if we encrypt some elements of a table in a database, the encryption password used is consistent with the decrypted password, and the non-symmetric key is just the opposite.

We specifically see how to encrypt and decrypt a table data that we want to encrypt.

1, encrypting the information for a table in a database2, build Master Key:create Master Key encryption by PASSWORD='[email protected]'; The password database master key used to encrypt the database master key is the symmetric key used to protect the certificate's private key and the asymmetric key that exists in the database. When the master key is created, it is encrypted using the AES_256 algorithm and the user-supplied password. In SQL Server -and SQL Server -R2, the Triple DES algorithm is not used. To enable the automatic decryption of the master key, a copy of the master key is encrypted using the service master key, and the copy is stored in the database and in master. Typically, whenever the master key changes, the copy stored in master is updated without prompting. You can change this default behavior by using the DROP encryption by SERVICE master key option of Alter MASTER key. You must use the Open Master key statement and password to open a master key that is not encrypted using the service master key. The Is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted with the service master key. You can view information about the database master key in the Sys.symmetric_keys catalog view. 3, build table: Person CREATE table Person (ContactID INT PRIMARY KEY, FirstName NVARCHAR ( $), MiddleName NVARCHAR ( $), LastName NVARCHAR ( $), Efirstname VARBINARY ( $), Emiddlename VARBINARY ( $), Elastname VARBINARY ( $), );4, Build Certificate: CREATE CERTIFICATE testcertificate with SUBJECT='Adventureworks Test Certificate', expiry_date ='10/10/2009';5, built symmetrical key:create symmetric KEY testsymmetrickey with algorithm=triple_des encryption by CERTIFICATE Testcertificate; OPEN symmetric KEY Testsymmetrickey decryption by CERTIFICATE Testcertificate;6, insert the encrypted data into the table person insert into person (ContactID, Efirstname, Emiddlename, Elastname) VALUES (1, EncryptByKey (Key_GUID ('Testsymmetrickey'),'Efirstname'), EncryptByKey (Key_GUID ('Testsymmetrickey'),'Emiddlename'), EncryptByKey (Key_GUID ('Testsymmetrickey'),'Elastname') )7, decrypt the data in 6 UPDATE person SET FirstName=CONVERT (Varchar,decryptbykey (Efirstname)), MiddleName=CONVERT (Varchar,decryptbykey (Emiddlename)), LastName=CONVERT (Varchar,decryptbykey (Elastname));8, viewing the decrypted resultsSelectCast (DecryptByKey (Efirstname) asVarchar) from person9, delete the above structure drop TABLE person; CLOSE symmetric KEY Testsymmetrickey; DROP symmetric KEY Testsymmetrickey; DROP CERTIFICATE testcertificate; DROP MASTER Key;ps:expiry_date is a valid time, to be greater than the current time so after the creation we see the corresponding encrypted data will be garbled! 

With this example you can get a good understanding of the concept of cryptography. Rules on the application of specific statements this has a Web site

Http://technet.microsoft.com/zh-cn/library/ms187798.aspx

About symmetric keys and asymmetric keys for SQL (Basic)