Post reply

SELECT with encrypted data always return NULL fields

  • June 5, 2018 at 5:18 am

    #369969

    Hi,
    I hope this is the right section for this thread.
    I'm doing tests with encryption/decryption of SQL Server (2014 Express) and I'm facing some problems with SELECT queries.
    To start from the beginning, I created a simple table with two fields:

    CREATE TABLE PROVA(CAMPO1 VARBINARY(500), CAMPO2 VARCHAR(300))

    then I created tparameters for encryption:


    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@assw0rd'
    CREATE CERTIFICATE CertProva WITH SUBJECT = 'CertProva', EXPIRY_DATE='21001231';

    CREATE SYMMETRIC KEY DK02 WITH
    IDENTITY_VALUE = 'IDVal',
    ALGORITHM = AES_256,
    KEY_SOURCE = 'kSource'
    ENCRYPTION BY CERTIFICATE CertProva;

    No errors so far, then I created a record on the table PROVA:


    OPEN SYMMETRIC KEY DK02 DECRYPTION BY CERTIFICATE CertProva
    INSERT INTO PROVA(CAMPO1) VALUES (ENCRYPTBYKEY(KEY_GUID('DK02'), 'Prova'))

    No errors, so I tried to read  the record with:


    OPEN SYMMETRIC KEY DK02 DECRYPTION BY CERTIFICATE CertProva
    SELECT convert(varchar(100), DECRYPTBYKEY('CAMPO1')) as campo FROM PROVA

    but the result was CAMPO=null.
    I thought that maybe the problem was during the INSERT INTO but trying this query:


    SELECT (ENCRYPTBYKEY(KEY_GUID('DK02'), 'Prova')) AS CAMPO

    I can  read CAMPO correctly encrypted so the problem is not in the INERT INTO.
    Where's my mistake?
    Thank you, best regards.

    Roberto

  • June 5, 2018 at 10:06 am

    #1992973

    Since you want to decrypt a column value, you don't want to put single quotes around the column name.  It thought you were decrypting a string literal.
    SELECT convert(varchar(100), DECRYPTBYKEY(CAMPO1)) as campo FROM PROVA
    this returns the value Prova as expected.

  • June 6, 2018 at 12:07 am

    #1993050

    Yes...you are right !
    Thank you, regards.

    Roberto

Viewing 3 posts - 1 through 3 (of 3 total)

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