Problem
You are a developer that is working with Microsoft technologies like SQL Server and Visual Studio. You have heard from colleagues that there is some kind of integration between SQL Server and .NET and you want to know more about this. In this tip I will cover the basics of SQL Server CLR integration that will be your starting point.
Solution
Just about every relational database system has its own programming language defined as a procedural extension to the ANSI SQL standard. In Microsoft SQL Server we have Transact SQL. Even though this covers almost all the needs to develop complex and robust applications, there are some special circumstances in which it falls short. Here is where SQL Server’s CLR integration kicks in.
SQL Server CLR Integration
The Common Language Runtime (CLR) is the virtual runtime environment that manages the execution of .NET programs. It acts as an intermediary between the application and the operating system (the win32 native API) by providing services such as memory management and exception handling. In an attempt to simplify the comparison, we can say that CLR is to .NET what Java Runtime Environment (JRE) is for Java.
The integration of CLR into SQL Server allows us to run .NET code in a virtual runtime environment (CLR) owned by the SQL Server process which is independent of the CLR component you may have running on Windows. This means that you won’t be able to use all the .NET Framework available namespaces. As an example of this, you won’t be able to show message boxes, display any type of graphic output or perform user interaction requests. The reason behind this is pretty obvious, the SQL Server process should not be affected in order to maintain its integrity.
Now thanks to CLR integration, we can write stored procedures, triggers, user-defined types, user-defined functions and aggregate functions using managed code written in languages like C# or Visual Basic .NET.
Why We Should Use CLR Integration?
The main reason that drives us to use CLR integration is the different programming paradigm it represents compared with Transact SQL. Assemblies written in .NET code benefit us by allowing us to use the Object-Oriented Programming model. This way we can use inheritance, polymorphism and constructor overload to build complex solutions that can be organized into classes and namespaces.
Additionally, managed code offers benefits compared with Transact SQL on string handling, complex math operations especially those that can use bit shifting to increase performance; and in cases where an intricate decision-making logic is required.
Furthermore, we can replace the outdated extended stored procedures with .NET code. Extended stored procedures are DLL libraries compiled in native Windows code, usually written using low level languages like C or C++. This creates a potential security flaw and also it is a risk to SQL Server process stability.
How to Enable SQL Server CLR Integration?
Since this feature is disabled by default, we must enable it by setting the “clr enabled” server configuration option to 1 using the sp_configure system stored procedure as shown in the script below.
EXEC sp_configure 'clr enabled', 1; RECONFIGURE; GO
In the next image you can see the output generated when the code was executed in SQL Server Management Studio (SSMS).
Related Tip – How to Enable SQL Server CLR Integration using T-SQL for a SQL Server database
Things That We Can Do Using SQL CLR That We Cannot Do With T-SQL
At this point we saw that there are situations in which using CLR managed code is better than using Transact SQL. Now we will see scenarios when Transact SQL usage is not possible.
- We need to access web services like REST APIs and SOAP
- Perform read and write operations on files, compress files, compute the hash signature of files
- Communicate with external message brokers like RabbitMQ
- Work with network connections
- Connect to FTP servers
Security Considerations
There are some security aspects that we must be aware of before starting a project that aims to use CLR code. The reason behind this is because depending on which permission is given to the assembly, we may not be able to do some operations. This especially important if we aim to access external resources like the file system or perform network connections.
There are three types of code access permissions that can be granted to the assembly: SAFE, EXTERNAL_ACCESS and UNSAFE.
- SAFE: This is more secure because it prohibits accessing external system resources such as files, network connections, or the registry.
- EXTERNAL_ACCESS: With this permission, the assembly has access to external system resources such as files, networks, system environment variables, and the registry. Assemblies with this permission run in the security context of the SQL Server service account unless the code explicitly impersonates the caller. Something to note here is that even though the assembly can access resources external to SQL Server it cannot perform what is called type-unsafe operations like for example accessing memory buffers or using pointers.
- UNSAFE: Assemblies granted with this permission can do the same things that those with the EXTERNAL_ACCESS permission with the addition of type-unsafe operations. Furthermore, it allows execution of unmanaged code.
Starting with SQL Server 2017, there is a new server configuration option named “CLR strict security” which is enabled by default. When this server option is enabled, assemblies using the SAFE and EXTERNAL_ACCESS permissions are treated as UNSAFE. This is because Code Access Security (CAS), the solution to prevent untrusted code from performing privileged actions is no longer supported as a security boundary. This is an important factor to keep in mind if you are planning to migrate from a previous version of SQL Server.
Of course, we can change this configuration option status and disable it for backward compatibility. Take a look at the script below to see how you can disable it.
EXEC sp_configure 'clr strict security', 0; RECONFIGURE; GO
In the next image you will see the execution of the code above.
Next Steps
- For more information about type-unsafe operations take a look at the Unsafe code section of the C# Language Specification Guide: Unsafe code.
- When moving databases with CLR objects to another server you may forget to copy the .dll files and therefore lose those files. In such cases, you can take a look at how to export 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 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:
