I have created database master key. Then I have created asymmetric key:

CREATE ASYMMETRIC KEY smGK_АSymmetricKey_01
WITH ALGORITHM = RSA_512

In the documentation is said, that:

PASSWORD = 'password'

Specifies the password with which to encrypt the private key. If this clause is not present, the private key will be encrypted with the database master key.

So, the key should be encrypted with the database master key. Then, why the following is not working:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypass'   

SELECT [Email], ASYMKEY_ID('smGK_АSymmetricKey_01'), EncryptByAsymKey(AsymKey_ID('smGK_АSymmetricKey_01'), [Email])
FROM  myTable

CLOSE MASTER KEY

and returns NULL for the second and the third column.

Also, why the EncryptByAsymKey and DeCryptByAsymKey functions works with the same key ID? I think that when asymmetric key is used, I will grant to specific users to control the public key and to be able to decrypt data and other user to control the private key and to be the only one who can read it?

If this is not the case and only one group of users will be able to encrypt - decrypt the data, then as the example here, there is no logical difference between the two type of keys (only in terms of performance).

Note:

  1. The key is created for sure:

    SELECT * 
    FROM sys.asymmetric_keys;
    

    enter image description here

  2. I have try to create the key using PASSWORD but my code is still not working.

up vote 4 down vote accepted
  1. Why is this not working?

    The AsymKey_ID function is returning NULL because it cannot find that particular Asymmetric Key. I also got a NULL until I prefixed the literal string with an N. So the following should work:

    SELECT [Email],
           ASYMKEY_ID(N'smGK_АSymmetricKey_01'),
           EncryptByAsymKey(AsymKey_ID(N'smGK_АSymmetricKey_01'), [Email])
    FROM   myTable;
    

    The root cause of this issue is that you have a character in the key name that does not cleanly translate into ASCII (for the Code Page used by the default Collation of the Database that this is running in). In order to see the issue more clearly, run the following:

    SELECT 'smGK_АSymmetricKey_01';
    -- smGK_?SymmetricKey_01
    

    Now that we know which character is causing the issue, we can copy/paste it by itself into the UNICODE() built-in function to see its value, and converting that decimal value into binary/hex will give us the Code Point (remember to include the N prefix on the literal so that it doesn't get translated into "?"):

    SELECT UNICODE(N'А') AS [Decimal], CONVERT(BINARY(2), UNICODE(N'А')) AS [CodePoint];
    -- 1040 0x0410
    

    We can then look up that Code Point at http://unicode-table.com/en/0410/ to see that it is "Cyrillic Capital Letter A". This means that a second solution to this issue is to drop and recreate the Asymmetric Key with a Latin Capital "A" (i.e. CHAR(65) ) as that would not require the N prefix on the string literal.

    ALSO, you do not need the OPEN MASTER KEY... / CLOSE MASTER KEY statements, at least not to encrypt with an Asymmetric Key; the single statement shown above will work without the Open and Close. Not sure about when using DECRYPTBYASYMKEY. The Open / Close is needed, however, with a Symmetric Key.

  2. why the EncryptByAsymKey and DeCryptByAsymKey functions works with the same key ID? I think that when asymmetric key is used, I will grant to specific users to control the public key and ...

    These two functions use the same AsymKeyID because that is how they are supposed to work. "Controlling" a public key does not make sense because it is "public". The whole idea is that knowledge of the "public" key does not hurt you because it cannot be used to decrypt the data. This is why people sometimes put their PGP public key in their email signature (or at least used to). This allows for anyone to encrypt data so that only those who have the "private" key will be able to read it. This is why you don't need an open Key or even specify a password when encrypting via ENCRYPTBYASYMKEY. Also, the public_key column in sys.asymmetric_keys is available to anyone who can SELECT from that system catalog view.

  3. If this is not the case and only one group of users will be able to encrypt - decrypt the data, then there is no logical difference between the two type of keys (only in terms of performance).

    Again, anyone (who has rights to see the key **) can encrypt, but only those with access to the password of either the Database Master Key OR the Asymmetric Key itself (depending on which one the Asymmetric Key was protected with) will be able to decrypt. So you do have separation.

    There is also a rather large difference between Symmetric and Asymmetric encyption, besides Symmetric being much faster than Asymmetric:

    1. When using a Symmetric Key, it needs to be "open" in order to encrypt or decrypt, not just decrypt. Hence, no separation.
    2. Symmetric encryption is vulnerable to "Whole-value substitution" attacks. The following example is taken from the MSDN page for ENCRYPTBYKEY:

      Employee_ID Standard_Title Base_Pay
      345 Copy Room Assistant Fskj%7^edhn00
      697 Chief Financial Officer M0x8900f56543
      694 Data Entry Supervisor Cvc97824%^34f

      Without breaking the encryption, a malicious user can infer significant information from the context in which the ciphertext is stored. Because a Chief Financial Officer is paid more than a Copy Room Assistant, it follows that the value encrypted as M0x8900f56543 must be greater than the value encrypted as Fskj%7^edhn00. If so, any user with ALTER permission on the table can give the Copy Room Assistant a raise by replacing the data in his Base_Pay field with a copy of the data stored in the Base_Pay field of the Chief Financial Officer. This whole-value substitution attack bypasses encryption altogether.

      Whole-value substitution attacks can be thwarted by adding contextual information to the plaintext before encrypting it. This contextual information is used to verify that the plaintext data has not been moved.

      If an authenticator parameter is specified when data is encrypted, the same authenticator is required to decrypt the data by using DecryptByKey. ...


** The O.P. pointed out in a comment on this answer that Users need meta-data visibility rights to an Asymmetric Key in order to see it and use it. This can be done by:

  • being in a role that implicitly has such rights
  • being granted a Database-level permission that implicitly has such rights
  • being granted any of the Asymmetric Key permissions on a particular Asymmetric Key:

    GRANT VIEW DEFINITION ON ASYMMETRIC KEY::{asym_key_name} TO [{user_name}];
    

Please see the MSDN page for GRANT Asymmetric Key Permissions for full details.

Not having such rights would be another possible reason for the ASYMKEY_ID built-in function returning NULL. And because Asymmetric Keys can be "hidden" from Users, the ability to encrypt using one or more keys can be prohibited.

  • srutsky, that was some impressive investigative work. I would also recommend not opening the master key by password to decrypt the asymmetric key. Your screenshot shows that the DMK is encrypted by master key, meaning SMK. You don't want to put the DMK password in a procedure or SQL Agent job. – Michael Keleher Sep 2 '16 at 15:39
  • @MichaelKeleher Thanks for that compliment :). Regarding the use of the master key, I agree that it shouldn't be opened, and in fact, doesn't even need to be opened for encrypting. Actually, my preference is to not use the Database Master Key for protecting Asymmetric Keys / Certificates in the first place :). Regarding "your screenshot shows...", I did not post any screen show ;-). You might be referring to the code and related screen shot that the O.P. posted in the Question. But we are agreed on it not being wise to put a password in plain text code :). – Solomon Rutzky Sep 3 '16 at 20:07

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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