Throughout the course of this book and even before it I have come across conflicting information regarding how SQLCLR attempts to resolve system/CLR assembly references. For example, prior to my latest read thourgh April BOL 2005, I thought SQLCLR attempted to resolve these references implicity for you via the local machine's GAC. Yet here is what I found while trying to help another person in this forum yesterday in BOL...
Assembly Validation
SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:
The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.
The set of system assemblies it references is one of the following supported assemblies in SQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, and System.Data.SqlXml.dll. Other system assemblies can be referenced, but they must be explicitly registered in the database.
For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:
The assembly code should be type-safe. Type safety is established by running the common language runtime verifier against the assembly.
The assembly should not contain any static data members in its classes unless they are marked as read-only.
The classes in the assembly cannot contain finalizer methods.
The classes or methods of the assembly should be annotated only with allowed code attributes. For more information, see Custom Attributes for CLR Routines.
Besides the previous checks that are performed when CREATE ASSEMBLY executes, there are additional checks that are performed at execution time of the code in the assembly:
Calling certain Microsoft .NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.
For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.
COULD SOMEONE PLEASE GIVE ME THE DEFENITE ANSWER ON HOW THIS WORKS!
And as a side note even if there is nothing more "to it" then these few lines in the CREATE ASSEMBLY topic in BOL, I think both this forum and the others prove that more information needs to be in BOL regarding this topic. How it relates to the subset of the .Net framework we have access to, HPAs, and CAS permissions.|||Hi Derek,
Where have you found conflicting statements on the SQL CLR assembly loading process? If it's in BOL or in another MS reference, let me know or file a bug on connect.microsoft.com so it can be corrected.
The passage from BOL above is accurate: only the system assemblies on the "Supported .NET Framework Libraries" list can be implicitly referenced from the GAC; all others must be created explicitly through CREATE ASSEMBLY.
I think that CAS and HPA are also covered pretty thoroughly in BOL. "CLR Integration Code Access Security" describes all the permissions available under each permission set and you can use a tool such as PermView to see which ones are requested by your code. "Host Protection Attributes and CLR Integration Programming" provides the same treatment for HPAs, along with listing every class or method in the supported system assemblies that can't be used in SAFE or EXTERNAL_ACCESS.
Actually, I just noticed that "CLR Integration Programming Model Restrictions" does contain an error: the custom attributes it lists are not explicitly disallowed in the UNSAFE permission set (although they are still not recommended and may not work as expected). I'll file a bug to get this fixed.
Steven
