Are there any security issues with enabling CLR on SQL SERVER 2005?

Question

I am toying with the idea of enabling CLR on my SQL server, using EXEC sp_configure 'clr enabled', 1

However, I am sharing my database server with several other developers and their projects. I've heard vaguely that their might be security issues with enabling this.

Does anyone know what these issues might be? Is CLR safe to use on SQL Server?

Answer 1

No, SQLCLR code can’t do anything more in a database than an equivalent T-SQL code module running under the same security context.

This was and currently is the most misunderstood security claim in all of SQL Server (especially 2012 onwards), which can break UI connectivity to SCCM databases, the flagship ETL deployment model SSISDB (requires CLR), because a 3rd party security tool inheriting CIS benchmarks (DBProtect primarily) which also incorrectly flags SQL Server collation coefficients via 2000 even if the server is not running 2000, falsely directing DBAs to rebuild master and forever crippling their environment and applications as case sensitive if nobody speaks up on the finding. CLR is not a security risk, it allows for flagship enhancements (post SQL Server 2012) to security in multiple regards via RDP and file system right mitigation and SSIS code management e.g. SSISDB, which is likely to impact every 90% of SQL Server shops, including HA solutions built to not rely on a single SAN.

A side note regarding DBAs who are simply averse to CLR it because it can be "difficult to troubleshoot if made badly" - it is not primarily inside DBA purview to troubleshoot senior .NET developer code, if you hire bad DBAs they too can be difficult to troubleshoot (see above via collation). Further, the majority of people leveraging CLR are doing so for the flagship functions and have little or nothing to do with writing CLR code (although script tasks in SSIS leverage this to a degree), and have much more to do with SSISDB and using availability groups across SANs. DBAs who dislike this functionality should jump into a time warp and hit stasis mode for the year 2008. This is written from a full-stack BI/DBA perspective, not from a somewhat myopic sys internals vantage point.

Further, Availability Groups utilize CLR, causing an error if CLR is not enabled. More information also vetted on Technet

Both both Availability Groups and SSISDB are flagship features of modern SQL Server environments.

Currently, by enabling CLR and deploying SSIS packages via the SSISDB, you can mitigate file system organization mismanagement and clutter, gain inherited backup maintenance plans and even TDE, and actually drastically lower the need for RDP to troubleshoot an SSIS package.

Ask your DBA if he cares so much about security why Mixed Mode Authentication is set on, no SSL certs for SSMS or SSRS or Excel clients, TDE is not enabled, and lack of auditing or even logging successful and failed logins.

http://www.codemag.com/article/0603031

To enable CLR simply run

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Answer 2

I am with Squillman on this one; unfortunately, the answer is not as simple as a “yes” or “no”.

Enabling CLR on the server enables hosting user-defined CLR modules on your server, which opens a non-trivial set of possibilities to create custom modules & data types, but it also opens a significant attack surface area on your server.

There are further considerations to take:

• Who has privileged permissions on the DB? Do you trust all other people who may be able to create assemblies on your SQL Server? If not, be aware that they will be able to host CLR code on your server, something I would not recommend.

• Other settings on the server, including ownership of DBs & the Trustworthy bit setting on the database. The following article has more detail on this topic:
  https://blogs.msdn.microsoft.com/sqlsecurity/2007/12/03/the-trustworhy-bit-database-property-in-sql-server-2005/

• .Net itself is not bug free. While hosting CLR code is a much better idea than hosting eXtended stored Procedures (XPs), there is always a risk of a security vulnerability within the .Net framework itself, in which case what would typically be a safe CLR module hosted in SQL
  Server could become an attack vector to gain control of your SQL
  Server process. It doesn’t happen often, but you have to ever be vigilant and act accordingly when it happens.

I hope this helps,

-Raul Garcia

SQL Security