Risk Of Enabling CLR

Question

Risk Of Enabling CLR

Denby

Hall of Fame

Points: 3120
More actions
July 21, 2008 at 9:21 am

#194179

Heys guys, just want to know if there is any potential risks with enabling CLR in SQL server.
Developers wanted to utilize this feature in prod.
Also, is the anything i should be monitoring from a DBA's perpective if feature is enable.
thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

The first risk is poorly performing code. Why do the developers want to use the CLR? There have been a couple of threads here on SSC where there is a set-based solution that outperforms a CLR solution in areas where most would have thought CLR code would have been faster.

Otherwise, I don't think there are too many other "risks" if you do not allow assemblies with UNSAFE permissions.

There are 4 DMV's for CLR functions that you can use as well. Lookup DMVs in BOL and there is a link to them.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Enabling CLR doesn't do anything in reality, it just allows CLR to work if it exists in the database. You can create assemblies in SQL even with CLR disabled, they just won't run. You must be on 90 compatibility mode before you can use CLR inside of SQL Server. Things to monitor? The list grows every day it seems. Start with:

What do they want to do in CLR that can't be done in TSQL?

Most often the application of CLR is being misplaced, and a suitable TSQL implementation exists or can be created to prevent needing CLR.

Denby Hall of Fame Points: 3120 More actions · Answer 3

Denby

Hall of Fame

Points: 3120

July 21, 2008 at 10:14 am

#845865

Ok, thanks guys appreciate the inputs

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Denby (7/21/2008)
Also, is the anything i should be monitoring from a DBA's perpective if feature is enable.

Absolutely! First, I'd make bloody sure that the source code was in a versioning tool. Next thing I'd do is review the source code to make sure there's nothing really bad in the code that may bring the server to it's knees. The other reason for the source code review is to check and see if someone is just using CLR's because the can't figure it out in T-SQL... there's that overhead thing that Jack was talking about above. 🙂

Personnally... I won't turn the damned things on. Except for some RegEx functionality, I've been able to do what most folks turn to a CLR for in T-SQL... and beat the CLR for performance, to boot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ianT SSCrazy Points: 2956 More actions · Answer 5

Had a CLR procedure cause random server crashes in production. Only able to replicate the error in Dev after a lot of work and never realy fixed.

If you can, avoid.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 6

I don't like the idea of CLR code, but there have been a few people that have found it quite handy. I tend to side with those above and I'd want to know more about what exactly developers want to do with it that they can't do another way.

SQLRockstar SSCertifiable Points: 7014 More actions · Answer 7

hi denby,

we try to limit the use of CLR here, simply because it does not seem to be necessary. however, one caveat that i have seen has to do with memory. although I have been told that the CLR memory space will not affect the mssql memory space, what I have found is that periodically the CLR ememory space becomes so fragmented that a bounce of the service is necessary.

so, while you may be told that you do not have to worry about CLR bringing a server down, the fact is that if someone is using CLR and they rely on CLR for production, it may be necessary to bounce the services anyway. this results in a outage, plain and simple.

if possible, sit doen with the developers and ask questions, and explain some of the risks. chances are once they know the risks, they may think twice about using CLR.

----------------------
https://thomaslarock.com

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

Thomas LaRock (8/18/2008)
hi denby,
we try to limit the use of CLR here, simply because it does not seem to be necessary. however, one caveat that i have seen has to do with memory. although I have been told that the CLR memory space will not affect the mssql memory space, what I have found is that periodically the CLR ememory space becomes so fragmented that a bounce of the service is necessary.
so, while you may be told that you do not have to worry about CLR bringing a server down, the fact is that if someone is using CLR and they rely on CLR for production, it may be necessary to bounce the services anyway. this results in a outage, plain and simple.
if possible, sit doen with the developers and ask questions, and explain some of the risks. chances are once they know the risks, they may think twice about using CLR.

