Question on Internal Activation Stored Procedure Security Context

CLRfunctionhas 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 fromService Broker internal activation stored procedure.

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

SqlContext.WindowsIdentity.Nameis "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.

June 25th, 2006 10:49am

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

Free Windows Admin Tool Kit Click here and download it now
June 25th, 2006 5:14pm

Thanks for your reply. I have done "SET TRUSTWORTHY ON" to the DB initiating the dialog. Butstill "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 ?

June 26th, 2006 1:43pm

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

Free Windows Admin Tool Kit Click here and download it now
June 26th, 2006 8:27pm

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

June 26th, 2006 8:52pm

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 touse assembly bits.

Free Windows Admin Tool Kit Click here and download it now
June 26th, 2006 9:04pm

Hi to all,

Iam 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...

August 1st, 2007 6:26am

Is the database marked as TRUSTWORTHY ?

Free Windows Admin Tool Kit Click here and download it now
August 1st, 2007 9:34am

Nayi Jitendra wrote:

Hi to all,

Iam 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...

The impersonate problem is due to current user is not having the System Administrator (SA) right, try to create the assembly with SA user.

----V V----

Vikas Vaidya

March 27th, 2008 1:29pm

Try after executing below commands from sa login.

sp_configure 'clr enabled',1
go
reconfigure with override
go


Free Windows Admin Tool Kit Click here and download it now
April 17th, 2015 12:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics