Sqlser 2005 symmetric encryption, asymmetric encryption notes

One: symmetric encryption

original plaintext ---key --- encrypted data --- key --- original plaintext

fast, confusing plaintext through algorithms, consuming less system resources

Two: Asymmetric encryption

slow encryption and decryption, high system resource consumption

Three: Hybrid data encryption

encryption process: Randomly generates a symmetric key, using the public key to encrypt the symmetric key.

decryption Process: Use the private key to unlock the encrypted symmetric key, using a symmetric key to decrypt the data

Four: Backup, also key original service Master key (SQL Server host key )

Backup Key

Backup Service Master key to File = ' C:\xx.bak ';

Encryption by Password = ' password ';

Instance backup service Master key to File = ' D:\work Path\dbfile\data.bak '

Encryption by Password = ' Test '

Restore Key

Restore Service Master key from file = ' C:\xx.bak '

Decryption by password= ' password ';

Five: Create a database Master key ( you need to create the database master key manually )

Create master key encryption by password = ' password '

Create master key encryption by password = ' Databasetest '

Backing up the database key

Backup Master key to File = ' D:\work path\dbfile\database.bak ' encryption by password = ' Databasetest '

VI: View database key information in catalog view

SELECT * FROM Sys.symmetric_keys

SELECT * from sys.databases--is_master_key_encrypted_by_server 1 means to encrypt the database master key using the service Master key

Seven: Create a certificate

Create certificate cert_mycert;-- creating a certificate

Encryption by password= '-- Create a certificate password

With subject title

Create certificate timeliness, require manual validation of stored procedures, etc.

Start_data= ' expiry_date= '

The command is as follows:

Create certificate Myfirst_cert encryption by password= ' Myfirst_cert ' with subject = ' Myfirst_cert ', start_date = ' 1/1/201 0 ', expiry_date = ' 1/1/2015 '

Create certificate Test_cert encryption by password= ' Test_cert ' with subject = ' Test_cert ', start_date = ' 1/1/2010 ', Expir y_date = ' 1/1/2015 '

Querying certificates

Select * from Sys.certificates

Eight: certificate-related functions

Encyrptbycert (ID, ' cleartext ');

cert_id (' Myfirst_cert ')

Nine: Create an asymmetric key

You can also use the Sn.exe tool to create

Create asymmetric key asy_key1 with algorithm = rsa_2048 encryption by password= ' Asy_key1 '

10: Create a symmetric key

Create symmetric key sy_key1 with algorithm = aes_256 encryption by password= ' Sy_key1 '

Create symmetric key sy_key1_test with algorithm = aes_256 encryption by password= ' Testpassword '

11: Query Key

1: open key Directive

Open symmetric key sy_key1 decryption by password = ' Sy_key1 '

Query key

SELECT * FROM Sys.openkeys

Close key

Close symmetric key Sy_key1_test

12: Example of encrypting data with key ( Encrypt columns do not create indexes, meaningless, and consider the length of columns to be longer )

DECLARE @oldContent varbinary (200); -- Define the original variable

DECLARE @newContent varbinary ($);-- define the encrypted variable

Set @oldContent = CONVERT (varbinary, ' This is test data ');--Assign a value to the original variable

Set @newContent = EncryptByCert (cert_id (' Test_cert '), @oldContent)-- Encrypt data by certificate test_cert certificate title

Select @newContent-- encrypted query

Select CONVERT (varchar), DecryptByCert (cert_id (' Test_cert '), @newContent, N ' Test_cert ')) as [TS]-- decrypt query Test_cert Certificate name N ' Test_cert ' Certificate Password

13: Example

CREATE TABLE usertest (ID int primary key identity (20,1), username varbinary (), Usermoney int)

Insert into Usertest (Username,usermoney) VALUES (' AAA ', EncryptByCert (cert_id (' Test_cert '), ' 200 ')

Insert into Usertest (Username,usermoney) VALUES (' BBB ', EncryptByCert (cert_id (' Test_cert '), ' 300 ')

Insert into Usertest (Username,usermoney) VALUES (' CCC ', EncryptByCert (cert_id (' Test_cert '), ' 400 '))

Insert into Usertest (Username,usermoney) VALUES (' DDD ', EncryptByCert (cert_id (' Test_cert '), ' 500 '))

Inserting data correctly

Insert into Usertest (Username,usermoney) VALUES (' AAA ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 200 ')

Insert into Usertest (Username,usermoney) VALUES (' BBB ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 300 ')

Insert into Usertest (Username,usermoney) VALUES (' CCC ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 400 '))

Insert into Usertest (Username,usermoney) VALUES (' DDD ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 500 '))

Select Id,username,cast (DecryptByKey (Usermoney) as varchar) as ' test ' from usertest

Adding data through validators

Add Data Method EncryptByKey (Key_GUID (' certificate name '), encrypt value , use authenticator ,' authenticator value ')

Insert into Usertest (Username,usermoney) VALUES (' AAA ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 600 ', 1, ' 20 '))

Insert into Usertest (Username,usermoney) VALUES (' BBB ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 300 ', 1, ' 21 '))

Insert into Usertest (Username,usermoney) VALUES (' CCC ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 400 ', 1, ' 22 '))

Insert into Usertest (Username,usermoney) VALUES (' DDD ', EncryptByKey (Key_GUID (' sy_key1_test '), ' 500 ', 1, ' 23 '))

Decryption method

Cast (DecryptByKey ( decryption column 1,1,cast ( Validator value as Varcahar)) as varchar )

Select Id,username,cast (DecryptByKey (usermoney,1,cast (id as varchar (3)) as varchar) as ' test ' from usertest

Sqlser 2005 symmetric encryption, asymmetric encryption notes