Can''t deploy "unsafe assembly", permission problem(?)

Hello,

I think I have some kind of permission problem. But first things first:

I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of

"

CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

"

Of course I can not create "asymmetric key" for third-party-dll (Or can I?).

So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"

I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".

Login has server roles "sysadmin" and "securityadmin".

Login is mapped with DB User who has same name ( DOMAIN\UserName ) and has default schema "dbo".

Login has DB memberships "db_owner" and "db_securityadmin".

DB user owns schemas "db_owner" and "db_securityadmin".

Am I missing something?

Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.

Questions are:

- Is there other way to authorise third-party-dll than using trustworthy?

- Why deployment can be done in master-database?

And finally:

- Why deployment can not be done in other database?

So when you are deploying an assembly with the UNSAFE permission set (or EXTERNAL_ACCESS) and are using the TRUSTWORTHY settings (i.e. not assymetric keys etc), the login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY.

Niels
|||

Hello Niels,

" login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY."

As far as I know, permission _should_ be OK.

I checked following:

1. database -> properties (the database I want unsafe assembly to be created): owner is <DOMAIN\username>.

2. corresponding database user -> properties: Login name is same.

I have done "Grant UNSAFE ASSEMBLY to <DOMAIN\username>;" and "ALTER DATABASE <database> SET trustworthy on". Both are executed without errors.

<DOMAIN\username> is the Login name of owner of <database> and <database> is the db I want my assembly to be created.

Is there something still missing?

What I should check next?

- Jorma

|||Hmm, what is the exact error message you are getting? Is this database a new database, or is it restored from (perhaps) SQL 2000? What happens if you were to create a new database (with DOMAIN\username as owner), setting the database to TRUSTWORTHY and deply the assembly?

Niels
|||

Hello again,

Error message is:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'MillCC' failed because assembly 'USP.Express.Pro.2.0' is not authorized for PERMISSION_SET = UNSAFE.

"USP.Express.Pro.2.0" is the third-party-dll my code is using. Previously message were same but assembly named in the message was my code... Interesting.

I created new database as you suggested. And assembly was created without errors. Why in the world I didn't try this before... So, problem is somehow related with the database I am using.

The database is restored from SQL Server 2005 Express Edition.

Is there something I should know when using restored database and CLR?

