SQL Server 2016 Always Encrypted 解析

SQL Server 2016 Always Encrypted 解析

首先最好的文档在微软的网站:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017

always encrypted 有两种方式, 一种是deterministic 和 randomized. 两者的区别:

• Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.
• Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.

如果说要在某加密列上filter 或 join 建议使用Deterministic模式

Develop using Always Encrypted with .NET Framework Data Provider

Query characteristic	Always Encrypted is enabled and application can access the keys and key metadata	Always Encrypted is enabled and application cannot access the keys or key metadata	Always Encrypted is disabled
Queries with parameters targeting encrypted columns.	Parameter values are transparently encrypted.	Error	Error
Queries retrieving data from encrypted columns, without parameters targeting encrypted columns.	Results from encrypted columns are transparently decrypted. The application receives plaintext values of the .NET datatypes corresponding to the SQL Server types configured for the encrypted columns.	Error	Results from encrypted columns are not decrypted. The application receives encrypted values as byte arrays (byte[]).

所以对于Always Encrypted来说
有两个开关:
一个是所谓的"Always Encrypted is disabled" , 指的是是否在链接字符串里加上图中的这句话

如果开启的话, 当查询加密的字段时, 如果查询者有权限, 会自动解密, 透明传输.

另一个是Queries with parameters targeting encrypted columns.

指的是查询会不会对加密的列进行 where 这样的操作

declare @a char(10) ='str       '
select * from [dbo].[ttt] where [str] = @a

假设上面的str字段有使用always encrypted加密的话, 就必须在SSMS中开启一个选项, 见下图:

然后查询才能成功.

在dotnet编程时, 可以参考这个链接: 

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-2017

在加密列上进行操作.

补记:

在使用Azure SQL Database时, 可以用Azure Key Vault来来存储用户加密的master key(普通sqlserver使用证书)

Deterministic encryption must use a column collation with a binary2 sort order for character columns.