[Solved] Why isn\'t the ASYMKEY_ID function returning the Asymmetric Key ID?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Why isn\'t the ASYMKEY_ID function returning the Asymmetric Key ID?

Database type:

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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        * 
    FROM
        sys.asymmetric_keys

Related Articles



* original question posted on StackOverflow here.