SQL Server: How to Check If Clr Is Enabled

SQL Server: How to check if CLR is enabled?

SELECT * FROM sys.configurations
WHERE name = 'clr enabled'

Is CLR Integration enabled at a database level or the server/instance level?

CLR Integration is enabled at the server level (by the sp_configure 'clr enabled', 1 code you posted).

Assemblies are loaded at the database level. CLR Stored Procedures and Functions are created from methods in those loaded assemblies at a database level.

What is the purpose and risks of enabling SQL CLR?

PURPOSE

SQLCLR allows one to do things that either:

1. can't be done in T-SQL, or
2. can't be done as efficiently as in T-SQL

There are plenty of things that can be done in both, and for which T-SQL is actually much better at. In those cases it is an inappropriate use of SQLCLR to do those things so it is best to research first to make sure that the operation cannot be done in T-SQL, or would definitely be slower.

For example of performance, T-SQL Scalar UDFs prevent parallel execution plans. But SQLCLR scalar UDFs, as long as there is no data access and that they are marked as IsDeterministic=true, do not prevent parallel execution plans.

For more details on what SQLCLR is and is not, please see the first article in the Stairway to SQLCLR series that I am writing for SQL Server Central:

Stairway to SQLCLR Level 1: What is SQLCLR?

Or, to get a sense of what can be done in SQLCLR, please see my SQL# project, which is a library of over 320 stored procedures and functions, many of which are in the Free version, and many of which work in SAFE mode: SQLsharp.com.

RISKS

The risks vary based on the PERMISSION_SET (i.e. SAFE, EXTERNAL_ACCESS, and UNSAFE) that the Assembly is marked as, and what is being done. It is possible to do things in an UNSAFE Assembly that cannot be done in regular T-SQL (except that many of those dangerous things can already be done via some extended stored procedures, xp_cmdshell, and the OLE Automatic procedures -- sp_OA* ). An Assembly marked as SAFE cannot reach outside of the database, so generally quite safe, BUT you can still lock up the system via a Regular Expression that exposes "catastrophic backtracking" (of course, this can be mitigated starting in .NET Framework 4.5, so SQL Server 2012 and newer, by setting a max time limit on the RegEx operation). An Assembly marked as UNSAFE can write to static variables, which in the context of the shared App Domain model used by SQLCLR, allows for shared memory between Sessions. This can allow for caching, but when not used properly, easily leads to race conditions.

TESTING

As for tSQLt, I do not believe that you are required to use the SQLCLR component. I thought I saw that it just enabled some extended functionality. Either way, the source code is available on GitHub so you can check it out to see what it is doing. It has been a while since I looked at it, but from what I remember, it should not present much of a risk for the little that it is doing (especially in a Dev / QA environment).

Another option that doesn't use SQLCLR is DbFit. I have always prefered DbFit as it is completely external to the DB. It is based on the FitNesse framework, written in Java, and you manage the tests via wiki-style pages. It, by default, wraps the tests in a Transaction and rolls everything back when the test is finished (i.e. clean-up). It is worth taking a look at.

Download: DbFit project on GitHub

Tutorial: Using the DbFit Framework for Data Warehouse Regression Testing

How to disable clr strict security in SQL Server

Enabling advanced options resolved my problem:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

Now I can create assemblies.

Enabling CLR Integration on SQL Server 2008-r2

The accepted answer is incorrect. The WITH OVERRIDE option of RECONFIGURE has absolutely nothing to do with whether or not a restart of SQL Server is required. The MSDN documentation for RECONFIGURE states that WITH OVERRIDE:

Disables the configuration value checking (for values that are not valid or for nonrecommended values)...

The fact is, no restart of the SQL Server service is required when enabling, or disabling, the "CLR Integration" option in sp_configure. A simple test (run on SQL Server 2008 R2, but works the same across all versions that support SQLCLR) proves this:

EXEC sp_configure 'clr enabled'; -- show current value
EXEC sp_configure 'clr enabled', 0; RECONFIGURE;
EXEC sp_configure 'clr enabled'; -- show current value
GO
EXEC sp_configure 'clr enabled'; -- show current value
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
EXEC sp_configure 'clr enabled'; -- show current value
GO

