SQL Server – Why Isn’t the ASYMKEY_ID Function Returning the Asymmetric Key ID?

encryptionsql server 2014sql-server-2012t-sqlunicode

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:

The key is created for sure:
```
SELECT * 
FROM sys.asymmetric_keys;
```
I have try to create the key using PASSWORD but my code is still not working.

Best Answer

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.
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.
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.

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY

For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.

Sql-server – Why use asymmetric keys in SQL Server

The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.

The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.

All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.

Best Answer

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Sql-server – Why use asymmetric keys in SQL Server

Related Question