Very often, you may wish to factor out your code into separate assemblies in your application. For example, you separate your type in one assembly because it gets used by multiple areas in your application. And you save your proc that uses that type in another assembly.
So Let’s say you have two CLR assemblies. Assembly A contains a UDT. Assembly B has functions which use the UDT in their function signatures and/or in code inside the function. Assembly B references A.
A problem occurs when they are strong-named. When a function in B is called this error is thrown:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate ‘MyProc’:
System.Security.SecurityException: That assembly does not allow partially trusted callers.
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
What’s going on ?
You tried writing your own executable with the same callers that use the UDT in A and it works! but it doesn’t within the server and you are wondering whether its a bug in the server ?
Well this is actually a CLR design. MSDN says that Types in strong-named assemblies can be called by partially trusted code only when the assemblies have been marked with the AllowPartiallyTrustedCallers attribute (APTCA). It adds that this attribute essentially removes the implicit LinkDemand for the FullTrust permission set that is otherwise automatically placed on each publicly accessible method in each type. Please note the word “FullTrust” in CLR refers to unrestricted access or ‘unsafe’ permission set by sqlclr definition.
When assembly B calls strong-named assembly A,
Either A must have AllowPartiallyTrustedCallers attribute Or B must have unsafe (FullTrust) permission set.
What does this mean ?
It means that CLR enforces the callers of a strong named assembly to be fully trusted unless that assembly has the APTCA attribute. If you add another assembly C that calls the functions in B, then B should have APTCA attribute or C should be unsafe. We recommend that if your assemblies in the database are SAFE/EXTERNAL_ACCESS (not FullTrust / partial trust / restricted), you need to mark them with APTCA to support cross assembly calls.
The reason your executable worked is, by default every assembly on you local drive gets FullTrust. If you copy your assemblies to a network share and try to run it from your local machine, you will see the same error, because default CLR policy will not give FullTrust to assemblies on remote shares.
Here’s the history on this requirement for sqlclr assemblies. CLR introduced the APTCA model for providing protection for the assemblies that are shared across multiple applications. Typically such assemblies were put in the GAC by applications. Assemblies that are NOT shared are usually in the Application Path. Any application that did not have unrestricted access(FullTrust/unsafe) could only load only those assemblies that are in GAC or those found by probing in the location specified by the AppDomain’s APPBASE property (ApplicationPath / Database).CLR loader guaranteed that and provided protection against restricted assemblies from loading arbitrary assemblies. In the case of SQL Server, the hosting hooks guaranteed similar control on what gets loaded in its appdomain. But there was no corresponding mechanism for protecting shared assemblies ( such as in the GAC ) from being called by arbitrary assemblies. APTCA enforced that by either requiring the caller to have unrestricted access or requiring that those shared assemblies opt to allow arbitrary caller. Using the attribute implied that the shared assembly would be audited / reviewed and then deemed to be safe for use from any calleer.
The problem with APTCA is that CLR checks for it in *every* assembly instead of restricting the check to shared assemblies. Further, CLR could have skipped checking this attribute for assemblies in the ApplicationPath / Database which can be considered as NOT shared. For example, this relaxation makes perfect sense for SAFE assemblies since have only execute permissions. They could have been considered implicitly APTCA and they could have been allowed to call each other. CLR allows the host to dictate permissions for the assemblies but does not allow the host to control APTCA behaviour.
The workaround we suggest therefore is to mark your SAFE/EXTERNAL_ACCESS assemblies with APTCA attribute in order to allow them to talk to each other. Please note that specifying this attribute in sqlclr assemblies does not require any audit/review and can be considered the norm to allow cross assembly calls. So we recommend that. We are not changing the rules here but specifying its relevance in SQLCLR. The directive for audit / review continues to remain valid for assemblies in the GAC that are shared between multiple other applications. In addition, we would not recommend granting unrestricted access to the callers of sqlclr assemblies. That will be an extreme measure.