Creating the Always Encrypted Column Master Key

1. Open the Microsoft SQL Server Management Studio and then create a utimacoDB database.

Creating database window

2. Create a table name as Vehicle.

USE utimacoDB 
CREATE TABLE [dbo].[Vehicle] ( 
ID int NOT NULL, 
VehicleName varchar (255) NOT NULL, 
VehicleNumber varchar (255) NOT NULL, 
Dates varchar(255) NOT NULL 
) 
GO

3. Insert values of respective columns.

INSERT INTO Vehicle VALUES ('1', 'Alan', 'MH 12 FG 4767','30 Oct 2021'); 
INSERT INTO Vehicle VALUES ('2', 'James', 'MH 43 WD 7837','12 April 2021'); 
INSERT INTO Vehicle VALUES ('3', 'Mark', 'MH 22 GI 8963','7 May 2021');

4. To List the database table content, use the below query.

Select * from Vehicle

5. Create a new key using the cngtool command, as below.

cngtool Name=<Key_Name> CreateKey=<Algorithm,KeySize>

6. The user will get this output after executing the above command.

>cngtool Name=utimacoKey CreateKey=RSA,2048 

------------------------------------------------------------ 

Provider: Utimaco CryptoServer  Key Storage Provider 

Device : 10.44.223.140 

Group : CngCa1 

Mode : Internal Key Storage 

------------------------------------------------------------ C:\Users>cngtool ListKeys 

------------------------------------------------------------ 

Provider : Utimaco CryptoServer  Key Storage Provider 

Device : 10.44.223.140 

Group : CngCa1 

Mode : Internal Key Storage 

------------------------------------------------------------------------- 

Index     AlgId          Size         Group         Name           Spec 

------------------------------------------------------------------------- 

1         RSA            2048         CngCa1        utimacoKey       0 

7. Using Object Explorer, select the Security directory under the desired Database (in the example below this can be seen as “utimacoDB”). Click to expand “Always Encrypted Keys”.

Select: <Your_database> > Security > Always Encrypted Keys > Column Master Keys. Right-click on “Column Master Keys” and Select > New Column Master Key… . The “New Column Master Key” dialogue box will open.

Creating New Column Master Key window

8. Enter the name of the Master Key, e.g., Utimaco-CMK.

9. Select Key store as Key Storage Provider CNG, in this case for the current user or local machine. Select a provider as "Utimaco CryptoServer Key Storage Provider", then click OK.

Creating New Column Master Key window

10. To view the new Column Master Key, use the SQL Object Explorer. Navigate to the relevant database and expand by clicking the + sign. Expand the Security folder and then expand the Always Encrypted Keys folder. You will find two folders; one for the Column Master Key(s) and one for the Column Encryption Key(s).

New Column Master Key