SQLCLR – List of approved framework class libraries

Back from vacation, drove from Ashland back to Portland Saturday, then Portland to Redmond Sunday. A lot of driving. The Michael Brundage XQuery book was even better the second reading. Joe Celko's book was excellent (what else?) also. So was OSF presentation of Henry VI, Much Ado About Nothing, and King Lear, but my favorite was a non-Shakespeare play entitled "Humble Boy". It was about "aha moments" in the life of a scientist.

Back to teaching and to SQL Server 2005 SQLCLR.

While teaching folks about SQLCLR, I've always said that SQL Server uses a hardcoded list of "approved" framework class libraries (libraries that are part of the .NET framework, e.g. System.dll) to determine which libraries are allowed to load. Also, although user assemblies are cataloged as SAFE, UNSAFE, or EXTERNAL_ACCESS, which FX (framework class) libraries are allowed to load does not vary with safety level. Which methods in those libraries can be called without causing a security exception does vary; however if a library is not "on the list" it won't be loaded even if referenced in UNSAFE user assemblies. Folks always want to know where the list is (ie, "prove it to me").

Lately I came across the list in a rather unique way. Create a Visual Studio B1 SQLCLR project in any language (a Database/SQL Server project). Now choose "Add Reference" either by right-clicking on references or any only means. Note the list of libraries you can choose. These include only the FX assemblies allowed to load and also all of the libraries at your safety level or below that are already cataloged in the database that autodeploy is pointing to. Also, when you change the safety level in your project, the FX assembly list doesn't change. Aha…

I'd also like to see a browse button on the AddReference dialog as well. Not having a browse button means you can't add a user library that's not already cataloged. Why I might want to do that is a story for another day.

2 thoughts on “SQLCLR – List of approved framework class libraries

  1. Hi Bob,

    My understanding on this is it was implemented via an Attribute, rather than a list. They had retrofitted the attribute back through the framework and you could conceivably in the future use it on your own assemblies that were not viable for this. Haven’t investigated further to confirm this though.

    Regards,

    Greg

  2. Hi Greg,

    I think you’re probably referring to HostProtectionAttribute, which causes a runtime error (SecurityException). Not being on the list causes a catalog-time error (ie the error occurs at CREATE ASSEMBLY time). The error looks like this:

    CREATE ASSEMBLY mylib FROM ‘c:typesmylib.dll’
    GO

    Could not find assembly ‘system.windows.forms.dll’ in directory ‘c:types’.

    More on this soon…

    Cheers,
    Bob

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.