Installation of Database tier

Home / Installation / Database Tier

The PxStat Application Data Base Tier Installation and set up.

Please read the Requirements first.

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

N.B. You can skip this step if you have already a SysAdmin user in your SQL Server instance.

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

Install the AlwaysEncrypted Certificate on the Server

Check out the Installation of Always Encrypted Certificate

Run Windows PowerShell.

N.B. You must run Windows Powershell as Administrator

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

Follow the instruction on screen and provide the required information when prompted. This will create the Database, Data Model, User, Types, Views, Stored Procedures, Jobs.

N.B. The above script created the application user pxstat with a random password for security reasons. Please change the password for the pxstat login using SSMS.

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...

• Enter the name of the Column Master Key CMK
• Set the Key store to Windows Certificates Store - Local Machine and then select your Always Encrypted SSL certificate from the list.

• Right-click and select New Column Encryption Key...

• Enter the name of the column encryption key CEK_MTR_INPUT
• Use the drop-down and select the Column Master Key CMK

• Repeat the process above for the column encryption key CEK_TDT_VALUE

• Verify the Always Encrypted Keys have been created

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

• 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