SA0142 : Consider disabling CLR if user assemblies are not used in your environment

In SQL Server, the CLR (Common Language Runtime) integration allows for the execution of user-defined assemblies created in .NET languages. However, if these user assemblies are not actually used within your environment, having the CLR enabled unnecessarily consumes system resources and increases potential security risks.

For example:

1-- Enable CLR option unnecessarily
2exec sp_configure 'clr enabled', 1;
3reconfigure with override;

This configuration is problematic if no user assemblies are registered in the SQL Server environment. Keeping the CLR enabled when it’s not needed can lead to inefficient resource utilization, as SQL Server devotes memory and processing to support it without gaining any benefit.

• Enables potential security vulnerabilities by allowing execution of code within the SQL Server process.

• Wasteful consumption of system resources such as memory and CPU cycles without any functional gain.

Disable the CLR (Common Language Runtime) feature in SQL Server if user-defined assemblies are not in use to optimize resources and enhance security.

Follow these steps to address the issue:

1. Review your SQL Server environment to determine if user-defined assemblies created in .NET languages are actively used. You can query the sys.assemblies catalog view to check for registered assemblies.

2. If no user assemblies are being used, disable the CLR feature to free up system resources and minimize security risks. Execute the following T-SQL command to disable the CLR:

3. After disabling CLR, confirm the change by querying the current configuration settings or testing the performance to ensure improved resource efficiency.

For example:

1-- Disable CLR option if unnecessary
2exec sp_configure 'clr enabled', 0;
3reconfigure with override;

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Maintenance Rules, Bugs

Enabling CLR Integration

clr enabled Server Configuration Option

Other Resources