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

csql server

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

Best Answer

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.

Related Topic