Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

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.

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 "?"):

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:

When using a Symmetric Key, it needs to be "open" in order to encrypt or decrypt, not just decrypt. Hence, no separation.

Symmetric encryption is vulnerable to "Whole-value substitution" attacks. The following example is taken from the MSDN page for ENCRYPTBYKEY:

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:

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 KeleherSep 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 RutzkySep 3 '16 at 20:07