Load Multiple Signed Assemblies

I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:

CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:\dba\bin\Assembly.dll'

CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey

GRANT UNSAFE ASSEMBLY TO CLRAssembler

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler

REVOKE CONNECT SQL FROM CLRAssembler

Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.

The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.

Is this the only way to do this? Or am I missing something?

First of all I think you mean:

CREATE ASYMMETRIC KEY SQLCLRKey FROM EXECUTABLE FILE = 'D:\dba\bin\Assembly.dll'

FROM FILE = '...' requires a file that has both the public and private key in it, but an assembly has only the public key in it. Also you should be creating this key in the master database.

In order to use an asymmetic key to enable an assembly to be loaded the asymmetric key must be the master database and include public key, but the private key is not required. When FROM EXECUTABLE FILE = '...' is used the only the public key for the asymmetric key is saved. This key can be used to create a login to grant usafe assembly to. Then, assuming the use has the other appropriate permissions, any assembly signed with this key can be loaded with permission_set = unsafe. A single login is used to load all of the assemblies that are signed with the same key... you can't load the same asymmetric key more than once in the same database. You will have to be sure that Visual Studio is signing all your assemblies with the same key. If you are having to create a new login for each assembly it sounds like Visual Studio is creating a new key for each of these assemblies. When you go to the properties for your visual studio project browse for a common key, don't create a new one.

You can create the asymmetric key directly from the snk file that visual studio creates, for example if myKey.snk is the key pair created by visual studio then:

USE master
GO

CREATE ASYMMETRIC KEY [MyAssemblyKey] FROM FILE = 'c:\keys\myKey.snk'
-- remove the private key, no reason to leave it hanging around.
ALTER ASYMMETRIC KEY [MyAssemblyKey] REMOVE PRIVATE KEY

CREATE LOGIN [LoginMyAssemblyKey] FROM ASYMMETRIC KEY [Key MyAssemblyKey]
GRANT EXTERNAL ACCESS ASSEMBLY TO [LoginMyAssemblyKey]

GO

Once you have done this any assemblies signed with myKey.snk can be deployed from visual studio with unsafe permission set.

Dan

Dan

|||

My mistake. I did mean EXECUTABLE FILE.

I started out trying to sign them all with the same key and then loading them individually and dropping the key and login, however this was producing an error (which I can post once I get back into the office).

Do I need to load them in the same batch or script if I want to use the same login? Because I was running them separately.

If not, how do I specify the login to use? I tried using the AUTHORIZATION command with it and it threw a permissions error.

|||

I'm not sure what you mean when you say you drop the login after creating the assembly.

If you drop the login, or take away the login's USAFE ASSEMBLY permission, you will not be able to use the assembly even though even though it has been created. The login created with the assemblies key is required whenever any function from the assembly is used.

Dan

|||

You need only use CREATE ASSEMBLY. Authorization is used to specify an owner, it is not related to whether or not the assembly can be external acess or unsafe. If the assembly is being created WITH EXTERNAL_ACCESS or UNSAFE, SQL Server will use the key inside of the assembly to find the login created with that key, then check the permissions granted to that login. It, in effect, does this whenever a function from that assembly is used too.

Dan

Load assemblies - CLRIntegration

