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.

• 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 in-place Device GuardPoint 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 /u01/app/mssql/data sudo /opt/mssql/bin/mssql-conf set /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

       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. cl :::text 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 --eanup_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 transformed 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

   Note

   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.