1

i have encrypted the existing table columns using always encrypted feature in SQL server 2016. few of my existing queries are failing as we have used case statement in where clause, LEN and substring function. can someone tell me how to use these function's on always encrypted column. i am getting the below error -

Substring(SSN,2,5)

Encryption scheme mismatch for columns/variables 'SSN'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', e... expects it to be (encryption_type = 'PLAINTEXT') (or weaker).

Windows10
  • 51
  • 9

1 Answers1

1

Currently like, Len, or other string functions are not supported on encrypted columns.
Always Encrypted supports two types of encryption schemes:

Deterministic: Supports equality operation
Randomized: Does not support any operations

•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.

Hope that helps, if you have additional question, please post them in the comments section

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
  • Thanks Nikhil. I am using the Deterministic encryption type and i agree with above, but if we have to use like len or string related function in procedure then how to achieve it in SQL procedure? – Windows10 Apr 05 '17 at 19:09
  • 1
    Unfortunately, currently like, Len, or other string functions are not supported on encrypted columns. – Nikhil Vithlani - Microsoft Apr 05 '17 at 19:43
  • Nikhil, do you guys have any plan to support wild card search and SQL aggregate function in next release? – Windows10 Apr 06 '17 at 18:39