SQL CLR AppDomain problem

Question

SQL CLR AppDomain problem

davidc-702140

Say Hey Kid

Points: 665
More actions
December 17, 2009 at 5:19 am

#385964

Hi,
I've got a SQL CLR procedure which insepacts assemblies and sends data about the assembly back to the client. Seeing as I'm only inspecting the assembly, not running it, I'm using Assembly.ReflectionOnlyLoad which according to MSDN loads the assembly into the reflection only context of the caller's application domain. Here's the problem - once loaded, it stays in the app domain and to ensure it's unloaded after inspection I need to load it into a new domain and unload the new domain. To achieve this I did the following.....
[Microsoft.SqlServer.Server.SqlProcedure]
public static void AssemblyCheck()
{
// Create the new app domain
AppDomain domain = AppDomain.CreateDomain("NewDomain");
domain.DoCallBack(new CrossAppDomainDelegate(AssemblyCheckWorker));
AppDomain.Unload(domain);
}
private static void AssemblyCheckWorker()
{
// Load an assembly
Assembly loadedAssembly = Assembly.ReflectionOnlyLoad("Assembly1");
// Do some inspection on the assembly
}
However when I run this I get the following error at the client end:
"A .NET Framework error occurred during execution of user-defined routine or aggregate \"p_InspectAssembly\": \rSystem.IO.FileNotFoundException: Could not load file or assembly 'AssemblyTools, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.\rSystem.IO.FileNotFoundException: \r at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)\r at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)\r at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)\r at System.Reflection.Assembly.Load(String assemblyString)\r at System.Reflection.MemberInfoSerializationHolder..ctor(SerializationInfo info, StreamingContext context)\r at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)\r at CLRStoredProc.AssemblyCheck()\r."}
I've tried a console app in c# which creates a new domain and inspects an assembly exactly as above and it works fine. But from inside SQL server it won't run. Do anybody know why this is, or if it's even possible to create new app domains from the server?
I'm using SQL Server 2008, and the assembly was created as UNSAFE.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I am pretty sure that what you are attempting falls under dynamic assembly loading which is disallowed in SQLCLR for security:

http://blogs.msdn.com/sqlclr/archive/2006/10/18/dynamic-assembly-loading.aspx

Just because you can doesn't mean you should. This is application functionality, not database functionality. You are trying to use SQLCLR for something it was never intended to do.

davidc-702140 Say Hey Kid Points: 665 More actions · Answer 2

Thanks for the reply.

SQL server is simply failing to execute any code in the DoCallBack method, I tried putting a return statement at the top of this method so that all the CLR proc was doing was creating a new app domain, and in the new domain it runs the code in the DoCallBack method, which itself simply returns and doesn't do anything, and then to unload the new app domain.

I don't know how new app domains get created, will this itself using dynamic assembly loading? If so I understand why having read the link you posted in your reply.

In the DoCallBack method I was using ReflectionOnlyLoad which when is not in a new app domain actually runs fine as it's loading an assembly for INSPECTION only, not to run, so the security issues are different. So at least I know can inspect an assembly.

The onlyt problem is that I have to have code like this:

private static Assembly LoadedAssembly = null;

then in the InspectAssembly method:

if(LoadedAssembly == null)

{

LoadedAssembly = Assembly.ReflectionOnlyLoad("Assembly1");

}

This means the assembly is only loaded once and persists in the current app domain. This is OK, but not ideal as I would like it to be removed from the app domain after inspection. The only way to achieve this is to load it in a separate app domain.

The limitation here is that if the assembly I want inspect is updated, I need to restart the SQL server. All achievable but not ideal.

So far I know the problem is that SQL server will NOT run the DoCallBack method. If I have

AppDomain newDomain = AppDomain.CreateNewDomain("NewDomain");

AppDomain.Unload(newDomain)

this runs fine. Only by adding: newDomain.DoCallBack(CallBackDelegate) do I get the sql error.

Is this line using dynamic assembly loading at all?

Thanks again for your help. The post was very helpful!