sql server 加密

介绍 (Intro)

We use cryptography every day: on the internet, mobile devices, ATM machines, and in almost every aspects of our digital life. In a nutshell, cryptography is about data scrambling and hiding, depending on the implementation and user-specific needs.

我们每天使用加密技术：在互联网，移动设备，ATM机以及我们数字生活的几乎所有方面。 简而言之，密码术是关于数据加扰和隐藏的，具体取决于实现方式和特定于用户的需求。

A database is the spine of every information system and is the specific target of potential data thieves. SQL Server has one of the best cryptographic set of features that we can use to create a state of the art security and privacy “aware” systems.

数据库是每个信息系统的骨干，并且是潜在数据窃贼的特定目标。 SQL Server具有最好的加密功能集之一，可用于创建最先进的安全性和隐私“感知”系统。

Cryptology is an art and science based in mathematics theory with the purpose of creating secret codes.  It has two major components:

密码学是一门基于数学理论的艺术和科学，旨在创建密码。 它具有两个主要组成部分：

• Cryptography
  密码学
• 

  Figure 1. Cryptology scheme

  图1.密码方案

Cryptography is about creating a secret codes, while cryptanalysis is the process of breaking secret codes.

Cryptography has two major parts:

密码学是关于创建密码的，而密码分析是破解密码的过程。

密码学有两个主要部分：

• Encryption – process of creating an obfuscated message from a plain text using a key.
  加密-使用密钥从纯文本创建混淆消息的过程。
• Decryption – process of returning plain text from an obfuscated message using a key.
  解密–使用密钥从混淆后的消息中返回纯文本的过程。

• Backdoor – a cryptosystem has a backdoor for accessing plain text from encrypted messages without the regular process of decryption.
  后门–密码系统具有后门，可用于从加密消息访问纯文本，而无需常规的解密过程。
• Brute force – the only way to decrypt a message, aside from the regular decryption process, is by testing all possible combinations.
  蛮力–除常规解密过程外，解密消息的唯一方法是测试所有可能的组合。

密码学的类型 (Types of Cryptography)

加密有两种类型：

• Symmetric cryptography
  对称密码学
• Asymmetric cryptography
  非对称密码学

对称密码学 (Symmetric Cryptography)

In symmetric cryptography cases, the sender and recipient share a key that is used to perform encryption and decryption. Symmetric cryptography is the most popular way for encryption in modern IT.

在对称加密的情况下，发送者和接收者共享一个密钥，该密钥用于执行加密和解密。 对称加密是现代IT中最流行的加密方式。

Figure 2. Symmetric cryptography

图2.对称密码学

Some of the most common symmetric algorithms are:  Rijndael (AES) and Triple DES (3DES).

一些最常见的对称算法是：Rijndael（AES）和Triple DES（3DES）。

Symmetric cryptography is simple because the same key that is used for encryption and decryption. But, before communication can occur, the sender and the recipient must exchange a secret key. The exchange of the shared secret key is the only weakness of symmetric cryptography.

对称加密很简单，因为用于加密和解密的密钥相同。 但是，在可以进行通信之前，发送方和接收方必须交换密钥。 共享密钥的交换是对称密码学的唯一弱点。

非对称密码 (Asymmetric Cryptography)

With asymmetric cryptography (also known as public key cryptography), the sender encrypts data with one key, and the recipient uses another key for decryption. The encryption and decryption key are known to us as a public/private key pair.

使用非对称密码学（也称为公钥密码学），发送方使用一个密钥对数据进行加密，而接收方使用另一密钥进行解密。 加密和解密密钥在我们看来是公钥/私钥对。

Figure 3. Asymmetric cryptography

图3.非对称密码

The most commonly used asymmetric algorithm is the RSA algorithm.

最常用的非对称算法是RSA算法。

Asymmetric encryption requires more processing power than symmetric encryption. Because of this, asymmetric encryption is usually optimized by adding a symmetric key to encrypt a message and then asymmetrically encrypting the shared key. This can reduce the amount of data that is asymmetrically encrypted and also improves performance.

与对称加密相比，非对称加密需要更多的处理能力。 因此，通常通过添加对称密钥对消息进行加密，然后对共享密钥进行非对称加密来优化非对称加密。 这样可以减少非对称加密的数据量，并提高性能。

什么是钥匙？ (What is a Key?)

A cipher or cryptosystem is used to encrypt data. A key is used to configure a cryptosystem for encryption and decryption. A fundamental principle of cryptography is that the inner workings of a cryptosystem are completely known to everyone. However, the key is the only secret. This principle is known as the Kerckhoffs’ Principle.

密码或密码系统用于加密数据。 密钥用于配置用于加密和解密的密码系统。 密码学的基本原理是，每个人都完全了解密码系统的内部工作原理。 但是，密钥是唯一的秘密。 该原理被称为Kerckhoffs原理 。

A key is the product of a specific cryptosystem and is based on randomly collected information, such as random numbers, the temperature of the CPU, sample data in RAM, etc. Both symmetric and asymmetric keys are measured in bits. Despite this similarity, symmetric and asymmetric keys are different. For example, a symmetric key using AES can be 256 bits long, while an asymmetric key using RSA can be as long as 2048 bits. Although 2048 bits may appear more secure than 256 bits, it does not mean that RSA is more secure than AES. Both RSA and AES are different and not comparable. For example, the security available with a 1024-bit key using asymmetric RSA is considered approximately equal in security to an 80-bit key using a symmetric algorithm.

