Poll: Do you see SQLCLR as a security threat?
When Ken Henderson asks, I listen. He's asked this question and there's been some good responses. My favorite is from Jeff Parker because its probably the most honest:
Most DBA's are not .NET programmers.
I'm not so sure that most DBA's aren't programmers at some level, though. Most DBAs do write code in T-SQL. Many of them are versed in scripting. For them programming means creating tools to help them do their job, not building applications. And that's why Jeff's statement is the most correct: .NET isn't about building tools, its about building applications. So while they get ideas embodied in the code, its a bit like asking a blacksmith how to build a skyscraper when you ask them about .NET.
And this is precisely why I think off by default isn't just a good security practice, its a good configuration practice. Many of other posters to this thread have noted that in and of itself, the hosted runtime isn't any more dangerous in its typical application and say, T-SQL code. SQLCLR used right doesn't try to much more than extend what SQL Server 2005 is already good at: processing T-SQL Queries. I've long believed that the best use case for SQLCLR is write functions that do complex calculations or that leverage something in the .NET Foundation Class Libraries that T-SQL is missing or is poor at. That includes things like financial functions, regular expressions and XML parsing. The idea is that we're just extending T-SQL via the CLR, not subverting or replacing it. Stored Procedures based on CLR are useful when you need to perform some CRUD operations as a result of procedural logic that's either easier to write and maintain in a .NET language or, again, you need to leverage some functionality in the framework like compression or Web Services. User Defined Types certainly have a place, but its no bridging business objects into in-proc environment. The best use for a UDT I've seen so far is to build composite scalar types, like complex numbers, geographical co-ordinates and so on. CLR triggers simply don't seem to do much for me. I'm hard pressed to think of cases where I wouldn't be better served to write T-SQL triggers that called SQLCLR UDFs when needed instead. Aggregators have some uses: obviously needed for UDTs and I've some other use cases for them.
Where I suspect we'll see the biggest security issues with SQLCLR is that folks won't take the "Extend T-SQL" message to heart. They've move business logic into the server and just assume that things work the they do in a non-hosted runtime. That may or may not be the case. For example, I've heard lots of people who got excited that they'd now be able to call SOAP Services from within SQL Server. And, sure, that's cool. But how do you know if you really trust the data your getting from that service? Or that the service is still the service you thought it was? My concern runs deeper too. Suppose you have a good chunk of business logic lodged in the server and you know its got holes. But fixing them means not only taking the application off-line for a while, but also the whole database. Maybe not so much of a problem for databases that just support a single application. Pretty much "no way" for a busy OLTP data warehouse.
But lets not forget on the best use cases for SQLCLR is to avoid writing something as an Extended Stored Procedure (XP). XPs are typically far more of a security and reliability concern than most developers and DBAs expected. At least SQLCLR gives SQL Server better control over the code and runtime while being nicely meshed into the current security model for other objects. I'd much rather tell somebody to blindly enable SQLCLR before I'd encourage them to use an XP.
This sort of answers Ken's third question: If you see SQLCLR as a security threat, what specific issues are you worried about? How do I answer Ken's second question: Do you think it makes sense for security-conscious users to disable SQLCLR and avoid apps that use it? The answer is yes and no. Yes, I do think that unless you have a good reason to enable CLR support in the first place, leave it off as it is by default (so logically, no, they shouldn't disable it, it's already disabled). Should it be avoided? That depends on the specific case. I'd say that the unless the DBA knows, understands and can support the code that coming onto the server, he or she might be well served to avoid it. But that's not specific to SQLCLR. The same holds true of T-SQL objects and Extended Stored Procedures. The best way that most DBAs will "grok" SQLCLR is if its presented as an simply extending T-SQL.