People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form "can I use assembly System.XYZ.dll in SQLCLR procedural code" or "why do I get "assembly System.XYZ.dll is not found" when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.
I explain that there is a hardcoded list of assemblies that SQL Server will load from the file system and these assemblies have undergone an extensive code review to ensure they don't destablize SQL Server when they are running in process. Since some assemblies contain both crucial and "unsafe" (to SQL Server) classes (mscorlib.dll is an example that comes to mind) these libraries must also be decorated with instances of HostProtectionAttribute. This attribute gives the host (SQL Server 2005, in this case) the final say over whether to run a method (e.g. Thread.Start in mscorlib.dll). SQL Server is very picky about when it will run an HPA decorated method, its only if the calling assembly is CREATEd with PERMISSION_SET = UNSAFE. There's more about this in chapter 2 of A Developer's Guide to SQL Server 2005.
I refer to this list of assemblies as "the approved list". You can see most of the "approved list" assemblies by creating a Visual Studio 2005 Database/SQL Server project and choosing "Add Reference". This is the entire list of approved assemblies with one exception. Right before RTM "System.Configuration" was added to the list. I'm not entirely sure why, because although you can successfully use a .NET config file with SQL Server 2005 currently, it neither supported or encouraged (see my blog articles about this).
Why the long story? Because, in SQL Server 2005 SP1 another assembly made the list: System.Deployment.dll. I'm rather puzzled by this one too, because it has to do almost entirely with click-once deployment. Not sure why someone would want a sproc to do this, unless it was used to tie SQL Server to .NET project deployment in conjunction with Visual Studio Team System. But, its in there. Hmmm…
The libraries that I'd hoped would make the list eventually, perhaps in a SQL Server service pack when Windows Vista and WinFX are released, are the Windows Communication Foundation (System.ServiceModel.dll) and Windows Workflow Foundation (System.Workflow.Runtime.dll et al). I've heard a lot of folks ask the same question, but so far there's been silence on this. Since System.WebServices (but not WSE) is aleady "in there", there might be some uses going forward. As a look toward "intent", I ran ILDASM against the just-released May CTP version of these. Not a HostProtectionAttribute in sight. Oh well.
One thought on “New “SQLCLR-approved” assembly in SP1”
On system.configuration.install, I’m thinking that the reason its blessed is so that you can more easily use EventLogInstaller and PerformanceCounterInstaller.
Comments are closed.