code security in SQL Server 2008 (iii) passphrase encryption _mssql2008

Preface :

Before SQL Server 2005 and SQL Server 2008. If you want to encrypt sensitive data, such as financial information, payroll, or identity card numbers, you must use an external application or algorithm. SQL Server 2005 introduces the ability to encrypt built-in data, using a combination of certificates, keys, and system functions to accomplish this.

Similar to the digital certificate. A SQL Server certificate includes a pair of keys, both public and private, that are used to encrypt and decrypt data. SQL Server also has the ability to create asymmetric keys and symmetric key objects. An asymmetric key (asymmetric key) is similar to a certificate in that the public key is used to encrypt the database and the private key to decrypt the data. Asymmetric keys and certificates both provide strong encryption strength. But there is more performance overhead in completing the complex encryption | decryption process. A better fit for encrypting large amounts of data, and a solution with a lower performance cost is the symmetric key (symmetric key), which is a key that encrypts and decrypts the same data.

SQL Server allows these encryption capabilities to be placed in the encryption hierarchy. When SQL Server is installed, create a server-level certificate called the Service Master key in database master and bind it implicitly to the SQL Server service account login name. The Service master key is used to encrypt all other database certificates and keys created in the SQL Server instance. Alternatively, you can create a database master key in the user database, which can be used to encrypt database certificates and keys.

In SQL Server 2008, Microsoft introduced transparent data encryption (TDE), which encrypts the entire database without having to modify any applications that access it. Data, log files, and related database backups are encrypted. If the database is stolen, the data cannot be accessed without a database encryption key (DEK). This article and the following articles will be illustrated.

In SQL Server 2008, support for Extensible Key Management (EKM) is also introduced, which means that SQL Server can use a hardware security module (HSM) to store and manage encryption keys. HSM can reduce data and actual cryptographic key coupling.

This part of the content is divided into six articles:

1, through the passphrase encryption

2. Master Key

3. Asymmetric key Encryption

4. Symmetric key Encryption

5. Certificate Encryption

6. Transparent Data encryption

One, through the pass phrase (passphrase) encryption

For emergency data encryption that does not involve certificates and keys, you can encrypt and decrypt data directly based on the user-supplied password. A pass phrase (passphrase) is a password that allows a space to exist. This passphrase is not stored in the database and thus means that it will not be "cracked" using stored system data. At the same time, you can use spaces to create a long, easy to remember sentence to encrypt and decrypt sensitive data.

The pair of functions we need to know is encryptbypassphrase (http://technet.microsoft.com/zh-cn/library/ ms190357.aspx) and Decryptbypassphrase (http://technet.microsoft.com/zh-cn/library/ms188910.aspx)

This pair of functions must use the same parameters.

Let's look at an example:

Copy Code code as follows:

Use DEMODB2008R2
Go

--Table used to store the encrypted data
--For the purposes of this recipe
CREATE TABLE #SecretInfo
(MySecret varbinary (max) not NULL)
Go
INSERT #SecretInfo (MySecret)
SELECT Encryptbypassphrase (
' My Password Used to Encrypt this String in 2008. ',
' I put the company's server database password in that little green Book. ')

SELECT MySecret from #SecretInfo

/*
The usual query results:
0x01000000ccbc800c6b5cece3f211fb975c2f8b681cc8301dde74cf783c6a593
16c2bac08d568151fd573e2fde01c800805c8d1d5984727dee95040c1fb604003
A3efdd0444ce72d7e505d1a4
*/

Copy Code code as follows:

/************
To know the contents of the original text, use the following statement:
--------3w@live.cn
**********************/

SELECT CAST (Decryptbypassphrase (
' My Password Used to Encrypt this String in 2008. ',
MySecret) as varchar (max)
From #SecretInfo

Attention:

1, using the pass phrase to encrypt data, do not worry about the sysadmin server role members read Data (in the following article will see that the server role member sysadmin has the ability to read other forms of encrypted data inherent permissions.) )

2. Assuming that the password is not stored in the table or used in any module (stored procedure, trigger, etc.), the encrypted data will prevent theft from the database backup or infiltration in the database in the SQL Server instance. If the pass phrase is not shared correctly, the data can be decrypted.

Invite the Month