Using Column encryption with symmetric keys

  • Hello All,

    I am trying to implement the column encryption on one of the tables, have used the below link as the reference and got stuck at the last step.

    http://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/

    I have completed the following steps so far.

    - CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’

    - CREATE CERTIFICATE MyCertificateName

    WITH SUBJECT = 'A label for this certificate'

    - CREATE SYMMETRIC KEY MySymmetricKeyName WITH

    IDENTITY_VALUE = 'a fairly secure name',

    ALGORITHM = AES_256,

    KEY_SOURCE = 'a very secure strong password or phrase'

    ENCRYPTION BY CERTIFICATE MyCertificateName;

    - CREATE PROCEDURE OpenKeys

    AS

    BEGIN

    SET NOCCOUNT ON;

    BEGIN TRY

    OPEN SYMMETRIC KEY MySymmetricKeyName

    DECRYPTION BY CERTIFICATE MyCertificateName

    END TRY

    BEGIN CATCH

    -- Handle non-existant key here

    END CATCH

    END

    -CREATE FUNCTION Encrypt

    (

    @ValueToEncrypt varchar(max)

    )

    RETURNS varbinary(256)

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Result varbinary(256)

    SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)

    -- Return the result of the function

    RETURN @Result

    END

    - CREATE FUNCTION Decrypt

    (

    @ValueToDecrypt varbinary(256)

    )

    RETURNS varchar(max)

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Result varchar(max)

    SET @Result = DecryptByKey(@ValueToDecrypt)

    -- Return the result of the function

    RETURN @Result

    END

    example by using the function

    EXEC OpenKeys

    -- Encrypting

    SELECT Encrypt(myColumn) FROM myTable

    -- Decrypting

    SELECT Decrypt(myColumn) FROM myTable

    when I ran the last command :

    -- Decrypting

    SELECT Decrypt(myColumn) FROM myTable

    I get the following error :

    Msg 257, Level 16, State 3, Line 2

    Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.

    Edit - where will I use the convert function, in decrypt function or in select statement?

    Can some one please help me in this regard?

  • You need to use the convert function, have a look at this thread

    😎

  • I understand that I need to use convert function, but how and where, need a bit more explanation. Hope people wont mind.

  • qur7 (6/25/2015)


    I understand that I need to use convert function, but how and where, need a bit more explanation. Hope people wont mind.

    Did you look at the thread in my post, there is an example there both where and how.

    😎

  • I did read the article, and since I am new at SQL Server , so I need a bit more direction. The relevant convert function I see is below from the article you mentioned.

    DECLARE @STRXHEX VARCHAR(50) = '0x1234567890abcdef';

    SELECT CONVERT(VARBINARY(50),@STRXHEX,1)

    Can you be a bit ore descriptive?

  • qur7 (6/25/2015)


    Can you be a bit ore descriptive?

    I'll give it a try;-)

    The error is coming from the Decrypt function when attempting to assign the output of DecryptByKey to the @Result variable, see the code below

    😎

    -- Decrypting

    CREATE FUNCTION Decrypt

    (

    @ValueToDecrypt varbinary(256)

    )

    RETURNS varchar(max)

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Result varchar(max)

    /* Explicit conversion from varbinary to varchar */

    SET @Result = CONVERT(VARCHAR(MAX),DecryptByKey(@ValueToDecrypt),1)

    -- Return the result of the function

    RETURN @Result

    END

  • Thank you very much. However, I still get the same error. just to recap this is what I did.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123'

    ..........................................................................................

    CREATE CERTIFICATE MyCertificateName

    WITH SUBJECT = 'A label for this certificate'

    ...............................................................................

    CREATE SYMMETRIC KEY MySymmetricKeyName WITH

    IDENTITY_VALUE = 'a fairly secure name',

    ALGORITHM = AES_256,

    KEY_SOURCE = 'a very secure strong password or phrase'

    ENCRYPTION BY CERTIFICATE MyCertificateName;

    ......................................................................................

    CREATE PROCEDURE OpenKeys

    AS

    BEGIN

    SET NOCOUNT ON;

    BEGIN TRY

    OPEN SYMMETRIC KEY MySymmetricKeyName

    DECRYPTION BY CERTIFICATE MyCertificateName

    END TRY

    BEGIN CATCH

    -- Handle non-existant key here

    END CATCH

    END

    .....................................................................................................

    CREATE FUNCTION Encrypt

    (

    @ValueToEncrypt varchar(max)

    )

    RETURNS varbinary(256)

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Result varbinary(256)

    SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)

    -- Return the result of the function

    RETURN @Result

    END

    -- Decrypting

    ALTER FUNCTION Decrypt

    (

    @ValueToDecrypt varbinary(256)

    )

    RETURNS varchar(max)

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Result varchar(max)

    /* Explicit conversion from varbinary to varchar */

    SET @Result = CONVERT(NVARCHAR(MAX),DecryptByKey(@ValueToDecrypt),1)

    -- Return the result of the function

    RETURN @Result

    END

    EXEC OpenKeys

    -- Encrypting

    SELECT dbo.Encrypt(CardNumber) FROM [Sales].[CreditCard]

    -- Decrypting

    SELECT dbo.Decrypt(CardNumber) FROM [Sales].[CreditCard]

    The error message is as follows when ran the Decrypting

    Msg 257, Level 16, State 3, Line 2

    Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.

    I am using AdventuresWorks database, creditcard table.

  • I am still stuck at this, can any one help?

  • well, for me, the question is:

    if you created A certificate, why are you not using it.

    I've always created a certificate, and then used EncryptByCert and DecryptByCert to handle the routines.

    no need to open keys, just call the function.

    using your setup, here's an encryption/decryption pair:

    SELECT EncryptByCert(Cert_ID('MyCertificateName'), '1234567812345678') AS [Evncalue]

    --copy paste that value from above

    SELECT CAST(DecryptByCert(Cert_ID('MyCertificateName'), 0xDE5D{snip example: paste the right value})

    AS VARCHAR(100)) AS [DecValue]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • for a start

    you have declared @Result as VARCHAR(MAX) so why are you converting to NVARCHAR(MAX) and so then forcing a further implicit conversion to VARCHAR(MAX)

    I'd also suggest checking what the return type is for the EncryptByKey function called in function Encrypt to see if that also needs to be converted.

    The error relates to something being converted to VARBINARY so check any assignments to variables of that type

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply