CLR Enabled
Job details
| Name: |
CLR Enabled |
| Platform: |
Sqlserver |
| Category: |
Security |
| Premium package: |
Security and compliance package |
| Description: |
Checks if the assemblies can be run by SQL Server. |
| Long description: |
Checks if the assemblies can be run by SQL Server. Enabling use of CLR assemblies widens the attack surface of SQL Server and puts it at risk from both inadvertent and malicious assemblies. |
| Version: |
1.2 |
| Default schedule: |
4 1 1 * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ |
engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name |
Default value |
Description |
| return status |
1 |
Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “clr enabled” parameter is enabled. |
| disable CLR user code execution |
NO |
If set to “YES“ the alert will disable “clr enabled” (if it is enabled) by running sp_configure stored procedure. |
| history threshold |
365 |
The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The primary goal of this job is to monitor the status of CLR (Common Language Runtime) enabled configuration in SQL Server, ensuring that the database environment conforms to specified security standards.
- Why: This job is vital as it helps detect whether CLR integration is enabled on SQL Server, which can increase the attack surface if not properly managed. By monitoring and conditionally disabling CLR, the job supports maintaining a secure server environment, preventing potential security vulnerabilities from malicious assemblies.
- Manual checking: To manually check the current CLR configuration settings you can use the SQL command below:
SELECT value_in_use FROM sys.configurations WHERE name = 'clr enabled'
Technical Details
The monitoring job involves several key components to ensure comprehensive and effective oversight:
- Stored Procedures and Tables: It involves the creation of procedures and tables such as ‘dbw_clr_enabled_proc’, ‘dbw_clr_enabled_status_info’, and ‘dbw_clr_enabled_status_histr’ to handle and store configuration statuses and adjustments effectively.
- CLR Status Checking: It checks whether CLR is currently enabled and if so, provides the option to disable it depending on configuration via SQL Server’s ‘sp_configure’ procedure.
- Security Measures: Implementation includes querying security settings to adjust them if required and monitors user-created assemblies in databases which can indicate potential misuse.
- Compliance Monitoring: Compliance-related information is tracked and updated within the job’s framework, aligning it with organizational or regulatory requirements.
Dependency Management
The job also manages dependencies effectively to ensure clean and consistent execution. Each component like stored procedures and tables are checked and cleaned if the job execution fails, preventing leftover or corrupt configurations.
Implementation and Output
The implementation code provided outlines the SQL for setting up necessary tables, checking and updating the CLR configuration, and handling condition-based configurations. The job output is mainly used for monitoring and alert purposes but can be configured to automatically alter system settings for security purposes based on the pre-set conditions.
Scheduling and Monitoring
- Scheduling: The job is scheduled to run periodically as defined, ensuring regular monitoring.
- Monitoring: It includes internal mechanisms to trace execution details, history, and current configuration. These are visualized in the report template provided with the job, like current and historical configuration values, and data on user-defined assemblies.
Reporting and Compliance
The dbWatch job configuration comes equipped with a framework for reporting, which includes:
- Presentations of current configurations,
- Historical data visualizations on a daily basis,
- Aggregated statistics of parameter values checked over time.
These components serve both operational monitoring and compliance auditing purposes, giving a comprehensive view of the CLR management on the SQL Server platform.