Your suggested change has been received. Thank you.

close

Suggest A Change

https://thales.na.market.dpondemand.io/docs/dpod/services/kmo….

Thales Logo

All

  • All
back

Using LDT with Microsoft SQL

Guarding SQL Server FileTable databases with LDT

search

Please Note:

Guarding SQL Server FileTable databases with LDT

There are specific steps for guarding an SQL FileTables database which must not be deviated from to correctly and safely guard the FileTables directory.

The following steps are for the scenario where all of the remote systems, installed with CTE, are guarding the FileTables Virtual Network Name (VNN) directory, as well as the FileTables server.

Note

The remote agent, the LDT primary, encrypts the files in the VNN, but it does not encrypt the actual database. As well, all remote agents perform transparent encryption/decryption on the VNN. But the agent running on the SQL Server does not perform any encryption at all, not for LDT, or for the transparent encryption/decryption. It strictly provides access control.

The benefit of this scenario is access to the files in the VNN path are transmitted across the encrypted LAN, and the files on the FileTables server cannot be decrypted.

Even though the FileTables server does not decrypt the files, it must still have CTE installed and guarding the real path to prevent the SQL Server from backing up the FileTables files during LDT transformation.

Prerequisite

Create a CIFS connection with CIFS credentials that LDT will use:

  1. Log on to CipherTrust Manager.

  2. In the left nav-bar, click Access Management > Connections.

  3. Select + Add Connection.

  4. In the Select Connection Type, click More.

  5. From the Select Connection dropdown, select CIFS/SMB and click Next.

  6. In the General Info section, enter the Name and Description for the connection and click Next.

  7. In the Configure Connection page, enter valid values for:

    • Host: IP or FQDN of the SMB share server.

    • Port: The port where the SMB service is running on the host.

      Note

      The Host and Port fields must be specified together. If Host and Port are not specified while creating a connection, these fields cannot be added later.

    • Username: Username to access the SMB share.

    • Password: Password to access the SMB share.

    • Domain: Workgroup or domain under which the username is configured. It is an optional field.

    • (Optional) Click Test Credentials to validate the connection. If the test is successful, the status is OK, else the status is Fail.

    Note

    The Host, Port, and Test Credentials fields are mandatory for testing the connection credentials.

  8. Click Add Connection.

Setup

  1. Install CTE agent for Windows on the FileTables server and register the CTE clients to the CipherTrust Manager.

  2. During registration, you must select Access Only to prevent this system’s LDT from rekeying the files unsynchronized with the other systems.

  3. Configure the FileTables server to treat all local GuardPoints as header-based. This is required because SQL Server FileTables technology does not support NTFS Alternate Data Streams (ADS), therefore, each file must contain a header for that file’s LDT metadata for storage. Open the CLI with administrator privileges and type:

    voradmin ldt header add 1 <path-to-GP>

    Where

    • Path is case sensitive

    • Path only needs to be a partial matching substring of any part of that path

    Example

    voradmin ldt header add 1 FileTables
    • This would match e:\GPFileTables2, but not e:\GPfiletables2

  4. Use the following commands to list or delete the header information:

    voradmin ldt header list
voradmin ldt header delete 1

  5. Create a DESKTOP_PRESENCE file in the virtual network path. Type the following in the command line:

    mkdir <VNN FileTable directory path>\.vorm_lgs

echo desktop >  <VNN FileTable directory path>\.vorm_lgs\DESKTOP_PRESENSE

  6. Create a policy for the FileTables server that prevents any local user, or any SQL Server, from accessing the clear data once it is encrypted.

    • For this scenario, no security rule can have apply key.

    • You must use the same encryption key for the policy used to guard the SQL FileTable Server, and the policy used to guard the VNN/FileTable directory from the ClientGroup.

  7. Guard the real path that the SQL Server uses with that policy:

    Example: If the database .mdf file is located at e:\db4filetable, then guard E:\db4filetable

  8. Right-click on the database and take it offline. Select to drop the connections.

  9. If necessary, restart the SQL Server instance to ensure all files are closed in each path to be guarded.

  10. Once guarded successfully on the FileTables server, bring the SQL Server database back online.

  11. Install CTE on each remote server that will access the VNN. During registration, select the same Client Group and the same Communication Group for each remote server.

  12. You can also install CTE and register it as access-only on some systems. Those systems will not be able to access files that are actively being rekeyed, but they will reduce LDT cross-system communication traffic during rekey.

  13. Create a policy that each remote CTE agent will use:

    a. Set up the security rules per customer’s required access control for the Virtual Network Path (VNN). Grant users access to clear data

    User set/Process Set Action: permit, apply key

    b. In the Client Group, guard the Virtual_Network_Name/FileTable_directory with that policy, and select the CIFS connection created as a prerequisite. Example:

    VNN: \\192.168.93.89\FileTableShared\FileTableDB8\FileTableTb_Dir8_2

At this point, the Virtual Network Name paths should be accessible and return clear data from each remote system. Additionally, Live Data Transform should begin transforming the files accessed through the VNN on a remote primary agent automatically selected to perform LDT.

Accessing the files directly on the FileTables server itself, or querying the FileTables files through SQL, will return the raw data of the file. This will include the LDT header followed by encrypted data.

SQL Server backups will backup the encrypted FileTables files with the database.

  1. Once LDT has completed transforming the data, create a new database backup. Any restore operation should be performed from that new backup. If an older backup needs to be restored, then you must unguard and reguard the VNN/FileTable directory GuardPoint from the client group once the older backup is restored, to recreate CTE metadata files in the GuardPoint.

  2. LDT should be completed, or paused, before performing any restore of the database.

Unsupported FileTables Use Cases

CTE does not support the following use cases:

  1. Install CTE agent on the SQL Server and locally apply the GuardPoint on the SQL Server storage.

  2. Access FileTables with Transact-SQL.

  3. Access FileTables with File I/O APIs on the SQL server. Perform all file I/O on the remote system running the CTE agent.

On this page