Using CLR

The Connector makes extensive use of the SQL Server Common Language Runtime (SQL CLR) as part of its architecture. SQL CLR hosts the .NET Framework inside SQL Server and allows managed code to run in-process with the Database Engine. T-SQL alone cannot call the M-Files .NET/COM APIs; SQL CLR provides the supported way to bridge from SQL Server to those APIs from stored procedures and functions.

T-SQL is excellent for set-based data manipulation. SQL CLR is strong at string/date processing, interacting with external APIs, and complex logic. The Connector deploys both T-SQL and SQL CLR objects so each is used where it fits best.

SQL Server configuration for CLR

CLR integration is disabled by default in SQL Server and must be enabled before SQL CLR objects can execute.

-- Requires sysadmin permissions
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

Additional considerations on modern SQL Server versions:

  • SQL Server 2017 and later: clr strict security is enabled by default. With this

    setting, assemblies are treated as UNSAFE unless they are trusted. Prefer one of:

    • Sign the assembly with a certificate/strong name and create a login from the

      certificate with the appropriate permission (for UNSAFE, grant UNSAFE ASSEMBLY), or

    • Use sys.sp_add_trusted_assembly to whitelist the assembly by hash.

  • Avoid setting the database TRUSTWORTHY option ON. It is broadly discouraged for

    security reasons and is not required when assemblies are signed/trusted.

CLR can be disabled by setting 'clr enabled' = 0. When disabled, SQL Server stops executing all SQL CLR routines and unloads their application domains. The Connector will not function if CLR is disabled.

Learn more on the use of CLR in the Connector in the following sections

Architecture of CLR Integration

The user code runs inside the CLR-hosted environment in SQL Server (called CLR integration). The following design goals were applied in the Connector:

Reliability (Safety)

User code is not allowed to perform operations that compromise the integrity of the Database Engine process, such as displaying UI or exiting the process. User code does not overwrite Database Engine memory buffers or internal data structures.

Scalability

SQL Server and the CLR have different scheduling and memory models. SQL Server uses a cooperative, non-preemptive scheduler; the CLR uses preemptive threads. Properly hosted SQL CLR code avoids direct OS threading primitives and relies on SQL Server’s host integration to preserve scalability. The Connector’s assemblies are designed with this in mind and do not call OS threading/memory primitives directly.

Performance

Managed code running in the Database Engine can provide comparable performance for algorithmic work. Database access from managed code is often slower than equivalent T-SQL, so the Connector uses T-SQL where it performs better and SQL CLR where it adds capabilities that T-SQL does not have.

Type safety verification

Type-safe code accesses memory only in well-defined ways. When assemblies are loaded into the CLR, the runtime verifies type safety before JIT compilation. The Connector’s managed code is verifiably type-safe.

Application domains

SQL Server hosts SQL CLR assemblies in application domains, providing isolation and the ability to unload all code by unloading the domain.

Code Access Security (CAS)

SQL Server primarily enforces security for SQL CLR via permission sets (SAFE, EXTERNAL_ACCESS, UNSAFE) and, in newer versions, clr strict security and trusted assemblies. Traditional .NET CAS policies are not the main enforcement mechanism in SQL Server’s hosting environment.

Application of CLR in stored procedures

Stored procedures are implemented as public static methods in .NET assemblies deployed to SQL Server. Methods can return void (return code 0) or an int (returned to the caller) and must have parameters matching their T-SQL declarations.

Using SQL CLR allows the Connector to integrate stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregates where appropriate.

Benefits of using CLR

Among the major benefits of this integration are:

  • A better programming model. .NET languages offer constructs and capabilities beyond T-SQL. We can leverage the .NET Base Class Library to efficiently solve problems.

  • Improved safety and security. Managed code runs in a common language run-time environment, hosted by the Database Engine. SQL Server leverages this to provide a safer and more secure alternative to the extended stored procedures available in earlier versions of SQL Server.

  • Ability to define data types and aggregate functions. User defined types and user defined aggregates are new managed database objects which expand the storage and querying capabilities of SQL Server.

  • Streamlined development through a standardized environment. Development tooling is consistent across database and middle-tier components.

  • Potential for improved performance and scalability. In many situations, the .NET Framework language compilation and execution models deliver improved performance over Transact-SQL.

Securing the use of CLR

SQL CLR introduces additional surface area. Our deployment approach keeps control with DBAs: assemblies are deployed/updated via SQL scripts, not from Visual Studio.

The deployment process allows installation and upgrade/uninstall using standard SQL procedures under DBA control.

CLR Integration Security

SQL Server enforces SQL CLR via permission sets: SAFE, EXTERNAL_ACCESS and UNSAFE.

  • The Connector requires UNSAFE permission for its assemblies because it interoperates

    with the M-Files COM/Interop APIs, which require unmanaged code access.

  • On SQL Server 2017 and later with clr strict security enabled, ensure the assemblies

    are trusted (signed + login with UNSAFE ASSEMBLY or added via sp_add_trusted_assembly).

  • Prefer trusting/signed assemblies over enabling database TRUSTWORTHY.

By design, the Connector’s assemblies only interact with M-Files and do not access OS resources beyond what is required for that integration. Nevertheless, UNSAFE assemblies are fully trusted inside SQL Server; DBAs should review deployment scripts and restrict who can create/alter assemblies.