Installing the MSSQL Database Protector

1: Installing the Policy Enforcement Point (PEP)
2: Managing Certificate Based Login

2.1: Creating the certificate
2.2: Upgrading the certificate

3: Creating the User Defined Functions

This section describes the procedure to install the MSSQL Database Protector and create the required User‑Defined Functions (UDFs). The process includes instructions to install the Policy Enforcement Point (PEP) for Microsoft SQL Server, optionally managing certificate‑based logins, and creating the UDFs.

Note: Certificate-based login management is only required in case of certificate creation. Depending on the specific requirements, create the User-defined functions (UDFs) with or without the certificates.

1 - Installing the Policy Enforcement Point (PEP)

The Policy Enforcement Point (PEP) integrates with SQL Server to enforce data protection and security policies at runtime, ensuring controlled access to sensitive data. This section outlines the installation procedure, and key considerations to ensure a successful installation.

To install the PEP:

Navigate to the directory containing the installation files.
To install the PEP for MSSQL server, double-click the PepSQLServerSetup_Windows_x64_<version>.exe file.
The Select Destination Location dialog box appears.
To change the default installation directory:
1. Click Browse.
2. Select the required installation directory to install the PEP for MSSQL Server.
3. Click OK.
Note: The default directory is C:\Program Files\Protegrity\Database Protector.
Click Next.
The Ready to Install dialog box appears.
Click Next.
The Configure SQL Server Database Name dialog box appears.
Note: The default database name is set to master.
Enter the database name in the box.
Click Install.
The installer completes the installation successfully and the Completing the Protegrity Data Security Platform - SQL Server UDF Setup Wizard dialog box appears.
Click Finish.
Note: After creating the UDFs, Protegrity recommends to restart the MSSQL service. For more information, refer to Restarting the SQL server.

Note: After installing Database Protector, configure Certificate-based login or continue with UDF installation without certificate-based authentication.

2 - Managing Certificate Based Login

Certificate‑based login securely authorizes Protegrity assemblies in SQL Server by using a certificate created from the Protegrity‑signed dll.

This section provides information about creating and upgrading the certificate based login for the MSSQL database protector.

2.1 - Creating the certificate

Certificate‑based login securely authorizes Protegrity assemblies in SQL Server by using a certificate created from the Protegrity‑signed dll. The certificate is mapped to a login with the UNSAFE ASSEMBLY permission. This permission enables the trusted execution of assemblies and UDFs without enabling the database‑wide TRUSTWORTHY setting.

To create a certificate-based login:

To create a database certificate using a signed dll. execute the following query.

CREATE CERTIFICATE MSSQL_90009_cert
FROM EXECUTABLE FILE = 'C:\Program Files\Protegrity\Database Protector\sqlserver\DNPepConnector.dll'
GO

To create a certificate‑based login and grant the UNSAFE ASSEMBLY permissions, execute the following query.
```
CREATE LOGIN John
FROM CERTIFICATE MSSQL_90009_cert
GO

GRANT UNSAFE ASSEMBLY TO <John>;
GO
```
To verify the certificate, navigate to System Database > Security > Certificates.
Select the appropriate database type to install the UDFs.

To create an assembly:

Verify whether any assembly exists.
If the assembly exists, then drop the existing assembly.
Open the createassembly.sql script.
Set the value of the TRUSTWORTHY parameter to OFF.
Save the changes to the createassembly.sql script.
Execute the createassembly.sql script.

A sample script is given below.

USE [master]
GO

sp_configure 'clr enable',1
RECONFIGURE
GO

alter database [master] set trustworthy OFF
GO

if exists(SELECT name FROM sys.assemblies WHERE name = 'DNPepConnector')
DROP ASSEMBLY [DNPepConnector]
GO

CREATE ASSEMBLY [DNPepConnector]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Protegrity\Database Protector\sqlserver\DNPepConnector.dll'
WITH PERMISSION_SET = UNSAFE
GO

To verify the user, navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver.
To install the database objects, execute the CreateObjects.sql script.
Execute the protect/unprotect operations.

2.2 - Upgrading the certificate

This section describes the instructions to upgrade certificates for certificate‑based logins. Certificate upgrades are required only when replacing or renewing an existing certificate and are not part of the initial installation. Perform this procedure only if certificate‑based login is already configured.

To upgrade the certificate:

Login to SQL Server Management Studio.
To identify the login mapped to the certificate, execute the following query:
```
SELECT name
FROM sys.server_principals
WHERE sid IN (
   SELECT sid
   FROM sys.certificates
   WHERE name = '<certificate_name>'
);
```
Note: Replace the placeholder with actual names.
This query returns the login_name associated with the existing certificate.
To drop the login name, execute the following query:
```
DROP LOGIN [login_name];
```
Note: Replace the placeholder with actual names.
This command removes the login_name associated with the existing certificate.
To drop the associated objects, execute the following script:
```
DropObjects.sql
```
To drop the user, execute the following query:
```
DROP USER [user_name];
```
Note: Replace the placeholder with actual names.
This command removes the user_name associated with the existing certificate.
To drop the certificate, execute the following query:
```
DROP CERTIFICATE <certificate_name>;
```
To re-create the certificate, follow the steps mentioned in Creating a Certificate-Based Login.

3 - Creating the User Defined Functions

This section describes the instructions install the User Defined Functions (UDFs). Create the UDFs with or without a certificate-based login. For more information about certificate-based login, refer to Managing Certificate-Based Login.

Note: If the TRUSTWORTHY database property is OFF, create a certificate-based login from the signed .dll file provided by Protegrity. Create the certificate-based login before creating the UDFs. If the TRUSTWORTHY database property is ON, create the UDFs without creating a certificate-based login.

To install the user-defined functions:

Note: In MSSQL Server, the sa login is disabled by default. Enable the sa login to connect to the database using the sa user. By default, the database name is master.

To connect to the database, login as the privileged user with the CREATE ASSEMBLY permissions.
To run the scripts, navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\sqlscripts directory.
To execute the registered assembly, execute the CreateAssembly.sql script.
Open the CreateFunctions.sql file.
Replace the default database name with the database to install the UDFs.
Save the changes to the CreateFunctions.sql script.
To create the UDFs, execute the CreateFunctions.sql script.
Note: After creating the UDFs, Protegrity recommends to restart the MSSQL service. For more information, refer to Restarting the SQL server.