Installation of Database tier
The present documentation is based on SQL Server 2016 (Standard Edition) and SQL Server Management Studio v.17.4
The items selected in blue are optional depending on your organisations password policy.
Installation-of-AlwaysEncrypted-Certificate
N.B. Use the DB_SYS_ADMIN as Username/Password when prompted.
This will create the Data Model, User, Types, Views and Stored Procedures objects in the database.
N.B. This should be done locally on the server. To add the encryption to the database you will need to be logged into the Sql Server box and run SSMS (SQL server Management Studio) as an Administrator.
The TD_DATA/TDT_VALUE and TD_MATRIX/MTR_INPUT columns need to be encrypted.
Use the Object Explorer to locate the database:
- Go to the Security tab and select the Always Encrypted Keys option
- Right-click and select New Column Master Key
- Enter the name of the Column Master Key (
CMK) - Set the Key Store toLocal Machine
- Specify Key store, (Windows Certificates Store in this case) for the local machine certificate store and then select your AlwaysEncrypted SSL certificate from the list
- Select New Column Encryption Keys
- Enter the name of the column encryption key (
CEK_MTR_INPUT,CEK_TDT_VALUE) - Use the drop-down and select the Column Master Key (
CMK)
- Verify the Always Encrypted Keys have been created
Encrypt the columns in the Database as SYS_ADMIN_USER.
Apply the encryption settings to the column(s) by browsing the table and selecting the needed column(s) for encryption.
- Browse the Columns tab
- Right-click the column and select Encrypt Column . Repeat for the
TD_DATA/TDT_VALUEcolumn andTD_MATRIX/MTR_INPUTcolumn
- Tick the box next to the column you are encrypting.
- Set the Encryption Type to Deterministic from the options.
- Use the drop-down, and select the relevant Column Encryption Key, which is already tied with the Column Master Key
