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
NULL for the second and the third column.
Also, why the
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).
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM sys.asymmetric_keys