SQL Server 和数据库加密密钥(数据库引擎)SQL Server and Database Encryption Keys (Database Engine)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 使用加密密钥帮助保护存储在服务器数据库中的数据、凭据和连接信息。

uses encryption keys to help secure data, credentials, and connection information that is stored in a server database. SQL ServerSQL Server 的密钥分为两种:“对称” 和“非对称” 。has two kinds of keys: symmetric and asymmetric. 对称密钥使用相同的密码对数据进行加密和解密。Symmetric keys use the same password to encrypt and decrypt data. 非对称密钥使用一个密码来加密数据(称为公 钥),使用另一个密码来解密数据(称为私 钥)。Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

SQL ServerSQL Server中,加密密钥包括一组用来保护敏感数据的公钥、私钥和对称密钥。In SQL ServerSQL Server, encryption keys include a combination of public, private, and symmetric keys that are used to protect sensitive data. 当第一次启动 SQL ServerSQL Server 实例时,将在 SQL ServerSQL Server 初始化过程中创建对称密钥。The symmetric key is created during SQL ServerSQL Server initialization when you first start the SQL ServerSQL Server instance. SQL ServerSQL Server 使用此密钥来加密存储在 SQL ServerSQL Server中的敏感数据。The key is used by SQL ServerSQL Server to encrypt sensitive data that is stored in SQL ServerSQL Server. 公钥和私钥由操作系统创建,用于保护对称密钥。Public and private keys are created by the operating system and they are used to protect the symmetric key. 对于在数据库中存储敏感数据的每个 SQL ServerSQL Server 实例,都要创建一个公钥私钥对。A public and private key pair is created for each SQL ServerSQL Server instance that stores sensitive data in a database.

SQL Server 和数据库密钥的应用Applications for SQL Server and Database Keys

SQL ServerSQL Server 在密钥中的应用主要有两个方面:作为某 SQL ServerSQL Server 实例上为该实例生成的服务主密钥 (SMK) 和作为用于数据库的数据库主密钥 (DMK)。has two primary applications for keys: a service master key (SMK) generated on and for a SQL ServerSQL Server instance, and a database master key (DMK) used for a database.

服务主密钥Service master key

服务主密钥是 SQL Server 加密层次结构的根。The Service Master Key is the root of the SQL Server encryption hierarchy. 当第一次启动 SQL ServerSQL Server 实例时,将自动生成 SMK 并用于对链接的服务器密码、凭据和数据库主密钥进行加密。The SMK is automatically generated the first time the SQL ServerSQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key. SMK 是通过使用采用 Windows 数据保护 API (DPAPI) 的本地计算机密钥进行加密的。The SMK is encrypted by using the local machine key using the Windows Data Protection API (DPAPI). DPAPI 使用从 SQL ServerSQL Server 服务帐户的 Windows 凭据和计算机的凭据派生的密钥。The DPAPI uses a key that is derived from the Windows credentials of the SQL ServerSQL Server service account and the computer's credentials. 服务主密钥只能由创建它时所用的服务帐户或可以访问该计算机凭据的主体进行解密。The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the machine's credentials.

只有创建服务主密钥的 Windows 服务帐户或有权访问服务帐户名称和密码的主体能够打开服务主密钥。The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 使用 AES-256 加密算法来保护服务主密钥 (SMK) 和数据库主密钥 (DMK)。uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). AES 是一种比早期版本中使用的 3DES 更新的加密算法。AES is a newer encryption algorithm than 3DES used in earlier versions. 在将 数据库引擎Database Engine 实例升级到 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 后,应重新生成 SMK 和 DMK 以便将主密钥升级到 AES。After upgrading an instance of the 数据库引擎Database Engine to SQL Server 2019 (15.x)SQL Server 2019 (15.x) the SMK and DMK should be regenerated in order to upgrade the master keys to AES. 有关重新生成 SMK 的详细信息,请参阅 ALTER SERVICE MASTER KEY (Transact-SQL)ALTER MASTER KEY (Transact-SQL)For more information about regenerating the SMK, see ALTER SERVICE MASTER KEY (Transact-SQL) and ALTER MASTER KEY (Transact-SQL).

数据库主密钥Database master key

数据库主密钥是一种用于保护数据库中存在的证书私钥和非对称密钥的对称密钥。The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database. 它还可用于对数据进行加密,但由于它有长度限制,所以用于数据加密时实用性不如对称密钥。It can also be used to encrypt data, but it has length limitations that make it less practical for data than using a symmetric key. 要启用数据库主密钥的自动解密,请使用 SMK 对此密钥的副本进行加密。To enable the automatic decryption of the database master key, a copy of the key is encrypted by using the SMK. 此密钥的副本存储在使用它的数据库和 master 系统数据库中。It is stored in both the database where it is used and in the master system database.

每当更改 DMK 时,存储在 master 系统数据库中的 DMK 副本都将在没有提示的情况下更新。The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed. 但是,使用 DROP ENCRYPTION BY SERVICE MASTER KEY 语句的 ALTER MASTER KEY 选项可以更改此默认设置。However, this default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. 必须使用 OPEN MASTER KEY 语句和密码打开未使用服务主密钥进行加密的 DMK。A DMK that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

管理 SQL Server 和数据库密钥Managing SQL Server and Database Keys

对加密密钥的管理包括创建新数据库密钥,创建服务器和数据库密钥的备份以及了解还原、删除或更改密钥的条件和方式。Managing encryption keys consists of creating new database keys, creating a backup of the server and database keys, and knowing when and how to restore, delete, or change the keys.

若要管理对称密钥,可以使用 SQL ServerSQL Server 中包括的工具执行下列操作:To manage symmetric keys, you can use the tools included in SQL ServerSQL Server to do the following:

  • 备份服务器和数据库密钥的副本,以便可以使用这些密钥来恢复服务器安装,或作为计划迁移的一部分。Back up a copy of the server and database keys so that you can use them to recover a server installation, or as part of a planned migration.

  • 将以前保存的密钥还原到数据库。Restore a previously saved key to a database. 这样,新服务器实例就可以访问最初不是由其加密的现有数据。This enables a new server instance to access existing data that it did not originally encrypt.

  • 当不能再访问加密数据时删除数据库中的加密数据,这种情况极少出现。Delete the encrypted data in a database in the unlikely event that you can no longer access encrypted data.

  • 当密钥的安全性受到威胁时,重新创建密钥并重新对数据进行加密,这种情况极少出现。Re-create keys and re-encrypt data in the unlikely event that the key is compromised. 作为安全性方面的最佳做法,您应定期(例如,每隔几个月)重新创建密钥以保护服务器,使其能够抵御试图解开密钥的攻击。As a security best practice, you should re-create the keys periodically (for example, every few months) to protect the server from attacks that try to decipher the keys.

  • 在服务器扩展部署(多个服务器同时共享单个数据库以及为该数据库提供可逆加密的密钥)中添加或删除服务器实例。Add or remove a server instance from a server scale-out deployment where multiple servers share both a single database and the key that provides reversible encryption for that database.

重要的安全信息Important Security Information

访问由服务主密钥保护的对象需要使用用来创建该密钥的 SQL ServerSQL Server 服务帐户或计算机帐户。Accessing objects secured by the service master key requires either the SQL ServerSQL Server Service account that was used to create the key or the computer (machine) account. 即,计算机与创建密钥的系统绑定在一起。That is, the computer is tied to the system where the key was created. 可以更改 SQL ServerSQL Server 服务帐户或 计算机帐户,而不会失去对密钥的访问权限。You can change the SQL ServerSQL Server Service account or the computer account without losing access to the key. 但是,如果同时更改两者,则将失去对服务主密钥的访问权限。However, if you change both, you will lose access to the service master key. 如果在不具有这两个元素中的任何一个的情况下失去了服务主密钥的访问权限,则将无法对使用原始密钥加密的数据和对象进行解密。If you lose access to the service master key without one of these two elements, you be unable to decrypt data and objects encrypted by using the original key.

如果没有服务主密钥,则将无法还原受服务主密钥保护的连接。Connections secured with the service master key cannot be restored without the service master key.

访问受数据库主密钥保护的对象和数据只需要有用于帮助保护密钥的密码。Access to objects and data secured with the database master key require only the password that is used to help secure the key.

注意

如果失去了对前述密钥的所有访问权限,则将无法访问由这些密钥保护的对象、连接和数据。If you lose all access to the keys described earlier, you will lose access to the objects, connections, and data secured by those keys. 可按照此处显示的链接中说明的方法还原服务主密钥,也可以返回原始加密系统来恢复访问权限。You can restore the service master key, as described in the links that are shown here, or you can go back to the original encrypting system to recover the access. 没有用来恢复访问权限的“后门”。There is no "back-door" to recover the access.

本节内容In This Section

服务主密钥SERVICE MASTER KEY
简要介绍服务主密钥及其最佳用法。Provides a brief explanation for the service master key and its best practices.

可扩展密钥管理 (EKM) Extensible Key Management (EKM)
说明如何在 SQL ServerSQL Server中使用第三方密钥管理系统。Explains how to use third-party key management systems with SQL ServerSQL Server.

备份服务主密钥 Back Up the Service Master Key

还原服务主密钥 Restore the Service Master Key

创建数据库主密钥 Create a Database Master Key:

备份数据库主密钥Back up a database master key

还原数据库主密钥Restore a database master key

在两个服务器上创建相同的对称密钥Create identical symmetric keys on two servers

使用 EKM 在 SQL Server 上启用 TDE Enable TDE on SQL Server Using EKM

使用 Azure 密钥保管库的可扩展密钥管理 (SQL Server) Extensible Key Management Using Azure Key Vault (SQL Server)

加密数据列 Encrypt a column of data

CREATE MASTER KEY (Transact-SQL) CREATE MASTER KEY (Transact-SQL)

ALTER SERVICE MASTER KEY (Transact-SQL) ALTER SERVICE MASTER KEY (Transact-SQL)

还原数据库主密钥Restore a database master key

另请参阅See Also

备份和还原 Reporting Services 加密密钥 Back Up and Restore Reporting Services Encryption Keys
删除和重新创建加密密钥(SSRS 配置管理器) Delete and Re-create Encryption Keys (SSRS Configuration Manager)
添加和删除扩展部署的加密密钥(SSRS 配置管理器) Add and Remove Encryption Keys for Scale-Out Deployment (SSRS Configuration Manager)
透明数据加密 (TDE) Transparent Data Encryption (TDE)