Enterprise Service Cloud - Docs

Manage certificates and assemblies

Overview

On the ESC Managed SQL Server starting with SQL version 2017 it is possible to deploy functions or stored procedures written in CLR (common language runtime) through the upload of a dll file.

The feature is based on the SQL 2017 "clr strict security" mode (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security?view=sql-server-2017).

The day 2 action is available on the following services:

  • Managed MS SQL DBMS 2017 and newer versions
  • Managed MS SQL DBMS DBA Mode 2017 and newer versions

Note: The following explanations apply to the Managed MS SQL DBMS 2017 and newer versions only. For the DBA Mode as customer are sysadmin, they can follow the same procedure but they can also decide to completely change the configuration (i.e. disabling clr strict security or loading key signed certificate or implement the trusted list).

Security

The MS SQL 2017 and newer versions "clr strict security" mode is enabled by default and allows assemblies which are either

  • signed by a certificate
  • signed by a key
  • marked as trusted in the SQL DMBS

The implementation for the Managed DBMS is based on certificate signed assemblies only.

Just one certificate per DMBS is allowed. Therefore if multiple assemblies will be loaded into the same DBMS, they have to be signed by the same certificate.

Certificates are managed and delivered by the customer. Swisscom won't proof the certificate.

Configuration overview

The configuration of assemblies is composed of multiple steps (for details of "how to" consult the user guide), which are summarized here as follow:

  • Code your assembly as dll file
  • Create a certificate or obtain one from your certificate authority
  • Create a pfx file from the certificate and use it to sign your assembly
  • Load the dll file and certificate (if not already installed) to the target Managed DBMS
  • Enable the SQL server CLR mode (not to be confused with the "clr strict security")
  • Install the certificate into the DMBS with by name Hostname_yourcertificatename
  • Load assembly into the database

A login of type "CERTIFICATE_MAPPED_LOGIN" will be automatically configured as yourcertificatename_CertLogin by the installation of the certificate. If needed you can create a database user from this login.

The management of the assemblies and their dependent objects (CRUD stored procedures, functions) can be done through SSMS or SSDT connection.

Last Updated: