Always Encrypted–Key Stores

ByHariharan Rajendran

Always Encrypted–Key Stores

As you know, we can create “Column Master Key” & “Column Encryption Key” to encrypt the column values. You can check here to learn about key creation. Usually these keys are associated with the certificate which includes the details like,

Issued To, Issued By, Expiration Date, Thumbprint & etc.

In Always encryption, we have four different Key store options are available which helps us to secure our keys. These keys are associated with the table columns. We can decrypt the values using these keys so we need to make sure to secure the keys.

Key stores,

  1. Windows Certificate Store – Current User
  2. Windows Certificate Store – Local Machine
  3. Azure Key Vault
  4. Key Storage Provider (CNG)

image

 

Widows Certificate Store – Current User

This type of certificate store is local to current user on the computer. This is located in the registry of the computer, specifically on HKEY_CURRENT_USER root

We need to compare the current user certificate store with local machine certificate store. Current user certificate always inherit the content of the local machine certificate.

image

We can generate the certificate by our self and that will be available for one year and we can use that to encrypt the column values.

T-SQL Statement to create a master key, we can select the certificate (Thumbprint) which I highlighted.

image

Windows Certificate Store – Local Machine

This will be available on the computer which is global to all users on the computer. This is located under HKEY_LOCAL_MACHINE registry.

This will list out all the available certificates in your computer and choose for encryption.

 

image

Azure Key Vault

This should be used to safeguard the keys in cloud (Azure). It requires a Azure subscription.

 

Key Storage Provider (CNG)

It provides different providers,

  1. Microsoft Passport Key Storage Provider
  2. Microsoft Smart Card Key Storage Provider
  3. Microsoft Software Key Storage Provider

We can choose any of the above provider, for more information about the algorithm, key length & etc., check Microsoft article here.

image

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!