Poll: Do you see SQLCLR as a security threat?


I’ve been hearing from some folk that they consider the SQLCLR functionality in SQL Server 2005 to be a potential security hole.  Some have even gone so far as to say that they plan to leave it disabled (it’s not enabled by default).  When pressed, many can’t articulate what their specific concerns are, or they raise concerns that aren’t valid — that the architecture already addresses.  At first blush, not using this wonderful facility because of vague security concerns seems a bit much.  I think the dev team has done a reasonably good job of locking it down and balancing functionality with security.  That said, I’m interested to hear your opinions.  Do you think it makes sense for security-conscious users to disable SQLCLR and avoid apps that use it?  If you see SQLCLR as a security threat, what specific issues are you worried about?


Comments (11)

  1. I think it’s mainly a matter of reducing the surface area for attacks, and that’s probably why it’s also included in SAC: if it’s not there, you have less things that can go wrong. This may be seem a bit paranoic but a lot of security bulletins are still issued every day. On the other hand every non trivial piece of software potentially has bugs, choosing whether to use it or not should be based on other architectural factors. I know this has been already discussed a lot, but IMO the big problem will be whether people will use it wisely or not. It doesnn’t have anything to do with security threats, but I also think that many DBAs feel they can’t do proper troubleshooting and problem determination until they have learned very well how it works, so they may choose to leave it off until they feel more confortable.

  2. Jeff Parker says:

    Programming .net since before the release of 1.0 I have come to learn that the .net framework is a very powerful tool. But here is my point of view. If CAS is enforced by default and permissions set properly on the database I think it should be fine and secure.

    Here in lies the problem. Most DBA’s are not .net programmers. I have admins coming to me all the time more and more asking me what all is .net about and what do I need to know about it as an administrator of these servers. VB runtime libraries most admins didn’t have a problem with because they couldn’t see them. They knew in the back recesses of their minds they were there but to them they weren’t. .Net has now added Icons to the Administrators group. This adds confusion. There is something to be said about Raymond’s Rat Poker post today. Even admins have this problem at a different level. http://blogs.msdn.com/oldnewthing/archive/2005/10/19/482632.aspx

    Now admins get scared as well as the normal users. Server admins, DBA’s, Infrastructure guys all over the world are seeing .net coming at them. They have Icons in the administrator group. Are they supposed to be doing something with them? Why are they there? What do I need to know about .net without learning how to write applications? Code Access Security, what is that and how does it work? Garbage collection, what is this app garbage? These are all questions I get monthly. Well it is very difficult for me to explain these things to non programmers. Many admins know what xml is or looks like. But they do not know how to change a config file it and when you look at the documentation for CAS and some of these other things. It is definitely geared towards developers. There really needs to be a book maybe titled ".net for the Lan Administrator" with prebuilt working code on a cd for these guys so they can experiment with settings and stuff. DBA’s same way maybe my vision of how it is working out in the real world is slightly skewed but I think us developers out here are pushing for SQL 2005 because we like it and are comfortable in the .net world but DBA’s and Infrastructure guys would be perfectly happy sitting here with W2k servers running SQL 2000. There are some really radical changes in the SQL world I think TechNet needs to do some promotion and knowledge sharing on these things. Most infrastructure fear what they do not know and what is going to change their confort zone and all the MSDN documentation in the world won’t help them since they are not developers. They really need a lot more information to explain things in their way.

  3. matt says:

    I’m a huge proponent of separating code from data/documents. Having program code within SQL server violates that rule. From a security standpoint, we need to take a big step back and stop adding scripting/programming capabilities to things like documents, movie files, and databases.

    If you want a program, create a program. Don’t embed/hide it in something like a Word doc or a database. True separation of what is code and what is data will go a long way toward improving security all the way around.

    Proponents of SQLCLR will claim that SQL Server has had code capabilities for a long time with stored procedures. But we would be wise to note that this has always been a security risk.

  4. kfarmer says:

    I see a risk, of course, but I think it’s as manageable as any other risk related to stored procedures.

    Matt: Stored procedures allow you to host code local to the server. This allows for performance as well as development benefits (for example, the procs define an API through which the data can be safely manipulated). They *benefit* the code/data seperation by making the remote code blind as regards the schema. Take any large database analysis and you’ll see that it’s intractable without running code at the database, because of the latencies involved in wire transport, and in the fact that, in general, non-database systems aren’t granted the boost to hold all the data required in-memory.

    There’s a reason that stored procs are a recommended best-practice. SQLCLR is the next logical step toward making them safer and more manageable.

  5. badri says:

    Well because security comes first nowadays. And it requires admins a few months to learn the technology before we can allow devs to use it. It allows us to take precautions, and avoid the usual pitfalls of learning things later on.

    Jeff: I find it sad that your admins are not capable of or do not want to understand .net. Just like it is important for a DBA to know the inner parts of tsql, sql, procs etc. it is important that your web app admins know IIS and asp.net and their security capabilities. If they cannot learn and edit asp.net config files now, they are going to have a real tough time with iis 7.

  6. Simon says:

    I started of thinking oh my god this is going to be a nightmare. However the more I think about it the more that it has a purpose.

    I am sure I can find in every database a function, stored procedure that is unreadable due to the nature of TSQL. Unreadable can often meean not performant, and more often difficult to support.

    SQLCLR code can provide answers to lots of problems, encapsulating a repetitive function in a reusable routine is the firstthing I think people should look at.

    In my testing even the simplest user defined function is slower when written in TSQL compared with SQLCLR. Moreover when used in a SELECT, you can still use profile to capture statements which the profile being full of calls to your function.

    More complex functions i.e. stripping out HTML tags and Carriage returns to be able to produce a flat file. A TSQL UDF for this can be 100 times slower than the SQLCLR equivalent.

    So my view is. Don’t cut your nose of to spite your face. Manage the risk because the benefits will out way the risk.

  7. Brian Lawton says:

    Do I see the SQLCLR in and of itself as a security threat, no, but it does increase the exposed surface area thereby potentially violating the good security practice of not installing/enabling unused tools. My bottom line recommendation to clients is that unless they have a specific business driver requiring SQLCLR functionality as part of their solution’s design, leave the SQLCLR disabled.

  8. IMO, lot’s of these security fears are based on the fear about the un-known. Face it; a SQLCLR assembly catalogued under default permission set is as safe as T-SQL code executing inside SQL Server.

    Matt: I’d be interested in hearing your arguments why you consider sp’s being a security risk.

    Niels

  9. Simply put: Not at all.

    Do I think there are some risks? Sure. But we already have plenty of risks in SQL Server 2000. xp_cmdshell, sp_oa*, extended stored procedures. Each of these are riskier from a security point of view than using managed code, and managed code will help us do away with using them.

    If you are concerned, just remember your two options:

    A) sp_configure ‘clr enabled’, 0

    B) WITH PERMISSION_SET = SAFE

  10. I’m interested to hear what you consider the biggest potential security issues with SQL Server 2005. …

  11. Nguyen Tran says:

    I think it is a great tool for developers.

    There are things you can do in the new CLR that was not available before the CLR (or at least you couldn’t develop it easily).  Actually, I think it makes it more security because it allows developers to develop security functionality (like encryption algorithms) in the SQL Server verses an external process.  Since .Net includes basic encryption algorithms, then you don’t have to rely on 3rd party encryption algorithms.  Also, you can manipulate low level data (like image data) in .Net much more easily than in T-SQL.

    From a SQL Developer standpoint, you can achieve more advance development options with the CLR.