Configuring the MSSQL Database Protector

The installer script implements the required configurations automatically while installing the MSSQL Database Protector. These settings are done automatically by the installation process and do not require manual intervention. The following table describes these settings.

Note: Truncating the user names could lead to a security vulnerability and could result in user names, without the domain names, being treated as duplicate.

Note: It is recommended not to truncate the domain name as it is insecure. If the SQL Server instance is configured to perform windows authentication, then the mixed mode authentication should be disabled. A Windows authenticated user must provide the user name with the domain or host name prepended.

Configuring the TRUSTWORTHY Database Property

It is necessary to secure the connection between any client application and a SQL Server instance. The TRUSTWORTHY property for the MSSQL database is used to indicate whether the SQL Server instance trusts the database and its contents.

Earlier, while running the CreateAssembly.sql script during installation of the MSSQL Database Protector, the TRUSTWORTHY property was set to ON in the ALTER DATABASE statement. Keeping the TRUSTWORTHY property set to ON, increases security risk. It is recommended to keep the TRUSTWORTHY property set to OFF to avoid malicious threats when the database is connected to the server. However, if the TRUSTWORTHY database property is set to OFF while running the CreateAssembly.sql script, then the installation fails with the following error:

CREATE ASSEMBLY for assembly 'DNPepConnector' failed because assembly 'DNPepConnector' is not authorized for PERMISSION_SET = UNSAFE. 
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Note: It is recommended to avoid changing the TRUSTWORTHY property setting. An alternative method to mitigate this issue is that a certificate can be created for the MSSQL database using the signed dll from Protegrity. From this certificate a certificate-based login can be created for the database. An authorized certificate signed by a trusted source can validate the secured connection between the SQL Server instance and the database. A login is created with the certificate to connect the database securely with the server.

For more information about how to create a certificate-based login for the MSSQL database using the signed dll from Protegrity, refer to the section Managing Certificate-Based Login.

For more information about configuring the TRUSTWORTHY** property and creating a certificate, refer to the sections TRUSTWORTHY Database Property and Create a certificate for package signing respectively, in Microsoft’s website.

Updating Parameters in the config.ini File

The MSSQL Database Protector provides the following files that contain different parameters to control the protector behavior:

• config.ini - provides parameters to control the protector behavior.
• rpagent.cfg - provides parameters to control the RPAgent behavior.

To update paramenters in the config.ini file follow the steps below:

1. Log in to the node.

2. Navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\data directory.

3. To open the config.ini file, run the following command:

   vi config.ini
   

4. Press ENTER.

   The command opens the config.ini file.

   ###############################################################################
   # Protector configuration
   ###############################################################################
   [protector]
   
   # Cadence determines how often the protector connects with ESA / proxy to fetch the policy updates in background.
   # Default is 60 seconds. So by default, every 60 seconds protector tries to fetch the policy updates.
   # If the cadence is set to "0", then the protector will get the policy only once.
   #
   # Default 60.
   cadence = 60
   
   
   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   #
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   

5. Update the parameters, as per the description in the table.

   Parameter	Description
   cadence	Specifies the frequency at which the protector retrieves the policy. The default value is 60 seconds. If the cadence is set to “0”, then the protector will get the policy only once.
   mode	Specifies the approach of handling logs when the connection to the Log Forwarder is lost.

6. Save the changes to the config.ini file.

Updating the parameters in the rpagent.cfg file

1. Log in to the required node.

2. Navigate to the C:\Program Files\Protegrity\rpagent\data directory.

3. To open the rpagent.cfg file, run the following command:

   vi rpagent.cfg
   

4. Press ENTER.

   The command opens the rpagent.cfg file.

   ###############################################################################
   # Resilient Package Sync Config
   ###############################################################################
   [sync]
   
   # Protocol to use when communicating with the service providing Resilient Packages.
   # Use 'https' for ESA or 'shmem' for local shared memory.
   protocol = https
   
   # Host/IP to the service providing Resilient Packages
   host = <IP_address>
   port = 8443
   
   # Path to CA certificate
   ca = /opt/protegrity/rpagent/data/CA.pem
   
   # Path to client certificate
   cert = /opt/protegrity/rpagent/data/cert.pem
   
   # Path to client certificate key
   key = /opt/protegrity/rpagent/data/cert.key
   
   # Path to a secret file that is used to decrypt the client certificate key.
   # When using a custom certificate bundle, the 'secretcommand' can instead be
   # used to execute an external command that obtains the secret.
   secretfile = /opt/protegrity/rpagent/data/secret.txt
   
   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   #
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   

5. Update the parameters, as per the description in the table.

   Parameter	Description
   interval	Specifies the frequency at which the RPAgent retrieves the policy. The minimum value is 1 second and the maximum value is 86400 seconds. This is an optional parameter and must be included in the Sync section of the rpagent.cfg file.
   protocol	Specifies the protocol to use when communicating with the service providing Resilient Packages.
   host	Specifies the hostname to the service providing the Resilient packages.
   port	Specifies the port to the service providing the Resilient packages.
   ca	Specifies the path to the CA certificate.
   cert	Specifies the path to the client certificate.
   key	Specifies the path to the client certificate key.
   secretfile	Specifies the path to the secret file that is used to decrypt the client certificate key.
   mode	Specifies the approach of handling logs when the connection to the Log Forwarder is lost.
   host	Specifies the hostname or the IP address to where the Log Forwarder will forward the audit logs from the protector.

6. Save the changes to the rpagent.cfg file.

Restarting SQL server to apply configuration changes

Note: After the initial installation using the default path, restart the MSSQLSERVER service before executing any queries. If the installation path is modified in later installations, the MSSQLSERVER service must be restarted again prior to query execution.

If the protector is already installed, restart the SQL Server to apply the config.ini changes.
To restart the SQL Server follow the steps below:

1. Open SQL Server Management Studio > SQL Server Configuration Manager.
2. In the left pane, select SQL Server Services.
3. Identify the SQL Server instance to restart:
   • SQL Server MSSQLSERVER for the default instance, or
   • SQL Server <instance_name> for a named instance.
4. Right-click the selected SQL Server instance.
5. Click Restart.
   The SQL Server service restarts and the configuration changes are implemented.