Results:

Pay attention to the run_value field. It starts out as "1" since "CLR Integration" is already enabled on my system. But it switches with only calling RECONFIGURE.

name          minimum   maximum   config_value   run_value
clr enabled   0         1         1              1

clr enabled   0         1         0              0

clr enabled   0         1         0              0

clr enabled   0         1         1              1

Additionally, it should be stated with regards to the initial code shown in the Question, the statement for

sp_configure 'show advanced options', 1;

is unnecessary since clr enabled is not an advanced option.

To prove the point about clr enabled not being an advanced option, and even showing another way to prove that this option does not require a reboot, just execute the following simple query:

SELECT [name], [value], [value_in_use], [is_dynamic], [is_advanced]
FROM   sys.configurations
WHERE  [configuration_id] = 1562;
/*
name           value    value_in_use    is_dynamic    is_advanced
clr enabled    1        1               1             0
*/

As you can see in the result set shown above, is_advanced is 0, meaning "not an advanced option (yes, the official Microsoft documentation is currently incorrect; I will update it when I have time). Also, is_dynamic is 1, meaning that simply executing RECONFIGURE will enable the option immediately, not requiring a restart of the instance.

To summarize: The sum total of all steps required to enable "CLR Integration", and without needing to restart the SQL Server service, are as follows:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

That's it. ^**

---

^** WOW64 servers will require a restart of the server in order for this option to take effect. ( clr enabled Server Configuration Option )

How to enable CLR integration for just a specific database in Microsoft SQL Server?

Enabling "CLR Integration" is a Server/Instance-level only option; it cannot be handled separately per Database.

However, while I am not sure why it would matter to handle this on a per-Database basis, you could always DENY CREATE ASSEMBLY in Databases where you do not want custom SQLCLR code being used.

UPDATE

I just tested DENY CREATE ASSEMBLY TO [Public]; and this does not work for Logins that are a member of the sysadmin fixed server role. So another way to effectively deny permission to an action is to trap the action via a DDL Trigger and issue a ROLLBACK which will cancel the action.

The following is a DDL Trigger that works in whatever Database it is created in and traps both CREATE ASSEMBLY and ALTER ASSEMBLY statements, but still allows DROP ASSEMBLY (using DDL_ASSEMBLY_EVENTS as the Event Group would disallow all three actions):

USE [db_name];
GO
CREATE TRIGGER [PreventCreateAssembly]
ON DATABASE
AFTER ALTER_ASSEMBLY, CREATE_ASSEMBLY -- DDL_ASSEMBLY_EVENTS
AS

RAISERROR(N'Cannot CREATE or ALTER Assemblies!', 16, 1);
ROLLBACK;
GO

With this DDL Trigger in place, when I tried to create the Assembly, I now receive the following error, even though my Login is a sysadmin:

Msg 50000, Level 16, State 1, Procedure PreventCreateAssembly, Line 59

Cannot CREATE or ALTER Assemblies!

Msg 3609, Level 16, State 2, Line 54

The transaction ended in the trigger. The batch has been aborted.

deploy SQL Server Express LocalDB database app with clr enabled

How are you installing LocalDB? I assume you are attaching the MDF file via the Connection String, hence unable to execute these statements in a SQL script.

Assuming there is also no SQLCMD.EXE to run a query with, you should be able to create a separate Console App that the installer can run after installing LocalDB. That Console App can run SQL statements via SqlCommand.ExecuteNonQuery(). Those SQL statements can either be hard-coded (harder to change) or it can get the configuration SQL from the app.exe.Config file.

Something along the lines of:

SqlConnection _Connection = new SqlConnection(@"(localdb)\InstanceName");
SqlCommand _Command = null;

try
{
  _Command = _Connection.CreateCommand();
  _Connection.Open();

  _Command.CommandText = "EXEC sp_configure 'clr enabled', 1;";
  _Command.ExecuteNonQuery();

  _Command.CommandText = "RECONFIGURE;";
  _Command.ExecuteNonQuery();
}
finally
{
  if (_Command != null)
  {
    _Command.Dispose();
  }
  _Connection.Dispose();
}

---

Related Topics