The certificate also contains both the public key and the key, which is used for encryption and the latter to decrypt. SQL Server can generate its own certificate, or it can be loaded from an external file or assembly. Because they can be backed up and loaded from a file, the certificate is easier to migrate than the asymmetric key, but the asymmetric key does not. This means that you can easily reuse the same certificate in the database.
Note: Certificates and asymmetric keys consume the same resources.
Let's look at a set of examples:
Example one, creating a database certificate
Creating a Database Certificate: Create symmetric KEY (http://msdn.microsoft.com/en-us/library/ms187798.aspx)
Copy Code code as follows:
Use Db_encrypt_demo
Go
--Create a certificate
CREATE Certificate Cert_demo--Certificate name
Encryption by PASSWORD = ' ASDFG!!! '--Password for the encryption certificate
with SUBJECT = ' Db_encrypt_demo Database encryption certificate ',--certificate subject
start_date = ' 3/14/2011 ', expiry_date = ' 10/20/2012 '--starting and ending dates
Go
example Two, viewing a certificate in a database
Use catalog view sys.certificates (http://msdn.microsoft.com/en-us/library/ms189774.aspx) to view.
Copy Code code as follows:
--View the certificate in the current database
Use Db_encrypt_demo
Go
--View Certificate
SELECT name, Pvt_key_encryption_type_desc, Issuer_name
From Sys.certificates
return----Results
/*
Name Pvt_key_encryption_type_desc Issuer_name
Cert_demo Encrypted_by_password Db_encrypt_demo Database Encryption certificate
*/
example three, backup and restore certificates
After you create a certificate, you can also use the backup certificate (http://msdn.microsoft.com/en-us/library/ms178578.aspx) command to back up to a file, either to save it securely or to restore it in another database.
Copy Code code as follows:
--Backup Certificate
BACKUP Certificate Cert_demo
to FILE = ' H:\SqlBackup\certDemo.BAK '--Certificate backup path, used to encrypt
With private key (file= ' H:\SqlBackup\certDemoPK.BAK ',--the certificate private key file path used to decrypt
Encryption by PASSWORD = ' 1234GH!!! ',--encrypt private key password
Decryption by PASSWORD = ' ASDFG!!! ') --Decrypt the private key password
-After a backup, you can use the certificate in another database or delete it using the Drop Certificate command.
DROP Certificate Cert_demo
Go
--Restore the certificate to the database from the backup file
CREATE Certificate Cert_demo
From FILE = ' H:\SqlBackup\certDemo.BAK '
With PRIVATE KEY (FILE = ' H:\SqlBackup\certDemoPK.BAK ',
Decryption by PASSWORD = ' 1234GH!!! ',--decrypt the private key password
Encryption by PASSWORD = ' ASDFG!!! ') --Encrypt private key password
Example four, managing the private key of a certificate
Use the ALTER certificate (http://msdn.microsoft.com/en-us/library/ms189511.aspx) command to add or remove private keys for certificates. This command allows you to delete the private key (the default is to encrypt through the database master key), to increase the private key, or to modify the private key's password.
Copy Code code as follows:
--Remove the private key from the certificate
ALTER Certificate Cert_demo
REMOVE PRIVATE KEY
--Re-add private key from backup file to existing certificate
ALTER Certificate Cert_demo
With PRIVATE KEY
(FILE = ' H:\SqlBackup\certDemoPK.BAK ',
Decryption by PASSWORD = ' 1234GH!!! ',--decrypt the private key password
Encryption by PASSWORD = ' ASDFG!!! ') --Encrypt private key password
--Modify the password with the private key
ALTER Certificate Cert_demo
With the PRIVATE KEY (decryption by PASSWORD = ' ASDFG!!! ',
Encryption by PASSWORD = ' Mynewpassword!!! 13E ')
example Five, use certificate encryption and decryption .
Use function EncryptByCert to encrypt data. (http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
Copy Code code as follows:
--Remove the private key from the certificate
ALTER Certificate Cert_demo
REMOVE PRIVATE KEY
--Re-add private key from backup file to existing certificate
ALTER Certificate Cert_demo
With PRIVATE KEY
(FILE = ' H:\SqlBackup\certDemoPK.BAK ',
Decryption by PASSWORD = ' 1234GH!!! ',--decrypt the private key password
Encryption by PASSWORD = ' ASDFG!!! ') --Encrypt private key password
--Modify the password with the private key
ALTER Certificate Cert_demo
With the PRIVATE KEY (decryption by PASSWORD = ' ASDFG!!! ',
Encryption by PASSWORD = ' Mynewpassword!!! 13E ')
Here is an example:
Copy Code code as follows:
Use Db_encrypt_demo
Go
--Inserting test data
INSERT dbo. Pwdquestion
(CustomerID, Passwordhintquestion, Passwordhintanswer)
VALUES
(10, ' The name of the hospital you were born in? '
EncryptByCert (cert_id (' Cert_demo '), ' Beijing Courtyard House '))
--View PlainText
SELECT CAST (passwordhintanswer as varchar) passwordhintanswer
FROM dbo. Pwdquestion
WHERE CustomerID = 10
Copy Code code as follows:
--View the original 3w@live.cn
SELECT Passwordhintquestion,
CAST (DecryptByCert cert_id (' Cert_demo '), Passwordhintanswer,
N ' Mynewpassword!!! 13E ')
As varchar) passwordhintanswer
FROM dbo. Pwdquestion WHERE CustomerID = 10
example Six, the data is encrypted and decrypted using a symmetric key.
In the previous article, you have seen the demo of opening a symmetric key encrypted with an asymmetric key, which is divided into two steps, first with the open symmetric key command, and then the actual DecryptByKey function call. SQL Server also provides an additional decryption function that can combine these two steps: Decryptbykeyautoasymkey (http://msdn.microsoft.com/en-us/library/ ms365420.aspx) and DecryptByKeyAutoCert (http://msdn.microsoft.com/en-us/library/ms182559.aspx)
Copy Code code as follows:
Use Db_encrypt_demo
Go
This example uses the database master password for encryption, and therefore does not require a password. 3w@live.cn
----Create Master Key encryption by password= ' 123asd! '
----Go
--Create an asymmetric key 3w@live.cn
CREATE Asymmetric KEY asymdemo_v2
with algorithm = rsa_512
--Create a symmetric key 3w@live.cn
CREATE symmetric KEY Sym_demo_v2
with algorithm = Triple_des
Encryption by asymmetric KEY ASYMDEMO_V2
--Open the symmetric key, insert the record
OPEN symmetric KEY Sym_demo_v2
Decryption by asymmetric KEY ASYMDEMO_V2
INSERT dbo. Pwdquestion
(CustomerID, Passwordhintquestion, Passwordhintanswer)
VALUES
(22, ' The name of the hospital you were born in? '
EncryptByKey (Key_GUID (' sym_demo_v2 '), ' Shaw Hospital '))
Close symmetric KEY Sym_demo_v2
At this point, using Decryptbykeyautoasymkey to decrypt the data requires only one action
Copy Code code as follows:
SELECT CAST (Decryptbykeyautoasymkey asymkey_id (' Asymdemo_v2 '), NULL,
Passwordhintanswer) as varchar)
FROM dbo. Pwdquestion
WHERE CustomerID = 22
Summary:
1. This article mainly introduces the creation, deletion, view and use of the certificate to modify the encryption method, data encryption and decryption.
2. Certificate encryption and asymmetric key encryption are more resource-intensive than symmetric key encryption.
The most encouraging transparent data encryption (TDE) in SQL Server is described below.
