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

Source: Internet
Author: User
Tags decrypt

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 ( ms190357.aspx) and Decryptbypassphrase (

This pair of functions must use the same parameters.

Let's look at an example:

Copy Code code as follows:

Use DEMODB2008R2

--Table used to store the encrypted data
--For the purposes of this recipe
(MySecret varbinary (max) not NULL)
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:

Copy Code code as follows:

To know the contents of the original text, use the following statement:

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


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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.