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.
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.
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
- When moving databases with CLR objects to another server you may forget to copy the .dll files and therefore lose those files. In such case you can take a look at the next tip that will teach you how to export the assemblies back to their original .dll files: Exporting CLR Assemblies from SQL Server back to .dll files.
- CLR objects are not exempt of coding errors so you may need to debug the code. In this tip you will find how to Debug SQL Server CLR functions, triggers and stored procedures.
- Are you new to SQL Server CLR integration and don’t know what you can do with it? I selected a few tips that show some practical cases to use CLR integration:
