CODEWITHSUNDEEP
c#sql-server
ben1984
ben1984

Reputation: 101

System.Security.SecurityException: That assembly does not allow partially trusted callers

I am trying to create a simple msmq messaging application using clr stored procedure integrated in sql server 2008.

Followed the below steps

  1. By defualt System.Messaging reference wont be available in sql server,so created this assembly

    ALTER DATABASE [AdventureWorksLT2008] SET TRUSTWORTHY on create ASSEMBLY Messaging AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO

2.created sql server application with a stored procedure

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Messaging;
using System.Security;

    [assembly: AllowPartiallyTrustedCallers]
    namespace CLRProcedure
    {

        public class StoredProcedure
        {
            /// <summary>
            /// Sends message to queue
            /// </summary>
            /// <param name="queue">Queue path</param>
            /// <param name="msg">Message</param>
            [SqlProcedure]

            public static void Proc_Send_Queue(SqlString queue, SqlString msg)
            {
                using (MessageQueue msgQueue = new MessageQueue(queue.ToString(), QueueAccessMode.Send))
                {
                    msgQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
                    msgQueue.Send(msg.Value);
                }
            }
         }     
     }
  1. registered that assembly in sql server create assembly MSMQApp from 'E:\CCA Parctise\SqlMSMQ.dll'

SqlMSMQ.dll is the sql server applicaiton dll.

  1. create stored procedure create PROCEDURE [dbo].[Proc_Send_Queue] (@queue nvarchar, @msg nvarchar) WITH EXECUTE AS CALLER AS EXTERNAL NAME [MSMQApp].[CLRProcedure.StoredProcedure].[Proc_Send_Queue]

  2. while executing stored procedure USE [AdventureWorksLT2008] GO DECLARE @return_value int EXEC @return_value = [dbo].[Proc_Send_Queue] @queue = N'SampleQ', --msmq name @msg = N'Simpel queue message' -- message SELECT 'Return Value' = @return_value GO

    throwing below error

    Msg 6522, Level 16, State 1, Procedure Proc_Send_Queue, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "Proc_Send_Queue": System.Security.SecurityException: That assembly does not allow partially trusted callers. System.Security.SecurityException: at CLRProcedure.StoredProcedure.Proc_Send_Queue(SqlString queue, SqlString msg)

Appreciating your help to solve this.

Thanks in advance

Upvotes: 3

Views: 6587

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294467

Allowing Partially Trusted Callers states:

We recommend that all assemblies registered in SQL Server, except those assemblies added to the global assembly cache, be marked with the AllowPartiallyTrustedCallers attribute so that assemblies loaded by SQL Server can access each other.

You did exactly what the recommendation says, and you get the error. So what's going on? See Strong named assemblies and AllowPartiallyTrustedCallers:

When assembly B calls strong-named assembly A,

Either A must have AllowPartiallyTrustedCallers attribute Or B must have unsafe (FullTrust) permission set.

In your case 'B' is SqlMSMQ.dll and the strong-named 'A' is System.Messaging.dll. Since you cannot modify 'A' to have the APTC attribute, the only solution is to modify 'B' to be full trusted (ie. UNSAFE):

 create assembly MSMQApp from 'E:\CCA Parctise\SqlMSMQ.dll'
   with permission_set = unsafe;

What is the recipient of your messages? If is another application backed by SQL Server then you have a much better alternative in Service Broker.

Upvotes: 2

Related Questions