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:

1. Login to SQL Server Management Studio.

2. 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 
   

3. 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
   

4. To verify the certificate, navigate to System Database > Security > Certificates.

5. Select the appropriate database type to install the UDFs.

6. To create an assembly:

   1. Verify whether any assembly exists.
   2. If the assembly exists, then drop the existing assembly.
   3. Open the createassembly.sql script.
   4. Set the value of the TRUSTWORTHY parameter to OFF.
   5. Save the changes to the createassembly.sql script.
   6. 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
   

7. To verify the user, navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver.

8. To install the database objects, execute the CreateObjects.sql script.

9. Execute the protect/unprotect operations.