|||Ah, it is restored. Try this for me please:
1. Run ALTER AUTHORIZATION against the database and change it to [Domain\username] (I know that you said that was the owner, but do it once again, please.
2. Run ALTER DATABASE and reset TRUSTWORTHY to on
3. Try and deploy

Niels
|||

I tried

Alter authorization on DATABASE::<database> to <[DOMAIN\username]>

but there came out following error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Should I remove something before running ALTER AUTHORIZATION? I am quite confused about the permissions at this stage |||OK, drop the user in the database, which is the user for the login [Domain\username], then run the ALTER AUTHORIZATION again.

Alternatively, you can create a SQL login (just a login) and make that login the owner of the database and assign the login the necessary permissions.

Niels
|||

OK,

first I tried to drop my user but there came out errors because user owns schemas etc.

I was impatient to test this, so I created new login and made login owner of the database. After givin permissions to the login I was able to create assembly.

In other words, now it is working! Thank you very much Niels!

Can you explain to me what was wrong with the "original" owner? I quess there happens "something" when database is restored...

Can''t deploy "unsafe assembly", permission problem(?)

Hello,

I think I have some kind of permission problem. But first things first:

I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of

"

CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

"

Of course I can not create "asymmetric key" for third-party-dll (Or can I?).

So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"

I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".

Login has server roles "sysadmin" and "securityadmin".

Login is mapped with DB User who has same name ( DOMAIN\UserName ) and has default schema "dbo".

Login has DB memberships "db_owner" and "db_securityadmin".

DB user owns schemas "db_owner" and "db_securityadmin".

Am I missing something?

Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.

Questions are:

- Is there other way to authorise third-party-dll than using trustworthy?

- Why deployment can be done in master-database?

And finally:

- Why deployment can not be done in other database?

So when you are deploying an assembly with the UNSAFE permission set (or EXTERNAL_ACCESS) and are using the TRUSTWORTHY settings (i.e. not assymetric keys etc), the login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY.

Niels
|||

Hello Niels,

" login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY."

As far as I know, permission _should_ be OK.

I checked following:

1. database -> properties (the database I want unsafe assembly to be created): owner is <DOMAIN\username>.

2. corresponding database user -> properties: Login name is same.

I have done "Grant UNSAFE ASSEMBLY to <DOMAIN\username>;" and "ALTER DATABASE <database> SET trustworthy on". Both are executed without errors.

<DOMAIN\username> is the Login name of owner of <database> and <database> is the db I want my assembly to be created.

Is there something still missing?

What I should check next?

- Jorma

|||Hmm, what is the exact error message you are getting? Is this database a new database, or is it restored from (perhaps) SQL 2000? What happens if you were to create a new database (with DOMAIN\username as owner), setting the database to TRUSTWORTHY and deply the assembly?

Niels
|||

Hello again,

Error message is:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'MillCC' failed because assembly 'USP.Express.Pro.2.0' is not authorized for PERMISSION_SET = UNSAFE.

"USP.Express.Pro.2.0" is the third-party-dll my code is using. Previously message were same but assembly named in the message was my code... Interesting.

I created new database as you suggested. And assembly was created without errors. Why in the world I didn't try this before... So, problem is somehow related with the database I am using.

The database is restored from SQL Server 2005 Express Edition.

Is there something I should know when using restored database and CLR?

|||Ah, it is restored. Try this for me please:
1. Run ALTER AUTHORIZATION against the database and change it to [Domain\username] (I know that you said that was the owner, but do it once again, please.
2. Run ALTER DATABASE and reset TRUSTWORTHY to on
3. Try and deploy

Niels
|||

I tried

Alter authorization on DATABASE::<database> to <[DOMAIN\username]>

but there came out following error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Should I remove something before running ALTER AUTHORIZATION? I am quite confused about the permissions at this stage |||OK, drop the user in the database, which is the user for the login [Domain\username], then run the ALTER AUTHORIZATION again.

Alternatively, you can create a SQL login (just a login) and make that login the owner of the database and assign the login the necessary permissions.

Niels
|||

OK,

first I tried to drop my user but there came out errors because user owns schemas etc.

I was impatient to test this, so I created new login and made login owner of the database. After givin permissions to the login I was able to create assembly.

In other words, now it is working! Thank you very much Niels!

Can you explain to me what was wrong with the "original" owner? I quess there happens "something" when database is restored...

Can't Create Asymmetric Key

I'm trying to get my first clr stored procedure going.
I believe I was able to create the clr stored procedure in visual studio,
set 'clr enabled' on the server, create an assembly, create an sp based on
the external name, and finally EXEC it from T-SQL to call it. When I call it
,
I get the System.Security.HostProtectionException below.
I read the documentation, and decided I needed to create an asymmetric key
to give myself permission to execute the clr stored procedure. I tried the
following in SS Management Studio:
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE =
'D:\ClrStoredProcedures\ClrStoredProcedu
res.dll'
This generates the following error:
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has
invalid format.
Any suggestions on how to create the asymmetric key? The error seems to be
saying that I can't create the key because I don't already have one.
Thanks
--
Randy
Msg 6522, Level 16, State 1, Procedure sp_DotNetFunSProcPrint, Line 0
A .NET Framework error occurred during execution of user defined routine or
aggregate 'sp_DotNetFunSProcPrint':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
System.Security.HostProtectionException:
at
System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm
,
PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh,
SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object
assemblyOrString, PermissionSet granted, PermissionSet refused,
RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission
permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet
grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle
rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at
System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs,
PermissionSet grants, PermissionSet refused, PermissionSet demands,
RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at ClrStoredProcedures.TestClrStoredProcedure.PrintMessage(String Message)
.Hi Randy,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding is: you want to create a CLR stored
procedure and get the System.Security.HostProtectionException error.
If I misunderstood your concern, please feel free to point it out.
Based on my experience, the System.Security.HostProtectionException error
most related to your CLR code.
Please first try to follow the step in the SQL 2005 Books Online to create
a CLR stored procedure and deploy it to the database to check if this issue
is related to your code.
http://msdn2.microsoft.com/en-us/library/ms255336.aspx
If you can successfully deploy the stored procedure, would you please post
your CLR stored procedure here so that I could provide further assistance?
If you have installed Visual Studio 2005, you can create a SQL Server
Project and create a CLR stored procedure and then Deploy it to your
database. You may try this method to check if this issue appeared.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello randy1200,

> I believe I was able to create the clr stored procedure in visual
> studio, set 'clr enabled' on the server, create an assembly, create an
> sp based on the external name, and finally EXEC it from T-SQL to call
> it. When I call it, I get the System.Security.HostProtectionException
> below.
Here's the URL for an article I wrote to help with this kind of thing. Its
the same idea the BOL covers, but in more tutorial fashion.
http://www.sqljunkies.com/WebLog/kt...op.com/ktegels/|||You first make a assembly using CREATE ASSEMBLY statement.
And your guery must modify FROM clause.
ASSEMBLY Assembly_Name
"randy1200"?? ??? ??:

> I'm trying to get my first clr stored procedure going.
> I believe I was able to create the clr stored procedure in visual studio,
> set 'clr enabled' on the server, create an assembly, create an sp based on
> the external name, and finally EXEC it from T-SQL to call it. When I call
it,
> I get the System.Security.HostProtectionException below.
> I read the documentation, and decided I needed to create an asymmetric key
> to give myself permission to execute the clr stored procedure. I tried the
> following in SS Management Studio:
> CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE =
> 'D:\ClrStoredProcedures\ClrStoredProcedu
res.dll'
> This generates the following error:
> Msg 15208, Level 16, State 1, Line 1
> The certificate, asymmetric key, or private key file does not exist or has
> invalid format.
> Any suggestions on how to create the asymmetric key? The error seems to be
> saying that I can't create the key because I don't already have one.
> Thanks
> --
> Randy
>
> Msg 6522, Level 16, State 1, Procedure sp_DotNetFunSProcPrint, Line 0
> A .NET Framework error occurred during execution of user defined routine o
r
> aggregate 'sp_DotNetFunSProcPrint':
> System.Security.HostProtectionException: Attempted to perform an operation
> that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> System.Security.HostProtectionException:
> at
> System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly a
sm,
> PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh,
> SecurityAction action, Object demand, IPermission permThatFailed)
> at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Obje
ct
> assemblyOrString, PermissionSet granted, PermissionSet refused,
> RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission
> permThatFailed)
> at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSe
t
> grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle
> rmh, Object assemblyOrString, SecurityAction action, Boolean throwExceptio
n)
> at
> System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs
,
> PermissionSet grants, PermissionSet refused, PermissionSet demands,
> RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
> at ClrStoredProcedures.TestClrStoredProcedure.PrintMessage(String Messa
ge)
> .
>