Why should you care about CLR integration in SQL Server?

What is the CLR integration on SQL Server?

Included in SQL Server 2005, the CLR is the heart of the Microsoft .NET framework.

The common language runtime (CLR), also know as SQLCLR, allows you to create database objects (functions, triggers, store procedures, etc.) using .Net Framework (managed code).

CLR integration offers the potential for improved performance.

Managed code is better suited than Transact-SQL for some complex tasks, including string handling and regular expressions. Using the functionality found in the .NET Framework Library, you can have access to thousands of pre-built classes and routines.

Why should you care about it?

Many developers who implemented SQL CLR procedures (or other objects) do not understand the consequences of running assemblies in modes other than in the safe mode.

Assemblies created with UNSAFE permission can perform actions that can compromise the security features within the operating system or the SQL Server instance.

How can I check if CLR is enabled?

You can use the script below to check if CLR feature is enabled.

  1. SELECT case when value = 0 then 'Not Enabled' ELSE 'Enabled' END AS Result
  2.  
  3. FROM sys.configurations
  4.  
  5. WHERE name = 'clr enabled'

How can I fix it?

You can disabled it running the fallowing script:

  1. sp_configure 'clr enabled', 0
  2. GO
  3. RECONFIGURE
  4. GO

If you need CLR enabled on your SQL Server, it is strongly recommended (best practices) setting CLR strict security to 1.

Upgrading to a newer version of SQL gives you better security options.

More information

Microsoft – Update adds the “CLR strict security” feature to SQL Server.
Microsoft – CLR Integration Code Access Security
Sifiso W. Ndlovu, Impact of CLR Strict Security configuration setting in SQL Server 2017 – SQLShack.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.