none
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

    Tuesday, April 03, 2012 8:18 PM

Answers

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


    • Edited by Arbi Baghdanian Thursday, April 05, 2012 8:31 PM
    • Marked as answer by SAL Tuesday, June 19, 2012 1:31 PM
    Thursday, April 05, 2012 8:30 PM

All replies

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

    • Edited by Arbi Baghdanian Tuesday, April 03, 2012 9:07 PM
    • Marked as answer by SAL Thursday, April 05, 2012 1:20 PM
    • Unmarked as answer by SAL Thursday, April 05, 2012 8:08 PM
    Tuesday, April 03, 2012 9:06 PM
  • 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

    Wednesday, April 04, 2012 12:24 PM
  • 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).

    Thursday, April 05, 2012 8:21 PM
  • 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.


    • Edited by Arbi Baghdanian Thursday, April 05, 2012 8:31 PM
    • Marked as answer by SAL Tuesday, June 19, 2012 1:31 PM
    Thursday, April 05, 2012 8:30 PM
  • 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

    Thursday, April 05, 2012 9:35 PM
  • Thursday, April 19, 2012 6:21 PM