FILESYSTEM ENCRYPTION FOR MICROSOFT SQL USING FORTANIX DATA SECURITY MANAGER

1.0 Introduction

This document describes how to integrate Microsoft SQL Server (MSSQL) with Fortanix-Data-Security-Manager (DSM) Filesystem Encryption (FSE) to protect database data at rest.

In this integration, the Fortanix FSE agent encrypts the filesystem that stores the SQL Server database files, and logs and backups. Encryption keys are securely managed by Fortanix DSM. The encryption and decryption processes are transparent to the SQL Server application.

This ensures that database files stored on disk remains protected from unauthorized access while SQL Server continues to operate normally.

2.0 Prerequisites

Ensure the following:

• A Windows Server system with administrative privileges.

• Access to a Fortanix DSM endpoint.

• A Fortanix DSM account with a group and application.

• Internet access to download SQL Server and SSMS (or the installation packages copied locally).

• The Fortanix Filesystem Encryption (FSE) agent installer for Windows. To download the FSE agent, refer to Filesystem Encryption for Windows Using Fortanix Data Security Manager - Installation.

• Installation packages for:

  • Microsoft SQL Server

  • SQL Server Management Studio (SSMS)

3.0 Product Tested Version

The following product versions were tested:

• Fortanix Data Security Manager (DSM)

• Fortanix FSE Agent for Windows version 2.8.217

• Microsoft SQL Server 2019

• SQL Server Management Studio (SSMS) 2019

4.0 Configure Fortanix DSM

A Fortanix DSM service must be configured, and the URL must be accessible. To create a Fortanix DSM account and group, refer to the following sections:

4.1 Signing Up

4.2 Creating an Account

Access <Your_DSM_Service_URL> in a web browser and enter your credentials to log in to Fortanix DSM.

For more information on how to set up an account in Fortanix DSM, refer to the User's Guide: Getting Started with Fortanix Data Security Manager - UI.

4.3 Creating a Group

Perform the following steps to create a group in the Fortanix DSM:

1. In the DSM left navigation panel, click the Groups menu item, and then click ADD GROUP to create a new group.

   

2. On the Adding new group page:

   1. Title: Enter a name for your group.

   2. Description (optional): Enter a short description of the group.

3. Click SAVE to create the new group.

The new group is added to the Fortanix DSM successfully.

4.4 Creating an Application

Perform the following steps to create an application (app) in the Fortanix DSM:

1. In the DSM left navigation panel, click the Apps menu item, and then click ADD APP to create a new app.

   

2. On the Adding new app page:

   1. App name: Enter the name for your application.

   2. ADD DESCRIPTION (optional): Enter a short description of the application.

   3. Authentication method: Select the default API Key as the authentication method from the drop down menu. For more information on these authentication methods, refer to the User's Guide: Authentication.

   4. Assigning the new app to groups: Select the group created in Section 4.3: Creating a Group from the list.

3. Click SAVE to add the new application.

The new application is added to the Fortanix DSM successfully.

4.5 Copying the API Key

Perform the following steps to copy the API key from the Fortanix DSM:

1. In the DSM left navigation panel, click the Apps menu item and click the app created in Section 4.4: Creating an Application to go to the detailed view of the app.

2. On the INFO tab, click VIEW API KEY DETAILS.

3. From the API Key Details dialog box, copy the API Key of the app to be used when initializing the FSE agent in Section 6.2: Initialize and Mount FSE.

5.0 Install SQL Server Management Studio

SQL Server Management Studio is used to manage and configure Microsoft SQL Server.

Perform the following steps to install SSMS:

1. Open a web browser on the Windows Server.

2. Navigate to the Microsoft download page for SQL Server Management Studio.

3. Click Download SSMS.

This downloads the SSMS-Setup-ENU.exe file.

NOTE

If internet access is restricted on the server, download the installer on another machine and copy it to the server.

5.1 Install SSMS

Perform the following steps to install SSMS on the Windows Server:

1. Double-click SSMS-Setup-ENU.exe.

2. Click Install.

3. Wait for the installation to complete.

4. Restart the server if prompted.

5.2 Verify Installation

Perform the following steps to verify that SSMS is installed successfully:

1. Open Start Menu → Microsoft SQL Server Tools.

2. Launch SQL Server Management Studio.

Expected result:

• SSMS launches successfully.

• No runtime errors are displayed.

6.0 Install and Configure Fortanix FSE Agent

The Fortanix FSE agent encrypts the filesystem used by SQL Server to store database files.

6.1 Install FSE Agent

You can install the Fortanix DSM FSE Agent using one of the following methods:

• A bundled executable file (Recommended)

• Microsoft installer file (MSI)

For more information on installing the FSE agent, refer to Filesystem Encryption for Windows as a Service Using Fortanix Data Security Manager – Installation.

