Encrypting a varbinary(max) field

• RaiderX

  Say Hey Kid

  Points: 678

  More actions

  March 9, 2011 at 3:06 pm

  #233814

  Hi Everyone. I'm hoping someone can help me out in this.

  I have a requirement to store a small number of PDF's within a SQL Server table. However, they also need to be encrypted, due to the nature of some information contained in the documents.

  I can create a field that is varbinary(max) and store the PDF in that field (I used the following simple insert (although I'm sure there's probably an easier way):

  INSERT INTO table2 (account_id, form, date_created, pdf_image_max1)

  SELECT 99999999 AS account_id, 'PXX' AS form, getdate() AS date_created, * FROM OPENROWSET(BULK N'D:\mydoc.pdf', SINGLE_BLOB) AS pdf_image_max1

  This is in an unencrypted state and I'm just researching at this point.

  I also have another varbinary(max) field in the table, where I was going to stored the encrypted value of the insert I just did. However, when I try to encrypt it using:

  update letters2 set pdf_image_max2=EncryptByKey(Key_GUID('MySymKey'), pdf_image_max1)

  I get a truncation error. I Googled this and it seems like the encrypted value is always larger than the unencypted value and because of that, the encryption of a varbinary(max) field causes the truncation (but I'm not 100% sure). The actual binary size of the PDF is much smaller than varbinary(max), but more than varbinary(8000) so I'm a little stuck now.

  Has anyone run across this and worked through a solution?

  Many thanks in advance.

  David

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 9, 2011 at 4:00 pm

  #1296842

  The return type of ENCRYPTBYKEY is varbinary(8000) http://msdn.microsoft.com/en-us/library/ms174361.aspx

  You may want to look into doing something homegrown using SQLCLR, or look around for projects on codeplex or sourceforge.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato