Symmetric Key EncryptByKey returns NULL value

  • I haven't used column level encryption because usually the vendors I deal with have .dlls to do that work. But now I have a dev working on this and having issues in one specific environment. Because the code works in the other environments, I feel like the issue is more about something missed in Symmetric Key setup.

    They are using OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD = 'MyPassword' (yes, I told them they can't do that. They will fix it after we resolve current issue).

    Then they are passing a variable into the EncryptByKey() function like so: EncryptByKey(Key_GUID('MyKey'), RTRIM(@MyVariable))

    @MyVariable is not NULL, but the result becomes NULL in this EncryptByKey() function and I cannot figure out why. MyKey is the same on all servers, using different passwords though. And the test code works and returns an encrypted hash in the SELECT on other servers, just not this one. The master key also exists on all the servers.

    Where else can I check for problems?

    DECLARE @MyVariable VARCHAR(25) = 'Thisisarandomstring';

    OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD = 'MyPassword';

    SELECT @MyVariable AS MyVar, EncryptByKey(Key_GUID('MyKey'), RTRIM(@MyVariable)) AS EncryptVar;

    returns on this one server

    Thisisarandomstring    NULL

    Every other server, the NULL is replaced by the encrypted hash. Thoughts?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Quick thought, try using VARBINARY(MAX) for the encrypted value.

    😎

    The encrypted hash is always going to be much larger than the actual input, truncated values may return NULL.

  • Here is a quick example that should work on all the servers 😉

    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;
    GO


    -- Create a encryption key for this exercise
    IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES128SecureSymmetricKey')
    BEGIN
    CREATE SYMMETRIC KEY AES128SecureSymmetricKey
    WITH ALGORITHM = AES_128
    ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
    END

    -- must open the key if it is not already
    OPEN SYMMETRIC KEY AES128SecureSymmetricKey
    DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';

    DECLARE @TEXTVAR VARCHAR(50) = 'PLAIN TEXT VALUE';
    DECLARE @ENCVAL VARBINARY(MAX) = EncryptByKey(Key_GUID('AES128SecureSymmetricKey'),@TEXTVAR);

    SELECT @TEXTVAR,@ENCVAL;
    SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(@ENCVAL),0),@ENCVAL;

    -- close and drop the key
    CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;
    DROP SYMMETRIC KEY AES128SecureSymmetricKey;

  • Eirikur,

    Thank you for the post but what I'm trying to figure out is how to troubleshoot the problem. The key is being opened in the Dev's code, so the issue isn't that they forgot to open the key before trying to encrypt. And the code does work on other servers, so the issue is actually something to do with the key on this specific server. But I'm unsure of how to find the problem.

     

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • What editions and versions are the servers?

    😎

    Few things could be a problem, the place to start is to compare server settings, schema and then the code. Codepage could be a problem etc.

     

  • Well, well, well...

    Server not working : Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5040940) - 14.0.3471.2 (X64) Jun 20 2024 09:33:43 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

    Server that is working: Microsoft SQL Server 2019 (RTM-CU27-GDR) (KB5040948) - 15.0.4382.1 (X64) Jul 1 2024 20:03:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

    So now I need to figure out how to make this work for 2017.

    Thanks, Eirikur, for pointing me in a direction that gives me somewhere to go.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I checked everything I could think of and the problem wasn't related to version. I even used the test code pasted above and it worked. Which lead me to the conclusion that it was the key itself that was broken.

    The users dropped and recreated and then it worked.

    Thank you for your advice and assistance.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 7 posts - 1 through 6 (of 6 total)

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