Elaborate on encryption in SQL Server

Introduction

Encryption refers to the process of blurring data by using a key or a password. In SQL Server, encryption does not replace other security settings, such as preventing unauthorized people from accessing the database or the Windows system where the database instance resides, or even the computer room where the database resides, but as the last line of defense when the database is cracked or the backup is stolen. By encrypting, the data stolen by an unauthorized person without a key or password becomes meaningless. This approach is not just for your data security, and sometimes even required by law (such as the disclosure of passwords in a well-known it site in China can not take any responsibility after the Chinese apology, the America duly bankruptcy liquidation).

Introduction to encryption in SQL Server

In SQL Server2000 and previous versions, encryption is not supported. All cryptographic operations need to be done in the program. This leads to a problem where the data encrypted in the database is only meaningful for a particular program, and if the other program does not have a corresponding decryption algorithm, the data becomes meaningless.

To SQL Server2005, column-level encryption was introduced. Enables encryption to be performed on a specific column, involving 4 of built-in functions for encryption and decryption

In the SQL Server 2008 era, the introduction of transparent Data encryption (TDE), the so-called transparent data encryption, is the encryption in the database, but from the point of view of the program as if there is no encryption, and column-level encryption is different, TDE encryption level is the entire database. A database file or backup that is encrypted with TDE cannot be attached or recovered on another instance that does not have a certificate.

Some basic knowledge of cryptography

Encryption refers to the process of blurring data by using a key or a password. Cryptographic decryption is shown in the simplest of procedure 1.

Figure 1: A simple encryption and decryption process

In general, encryption can be divided into two main classes, symmetric (symmetric) and asymmetric (asymmetric) encryption.

Symmetric encryption is a cryptographic algorithm that encrypts and decrypts the same key, and in Figure 1 is the encryption key = Decryption key. Symmetric encryption is usually weak, because the data is used not only to transmit the data itself, but also to transfer the key in some way, which is likely to cause the key to be stolen during transmission.

Asymmetric encryption is a cryptographic algorithm that encrypts and decrypts a different key, and in Figure 1, it's the encryption key! = Decryption key. The key used for encryption is called the public key, and the key used for decryption is called the private key. As a result, security is much better than symmetric encryption. Of course, there will be a short, asymmetric encryption in the usual way the algorithm will be more complex than the symmetric key, so it can lead to a performance loss.

Therefore, a compromise is to use a symmetric key to encrypt the data and use an asymmetric key to encrypt the symmetric key. This can take advantage of the high performance of symmetric keys and the reliability of asymmetric keys.

selection of cryptographic algorithms

Many of the popular cryptographic algorithms are industrial-grade, such as the symmetric encryption algorithm: DES, 3DES, Idea, FEAL, BLOWFISH. Instead of symmetric encryption algorithms such as classic RSA. Because these algorithms have been published for a long time, and have been tested by many people, it is generally more secure.

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, none of which can accommodate all requirements, Each algorithm has strengths and weaknesses, the details of each encryption algorithm, please Bing ...

But the selection algorithm has some similarities:

• Strong encryption typically consumes more CPU resources than weaker encryption.

• Long keys typically generate stronger encryption than short keys.

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

• A block password that uses a long key is stronger than a stream password.

• A complex long password is stronger than a short password.

• If you are encrypting large amounts of data, you should 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 data that is already compressed. If compression is used, the data should be compressed before encryption.

encryption hierarchy in SQL Server

In SQL Server, encryption is hierarchical. Root-level encryption protects its child-level encryption. As shown in concept 2.

Figure 2. Levels of SQL Server encryption

As you can see from Figure 2, encryption is hierarchical. Each DB instance has a service master key, which corresponds to the orange portion of Figure 2. This key is the root key of the entire instance, which is automatically generated when the instance is installed, and is itself protected by the data Protection API provided by Windows (Pertection API), and the service master key is used to encrypt some instance-level information in addition to providing cryptographic services for its child nodes. such as the login 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 portion of Figure 2, which is encrypted by the service master key. This is a database-level key. Can be used to provide encryption for creating a database-level certificate or asymmetric key. Each database can have only one database master key, created through T-SQL statements, as shown in code 1.

CREATE KEY  by = ' pa$ $word '

Code 1: Creating a Database Master key

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

--Create a certificateCREATECERTIFICATE certtest withSUBJECT= 'Test Certificate'GO--creating an asymmetric keyCREATEAsymmetricKEYTestasymmetric withAlgorithm=rsa_2048 Encryption byPASSWORD= 'pa$ $word'; GO--Create a symmetric keyCREATESymmetricKEYTestsymmetric withAlgorithm=aes_256 Encryption byPASSWORD= 'pa$ $word';GO

Code 2: Creating certificates, asymmetric keys, and symmetric keys

In code 2, we see that there is no explicit designation of the encryption certificate using the database master key, symmetric key and asymmetric key. This is because each database can have only one database master key, so there is no need to specify. Once created, we can see in SSMs the certificate you just created, asymmetric key and symmetric key, as shown in 3.

Figure 3: View the certificate that was just created, asymmetric key and symmetric key

It is not difficult to infer from this encryption hierarchy that if the database master key is cracked, then the certificate created by it, the symmetric key, and the asymmetric key are likely to be cracked.

By the level of Figure 2 we can also see that the symmetric key can be created not only through a password, but also through other symmetric keys, asymmetric keys, and certificates. As shown in code 3.

--To encrypt a symmetric key by a certificateCREATESymmetricKEYSymmetricbycert withAlgorithm=aes_256 Encryption byCERTIFICATE certtest;GO--Symmetric key encryption by symmetric keyOPENSymmetricKEYTestsymmetric Decryption byPASSWORD='pa$ $word'CREATESymmetricKEYSymmetricbysy withAlgorithm=aes_256 Encryption bySymmetricKEYTestsymmetric;GO--symmetric key encryption by asymmetric keyCREATESymmetricKEYSymmetricbyasy withAlgorithm=aes_256 Encryption byAsymmetricKEYTestasymmetric;GO

Code 3: Create a symmetric key by several different encryption methods

data column encryption in SQL Server (Column-level encryption)

SQL Server introduced the functionality of column encryption in 2005. Enables the use of certificates, symmetric keys, and asymmetric keys to encrypt specific columns. In the specific implementation, according to the method of encryption decryption, built-in 4 pairs of functions for encryption and decryption:

• EncryptByCert () and DecryptByCert ()-use certificates to encrypt and decrypt data
• Encryptbyasymkey () and DecryptByAsymKey ()-Encrypt and decrypt data using an asymmetric key
  EncryptByKey () and DecryptByKey ()-Encrypt and decrypt data using a symmetric key
• Encryptbypassphrase () and Decryptbypassphrase ()-Encrypt and decrypt data using a password field to generate a symmetric key

Therefore, encrypted data columns are relatively cumbersome to use, requiring the program to explicitly invoke SQL Server's built-in encryption and decryption functions in code, which requires additional effort, and that the encrypted or decrypted columns first need to be converted to the varbinary type.

Let's look at an example:

In the AdventureWorks sample database, we found the Sales.creditcard table and found that the credit card number is clear text (how AdventureWorks is not moral integrity as well as an IT site that leaks the password). So you want to encrypt this column.

Figure 5. A well-known it site in the country like no moral integrity to save important information

First we need to convert the Cardnumber column to the varbinary type. Here, you create a new table from select INTO, as shown in code 4.

SELECT=CONVERT(varbinary(+), cardnumber), Expmonth, Expyear,ModifiedDateto fromWHERE1< >1

Code 4. Creating a new table from select INTO

At this point we use the previously created symmetric key encrypted by the certificate for column encryption, as shown in code 5.

--Open a previously created symmetric key that was encrypted by the certificateOPENSymmetricKEYsymmetricbycertdecryption byCERTIFICATE certtest--Use this key to encrypt the data and insert the newly created tableInsertSales.creditcard_encrypt (Cardtype,cardnumber_encrypt, Expmonth, Expyear, ModifiedDate)Select Top TenCardtype,cardnumber_encrypt=EncryptByKey (Key_GUID ('Symmetricbycert'), Cardnumber), Expmonth,expyear, ModifiedDate fromSales.creditcard

Code Listing 5. Encrypting data with a symmetric key encrypted by a certificate

The encrypted column cannot be viewed directly at this time, as shown in 6:

Figure 6: Encrypted columns cannot be viewed directly

The data can now be viewed through the corresponding decryption function, as shown in code 6.

OPEN KEY   by CERTIFICATE certtestSelect=convert(  nvarchar(+), DecryptByKey (Cardnumber_encrypt)), Expmonth, Expyear, ModifiedDate  from Sales.creditcard_encrypt

Figure 6: View the encrypted data by the corresponding decryption function

The resulting results are shown in 7.

Figure 7. The results can be displayed correctly after decryption

Encrypting and decrypting with asymmetric keys and certificates is just a different function, not tested here.

Transparent Data encryption (Transparent encryption)

Transparent data encryption was introduced in SQL Server 2008 (TDE), which is called transparent data encryption because it appears as if there is no encryption in the program or user that uses the database. TDE encryption is database-level. The encryption and decryption of data is performed by the data Engine, in page units. Encrypted at write time, decrypted when read out. The client program doesn't have to do anything at all.

The primary role of TDE is to prevent a database backup or data file from being stolen, and the person who steals the database backup or file cannot recover or attach the database without the data encryption key.

TDE encrypts using a data encryption Key (DEK). The DEK is present in the master database protected by the service master key, as shown by the protection level 8.

Figure 8. Encryption hierarchy of TDE

Logs and backups of the TDE-enabled database are automatically encrypted.

Because TDE causes the database to be encrypted at write time and decrypted during readout, additional CPU resources are required, according to Microsoft, which requires an additional 3% to 5% of CPU resources.

Let's see how to turn on TDE

It is very easy to turn on TDE by simply creating a data encryption Key (DEK) and turning on the encryption option on the line, as shown in code 7.

--based on the certificate Certtest we created earlier, create a dek--certtest need to be in the master database UseAdventureWorksGO CREATE DATABASEEncryptionKEY  withAlgorithm=aes_256 Encryption bySERVER CERTIFICATE certtestGO--Open TDEALTER DATABASEAdventureWorksSETEncryption on

Code 7. After creating the Dek, turn on TDE

It is worth noting that the DEK is in the database that has the TDE open. Of course, we can also do this by right-clicking the database in SSMs that needs to start TDE, selecting the task-managing database encryption. As shown in 9.

Figure 9. Open Tde in SSMs

When TDE is turned on, we can view the status of TDE through the statements in Figure 10.

Figure 10: View the Database encryption status

Summary

This article describes the basic concepts of cryptography, the level of encryption in SQL Server, and the two different encryption methods available in SQL Server. The SQL Server TDE is a very powerful feature that can be secured at the database level without any changes in the user program. Before using the encryption technology provided by SQL Server, it is necessary to have a systematic understanding of the various functional concepts of encryption, otherwise it is likely to result in the failure to open the database. Prepare to write again about certificates, key backup and recovery in subsequent articles ....

Elaborate on encryption in SQL Server