Question on Internal Activation Stored Procedure Security Context

CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + "\"" + AsmName + "\"" +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";

command.ExecuteNonQuery();

I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITY\SYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

You have to mark the database trustworthy. Because the activated procedure is under an EXECUTE AS context, you are seeing all the problems described here: http://msdn2.microsoft.com/en-us/library/ms188304.aspx

HTH,
~ Remus

|||

Thanks for your reply. I have done "SET TRUSTWORTHY ON" to the DB initiating the dialog. But still "Could not impersonate client" error is thrown. I am using CERTIFICATES for dialog security. I have few "SELECT" statements inside the CLR Stored procedure, they execute fine; Looks CREATE ASSEMBLY is denied in this security context.

Will switching from 'NT AUTHORITY\SYSTEM' to a domain account just before "CREATE ASSEMBLY" will help ?

|||

The problem is not related to activation, but to EXECUTE AS USER = '...' context. The same error is returned if you run

EXECUTE AS USER='dbo';

and then try to run the CREATE ASSEMBLY statement (e.g. from a SQL Server Management Studio query window).

Under this context, after the database is marked trusthworthy, the CREATE ASSEMBLY succeeds if the login that 'dbo' is mapped to is made member of sysadmin server role.

HTH,
~ Remus

|||

BTW, if you need to avoid the syadmin membership requirement, the easiest workaround is to create the assembly from the assembly bits, not from a file. Since you're talking about an activated proc, I assume the assembly bits are available as a message payload (otherwise I really don't see the need to create an assembly in an activated procedure).

HTH,
~ Remu

|||

Yes..As you mentioned within SQL Server management studio, I had tried earlier and it works - with "EXECUTE AS USER = domain account" as well. But within CLR proc it fails. I tried including "EXECUTE AS" inside the transact SQL batch, the error remains.

I am creating the assembly from a network path (just sending this path as a broker message), so I may not be able to use assembly bits.

|||

Hi to all,

I am facing the same problem with the following line of

//code which i am trying

CREATE ASSEMBLY MyAssembly FROM 'C:\Documents and Settings\Administrator.ORC80\My Documents\Visual Studio 2005\Projects\MyDB1\MyDB1\bin\Debug\MyDB1.dll'

WITH PERMISSION_SET=SAFE

GO

//error i am getting

Msg 6585, Level 16, State 1, Line 1

Could not impersonate the client during assembly file operation.

please somebody help me...

|||Is the database marked as TRUSTWORTHY ?