Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Enable SQL Server CLR Integration using T-SQL


By:   |   Last Updated: 2019-07-16   |   Comments (2)   |   Related Tips: More > SQL Server Configurations

Problem

You work as a SQL Server Database Administrator. In your company there is a database application that has grown to a point that needs to be moved to a new server with more resources. You perform the server migration and everything goes fine without any issues. A few minutes after ending the migration, your phone starts to ring and email starts flooding your inbox complaining that the one of the features of the application is not working as expected. After some digging, the development team tells you that the failing feature uses a CLR stored procedure which seems to be not working and they ask you if CLR is enabled in the new SQL Server instance. In this tip I will show you how to check and enable CLR integration.

Solution

One of the most overlooked features of SQL Server is the CLR integration. For those of you who don’t know, CLR means Common Language Runtime and it is the virtual machine component of Microsoft's .NET framework that manages the execution of .NET programs. CLR integration allows us to use user assemblies when coding a database solution in SQL Server. It was meant to be both an improvement and a future replacement to extended stored procedures, which are a special kind of stored procedure written using C language and compiled in machine code as a dll library.

SQL Server CLR Integration

The CLR integration feature was added in SQL Server 2005, which was the first version released without the legacy Sybase code.

With the beginning of the 21st century, Microsoft decided to introduce the .NET framework as part of the Visual Studio development suite. If we do a little historical research, we will find out that the people at Redmond were in need for a product to compete against Sun Microsystems Java Platform. The introduction of the.NET framework to SQL Server was also a response to Oracle’s adoption of Java as a language to create stored procedures and server-side applications.  Now as we know, Sun Microsystems was bought by Oracle Corporation.

Prerequisites Before Enabling CLR in SQL Server

There is one scenario in which you can’t use CLR. If you are using lightweight pooling (fiber mode) you won’t be able to enable CLR unless you disable lightweight pooling. As a side note, by enabling fiber mode features that depend on CLR like the hierarchy data type, replication, and Policy-Based Management won’t work properly or won’t work at all.

If you want to enable CLR the first step you have to do is to take a look if your instance is running using fiber mode. You can check it by executing the following script.

EXEC sp_configure 'show advanced', 1;
RECONFIGURE
GO
 
EXEC sp_configure 'lightweight pooling';
GO

The first query of the script uses the sp_configure system stored procedure to change the configuration option "show advanced" from 0 (disabled) to 1 (enabled), so we can see the advanced configuration options. In order to apply the changes, we run the RECONFIGURE statement.

Finally, we can see all the configuration options by running sp_configure alone.

Take a look at the next screen capture to see it more clearly by looking at the execution of the previous script.

This code is used to see if our instance of SQL Server is using fiber mode.

As you can see in the image above, the last execution of sp_configure returned a table that shows us the status of the "lightweight pooling" configuration option, which in this case is disabled.  I included a table below with a description of each column of the returned table.

Column Description
name This is the name of the configuration option.
minimum The minimum value allowed for the configuration option.
maximum The maximum value allowed for the configuration option.
config_value The value that was set for the configuration option.
run_value The currently running value of the configuration option.

Enabling CLR Integration in SQL Server

To enable CLR integration we have to change the value of the configuration option "clr enabled" from 0 to 1 by using the sp_configure system stored procedure.

In the next script, we are going to show the actual status of CLR integration and then enable it.

EXEC sp_configure 'clr enabled'
 
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
 
EXEC sp_configure 'clr enabled'

In the next screen capture you can see the execution of the script above and shows CLR is now enabled.

Enabling CLR integration.

Security Considerations for SQL Server CLR

After enabling CLR integration you should focus on its use and security. Starting from SQL Server version 2017 there is a new configuration option named "clr strict security" which is enabled by default and interprets all assemblies as "unsafe".

When the CLR strict security option is enabled, any assemblies that are not signed will not load successfully. To avoid this, you will have to recreate the assemblies with a signature of either a certificate or an asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server.

Another option is to set the database Trustworthy property to ON and change the owner of the database to a login that has the "unsafe assembly" permission enabled on the server. This option is not recommended by Microsoft and should be avoided.

Next Steps


Last Updated: 2019-07-16


get scripts

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 05, 2019 - 2:46:16 PM - Jeremy Kadlec Back To Top

Solomon,

Thank you for the comments.  The tip has been updated.

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, July 25, 2019 - 3:39:47 AM - Solomon Rutzky Back To Top

There is misinformation here and it should be corrected.

  1. Please do not suggest to people to enable "Allow Updates". That option used to allow for direct updates to system tables, but has not done anything for many versions now. Please do a search for "docs allow updates Server Configuration Option".

  2. Please do not suggest to people to enable "show advanced options" in order to enable CLR integration. The "clr enabled" option is not an advanced option. Run the following query, and you will get back a 0 for "is_advanced".

    SELECT [is_advanced] from sys.configurations WHERE [name] = N'clr enabled';

  3. You also do not need the "WITH OVERRIDE" clause of "RECONFIGURE". There is no reason to add that.

All that is needed to enable CLR Integration is the following:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Learn more about SQL Server tools