1

We want to implement the Always Encrypted feature for our application database. There is a table with a varbinary(max) column which is storing binary data of images, pdf documents, etc.

  • Does the Always Encrypted feature support varbinary(max) column encryption?
  • If yes, how to do I implement it using SQL Server 2016?

Any support is greatly appreciated.

0

The limitations are listed here and do not mention VARBINARY(MAX).

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

The way to do this when the feature first came out was:

https://justdaveinfo.wordpress.com/category/always-encrypted/

Disclosure
I am the author of the above blog

It is easier now though either via:

  • 1
    Hey David, thanks for the answer! Try to put some content in the post itself instead of just links to other sites, that way if the links die the post still has valuable content in it. – LowlyDBA Apr 3 '18 at 2:33
  • @David: Thanks a lot for your timely support. Yep, I didn't find VARBINARY(MAX) under the limitations section of AE and believed to be supported. – Sri Apr 3 '18 at 22:59
  • However, when I tried to implement this encryption on a varbinary(max) column, ran into a couple of issues. #1. When I tried to upload a pdf document, app thrown this error: Operand type clash: varbinary(max) is incompatible with varbinary(max) - Fixed this by setting the parameter type as SqlDbType.VarBinary on the data access layer. – Sri Apr 3 '18 at 22:59
  • #2. On further trying, got this error: The parameter "@FileData" does not have the same encryption information as the one it was created with. Use sp_refresh_parameter_encryption to refresh the parameter encryption information for the module. Fixed this issue by running the below script: EXEC sp_refresh_parameter_encryption [spUploadDocument]; – Sri Apr 3 '18 at 22:59
  • Initially, I had encrypted this varbinary(max) column for existing data using SSMS on DEV environment. When I tried to do the same thing on UAT/Staging environment, SSMS was throwing error and didn't complete the process. Today, I have figured out the option of using PowerShell Scripts for encrypting this column data and it worked well in DEV environment. Overall, it was a good lesson ... :) – Sri Apr 3 '18 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.