SQL Server Encryption

Brief 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 ).
Encryption in SQL Server does not support encryption in SQL Server or 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. So that encryption can be performed on specific columns. This process involves four 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 encrypted in the database, but from the perspective of the program, it seems that there is no encryption. What is different from column-level encryption, the level of TDE encryption is the entire database. Database files or backups encrypted with TDE cannot be attached or restored to another instance without a certificate. Encryption refers to the process of fuzzy processing of data by using keys or passwords. The simplest process of encryption and decryption is 1.
Figure 1. A simple encryption and decryption process generally involves two types of encryption: 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. The choice of encryption algorithms nowadays many popular encryption algorithms are industrial-level, such as symmetric encryption algorithms include DES, 3DES, IDEA, FEAL, BLOWFISH. asymmetric encryption algorithms, such as the classic RSA. Because these algorithms have been published for a long time and have been tested by many people, they are generally safer. Www.2cto.com 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. In SQL Server, encryption hierarchies are hierarchical. Root-level encryption protects its sub-level encryption. Concept 2.
Figure 2.The SQL Server encryption level is 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 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. We can see in Code 2 that the database master key is not explicitly specified to encrypt the certificate, symmetric key, and asymmetric key. 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. check the created certificate. The asymmetric key and symmetric key are not difficult to infer from this encryption level. If the database master key is cracked, the created certificate and symmetric key are used, asymmetric keys 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. -- The CERTIFICATE encrypts the symmetric key www.2cto.com create encryption Ric KEY into 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 data Column Encryption (Column-level Encryption) in the symmetric key SQL Server using several different Encryption methods)
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 built-in functions are used for encryption and decryption: EncryptByCert () and DecryptByCert () -encryption and decryption of data using certificates EncryptByAsymKey () and DecryptByAsymKey ()-encryption and decryption of data using asymmetric keys EncryptByKey () and DecryptByKey () -encryption and decryption of data using Symmetric keys EncryptByPassphrase () and DecryptByPassphrase ()-use the password field to generate symmetric keys for data encryption and decryption, encrypted data columns are relatively cumbersome to use and require the program to explicitly call SQL Server's 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 sample database of AdventureWorks, we find Sales. in the CreditCard table, IT is 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. Save 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. use Select Into to create a new table. At this time, 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 Week, ExpMonth, ExpYear, ModifiedDate) select top 10 CardType, week = EncryptByKey (KEY_GUID ('hour ricbycert'), CardNumber), ExpMonth, ExpYear, Sales. creditCard Code 5. you cannot directly view the encrypted column by using the encrypted symmetric key of the certificate, as shown in Figure 6:
Figure 6. You cannot directly view the encrypted column. In this case, you can use the corresponding decryption function to view the data, as shown in Code 6. OPEN encryption Ric KEY selected by certificate CertTest select CardType, CardNumber = convert (nvarchar (25), DecryptByKey (CardNumber_encrypt), ExpMonth, ExpYear, ModifiedDatefrom Sales. figure 6. CreditCard_encrypt. the result 7 is displayed when the encrypted data is viewed by the corresponding decryption function.
Figure 7. the decrypted results correctly show that the encryption and decryption using asymmetric keys and certificates is only different in functions and will not be tested here. Transparent Data Encryption (TDE) is introduced in SQL Server 2008. It is called Transparent Data Encryption, it is because this encryption seems like there is no encryption in the View 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. The log and backup of the TDE database are automatically encrypted at the TDE encryption level. 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. Under www.2cto.com, it is very easy to enable TDE. You only need to create a data encryption key (DEK) and enable the encryption option, as shown in code 7. -- Based on the certificate CertTest we created earlier, to create dek -- CertTest, you need to USE AdventureWorksGO create database encryption key in the Master database with algorithm = AES_256 encryption by server certificate CertTestGO -- enable tdealter database AdventureWorksSET encryp. after creating DEK, enable TDE. Here, 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. After TDE is enabled in SSMS, we can view the status of TDE through the statements in figure 10.
Figure 10. Summary of viewing database encryption status 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 ....
Author song xiaojian