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,
       COALESCE(C.name,AK.name,PSK.name) AS protector_name,
       KE.crypt_property AS encrypted_key,
                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:

BY PASSWORD = 'smGK_MasterKeyPassword@';

WITH SUBJECT = 'User for protecting SM symetric keys.'


DECLARE @Email NVARCHAR(128) = '[email protected]';
DECLARE @EmailEncrypted VARBINARY(256);


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

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];



CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];

--DROP SYMMETRIC KEY [SK_SecurityUsers_V001];

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, 2018 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, 2018 at 11:54

1 Answer 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, 2018 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. Mar 30, 2018 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 - learn.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, 2018 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. Mar 30, 2018 at 16:41
  • @gotqn If you're worried about stealing of database backups, files, or bad apple admins then I'd suggest Always Encrypted. Mar 30, 2018 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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