Installation of Database tier

Home / Installation / Database Tier

Database Tier

Please read the Requirements first.

Create a SysAdmin user (`DB_SYS_ADMIN`) with SQL Server Authentication.

The items selected in blue are optional depending on your organisations password policy.

adminuser2

Create a new database (`pxstat.test`)

database

newdatabase

Install the AlwaysEncrypted Certificate on the Server

Installation-of-Always-Encrypted-Certificate

Run Windows PowerShell script as Administrator.

runadmin

Open the `Deploy.ps1` script in Windows PowerShell and execute it.

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.

rundeploy

Manually create Encryption Keys on the database as `SYS_ADMIN_USER`

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

encryption

Enter the name of the Column Master Key (CMK) - Set the Key Store to Local Machine

masterkey

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

encryptionkey

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)

enckey1

enckey2

Verify the Always Encrypted Keys have been created

verify

Encrypt the columns in the Database

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_VALUE column and TD_MATRIX/MTR_INPUT column

encryptcolumn1

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

mtr_input