密钥是特定密码系统的产物，它基于随机收集的信息，例如随机数，CPU的温度，RAM中的样本数据等。对称和非对称密钥均以位为单位。 尽管存在相似之处，但对称和非对称密钥是不同的。 例如，使用AES的对称密钥的长度可以为256位，而使用RSA的非对称密钥的长度可以为2048位。 尽管2048位看上去比256位更安全，但这并不意味着RSA比AES更安全。 RSA和AES都是不同的，不可比。 例如，使用非对称RSA的1024位密钥的可用安全性被认为与使用对称算法的80位密钥的安全性大致相等。

SQL Server加密功能 (SQL Server Cryptographic Features)

SQL Server提供以下加密机制：

• Transact-SQL functions
  Transact-SQL函数
• Asymmetric keys
  非对称密钥
• Symmetric keys
  对称键
• Certificates
  证明书
• Transparent Data Encryption
  透明数据加密
• Backup encryption
  备份加密

加密层级 (Encryption Hierarchy)

SQL Server使用分层加密来加密数据。 每一层使用证书，非对称密钥和对称密钥对它下面的层进行加密。

Figure 4. Encryption hierarchy

图4.加密层次结构

服务主密钥 (Service Master Key)

SQL Server has two primary applications for keys: a Service Master Key (SMK) generated on and for a SQL Server instance, and a Database Master Key (DMK) used for a database.

SQL Server有两个主要的密钥应用程序：在SQL Server实例上和为SQL Server实例生成的服务主密钥（SMK），以及用于数据库的数据库主密钥（DMK）。

The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the DMK. The SMK is encrypted by using the local computer key which uses the Windows Data Protection API (DPAPI).

第一次启动SQL Server实例时，会自动生成SMK，并将其用于加密链接的服务器密码，凭据和DMK。 通过使用使用Windows数据保护API（DPAPI）的本地计算机密钥对SMK进行加密。

The SMK should be backed up and stored in a secure, off-site location.

应备份SMK并将其存储在安全的异地位置。

BACKUP SERVICE MASTER KEY TO FILE = 'c:\Keys\SMK'ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rd'

RESTORE SERVICE MASTER KEYFROM FILE = 'c:\Keys\SMK'DECRYPTION BY PASSWORD = 'S0m3C00lp4sw00rd';GO

When you are playing with SMK (it is not recommended if it is not REALY necessary) keep in mind that the SMK is the foundation of the SQL Server encryption hierarchy. The SMK directly or indirectly protects all other keys and data in the encryption hierarchy tree. If a dependent key cannot be decrypted during a forced regeneration, the data that the key secures will be lost.

当您在使用SMK时（如果不是真的不建议这样做，则不建议使用），请记住SMK是SQL Server加密层次结构的基础。 SMK直接或间接保护加密层次结构树中的所有其他密钥和数据。 如果在强制再生期间不能解密从属密钥，则密钥保护的数据将丢失。

数据库主密钥 (Database Master Key)

The Database Master Key (DMK) is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a password. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the SMK and stored in both the database user and in the master database.

数据库主密钥（DMK）是一个对称密钥，用于保护证书中的私钥和数据库中存在的非对称密钥。 创建密钥后，将使用AES_256算法和密码对主密钥进行加密。 为了启用主密钥的自动解密，使用SMK对密钥副本进行加密，并将其存储在数据库用户和主数据库中。

We can create a database master with the CREATE MASTER KEY statement: (This example use sample AdventureWorks2012 database)

我们可以使用CREATE MASTER KEY语句创建数据库主数据库：（此示例使用示例AdventureWorks2012数据库）

USE AdventureWorks2012GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rd'GO

SELECT * FROM sys.symmetric_keys

You will get something like this:

您将获得如下内容：

Figure 5. Result of the SELECT * FROM sys.symmetric_keys statement on AdventureWorks2012

图5. AdventureWorks2012上的SELECT * FROM sys.symmetric_keys语句的结果

You should backup the master key by using BACKUP MASTER KEY and store the backup in a secure and off-site location.

Following operations can be done with DMK, after you create one.

您应该使用BACKUP MASTER KEY备份主密钥，并将备份存储在安全的异地位置。

创建DMK之后，可以使用DMK完成以下操作。

• ALTER
  改变
• OPEN
  打开
• CLOSE
  关
• BACKUP
  备份
• RESTORE
  恢复
• DROP
  下降

USE AdventureWorks2012GOALTER MASTER KEY REGENERATEWITH ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforNewK3y'GO

USE AdventureWorks2012GOOPEN MASTER KEYDECRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforNewK3y'GO

USE AdventureWorks2012GOCLOSE MASTER KEYGO

USE AdventureWorks2012GOOPEN MASTER KEYDECRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforNewK3y';BACKUP MASTER KEY TO FILE = 'c:\Keys\DMK'ENCRYPTION BY PASSWORD = '4jfmdn48ndno20';GO

USE AdventureWorks2012GORESTORE MASTER KEYFROM FILE = 'c:\Keys\DMK'DECRYPTION BY PASSWORD = '4jfmdn48ndno20'ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforNewK3y';GO

USE AdventureWorks2012GODROP MASTER KEYGO

Next time it will be even more confidential. We will cover in details: TDE, Backup encryption and practice examples of data encryption using symmetric and asymmetric keys.

下次它将更加机密。 我们将详细介绍：TDE，备份加密以及使用对称和非对称密钥进行数据加密的实践示例。

翻译自:

sql server 加密