Common Language Runtime Integration
Microsoft SQL Server and Azure SQL Database Managed Instance enable you to implement some of the functionalities using .Net languages using the native common language runtime (CLR) integration as SQL Server server-side modules (procedures, functions and triggers). The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. SQL Server includes the .NET Framework version 4 pre-installed.
CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with
PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017 (14.x), an
sp_configure option called
clr strict security is introduced to enhance the security of CLR assemblies.
clr strict security is enabled by default, and treats
EXTERNAL_ACCESS assemblies as if they were marked
clr strict security option can be disabled for backward compatibility, but this is not recommended. Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted
UNSAFE ASSEMBLY permission in the master database. For more information, see CLR strict security. SQL Server administrators can also add assemblies to a list of assemblies, which the Database Engine should trust. For more information, see sys.sp_add_trusted_assembly.
You can also watch this 6-minute video that shows you how to use CLR in Azure SQL Database Managed Instance:
When to use CLR modules?
CLR Integration enables you to implement complex features that are available in .Net Framework such as Regular expressions, code for accessing external resources (servers, web services, databases), custom encryption, etc. Some of the benefits of the server-side CLR integration are:
A better programming model. The .NET Framework languages are in many respects richer than Transact-SQL, offering constructs and capabilities previously not available to SQL Server developers. Developers may also leverage the power of the .NET Framework Library, which provides an extensive set of classes that can be used to quickly and efficiently solve programming 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 two new managed database objects which expand the storage and querying capabilities of SQL Server.
Streamlined development through a standardized environment. Database development is integrated into future releases of the Microsoft Visual Studio .NET development environment. Developers use the same tools for developing and debugging database objects and scripts as they use to write middle-tier or client-tier .NET Framework components and services.
Potential for improved performance and scalability. In many situations, the .NET Framework language compilation and execution models deliver improved performance over Transact-SQL.
This following table lists the topics in this section.
Overview of CLR Integration
Describes the kinds of objects that can be built using CLR integration, and reviews the requirements for building database objects using CLR integration.
What's New in CLR Integration
Describes the new features in this release.
Architecture of CLR Integration
Describes the design goals of CLR integration.
Enabling CLR Integration
Describes how to enable CLR integration.