In the example you linked the data is encrypted with the symmetric key (SSN_Key_01) which in turn is encrypted with the a certificate (HumanResources037) that in turn is encrypted by the database master key that in turn is encrypted with the service master key that is stored in DPAPI and therefore encrypted with a key that can is encrypted with a key derived from the service account password. This is a fairly typical encryption key hierarchy. It protects primarily against accidental media loss: if someone gets hold of the MDF/LDF files or a backup file (eg. from an old HDD improperly disposed or from a lost/stolen laptop), these files cannot be used to retrieve the encrypted information because the 'founder' cannot decrypt the database master key. As a side note, accidental media loss is the major reason for sensitive data leaks, so its well worth protecting against. This scheme though it does not protect against compromised access to the SQL Server itself: if an attacker can run queries on the server (eg. SQL injection) it can retrieve the encrypted data even though it does not know the encryption key (of course, data is still subject to access protection, ie. read/write privileges on the data and on the keys). While it may sound bad that any user can decrypt the data (given enough privileges, even if it doesn't know the password), it is a given for any scheme in which the server has to serve the data w/o asking the user for the decryption password. In general though, if you can afford it (Enterprise Edition license required) Transparent Data Encryption is a better alternative that this scheme.
Another common scheme is when the data is encrypted with a symmetric key that in turn is encrypted with a certificate that in turn is encrypted with a password. In this scheme the server cannot decrypt the data unless the certificate is open in the session using the password. This scheme is common in multi-tenant applications, where each tenant wants to make sure that other tenants and/or the system administrators/owners cannot access the data (since they do not know the password). The application has to request the data access password on each session, since the server cannot decrypt the data (it doesn't know the keys).
发布评论
评论（1）
在您链接的示例中，数据使用对称密钥 (
SSN_Key_01) 加密，而对称密钥又使用证书 (HumanResources037) 加密，而证书又由数据库加密主密钥又使用存储在 DPAPI，因此使用密钥进行加密，该密钥可以使用从服务帐户密码派生的密钥进行加密。这是一个相当典型的加密密钥层次结构。它主要防止意外介质丢失：如果有人获取了 MDF/LDF 文件或备份文件（例如，来自处理不当的旧 HDD 或来自丢失/被盗的笔记本电脑），这些文件将无法用于检索加密信息因为“创始人”无法解密数据库主密钥。顺便说一句，意外媒体丢失是敏感数据泄露的主要原因，因此值得防范。该方案虽然不能防止对 SQL Server 本身的访问受到损害：如果攻击者可以在服务器上运行查询（例如 SQL 注入），那么即使攻击者不知道，它也可以检索加密的数据加密密钥（当然，数据仍然受到访问保护，即数据和密钥的读/写权限）。虽然任何用户都可以解密数据（即使不知道密码，也有足够的权限）听起来可能很糟糕，但对于服务器必须提供服务的任何方案来说，这是一个给定的情况。数据不询问用户解密密码。一般来说，如果您负担得起（需要企业版许可证）透明数据加密< /a> 是比该方案更好的替代方案。另一种常见的方案是使用对称密钥对数据进行加密，而对称密钥又使用证书进行加密，而证书又使用密码进行加密。在此方案中，除非使用密码在会话中打开证书，否则服务器无法解密数据。此方案在多租户应用程序中很常见，其中每个租户都希望确保其他租户和/或系统管理员/所有者无法访问数据（因为他们不知道密码）。应用程序必须在每个会话上请求数据访问密码，因为服务器无法解密数据（它不知道密钥）。
In the example you linked the data is encrypted with the symmetric key (
SSN_Key_01) which in turn is encrypted with the a certificate (HumanResources037) that in turn is encrypted by the database master key that in turn is encrypted with the service master key that is stored in DPAPI and therefore encrypted with a key that can is encrypted with a key derived from the service account password. This is a fairly typical encryption key hierarchy. It protects primarily against accidental media loss: if someone gets hold of the MDF/LDF files or a backup file (eg. from an old HDD improperly disposed or from a lost/stolen laptop), these files cannot be used to retrieve the encrypted information because the 'founder' cannot decrypt the database master key. As a side note, accidental media loss is the major reason for sensitive data leaks, so its well worth protecting against. This scheme though it does not protect against compromised access to the SQL Server itself: if an attacker can run queries on the server (eg. SQL injection) it can retrieve the encrypted data even though it does not know the encryption key (of course, data is still subject to access protection, ie. read/write privileges on the data and on the keys). While it may sound bad that any user can decrypt the data (given enough privileges, even if it doesn't know the password), it is a given for any scheme in which the server has to serve the data w/o asking the user for the decryption password. In general though, if you can afford it (Enterprise Edition license required) Transparent Data Encryption is a better alternative that this scheme.Another common scheme is when the data is encrypted with a symmetric key that in turn is encrypted with a certificate that in turn is encrypted with a password. In this scheme the server cannot decrypt the data unless the certificate is open in the session using the password. This scheme is common in multi-tenant applications, where each tenant wants to make sure that other tenants and/or the system administrators/owners cannot access the data (since they do not know the password). The application has to request the data access password on each session, since the server cannot decrypt the data (it doesn't know the keys).