Dependant assemblies in CLR

This is related to one of my previous posts.

I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.

I am using the Novell ldap library.

I want to do this with SSL so my code referneces the Mono security library as well.

However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.

Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)

Any ideas?

Thanks,

BIG

Hi BIG,

Are you loading the Novell library into SQL? One restriction with CLR integration is that outside of a static list of "approved" assemblies that we access in the GAC, all assemblies must be loaded into SQL.

Cheers,

-Isaac

|||Yes, they are loaded assemblies.
New info:

System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile)
at Novell.Directory.Ldap.Connection.connect(String host, Int32 port, Int32 semaphoreId)|

what the heck?|||

Hi BIG,

As you surmised, Novell is trying to load the Mono security assembly as part of the connect method. The problem is that dynamically loading assemblies under SQL CLR is explicitly disallowed as the error message states.

Unfortunately, Novell is explicitly calling Assembly.LoadFrom("Mono.Security.dll") which will always try to load the assembly from disk so there is no easy way to work around this problem. If instead the call had been Assembly.Load with the Mono.Security DLL's four-part name then you could solve this by preloading the Mono.Security.Dll into SQL Server yourself.

Steven

|||What if I use the sgen.exe tool and serialize the assembly and load teh serialized assembly?|||

That is the solution for a different problem where Xml Serialization also tries to load a dynamic assembly. Good try though.

Without changing the Novell source code to use Assembly.Load rather than Assembly.LoadFrom, I don't think there is any way around this. Sorry I can't be of any help.

Steven

|||Believe it or not, I got the source code for that .dll and did change it to Load now it does not want to work...gonna contact Novell.

Thanks,

BIG|||

So the Mono.Security assembly now loads correctly, but you're hitting a different error that prevents you from using it? Or are you still running into Loading issues?

|||Loading issue...
Original code: Assembly.LoadFrom("Mono.Security.dll");
My code: Assembly.Load("Mono.Security.dll");

Actually:

// Load Mono.Security.dll
Assembly a;
try
{
a = Assembly.Load("Mono.Security.dll");
}
catch(System.IO.FileNotFoundException)
{
throw new LdapException(ExceptionMessages.SSL_PROVIDER_MISSING,LdapException.SSL_PROVIDER_NOT_FOUND, null);
}

Error message is from the Exception thrown when the assembly is not found.

I am gonna research other ways to load an assembly, but I am sure I am screwed here, will try to post this in the relavant Novell forum as well.

What I may end up doing is trying to integrate these two assemblies into one (which I think Novell should have done in the first place :( )
OR
Making this into a standard exec and have SQL Server run the job calling that exec every night, so not in CLR, and lose portability with db.

Unless anyone has a better idea?

Thanks,

BIG S|||

Do you have the Mono.Security.Dll loaded in SQL Server yet? In order for the Assembly.Load to suceed, the assembly already needs to exist in your appdomain. So you need to do CREATE ASSEMBLY [Mono.Security] FROM 'path\Mono.Security.dll' with permission_set = unsafe first.

Also, you need to pass the full, 4-part assembly name to the Mono.Security assembly as documented: http://msdn2.microsoft.com/en-us/library/ky3942xh.aspx. You can get the 4-part name from sys.assemblies.

If this still doesn't work, you can verify that the Mono.Security.dll is reported as being loaded in your appdomain from the sys.dm_clr_loaded_assemblies dmv. If it's not there, try doing a dummy CREATE FUNCTION foo() returns int as external name [Mono.Security].bar.foo. (Sorry I can't verify if this is necessary right now - I just moved to Vista at home and don't have SQL Server installed yet).

Steven

|||ok working on it...

BTW: Even if I can't get it going, Steven u rock!

BIG S|||You are bloody brilliant...I got it working!

If you are ever in Toronto, beer is on me!

BIG S