This points to either a problem in your server configuration or a problem with your CLR code where it is not allowing the GC to activate and free the memory properly. I have had CLR running for many months with no appdomain unloads or server restarts necessary on any server that is running it. In a worst case scenario, you shouldn't have to restart the service, as a DBCC FREESYSTEMCACHE call will clear the CLR stack from its MTL allocations.

CLR runs in a very limited and finite memory space in SQL Server called the MemToLeave area. This is generally only allocated 256MB of memory at startup, depending on a number of factors to include how much memory your server has, network packet size, and various other configuration options. It isn't very hard for SQL Server to bump into MTL memory pressure without CLR running let alone with CLR running. I'd recommend that you investigate what is actually causing your problem in the sys.dm_os_memory_clerks and see where your memory is actually being allocated. XP's also work in the MTL area, which is why some tools like litespeed used to create MTL memory pressure. There is a lot more to play here than CLR causing a problem.

The above also applies to the post about CLR crashing a SQL Server. By design CLR won't do this unless your wrote code that leaks pointers in UNSAFE registered assemblies. If you build appropriate CLR code for use in SQL only for Instances where CLR is appropriate, and I admit that these are extremely limited, then you shouldn't have problems with running CLR in SQL. 99% of the CLR code that I have seen on this forum, as well as the others that I answer on is misplaced, and belongs in an application layer outside of SQL Server. 99% of String manipulations being done in CLR can be done in TSQL much faster, as Jeff says, regular expressions replacements and matching seem to be the only two that outperform a solid TSQL solution.

SQLRockstar SSCertifiable Points: 7014 More actions · Answer 9

Thanks Jonathon, I will try the DBCC FREESYSTEMCACHE the next time we have this issue.

It doesn't happen often, and I am familiar with the MemToLeave area, and the various things that eat up space in there, including Litespeed. However, I had not thought about all the connections until reading your post, thanks.

In most cases, the CLR works fine, but every now and then there seems to be a slight bump in the road.

----------------------
https://thomaslarock.com

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

You can try to add the -g384 or -g512 startup parameter to see if it is a memory leak causing the problems. I have one CLR object that interacts with third party application webservice that has a poor API and returns large datasets back. I had to increase the MTL allocation size to prevent having appdomain unloads occur from memory pressure.

If you are truly leaking memory and preventing the GC from cleaning out objects, adding the startup option won't solve the problem, it will just provide more memory to be leaked.

SQLRockstar SSCertifiable Points: 7014 More actions · Answer 11

i have used that -g flag previously, but as you said it does not solve the real issue, it just delays it from happening as frequently.

----------------------
https://thomaslarock.com

DC-SQLServer SSC Veteran Points: 281 More actions · Answer 12

To be clear there is no security issue, SQLCLR code can’t do anything more in a database than an equivalent T-SQL code module running under the same security context. Ignorantly prohibiting CLR also prevents SSISDB deployment, which drastically improves security via less RDP accounts, FileSystem management and less rights needs, Backup inheritance inside the maintenance plans, Full Package Encryption via TDE, to say nothing of SSIS package deployments and maintenance via the environment section of the SSISDB and lack of many C# functions in SSIS which require CLR.

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

This is currently the most ignorant security claim in all of SQL Server, which can prohibit the flagship ETL deployment model, the SSISDB (requires CLR), because some 3rd party security tool which also incorrectly flags SQL Server collation coefficients via 2000, or simply a severely misinformed DBA. **CLR is not a security risk**, it actually has enhancements to security in multiple regards. Ask your DBA if he cares so much about security why Mixed Mode Authentication is set on and TDE is not enabled and they have non-existent auditing.

To enable CLR simply run

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

You do realize you replied to a post that is 7 years old and completely predates SSISDB in SQL 2012?

DC-SQLServer SSC Veteran Points: 281 More actions · Answer 14

Yes, and ignorant people are still keeping it disabled in heavy SSIS shops.