Description: encryption-zt in SQL Server

Introduction

Encryption refers to the process of fuzzy processing of data by using a key or password. In SQL Server, encryption cannot replace other security settings, such as preventing unauthorized users from accessing the database, the Windows system where the database instance is located, or even the data center where the database is located, it serves as the last line of defense after the database is cracked or the backup is stolen. Encryption makes it meaningless for unauthorized users to steal data without a key or password. This is not just for your data security, but sometimes IT is required by law (such as a well-known IT website in China that has leaked a password can be apologized in China and will not be liable for anything, in the US state, bankruptcy liquidation is required ).

 

Introduction to encryption in SQL Server

Encryption is not supported in SQL Server and earlier versions. All encryption operations must be completed in the program. This leads to a problem. The encrypted data in the database is only meaningful to a specific program. If another program does not have a corresponding decryption algorithm, the data becomes meaningless.

In SQL Server2005, column-level encryption is introduced. This allows encryption to be performed on specific columns. This process involves four built-in functions for encryption and decryption.

In the SQL Server 2008 era, TDE is introduced. The so-called transparent data encryption is encrypted in the database, however, from the perspective of the program, it seems that there is no encryption. What is different from column-level encryption is that the TDE encryption level is the entire database. Database files or backups encrypted with TDE cannot be attached or restored to another instance without a certificate.

 

Basic encryption knowledge

Encryption refers to the process of fuzzy processing of data by using a key or password. The simplest process of encryption and decryption is 1.

Figure 1. A simple encryption and decryption process

 

Generally, encryption can be divided into two categories: Symmetric encryption and asypolicric encryption.

Symmetric encryption is the encryption algorithm that uses the same key for encryption and decryption. in figure 1, the encryption key = decryption key. Symmetric encryption is usually weak, because not only data needs to be transmitted, but also keys need to be transmitted in some way, which may cause the keys to be stolen during transmission.

Asymmetric encryption is the encryption algorithm that uses different keys for encryption and decryption. In Figure 1, it is the encryption key! = Decrypt the key. The key used for encryption is called the public key, and the key used for decryption is called the private key. Therefore, the security is greatly improved compared with symmetric encryption. Of course, there must be a short length. The asymmetric encryption method is usually much more complicated than symmetric keys, resulting in performance loss.

Therefore, an asymmetric key is used to encrypt data, while an asymmetric key is used to encrypt the symmetric key. In this way, both the high performance of symmetric keys and the reliability of asymmetric keys can be used.

 

Encryption Algorithm Selection

Many popular encryption algorithms are industrial-level. For example, symmetric encryption algorithms include DES, 3DES, IDEA, FEAL, and BLOWFISH. asymmetric encryption algorithms include the classic RSA. Because these algorithms have been published for a long time and have been tested by many people, they are generally safer.

SQL Server provides encryption algorithms such as DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES, no algorithm can meet all requirements. Each algorithm has its strengths and weaknesses. For details about each encryption algorithm, see Bing...

However, algorithms have some similarities:

• Strong Encryption usually takes up more CPU resources.

• Long keys generally generate stronger encryption than short keys.

• Asymmetric encryption is stronger than symmetric encryption with the same key length, but is relatively slow.

• The block password with a long key is stronger than the flow password.

• Complex long passwords are stronger than short passwords.

• If you are encrypting a large amount of data, use a symmetric key to encrypt the data and use an asymmetric key to encrypt the symmetric key.

• You cannot compress encrypted data, but you can encrypt the compressed data. If compression is used, data should be compressed before encryption.

 

Encryption hierarchies in SQL Server

In SQL Server, encryption is hierarchical. Root-level encryption protects its sub-level encryption. Concept 2.

Figure 2. SQL Server encryption level

 

As shown in figure 2, encryption is hierarchical. Each database instance has a Service Master Key, which corresponds to the orange part in Figure 2. This key is the root key of the entire instance. It is automatically generated when the instance is installed. It is protected by the Data Protection API provided by Windows ), in addition to providing encryption services for its subnodes, the Service master key is also used to encrypt information at the instance level, such as the username and password of the instance or the information of the linked server.

Under the service Master Key is the Database Master Key, which is the yellow part in Figure 2. This Key is encrypted by the Service Master Key. This is a database-level key. It can be used to provide encryption for database-level certificates or asymmetric keys. Each database can have only one database master key, which is created with a T-SQL statement, as shown in Code 1.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Pa$$word'

 

Code 1. Create a database master key

 

The database master key is jointly protected by the password shown in code 1 and the Service master key. After the database master key is successfully created, we can use this key to create a symmetric key, asymmetric key, and certificate. As shown in Code 2.

-- CREATE a certificate create Certificate CertTest with SUBJECT = 'test certificate' GO -- CREATE an asymmetric key create asypolicric KEY testasypolicric with algorithm = RSA_2048 encryption by password = 'Pa $ word '; GO -- create symmetric key testequalric with algorithm = AES_256 encryption by password = 'Pa $ word'; GO

 

Code 2. Create a certificate, asymmetric key, and symmetric key

 

In code 2, we can see that the database master key is not explicitly specified to encrypt certificates, symmetric keys, and asymmetric keys. This is because each database can only have one master key, so you do not need to specify it. After the certificate is created, you can view the certificate, asymmetric key, and symmetric key in SSMS, as shown in figure 3.

Figure 3. view the created certificate, asymmetric key, and symmetric key

 

It is not difficult to infer from this encryption level that if the database master key is cracked, the certificate, symmetric key, and asymmetric key created by the database may be cracked.

From the hierarchy in Figure 2, we can also see that symmetric keys can be created not only through passwords, but also through other symmetric keys, asymmetric keys and certificates. As shown in code 3.

