Encryption using ENCRYPTBYKEY and DECRYPTBYKEY SQL 2008

Question

I'm trying to make sure that my calls to ENCRYPTBYKEY and DECRYPTBYKEY work correctly before I implement them in my Stored Procedures.  So far they all work correctly and I get no errors. 

However, when I DECRYPT THE DATA with DECRYPTBYKEY the resultset comes back as NULL values.  This is my first attempt at column encryption so I'm not sure if I missed a step or not.

Here are the statements that I am using, and the datatypes I have to work with are IMAGE and VARBINARY:

/* Create Database Master Key  */
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'MyPa$$w0rd'
GO

/* Create Encryption Certificate  */
CREATE CERTIFICATE MyTestEncryptCertificate
WITH SUBJECT = 'MyTest'
GO

/* Create Symmetric Key  */
CREATE SYMMETRIC KEY MyTestKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyTestEncryptCertificate
GO

/* Encrypt Data using Key and Certificate  */
OPEN SYMMETRIC KEY MyTestKey DECRYPTION
BY CERTIFICATE MyTestEncryptCertificate
UPDATE MyTestTable
SET Data = CAST(ENCRYPTBYKEY(KEY_GUID('MyTestKey'), CAST(Data AS VARBINARY)) AS IMAGE)
GO

/* Decrypt the data */
OPEN SYMMETRIC KEY MyTestKey DECRYPTION
BY CERTIFICATE MyTestEncryptCertificate
SELECT CONVERT(IMAGE,DECRYPTBYKEY(CAST(Data AS VARBINARY))) AS Data
FROM MyTestTable
GO

/* Clean up database  */
DROP SYMMETRIC KEY MyTestKey
GO
DROP CERTIFICATE MyTestEncryptCertificate
GO
DROP MASTER KEY
GO

If I test with datatypes that are VARCHAR and VARBINARY those statements will work just fine, but IMAGE not so much so.

My table structure is:

col1 (PK, int, not null)
col2 (FK, int, not null)
col3 (image, null)
col4 (int, null)

Here is my Server Info that I am working on:

Version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation 
  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 
Edition: Developer Edition (64-bit)
Product Version: 10.0.5500.0
Product Level: SP3

Answer 1

Please try:

OPEN SYMMETRIC KEY MyTestKey DECRYPTION
BY CERTIFICATE MyTestEncryptCertificate
Go
SELECT CONVERT(IMAGE,DECRYPTBYKEY(CAST(Data AS VARBINARY(Max)))) AS Data
FROM MyTestTable
GO

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

Thanks Arbi.  That worked.

I was under the impression from what I read that VARBINARY(MAX) would not work with DECRYPTBYKEY or ENCRYPTBYKEY only nvarchar, char, varchar, binary, varbinary, or nchar were supported.  I read that here: http://msdn.microsoft.com/en-us/library/ms174361(v=sql.100).aspx.

Also, I'm a C# programmer not a DBA, so the way I set this up my Stored Precedures should be able to encrypt and decrypt my column(s) via a CERTIFICATE without supplying a PASSWORD in the stored procedure, since I already defined the password when I created the MASTER KEY, is that correct?

I have seen many examples were the password is exposed when doing CREATE SYMMETRIC KEY and OPEN SYMMETRIC KEY with either ENCRYPTION BY PASSWORD or DECRYPTION BY PASSWORD which I don't want to do because it seems unsecure to me.

Thanks

Answer 3

Arbi,

Initially I thought your solution worked but the database I tested it on had smaller datalengths in the column.

When I run the following statement on the database where I am having issues, the datalengths are identical:

SELECT datalength(Data) AS ImageLength, datalength(CAST(Data AS VARBINARY(MAX))) AS VARBINARYMAXLength FROM MyTestTable

It is looking more and more like ENCRYPTBYKEY and DECRYPTBYKEY will only accept data types that are nvarchar, char, varchar, binary, varbinary, or nchar because I get "String or binary data would be truncated" when you use VARBINARY(MAX).

Answer 4

Thanks Arbi.  That worked.

I was under the impression from what I read that VARBINARY(MAX) would not work with DECRYPTBYKEY or ENCRYPTBYKEY only nvarchar, char, varchar, binary, varbinary, or nchar were supported.  I read that here: http://msdn.microsoft.com/en-us/library/ms174361(v=sql.100).aspx.

--> Where did you read that VARBINARY(MAX) could NOT be decrypted? I am reading same article and did NOT find that.

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 5

In the DecryptByKey

 Return Types

varbinary with a maximum size of 8,000 bytes.

Indirectly it means, that we can not encrypt varbinary(max) column.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Any progress?

Encryption example:

http://www.sqlusa.com/bestpractices2005/varbinary/

---

Kalman Toth SQL SERVER & BI TRAINING