Explain the encryption in SQL Server

Introduction
Encryption refers to the process of obfuscating data by using a key or 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 is located, or even the database where the database is located, but as the database is cracked or backed up. The last line of defense after stealing. By encryption, data that is stolen by unauthorized persons without a key or password becomes meaningless. This is not just for your data security, and sometimes even for the law. (Like a leaked password on a well-known IT website in China, you can apologize in China and take no responsibility for it. It is going to be bankrupt in the country.) Liquidation). Introduction to Encryption in SQL Server In SQL Server 2000 and previous versions, encryption is not supported. All encryption operations need to be done in the program. This leads to the problem that the encrypted data in the database is only meaningful to a particular program, and the other program becomes meaningless if there is no corresponding decryption algorithm. In SQL Server 2005, column-level encryption was introduced. Enables encryption to be performed on specific columns. This process involves four pairs of built-in functions for encryption and decryption. In the SQL Server 2008 era, Transparent Data Encryption (TDE) was introduced. The so-called transparent data encryption is that encryption is performed in the database, but from The point of view of the program is as if there is no encryption. Unlike column-level encryption, the level of TDE encryption is the entire database. Database files or backups encrypted with TDE cannot be attached or restored on another instance without a certificate. Some basic knowledge of encryption refers to the process of obfuscating data by using a key or password. The simplest process of encryption and decryption is shown in Figure 1. Figure 1. A simple encryption and decryption process In general, encryption can be divided into two categories, symmetric (symmetric) encryption and asymmetric (Asymmetric) encryption. Symmetric encryption is an encryption algorithm that uses the same key for encryption and decryption. In Figure 1, it is the encryption key = decryption key. Symmetric encryption is usually weak, because the data is used not only to transfer 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 an encryption algorithm that uses different keys for encryption and decryption. In Figure 1, it is the encryption key! = decryption key. The key used for encryption is called a public key, and the key used for decryption is called a private key. Therefore, security is greatly improved compared to symmetric encryption. Of course, there must be a short one. The asymmetric encryption method is usually more complicated than the symmetric key, so it will bring performance loss. Therefore, a compromise is to use a symmetric key to encrypt the data and 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. Choice of Encryption Algorithms Many of the popular encryption algorithms are industrial-grade. For example, symmetric encryption algorithms are: 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 withstood many people's tests, they are generally safer. www.2cto.comSQL 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. There is no algorithm that can accommodate all requirements. Each algorithm has strengths and weaknesses. For details on each encryption algorithm, please Bing… but the selection algorithm has some things in common: strong encryption usually takes more CPU resources than weaker encryption. Long keys usually generate stronger encryption than short keys. Asymmetric encryption is stronger than symmetric encryption using the same key length, but at a relatively slow rate. Block ciphers that use long keys are stronger than stream ciphers. Complex long passwords are stronger than short passwords. 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. Encrypted data cannot be compressed, but compressed data can be encrypted. 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 sub-level encryption. The concept is shown in Figure 2. Figure 2. Hierarchy of SQL Server Encryption As can be seen in Figure 2, encryption is hierarchical. Each database 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. It is automatically generated when the instance is installed. It is protected by the Data Protection API provided by Windows. The service master key provides encryption services for its child nodes. It is also used to encrypt some instance level information, such as the login password of the instance or the information of the linked server. Below the service master key is the Database Master Key, which is the khony part 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 database-level certificates or asymmetric keys. Each database can only have one database master key, created by T-SQL statements, as shown in code 1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$word' Code 1. Create Database Master Key The database master key is protected by the password shown in Code 1 and the service master key. When the database master key is created successfully, we can use this key to create symmetric keys, asymmetric keys and certificates. As shown in code 2. --Create certificate CREATE CERTIFICATE CertTestwith SUBJECT = 'Test Certificate'GO--Create asymmetric key CREATE ASYMMETRIC KEY TestAsymmetricWITH ALGORITHM = RSA_2048ENCRYPTION BY PASSWORD = 'pa$$word';GO--Create symmetric key CREATE SYMMETRIC KEY TestSymmetricWITH ALGORITHM = AES_256ENCRYPTION BY PASSWORD = 'pa$$word';GO code 2. Create a certificate, asymmetric key and symmetric key We see in code 2 that there is no explicit specification to use the database master key to encrypt the certificate, symmetrically Key and asymmetric key. This is because each database can only have one database master key, so there is no need to specify it. After the creation is successful, we can view the certificate, asymmetric key and symmetric key just created in SSMS, as shown in Figure 3.