Configuring the MSSQL Database Protector

1: Configuring MSSQL User Access Permissions
2: Impersonating a User

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.

Settings	Description
Communication	Set the Communication ID to 0 in the following registry entry: HKEY_LOCAL_MACHINE > SOFTWARE > Protegrity > Defiance DPS > SQL CLR This becomes the MSSQL Server default setting.
Domain Name	Using the LDAP member-source component, update the registry value in: HKEY_LOCAL_MACHINE > SOFTWARE > Protegrity > Defiance DPS > SQL CLR This helps the Administrator include domain names with every user name, making the user name unique.

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:

Log in to the node.
Navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\data directory.
To open the config.ini file, run the following command:
```
vi config.ini
```

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

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.

Save the changes to the config.ini file.

Updating the parameters in the rpagent.cfg file

Log in to the required node.
Navigate to the C:\Program Files\Protegrity\rpagent\data directory.
To open the rpagent.cfg file, run the following command:
```
vi rpagent.cfg
```

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

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.

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:

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

1 - Configuring MSSQL User Access Permissions

A user must be granted access and permissions to the certain tables such that they can query the database for members and groups.

Grant the following privilege rights to the user, defined in the member source configuration on the ESA, for executing the queries:

Select access to MASTER.SYSLOGINS
Select access to SYSUSERS

2 - Impersonating a User

User impersonation is a security feature that allows one user to temporarily act under the permissions of another user (usually a privileged user) within the same session. This enables controlled execution of privileged actions without permanently granting elevated access, and all actions are audited as performed by the impersonated user.

To impersonate a user:

Login to the server as the proxy user, USER1.
To change the user execution to privileged user, USER2, run the following command:
```
EXECUTE AS USER = 'USER2'
```
Perform the protect/unprotect functionality on the data using a data element.
Disconnect the current session and initiate the session as USER1.
Similarly, the EXEC AS statement enables impersonation of a privileged user to perform operations according to the user’s assigned privileges.
Note:
- User impersonation settings apply only to the current session.
- If a proxy user impersonates a privileged user and performs any operation, then entries in the Audit logs are displayed as performed by the privileged user, and not the proxy user.