Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how SQL Server data encryption is". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Data encryption is the last line of defense in which the database is cracked, the physical media is stolen and the backup is stolen. Data encryption solves the security problem of data theft on the one hand, and the relevant laws require mandatory encryption of data on the other. Compared with other databases, SQL Server data encryption has relatively perfect functions and many encryption methods. Generally speaking, data encryption is divided into symmetric encryption and asymmetric encryption. Symmetric encryption: encryption and decryption use the same key, the key needs to be transmitted, the security is weak, but the performance is better than asymmetric. Asymmetric encryption: encryption and decryption use different keys (public key and private key), the security of the symmetric key is better, but the algorithm is more complex, resulting in performance loss. Therefore, the compromise is to encrypt the data with a symmetric key and an asymmetric key to encrypt the symmetric key. This not only ensures high performance, but also improves the reliability of the key.

Similarly, SQL Server uses a compromise approach, so the SQL Server encryption function consists of two parts: data encryption and key management

one。 data encryption

When it comes to data encryption, we have to talk about encryption algorithms. SQL Server supports multiple encryption algorithms:

two。 Key management: 1.SQL Server encryption hierarchy

As can be seen from the figure, encryption is hierarchical. Each database instance has a service master key (Service Master Key), which is the root key of the instance and is automatically generated when the instance is installed. It is protected (Data Pertection API) by the data protection API provided by Windows. In addition to providing encryption services for its child nodes, the service master key is also used to encrypt some instance-level information, 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 (Database Master Key), which is encrypted by the service master key. This is a database-level key that can be used to provide encryption for creating database-level certificates or asymmetric keys, and each database can have only one database master key.

EKM module, which is a special extensible key management module, enhances the ability of sqlserver key management, allows keys to be stored outside the database, including hardware such as smart cards, USB devices, or hardware security modules (HSM), and allows third-party products to manage keys and perform encryption In addition, a higher performance HSM module can be used to encrypt and decrypt conditionally to reduce the loss of performance on encryption and decryption.

2.SQL Server encryption method object defines encryption

Without guaranteeing the definition information of triggers, stored procedures, views, etc., we can add WITH ENCRYPTION fields to encrypt sqlserver objects when defining them.

Column data encryption

Encrypt a column of data in a table through a function. It can be encrypted by password, symmetric key, asymmetric key, certificate and so on. Among them, it also includes an encryption function with a verifier, which is used to solve the problem of ciphertext replacement. The verifier generally chooses the unique id that does not change, so that even if the ciphertext is replaced and the verifier is wrong, it still fails.

Connection encryption

Encrypt the ssl connection through a certificate, which is generally used for mirroring, and the previous connection between the master and slave machines is in this way.

TDE (transparent data encryption)

Transparent data encryption, as the name implies, is to complete the encryption and decryption operation without the user's perception.

It encrypts at the page level, encrypts before writing to disk, decrypts when reading into memory, and encrypts data and log files in real time, and backup files are encrypted together.

The key is stored in the database boot record, protected by the certificate or asymmetric key, and can also be used with the EKM module. Officially, it takes up 3% more than 5% of cpu resources.

TDE also has some disadvantages:

The compression ratio is small, and because it is encrypted and then compressed, the backup cannot be significantly compressed.

Backup is also encrypted. Be careful when you restore, and backup the certificate and key to restore the database in another instance.

There is some loss in performance.

Backup encryption (2014)

Support encryption in the process of backup, and support compression in encryption first, maintaining a high compression ratio, breaking the dilemma of almost no compression ratio after using transparent data encryption.

Therefore, using native backup encryption can provide additional security for data at a very low cost, whether backing up data to a remote data center or to the cloud.

Full encryption (2016)

Full encryption, the data is always encrypted, you can perform operations on the encrypted data without first decrypting them, that is to say, the encrypted sensitive information will not have a chance to become plaintext.

Full encryption is processed for columns. After creating a column master key and a column encryption key, you can set column encryption when you create a table.

There are two encryption modes: deterministic encryption and random encryption.

Deterministic encryption ensures that the result of encrypting a value is always the same, which allows the user to compare, join, and group the data column. The disadvantage of deterministic encryption is that it is possible to guess the original text, while random encryption can ensure that the results of a given value will always be different after any two encryption, thus eliminating the possibility of guessing the original value. It is officially recommended that columns that need to be searched and grouped use deterministic encryption, while comments and other sensitive information that do not search for grouping use random encryption.

This is the end of the content of "how to encrypt SQL Server data". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.