SQL Server 2016 - Is it possible to concatenate two nvarchar always encrypted columns?
I created a table using:
create table dbo.employee(firstname nvarchar(100) null,lastname nvarchar(100) null)
Insert some example 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')
Then I used the always encrypted wizard to encrypt the columns and this table using SSMS v17. And now I am trying to associate firstname with lastname like this:
select concat(firstname, lastname) from dbo.employee
And it gives me below error:
Operand type collision: 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 ', column_encryption_keyname is not' Column_encryption '
When I try this:
select firstname + lastname from dbo.employee
It gives the following error:
Encryption scheme mismatch for columns / variables 'firstname', 'Lastname'. Encryption scheme for columns / variables (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 1, it is SampleDB 'expecting a string PLAINTEXT ") (or weaker).
Any help was appreciated.
source to share
Concatenation is not allowed on encrypted columns. Currently, the only possible operation for encrypted columns is equality. This is because SQL Server does not have a key.
You may need to implement this logic in your client application.
From the official documentation
Deterministic encryption always generates the same encrypted value for any given plain text value. The use of deterministic encryption allows point search, equality joining, grouping, and indexing on encrypted columns. However, it can also allow unauthorized users to guess the encrypted value information 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 binary2 collation for character columns.
Randomized encryption uses a method that encrypts data in a less predictable way. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining into encrypted columns.
source to share