CODEWITHSUNDEEP
c#sqlencryptionsql-server-2016always-encrypted
April
April

Reputation: 151

How to query data when columns are encrypted in SQL Server 2016

I've been trying to find solution in this problem. I encrypted my columns from my database in SQL Server 2016 . In order to read the data I already set the parameters "Column Encryption Setting=Enabled"; I know I don't have problem in my certificate because I was able to query like SELECT * FROM TABLE, but I wasn't able to query using a WHERE condition. For example "SELECT column FROM Table WHERE column='abc'" something like that.

The Error is:

"Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC',... and so on"

I don't know why I can't retrieve the data from the encrypted column.

Upvotes: 5

Views: 18609

Answers (2)

dybzon
dybzon

Reputation: 1594

I assume you have encrypted your column using the Always Encrypted feature of SQL Server.

With Always Encrypted SQL Server does not know the keys for encrypting and decrypting your data. It's the job of your client application to encrypt data before sending it to SQL Server.

When you execute this query:

SELECT column FROM Table WHERE column='abc'

you are asking SQL Server to compare the non-encrypted varchar 'abc' to the encrypted values found in your encrypted column. This will fail - as you've noted yourself.

Your client application must encrypt the value you want to use in your WHERE clause before sending it off to SQL Server.

If you're using SSMS you can do this by enabling parameterization for always encrypted. You can enable this in SSMS under Query>>Query options>>Execution>>Advanced>>Enable Parameterization for Always Encrypted.

Once this is enabled you'll be able to filter your table like this:

DECLARE @MyValue VARCHAR(100) = 'abc'; 
SELECT [Column] FROM [Table] WHERE [Column] = @MyValue;

What happens behind the scenes is that SSMS encrypts the value of the @MyValue parameter for you before sending it off to SQL Server. It is important that you declare and initialize the parameter at once. Otherwise SSMS is unable to encrypt the value before sending the query to SQL Server.

You can read more about parameterization for always encrypted here. Feel free to comment below if the above explanation is unclear to you.

Upvotes: 3

S3S
S3S

Reputation: 25112

You need to use DECRYPTBYKEY

SELECT *
FROM Table WHERE convert(varchar,DecryptByKey(column))='abc'

The way you use this function depends on if you are using symmetric keys and a hash, or just a key, etc.

Upvotes: 0

Related Questions