Considerations and Requirements

• System Requirements:

  • SQL Server 2019. Other versions of SQL Server are not supported with CipherTrust Transparent Encryption.

  • Red Hat 8. Other versions of Red Hat are not supported with CipherTrust Transparent Encryption.

  Other database applications may be used instead of MySQL, but Thales has only tested this feature with MySQL.

• CipherTrust Transparent Encryption agent supports manual GuardPoints on a device or folder for both standalone and cluster SQL Server database. Automatic GuardPoints are supported on a standalone SQL Server database. Automatic GuardPoints are supported on a standalone SQL Server database but not for cluster SQL Server databases.

• The CipherTrust Transparent Encryption resource agent supports GuardPoints created from standard or Live Data Transformation policies. It does not support IDT-Capable GuardPoints or Efficient Storage GuardPoints.

Create a CTE guarded standalone SQL Server database

1. Configure the target database data and log directory location as mssql, type:

   sudo /opt/mssql/bin/mssql-conf set <filelocation.defaultdatadir> /u01/app/mssql/data
   sudo /opt/mssql/bin/mssql-conf set <filelocation.defaultlogdir> /u01/app/mssql/log 
   sudo systemctl restart mssql-server
   

2. Guard targeted directory with standard encryption policy using CS1 or CBC keys of either 128aes or 256aes encryption.

   Example

   Policy Type: Standard

   Rule	Value
   Security Rules
   Action	all_ops
   Effect	permit, audit, applykey
   Key Selection Rule
   Key	aes128 or 256 with CS1 or CBC

   # secfsd -status guard

   Response

       :::text
       GP                      Policy                  Type   ConfigState  Status   Reason
       ----------              ------                  ----   -----------  ------   ------
       /u01/app/mssql/data     encrypt_cs1_aes128_all  local  guarded      guarded  N/A
       /u01/app/mssql/data     encrypt_cbc_aes256_all  local  guarded      guarded  N/A
   

3. Create a target SQL Server database on a guarded directory as mssql, type:

   sqlcmd -S localhost -U SA -P '<password>'
   CREATE DATABASE <targetdb>
   go
   

4. Verify that data and log files are residing in the newly designated directory:

   use <targetdb>
   SELECT filename from sysfiles
   GO
   

Expected results: Should see data/log file in a guarded directory:

    /u01/app/mssql/data/testdb.mdf
    /u01/app/mssql/log/testdb_log.ldf

Convert a baseline SQL Server database to a CTE GuardPoint using Dataxform

1. Shutdown the SQL Server service, type:

   # sudo systemctl stop mssql-server
   # systemctl status mssql-server
   

2. Create an online policy using CS1 or CBC keys of either 128aes or 256aes encryption.

   Example of aes256

   Policy Name: dxf_clear_to_cbc_aes250_onhost

   Policy Type: Standard

   Rule	Value
   Security Rules
   Action	key_ops
   Effect	permit, applykey
   Key Selection Rule
   Key	clear_key
   Data Transformation
   Key	cbc_aes256_onhost

3. Add the GuardPoint with the policy dxf_clear_to_aes256_onhost to the targeted baseline database data/log directory, type:

   # secfsd -status guard

   Response

   GuardPoint            Policy                          Type  ConfigState  Status   Reason
   ----------            ------                          ----  -----------  ------   ------
   /u01/app/mssql/log    dxf_clear_to_cbc_aes250_onhost  local  guarded     guarded  N/A
   /u01/app/mssql/data   dxf_clear_to_cbc_aes250_onhost  local  guarded     guarded  N/A
   

4. Run dataxform with the above policy as the root user.

   dataxform --rekey --cleanup_on_success --gp /u01/app/mssql/dataxform/data
   dataxform --rekey --cleanup_on_success --gp /u01/app/mssql/dataxform/log
   

   Example

   # dataxform --rekey --cleanup_on_success --gp /u01/app/mssql/dataxform/data
   

   Response

   Checking if `/u01/app/mssql/dataxform/data` is a guard point with a rekey policy applied
   `/u01/app/mssql/dataxform/data` is a guard point with a rekey policy applied
   About to perform the requested data transform operation
   -- Be sure to back up your data
   -- Please do not attempt to terminate the application
   Do you wish to continue (y/n)?y
   .
   .
   .
   Data transform for guard point /data1/oracle/oradata/xformdb finished
   About to remove the data transformation status files
   Do you wish to continue (y/n)?y
   Removal of data transformation status files completed
   

5. Unguard GuardPoints /u01/app/mssql/dataxform/data and /u01/app/mssql/dataxform/data from policy dxf_clear_to_aes256_onhost.

6. Guard these GuardPoints again with a standard policy which encrypts using the same open aes256 key policy as dxf_clear_to_cbc_aes256_onhost.

   # secfsd -status guard
   

   Response

   GuardPoint            Policy                  Type   ConfigState  Status   Reason
   ----------            ------                  ----   -----------  ------   ------
   /u01/app/mssql/data   encrypt_cbc_aes256_all  local  guarded      guarded  N/A
   /u01/app/mssql/log    encrypt_cbc_aes256_all  local  guarded      guarded  N/A
   

7. Start the database and then test to see if the data is accessible, type:

   # sudo systemctl start mssql-server
   # sqlcmd -S localhost -U SA -P '<password>'
   use <targetdb>
   SELECT filename from sysfiles
   go
   

Expected results: You should be able to log into your targeted datatransformed database and query system files.

Create a CTE guarded Standalone SQL Server database using LDT

1. Enable the LDT setting in CipherTrust Manager using one of the following methods:

   • Install agent to your targeted database host with LDT enabled Configuring CTE with CipherTrust Manager.

   • Enable LDT on an existing CipherTrust Transparent Encryption installed host using the CM GUI.

2. Create a targeted new baseline in the SQL Server database:

   Example

   sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/LDT/data   
   sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /var/opt/mssql/LDT/data
   sudo systemctl restart mssql-server
   systemctl status mssql-server
   sqlcmd -S localhost -U SA -P '<password>'
   CREATE DATABASE `<targetdb>`
   SELECT Name from sys.Databases
   go
   

3. Create an LDT encryption policy using CS1 or CBC keys of either 128aes or 256aes.

   Example

   Field	Value
   Name	LDT_clear_to_CS1_aes256_onhost_key
   Expiration Date	<target date>
   Algorithm	AES256 or AES128
   Encryption Mode	CBC_CS1 or CBC
   Key Type	Cache on Host
   Automatic Key Rotation	Selected
   Key Version Life Span (days)	<target #>

4. Create an online LDT policy using the key created in the previous step.

   Example for CBC_CS1 with AES256

   Policy Name: LDT_clear_to_CS1_aes256_onhost

   Security Rules

   Rule	Action
   Order 1
   Action	key_op
   Effect	Permit, Apply Key
   Browsing	No

   Rule	Action
   Order 2
   Action	all_ops
   Effect	Permit, Audit, Apply Key
   Browsing	Yes

   Rule	Action
   Order 3
   Action	all_ops
   Effect	Deny, Audit
   Browsing	Yes

   Key Selection Rule:

   Key	Description
   Order 1
   Current Key	clear_key
   Transformation Key	LDT_clear_to_CS1_aes256_onhost_key
   Exclusion Rule	No

5. Stop the SQL Server, type:

   # sudo systemctl stop mssql-server
   # systemctl status mssql-server
   

6. Guard the new LDT baseline database with the LDT policy created in the previous step.

   # secfsd -status guard
   

   Response

   GuardPoint                     Policy                            Type       ConfigState  Status   Reason
   ----------                ------                            ----       -----------  ------   ------
   /var/opt/mssql/LDT/data   LDT_clear_to_CS1_aes256_onhost    local      guarded      guarded  N/A
   

   Wait for the Rekey Status in CipherTrust Manager to display: Rekeyed.

7. Run LDT with the same policy to verify the transformation of the baseline database.

   # voradmin ldt attr get <data/log directory of baseline database>
   

   Example:

   # voradmin ldt attr get /var/opt/mssql/LDT/data
   

   Response

   LDT stats: version=6, rekey_status=rekeyed
       Number of times rekeyed:                1 time
       Rekey start time:                       2022/09/02 05:41:26
       Last rekey completion time:             2022/09/02 05:41:28
       Estimated rekey completion time:        N/A
       Policy key version:                     0
       Pushed Policy key version:              0
       Policy ID:
           1001
       Data stats:
           total=16.0MB, rekeyed=11.0MB
           truncated=0.0MB, sparse=5.0MB
       File stats:
           total=2, rekeyed=2, failed=0
           passed=0, skipped=0, created=0, removed=0, excluded=0
   

   Expected results:

   • No errors in output

   • rekey_status=rekeyed

   Do not start the next step until rekey_status = rekeyed.

8. Restart the SQL Server, type:

   # su - mssql
   # sudo systemctl start mssql-server
   # systemctl status mssql-server
   

9. Access the targeted database and query the data, type:

   sqlcmd -S localhost -U SA -P '<password>'
   use <targetdb>
   use <targetdb>
   SELECT filename from sysfiles
   go
   

Expected results: You should be able to log into your targeted database and query system files.