MS SQL Server - CREATE ASSEMBLY - .dll dependencies fail to load because they need to be Unsafe

Author

CREATE ASSEMBLY - .dll dependencies fail to load because they need to be Unsafe

Keith

2006-10-26, 12:12 am

Hello,

I have an assembly that depends on an interop assembly which wraps a COM
..dll. When I use CREATE ASSEMBLY to load the main assembly into SQL Server,
it fails because the server tries to load the interop assembly as 'Safe',
which it isn't.

Now if I load the assemblies using Management Studio (r-click on
Programmability/Assemblies/New Assembly), dependencies are NOT loaded (for
some reason) so I don't see this issue. But if I call CREATE ASSEMBLY from
a query window or via a SqlConnection I get that faliure described above.

So is there a way to control the permission_set of an assembly's
dependencies, or some other way to go about this?

Keith

P.S. This is how I'm doing it:

CREATE ASSEMBLY MyAssembly FROM 'C:\Assemblies\MyAss
embly.dll' WITH
permission_set = Safe;
The interop .dll is in the same directory.

Kent Tegels

2006-10-28, 7:14 pm

Hello keith,

Catalog the dependent assemblies first, then catalog the depending assembly.
Assuming you have Assembly A which is supposedly safe, and Interop.dll (your
wrapper assembly) and its dependent assembly, try:

create assembly [appropriate-name-for] from 'c:\assemblies\inter
op.dll' with
permission_set = unsafe
go
create assembly MyAssembly from 'c:\assemblies\myass
embly.dll' with permission_set
= safe.
go

Search on sgen and sqlclr for more details.

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Keith

2006-10-28, 7:14 pm

Kent,

Thanks for the reply.

I tried loading them as you suggested after reading in the BOL that SQL
Server wouldn't try to load a dependency if it was already loaded. However,
that is not true. The interop assembly loads fine, but then loading the
primary still triggers an attempt to load the interop assembly, but this
time it fails because the interop assembly already exists!

So it seems I'm stuck...

Any other suggestions?

k

"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad74e91c8c8c
6e22a8330c0@news.microsoft.com...
> Hello keith,
>
> Catalog the dependent assemblies first, then catalog the depending
> assembly. Assuming you have Assembly A which is supposedly safe, and
> Interop.dll (your wrapper assembly) and its dependent assembly, try:
>
> create assembly [appropriate-name-for] from 'c:\assemblies\inter
op.dll'
> with permission_set = unsafe
> go
> create assembly MyAssembly from 'c:\assemblies\myass
embly.dll' with
> permission_set = safe.
> go
>
> Search on sgen and sqlclr for more details.
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>

Keith

2006-10-28, 7:14 pm

Kent,

I tried this again and got it to work. When I tried this before it was
failing because I had a copy of the Interop assembly in the same directory
as the primary assembly. In .NET fashion, the dependencies seen to be
resolved locally first, so the interop dependency was located before the
server was inspected for it, and a second load attempted. This failed
because that assembly was alread loaded.

Once I separated the 2 assemblies, your load order suggestion worked fine.

Now I have HRESULT problems, but that is in another thread ;-)

k

"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad74e91c8c8c
6e22a8330c0@news.microsoft.com...
> Hello keith,
>
> Catalog the dependent assemblies first, then catalog the depending
> assembly. Assuming you have Assembly A which is supposedly safe, and
> Interop.dll (your wrapper assembly) and its dependent assembly, try:
>
> create assembly [appropriate-name-for] from 'c:\assemblies\inter
op.dll'
> with permission_set = unsafe
> go
> create assembly MyAssembly from 'c:\assemblies\myass
embly.dll' with
> permission_set = safe.
> go
>
> Search on sgen and sqlclr for more details.
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>

Kent Tegels

2006-10-28, 7:14 pm

Hello keith,

k> Now I have HRESULT problems, but that is in another thread ;-)

Cool. Please post that to m.p.ss.clr.

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Sponsored Links