All -
I have two assemblies (lets say AsmA and AsmB) loaded into SQL Server
(Create Assembly)
Both these assemblies have EXTERNAL_ACCESS permissions.
When the code inside AsmA is executed (within SQLServer context), it tries
to load a type in AsmB "dynamically"
(using CreateInstanceAndUnwrap) and it fails saying "Unable to locate AsmB".
This is the exact error
"Could not load file or assembly 'xxxxx' or one of its dependencies. The
system cannot find the file specified. at System.Reflection.Assembly.nLoad
"
Where does SQLServer put these 2 assemblies when they are created?
Are they stored in the same location?
If yes then LoadAssembly should ve succeeded.
Is it permitted to load a assembly dynamically (I am not getting a
HostProtected Exception so i presume it is permitted ) And remember i am
loading the assembly which is *already* created in SQLServer.
Please provide a solution for this.
Regardz
Grafix.> Where does SQLServer put these 2 assemblies when they are created?
> Are they stored in the same location?
> If yes then LoadAssembly should ve succeeded.
> Is it permitted to load a assembly dynamically (I am not getting a
> HostProtected Exception so i presume it is permitted ) And remember i am
> loading the assembly which is *already* created in SQLServer.
>
You are allowed to call Assembly.Load(). However the assemblies have to
be owned by the same owner, i.e. they are in the same appdomain. From
what you write, it sounds like the assemblies are in different app
domains, and remoting is not allowed inside SQLCLR.
Niels|||> You are allowed to call Assembly.Load().
Thats surprising. As a simple test, wihtin Clr context, i tried to do a
Assembly.Load and got a HostProtection exception. May be i have to change
ething to UNSAFE from EXTERNAL_ACCESS then.

> From what you write, it sounds like the assemblies are in different app
> domains, and remoting is not allowed inside SQLCLR.
The assemblies belong to the same owner [dbo].
AsmB infact has a compile time reference to AsmA.
But AsmA doesnt know the types in AsmB and does a dynamic create on it.
[Popularly known by the term "Plugin components"]
[No remoting and other complications.]
While creating thetype of AsmB using
"AppDomain.CurrentDomain.CreateInstanceAndUnwrap" it fails.
The stack trace shows CreateInstacneAndUnwrap calls Assembly.Load internally
and that says "FileNotFound".
One possible reason i could imagin is that since AsmB is "not already
loaded" into SQLServer's addressspace - the first time Assembly.Load is
called it searches for the assmbly in current folder(windows\system32 where
sqlserver runs from) and doesnt find it there and cries.
How can we make SQLServer know that when i say CreateInstanceAndUnwrap -
look into ur own database where u have stored the assembly than search
externally?
Any thoughts/
Regardz
Grafix.
"Niels Berglund" wrote:

> You are allowed to call Assembly.Load(). However the assemblies have to
> be owned by the same owner, i.e. they are in the same appdomain. From
> what you write, it sounds like the assemblies are in different app
> domains, and remoting is not allowed inside SQLCLR.
> Niels
>|||Grafix wrote:
> Thats surprising. As a simple test, wihtin Clr context, i tried to do a
> Assembly.Load and got a HostProtection exception. May be i have to change
> ething to UNSAFE from EXTERNAL_ACCESS then.
Hmm, I can do that without any problems. Just tested.

>
> The assemblies belong to the same owner [dbo].
> AsmB infact has a compile time reference to AsmA.
> But AsmA doesnt know the types in AsmB and does a dynamic create on it.
> [Popularly known by the term "Plugin components"]
> [No remoting and other complications.]
> While creating thetype of AsmB using
> "AppDomain.CurrentDomain.CreateInstanceAndUnwrap" it fails.
> The stack trace shows CreateInstacneAndUnwrap calls Assembly.Load internal
ly
> and that says "FileNotFound".
You need to supply the fully qualified assembly name, something like so:
select dbo.LoadAsm('asm2, version=0.0.0.0, culture=neutral,
publickeytoken=null, processorarchitecture=msil')

List registered assemblies

Hi,

It's possible to register an assembly in SqlServer 2005 using the

CREATE ASSEMBLY

syntax.

How can I get a list of assemblies that have been registered?

Thanks

Herbj?rn

Select * from sys.assemblies ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

List of Approved Assemblies

Where is it documented what the list of approved assemblies are for SQL 2005. Also, is there a way via T-SQL, or other means to get it programatically?Didi you already try to query te system table sys.assemblies ?

Select * from sys.assemblies

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||That doesn't get you "approved" assemblies, that just lists the assemblies that have been loaded to the DB using the CREATE ASSEMBLY command.

I'm looking for the list of "Approved Assemblies".|||

Here's a link to BOL that provides this information:

http://msdn2.microsoft.com/en-us/library/ms403279.aspx

Steven