0

In this article is explained how to decrypt a symmetric key. For example:

SELECT SK.name, SK.symmetric_key_id, SK.key_length, SK.algorithm_desc,
       KE.crypt_type_desc,
       COALESCE(C.name,AK.name,PSK.name) AS protector_name,
       KE.crypt_property AS encrypted_key,
       COALESCE(DECRYPTBYCERT(C.certificate_id,KE.crypt_property),
                DECRYPTBYASYMKEY(AK.asymmetric_key_id,KE.crypt_property)) AS decrypted_key
  FROM sys.key_encryptions AS KE
  JOIN sys.symmetric_keys AS SK
    ON KE.key_id = SK.symmetric_key_id
  LEFT JOIN sys.certificates AS C
    ON KE.thumbprint = C.thumbprint
  LEFT JOIN sys.asymmetric_keys AS AK
    ON KE.thumbprint = AK.thumbprint
  LEFT JOIN sys.symmetric_keys AS PSK
    ON KE.thumbprint = CAST(PSK.key_guid AS VARBINARY(50));

It can be tested using the following query:

-- 
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'smGK_MasterKeyPassword@';

-- 
CREATE CERTIFICATE [CERT_V001]
WITH SUBJECT = 'User for protecting SM symetric keys.'


--
CREATE SYMMETRIC KEY [SK_SecurityUsers_V001]
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE [CERT_V001]
GO


DECLARE @Email NVARCHAR(128) = 'sometest@google.com';
DECLARE @EmailEncrypted VARBINARY(256);

OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT @EmailEncrypted = ENCRYPTBYKEY(KEY_GUID('SK_SecurityUsers_V001'),@Email);
SELECT @EmailEncrypted;

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];


OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT CONVERT(NVARCHAR(128), DECRYPTBYKEY(@EmailEncrypted));

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];


--DROP SYMMETRIC KEY [SK_SecurityUsers_V001];
--DROP CERTIFICATE [CERT_V001];
--DROP MASTER KEY;

I am wondering, does this mean the data is not protected at all?

In the article is said, that:

However, for password-protected and symmetric-key-protected keys this sadly does not work.

I guess this means I need to use one of these types of encryption in order to be sure the data is protected?

  • Could you explain with more details what kind of threat you are trying to mitigate? Why do you think encryption would be the solution? Security is hard and there are a lot of i's to dot and t's to cross. – vonPryz Mar 26 '18 at 11:52
  • GDPR (eugdpr.org) - the data must be encrypted in rest and I want to check if the above behavior can give a person who has access to the database, but do not know the database master key password, the ability to encrypt the data. – gotqn Mar 26 '18 at 11:54
1

I am wondering, does this mean the data is not protected at all?

No, it's protected but as shown in the article you need to have access to all of the keys in order to decrypt the values. The article takes a bunch of liberties and glosses over quite a few things when it comes to the protection of the keys. Try doing this without being a sysadmin, knowing the DMK password, or using transparent decryption hierarchy. Could you still do it - most likely not.

The secrets in SQL Server are generally protected by levels of encryption - where one key encrypts the other which encrypts the other. If you can unwind all of those levels (which again the article doesn't even really talk about and just kind of glosses over) then absolutely you can decrypt the data. SQL Server doesn't use any homemade encryption, it's all industry standard algorithms which are publically documented. There is nothing stopping anyone from implementing something to reverse it if they had all the keys. This is why over provisioning permissions is a bad thing.

the data must be encrypted in rest and I want to check if the above behavior can give a person who has access to the database, but do not know the database master key password, the ability to encrypt the data.

In this case, no they generally wouldn't be able to get to the decrypted data. There are, however, edge cases that won't stop someone with administrative access and a decent working knowledge of encryption from possibly getting the data.

It's all about "reasonable protection" wherein you've given a reasonable enough protection to the data. If you wanted it to be extremely secure then you'd need to use an HSM, which another team owns and audits on an extremely frequent basis. No one would have administrative access to the server, and no one would have sysadmin access to SQL Server. That's probably not going to fly - so "reasonable" protection should be sufficed.

  • The above seems reasonable if you are sure that a database/backup can not leave your storage. If someone has your database backup for example, he/she will be able to restore it, make their account sys.admin and having access to the symmetric keys decrypt all your data. So, in such case do not knowing the mater key or the certificate protecting the keys, cannot stop the attacker. – gotqn Mar 30 '18 at 11:27
  • @gotqn If the symmetric key is encrypted by the DMK and the DMK is encrypted by the SMK then no, just restoring the database won't "give them" the decrypted key unless they get the SMK with it. Again, something the article doesn't bring up. Now, if you use a symmetric key with a PW and put the plain text PW in a stored procedure or something - yes, but that's on you for putting it in plain text. – Sean Gallardy Mar 30 '18 at 12:20
  • The Database Master Key DMK can be encrypted only with password. You are not to allowed to protect it with other technique - docs.microsoft.com/en-us/sql/t-sql/statements/…. The article suppose you have access to the system views - if you can restore a database on your local laptop you will be sysadmin and will be able to query the keys - for example in my case, I have DMK -> Certificate -> SK – gotqn Mar 30 '18 at 12:51
  • @gotqn That's not true, by default the DMK is protected by the SMK, however a password can be added and the SMK protection removed if inclined to do so. – Sean Gallardy Mar 30 '18 at 16:41
  • @gotqn If you're worried about stealing of database backups, files, or bad apple admins then I'd suggest Always Encrypted. – Sean Gallardy Mar 30 '18 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.