SQL Server 2016-是否可以串联两个nvarchar始终加密的列？ [英] SQL Server 2016 - Is it possible to concatenate two nvarchar always encrypted columns?

问题描述

我使用以下方法创建了一个表：

I have created a table using:

create table dbo.employee(firstname nvarchar(100) null,lastname nvarchar(100) null)

使用以下方法插入一些示例数据：

Inserted some sample data using:

insert into dbo.employee values('Sachin','Tendulkar')
insert into dbo.employee values('Rohit','Sharma')
insert into dbo.employee values('Virendra','Sehwag')
insert into dbo.employee values('Irfan','Pathan')

然后，我使用始终加密向导使用SSMS v17对该表的两者列进行加密。现在，我正在尝试将姓氏与姓氏连接起来，例如：

Then I used always encrypted wizard to encrypt both columns of this table using SSMS v17. And now I am trying to concatenate firstname with lastname like this:

select concat(firstname, lastname) from dbo.employee

它给了我下面的错误：

操作符类型冲突：nvarchar（100）使用（encryption_type =
'DETERMINISTIC'，encryption_algorithm_name =
'AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name =
'Sampledata_base_key_name， 'SampleDB'）是
与varchar不兼容

Operand type clash: nvarchar(100) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 'SampleDB') is incompatible with varchar

当我尝试这样做时：

select firstname + lastname from dbo.employee

它给出以下错误：

列/变量 firstname，
lastname的加密方案不匹配。列/变量的加密方案为
（encryption_type ='DETERMINISTIC'，encryption_algorithm_name =
'AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name =
'SampleDB_CEK'，column_encryption_key'b $ amp $数据库数据库并且行'1'附近的表达式期望它是（encryption_type =
'PLAINTEXT'）（或更弱）。

Encryption scheme mismatch for columns/variables 'firstname', 'lastname'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 'SampleDB') and the expression near line '1' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).

Any help appreciated.

推荐答案

在加密列上不允许串联。当前，对加密列的唯一可能操作是相等性。这是由于SQL Server没有密钥这一事实。

Concatenation in not allowed on encrypted columns. Currently the only operation possible on encrypted columns is equality. This is due to the fact that SQL Server does not have the key.

您可能必须在客户端应用程序中实现此逻辑。

You might have to implement this logic in the client application.

摘自官方文档

对于给定的纯文本值，确定性加密总是为
生成相同的加密值。使用确定性加密可以对加密的
列进行
点查找，相等连接，分组和索引。但是，但也可能允许未经授权的用户通过检查
加密列中的模式来猜测有关加密值的
信息，尤其是在存在少量可能的
加密值的情况下，例如True /错误，或北部/南部/东部/西部地区。
确定性加密必须对字符列使用具有二进制2
排序顺序的列排序规则。

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.

这篇关于SQL Server 2016-是否可以串联两个nvarchar始终加密的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！