code security in SQL Server 2008 (v) asymmetric key encryption _mssql2008

An asymmetric key contains a database-level internal public and private key that can be used to encrypt and decrypt data in a SQL Server database, either imported from an external file or assembly, or generated in a SQL Server database. It is not like a certificate and cannot be backed up to a file. This means that once it is created in SQL Server, there is no easy way to reuse the same key in other user databases. Asymmetric keys are a high security option for database encryption and therefore require additional SQL Server resources.

Let's look at a set of examples:

Example one, creating an asymmetric key

Create an asymmetric key using the following command:

The Create asymmetric key creates an asymmetric secret key. (http://msdn.microsoft.com/en-us/library/ms174430.aspx)

Copy Code code as follows:

--The following statement creates an asymmetric key Asymdemokey

Use Db_encrypt_demo
Go

Create asymmetric key Asymdemokey--Creating an asymmetric key name
with algorithm = rsa_512--Encryption security type
Encryption by PASSWORD = ' testasym123! '--Password

Example two, viewing an asymmetric key in the current database

Use catalog view Sys.asymmetric_keys (http://msdn.microsoft.com/en-us/library/ms188399.aspx) to view.

Copy Code code as follows:

--View the asymmetric key in the current database
Use Db_encrypt_demo
Go

SELECT name, Algorithm_desc, Pvt_key_encryption_type_desc
From Sys.asymmetric_keys

return----Results

/*
Name Algorithm_desc Pvt_key_encryption_type_desc
Asymdemokey rsa_512 Encrypted_by_password
*/

Example three, modifying the private key password for an asymmetric key

You can use ALTER asymmetric KEY with encryption by password and decryption by password option (http://technet.microsoft.com/en-us/ library/ms189440.aspx) modifies the password for the private key.

Copy Code code as follows:

--Modifying the private key password
Alter asymmetric key asymdemokey--the name of the secret key to be modified
With private key--private key
(encryption by PASSWORD = ' newpassworde4d352f280e0 ',--specify a new password
Decryption by PASSWORD = ' testasym123! ') --The old password is used for decryption.

example four, use asymmetric key to encrypt and decrypt data .

Because both the public key and the key are required, it is a safe way to use asymmetric keys to encrypt data while maintaining confidential data. But when used in large datasets, more resources are consumed.

It is not recommended to encrypt data using an asymmetric key, but it is still a choice. Once an asymmetric key is added to the database, it can be used to encrypt and decrypt the data.

The following two SQL functions are used:

Encryptbyasymkey encrypt data. (http://technet.microsoft.com/en-us/library/ms186950.aspx)

DecryptByAsymKey decrypt the data. (http://msdn.microsoft.com/en-us/library/ms189507.aspx)

Note that when encrypting through a certificate, DecryptByAsymKey returns the varbinary type of encrypted data.

Here is an example:

Copy Code code as follows:

Use Db_encrypt_demo
Go

--Create data that needs to be encrypted
Create Table Bankuser
(Pkid int primary key identity (10001,1)
, Userno varbinary (1000) NULL
, curstate smallint default (0) NOT NULL
)
Go

INSERT INTO Bankuser
(userno,curstate)
VALUES (Encryptbyasymkey (asymkey_id (' Asymdemokey '), ' 137492837583249ABR '), 1)
--Inserts a record, the field Userno stores the encrypted number value
Go

Select Pkid,curstate, cast (userno as nvarchar (1000)) as Userno
From Bankuser
where pkid=10001

PlainText results:

To view unencrypted data:

Copy Code code as follows:

SELECT Pkid,curstate,
Cast
(DecryptByAsymKey asymkey_id (' Asymdemokey '), Userno,n ' newpassworde4d352f280e0 ')
As varchar (1000) as Userno--requires the original private key
From Bankuser where pkid=10001

Example v. Delete an asymmetric key

Command: Drop asymmetric key Deletes the specified asymmetric key (http://msdn.microsoft.com/en-us/library/ms188389.aspx)

Example:
DROP Asymmetric KEY Asymdemokey

Summary:

1. This article mainly introduces the creation, deletion, view and use of asymmetric key to modify the private key, encrypt and decrypt the data.

2. An asymmetric key contains a database-level internal public and private key that can be used to encrypt and decrypt data in a SQL Server database.

3. Asymmetric keys are a high security option for database encryption and therefore require more SQL Server resources and are not recommended for use.

The following will focus on relatively simple and widely used symmetric key cryptography (symmetric key encryption)