Is SQLCLR Trustworthy?

When the support for .Net code arrived in SQL Server 2005 SQLCLR there were many experienced DBA’s expressing the thought “Not on my watch!”. Microsoft listened and disabled the option by default when released in November 2005. Everyone calmed down at that point. But still, do we want developers coding Stored Procedures in our precious Database using languages we know little or nothing about? The default language in SQL Server is still Transact-SQL. If enabled, SQL CLR supports writing Stored Procedures, Triggers, Functions, Types and Aggregates using the .Net languages. In our SQL Server for Developers course, we write Stored Procedures and Functions using both language sets. We create string-manipulation based Functions in Transact-SQL and VB.Net. We write data-access based Stored Procedures in Transact-SQL and C#.Net. The results are the same so then we analyze performance. Transact-SQL was developed with data-access in mind. Not number-crunching or string-manipulation. You can still do a lot with Transact-SQL in that regard but the .Net languages have superior depth and performance capabilities for just these types of operations. On the other hand, .Net is not as proficient with data access. In this case, Transact-SQL pays off because it is a set-based language. In our lab environment we prove this distinction scientifically. The four main steps for enabling SQLCLR based objects are: 1. Enable SQLCLR using sp_configure at the instance level. 2. Code the SQLCLR based objects in Visual Studio using a Database project and associated templates. Build the Project to produce an Assembly (.DLL) 3. Upload the Assembly to SQL Server using the CREATE ASSEMBLY statement. Create the associated Database objects by referencing the Assembly. e.g. CREATE PROCEDURE usp_MyProc 4. Execute the objects as normal Database objects of the same type. E.g. EXEC usp_MyProc The good thing about SQLCLR is that you do not have to commit one way or another. You can code your data-access modules using Transact-SQL and code your number-crunching stuff using .Net. You can even, for example, have a Transact-SQL Stored Procedure call a SQLCLR Stored Procedure and vice versa. Once created in the Database, you cannot tell the difference between the two types of objects; they just run and do the job they were coded to do. So you can mix and match and use the language that suits the job. One major concern is security. SQLCLR supports code based security. In other words, the code is locked down irrespective of the user executing the code. By default the Permission Set for an Assembly is SAFE which means no access to the registry, environment variables or external files. Processing is limited to SQL Server objects only. No matter who is executing the code, external access is prevented. If you need to lower the security, you have two choices: EXTERNAL_ACCESS and UNSAFE. When creating the assembly, use the PERMISSION_SET option. But you cannot do this alone. You need to have the Database owner in on the situation. The easiest way is to have the DBO set TRUSTWORTHY ON at the Database level using ALTER DATABASE. But that’s a global setting for the whole Database. A better option is to create a Certificate and digitally sign the Assembly for specific use by an associated Login. That follows the mantra of giving “just enough permission, nothing more nothing less”. Judging by name, you can guess that UNSAFE is not recommended! That would be equivalent to SQL Server 2000 extended stored procedures – and we do not want to go down that road again… Here’s a good article highlighting the detail… http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/8/Default.aspx Cheers Brian