-- CREATE your Ric KEY encrypt ricbycert with algorithm = AES_256 encryption by certificate CertTest; GO -- encrypt symmetric key with symmetric key open encryption Ric KEY testequalric decryption by password = 'Pa $ word' create encryption Ric KEY secret ricbysy with algorithm = AES_256 encryption by symmetric key testequalric; GO -- create symmetric key pair ricbyasy with algorithm = AES_256 encryption by asypolicric KEY testasypolicric; GO

 

Code 3. Create symmetric keys using several different encryption methods

 

Column-level Encryption)

SQL Server introduced the column encryption function in 2005. This allows you to use certificates, symmetric keys, and asymmetric keys to encrypt specific columns. According to different encryption and decryption methods, four pairs of functions are built in for encryption and decryption:

• EncryptByCert () and DecryptByCert ()-encrypt and decrypt data using certificates
• EncryptByAsymKey () and DecryptByAsymKey ()-use asymmetric keys to encrypt and decrypt data
  EncryptByKey () and DecryptByKey ()-use symmetric keys to encrypt and decrypt data
• EncryptByPassphrase () and DecryptByPassphrase ()-use the password field to generate a symmetric key for data encryption and decryption

 

Therefore, the use of encrypted data columns is relatively cumbersome and requires the program to explicitly call the SQL Server built-in encryption and decryption functions in the Code. This requires additional work and, the encrypted or decrypted columns must first be converted to the Varbinary type.

Here is an example:

In the AdventureWorks example database, we found the Sales. CreditCard table and found that the credit card number is displayed in plain text (How can AdventureWorks be as inefficient as a Password Leak on an IT website ). Therefore, you want to encrypt this column.

Figure 5. Saving important information in plain text as well as a well-known IT website in China

 

First, we need to convert the CardNumber column to the Varbinary type. Here, Select Into is used to create a new table, as shown in code 4.

SELECT CreditCardID, CardType,CardNumber_encrypt = CONVERT(varbinary(500), CardNumber), ExpMonth, ExpYear, ModifiedDateINTO Sales.CreditCard_Encrypt FROM Sales.CreditCard WHERE 1<>1

 

Code 4. Create a new table using Select

 

At this point, we use the previously created symmetric key encrypted by the certificate to encrypt the row and column, as shown in code 5.

-- OPEN the previously created symmetric key that is encrypted BY the CERTIFICATE. OPEN your Ric KEY into ricbycertdecryption by certificate CertTest -- use this KEY to encrypt data and insert the new table insert Sales. week (CardType, week, ExpMonth, ExpYear, ModifiedDate) select top 10 CardType, Category = EncryptByKey (KEY_GUID ('privacy ricbycert'), CardNumber), ExpMonth, ExpYear, ModifiedDatefrom Sales. creditCard

 

Code 5. encrypt data with a certificate-encrypted symmetric key

 

In this case, the encrypted Column cannot be viewed directly, as shown in Figure 6:

Figure 6. Unable to directly view encrypted Columns

 

In this case, you can view the data through the corresponding decryption function, as shown in Code 6.

OPEN SYMMETRIC KEY SymmetricByCertDECRYPTION BY CERTIFICATE CertTestselect CardType,CardNumber = convert(nvarchar(25), DecryptByKey(CardNumber_encrypt)), ExpMonth, ExpYear, ModifiedDatefrom Sales.CreditCard_encrypt

 

Figure 6. View encrypted data by the corresponding decryption Function

 

The result 7 is displayed.

Figure 7. the decrypted result is displayed correctly.

 

Using asymmetric keys and certificates for encryption and decryption is just a function different from that, so we will not test it here.

 

Transparent Data Encryption)

The introduction of transparent data encryption (TDE) in SQL Server 2008 is called transparent data encryption because it seems that there is no encryption in the use of database programs or users. TDE encryption is database-level. Data encryption and decryption are performed by the Data Engine on pages. Encryption during write and decryption during read. The client does not need to perform any operations.

The main function of TDE is to prevent the database backup or data files from being stolen. The person who steals the database backup or files cannot restore or attach the database without the data encryption key.

TDE uses the data encryption key (DEK) for encryption. DEK is protected by the Service Master key in the Master database, as shown in Protection Level 8.

Figure 8. TDE encryption level

 

Logs and backups of databases with TDE enabled are automatically encrypted.

Because TDE encrypts the database during writing and decrypts the database during reading, additional CPU resources are required. According to Microsoft, an additional 3%-5% CPU resources are required.

The following describes how to enable TDE.

Enabling TDE is simple. You only need to create a data encryption key (DEK) and enable the encryption option, as shown in code 7.

-- Based ON the CertTest CERTIFICATE we created earlier, the DEK -- CertTest must be created in the Master database by using your create database encryption key with algorithm = AES_256 encryption by server certificate CertTestGO -- enable tdealter database encryption encry

 

Code 7. Enable TDE after creating DEK

 

It is worth noting that DEK exists in the Database where TDE is enabled. Of course, this operation can also be performed by right-clicking the database to start TDE in SSMS and selecting task-manage database encryption. 9.

Figure 9. Enable TDE in SSMS

 

After TDE is enabled, we can view the status of TDE through the statement shown in figure 10.

Figure 10. view the database encryption status

 

 

Summary

This article introduces the basic concepts of encryption, the encryption level in SQL Server, and two different encryption methods provided in SQL Server. SQL Server's TDE is a very powerful function that can achieve database security without any changes in your programs. Before using the encryption technology provided by SQL Server, you must first have a systematic understanding of each function concept of encryption. Otherwise, the possible consequence is that the database cannot be opened. Prepare to re-write about the certificate and key backup and recovery in subsequent articles ....