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 organisation's password policy.

adminuser2

Install the AlwaysEncrypted Certificate on the Server.

Check out the Installation of Always Encrypted Certificate.

Open Windows PowerShell.

N.B. You must run Windows Powershell as Administrator.

runadmin

Open the `/db/install.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....

encryption

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.

masterkey

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

encryptionkey

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

enckey1

Repeat the process above for the column encryption key CEK_TDT_VALUE.

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 columns TD_MATRIX.MTR_INPUT and TD_DATA.TDT_VALUE by browsing the table and selecting the needed column(s) for encryption.

Go to the table TD_MATRIX.
Browse the Columns tab.
Right-click the column MTR_INPUT and select Encrypt Column.

encryptcolumn1

Tick the box next to the column you are encrypting.
Set the Encryption Type to Deterministic (recommended over Randomized, unless you have a specific business case).
Use the drop-down, and select the relevant Column Encryption Key, which is already tied with the Column Master Key.

mtr_input