6.2 Initialize and Mount FSE

Perform the following steps to initialize the encrypted filesystem and mount it using the Fortanix FSE Agent:

1. Open the FSE Agent UI.

2. Click Initialize & Mount.

3. Enter the following details:

   1. DSM Endpoint: The Fortanix DSM service URL used during account creation in Section 4.1: Signing Up.

   2. API Key: The API key copied from the application created in Section 4.6: Copying the API Key.

4. Specify the mount directory. For example: F:\FSE_DATA.

For more information to initialize the encrypted filesystem and mount it using the Fortanix FSE Agent, refer to Initialize the Encrypted Filesystem.

6.3 Verify FSE Mount

Perform the following steps to verify that the encrypted filesystem has been mounted successfully:

1. Open File Explorer.

2. Navigate to the mounted directory. For example: F:\FSE_DATA.

3. Create a test file.

4. Restart the server.

5. Verify that the FSE mount is restored automatically after reboot.

7.0 Install Microsoft SQL SERVER

Microsoft SQL Server must be installed on the Windows Server before configuring the encrypted filesystem for database storage.

7.1 Download SQL Server

Download Microsoft SQL Server 2019 from the official Microsoft website.

For testing purposes, you can use the following editions:

• SQL Server Developer Edition

• SQL Server Standard Edition

7.2 Install SQL Server

Perform the following steps to download the Microsoft SQL Server installation package.

1. Launch the SQL Server installer.

2. Select New stand-alone SQL Server installation.

3. In Feature Selection, select the Database Engine Services check box.

4. Select the instance type as Default instance (MSSQLSERVER) or Named instance.

5. Configure authentication: Windows Authentication or Mixed Mode Authentication.

6. If Mixed Mode Authentication is selected, specify the SA password.

7. Complete the installation.

7.3 Verify SQL Server Service

Run the following command to verify that the SQL Server service is running:

Get-Service | findstr /i sql

Expected output:

SQL Server (MSSQLSERVER) → Running

8.0 Configure MSSQL Database on FSE Mount

In this section, the Microsoft SQL Server database files and transaction logs are configured to be stored on the encrypted FSE filesystem.

8.1 Create MSSQL Directory on FSE Volume

You must create the directory structure on the FSE mount to store SQL Server database files.

For example:

F:\FSE_DATA\MSSQL
 ├── DATA
 ├── LOG
 └── BACKUP

These directories will store:

• Database data files

• Transaction logs

• Database backups

8.2 Configure Permissions

You must configure filesystem permissions so that the SQL Server service account can access the directories on the FSE mount.

Ensure the SQL Server service account (for example, sqluser)  has read and write permissions for the cipher and plain directories created by the FSE mount.

Additionally, ensure that the SQL Server service account has read and write permissions for the database directories created under the FSE mount, such as

• F:\FSE_DATA\MSSQL\DATA

• F:\FSE_DATA\MSSQL\LOG

• F:\FSE_DATA\MSSQL\BACKUP

This allows Microsoft SQL Server to create, modify, and manage database files on the encrypted filesystem.

8.3 Create or Restore Database on FSE Volume

SQL Server databases can be configured to store their data files and logs on the encrypted FSE filesystem.

8.3.1 Case 1: Create a New Database

Perform the following steps to create a new SQL Server database on the FSE mount:

1. Open SQL Server Management Studio.

   

2. Create a new database.

3. Set the physical file path for:

   • Data files

   • Log files

Example paths:

F:\FSE_DATA\MSSQL\DATA
F:\FSE_DATA\MSSQL\LOG

SQL Server will create the database files in the specified directories on the encrypted filesystem.

8.3.2 Case 2: Restore an Existing Database

Perform the following steps to restore an existing SQL Server database on the FSE mount:

1. Create a backup of the existing database.

   

2. Restore the database.

3. Specify the database file location on the FSE mount directory. For example, F:\FSE_DATA\MSSQL.

After restoration, SQL Server stores all database files on the encrypted FSE filesystem while encryption and key management are handled by Fortanix DSM.

8.4 Verify Database Operation

Perform the following steps to verify that the SQL Server database is functioning correctly on the encrypted FSE filesystem:

1. Open SQL Server Management Studio.

2. Connect to the SQL Server instance.

3. Run the following SQL commands to create a sample table and insert test data:

   CREATE DATABASE FSE_Test_DB;
   USE FSE_Test_DB;
   
   CREATE TABLE Users (
   ID INT IDENTITY(1,1) PRIMARY KEY,
   Name VARCHAR(50),
   Email VARCHAR(100)
   );
   
   INSERT INTO Users (Name, Email)
   VALUES ('Test User', 'test@example.com');

4. Verify that the table is created successfully and the data is inserted.

5. Confirm that the database files are stored in the FSE mount directory. For example, F:\FSE_DATA\MSSQL.