January 16, 2025 at 12:33 pm
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?
January 16, 2025 at 1:04 pm
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.
January 16, 2025 at 1:41 pm
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;
January 16, 2025 at 2:55 pm
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.
January 16, 2025 at 4:58 pm
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.
January 16, 2025 at 6:34 pm
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.
January 20, 2025 at 1:56 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy