none
Attempted to perform an operation that was forbidden by the CLR host.

    Pregunta

  • Hi all,

    I am using SQL server 2005 - 9.00.1399.06 (Intel X86)

    I have written one generic CLR trigger for Audit. I have deployed the assembly through VS2008 environment. Then I created the trigger on 2 tables (Table1, Table2).  I wrote 2 update statements for these two tables as follows -

    Update Table1 Set ..  
     
    Update Table2 Set .. 

    It works fine. Then I tried the following -
    Begin Transaction 
     
    Update Table1 Set ..  
     
    Update Table2 Set ..  
     
    Commit Transaction 

    It also works fine. But when I reverse the order of update statements as follows -
    Begin Transaction    
        
    Update Table2 Set ..     
        
    Update Table1 Set ..     
        
    Commit Transaction    
     

    Then I am getting the error -

    (1 row(s) affected)
    Msg 6522, Level 16, State 1, Procedure Trigger1, Line 1
    A .NET Framework error occurred during execution of user defined routine or aggregate 'Trigger1':
    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: Synchronization

    System.Security.HostProtectionException:
       at System.Reflection.MethodBase.PerformSecurityCheck(Object obj, RuntimeMethodHandle method, IntPtr parent, UInt32 invocationFlags)
       at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Diagnostics.TraceUtils.GetRuntimeObject(String className, Type baseType, String initializeData)
       at System.Diagnostics.TypedElement.BaseGetRuntimeObject()
       at System.Diagnostics.ListenerElement.GetRuntimeObject()
       at System.Diagnostics.ListenerElementsCollection.GetRuntimeObject()
       at System.Diagnostics.TraceInternal.get_Listeners()
       at System.Diagnostics.TraceInternal.WriteLine(String message)
       at System.Diagnostics.Debug.Print(String message)
       at Trigger1.Triggers.AuditTrigger()
    .
    The statement has been terminated.

    Regards,
    Prabi


    • Cambiado Aaron Alton martes, 30 de diciembre de 2008 6:29 CLR Question (Spostato da Transact-SQL a .NET Framework inside SQL Server)
    martes, 30 de diciembre de 2008 5:49

Todas las respuestas

  •  

    If you give the code of Trigger1 will be great.

    It is nothing related to SQL. It is related to the .NET security  - Host Protection.


    I am Back..!
    martes, 30 de diciembre de 2008 6:01
  • Hi Manivannan,

     Here is my code for Audit Trigger -
    Imports System  
    Imports System.Data  
    Imports System.Data.SqlClient  
    Imports System.Data.SqlTypes  
    Imports Microsoft.SqlServer.Server  
     
     
    Partial Public Class Triggers  
        ' Enter existing table or view for the target and uncomment the attribute line  
        '<Microsoft.SqlServer.Server.SqlTrigger(Name:="AuditTrigger", Target:="Country", Event:="FOR UPDATE")> _  
        Public Shared Sub AuditTrigger()  
            Try 
     
                Dim tcontext As SqlTriggerContext = SqlContext.TriggerContext  
                'Trigger Context   
                Dim TName As String 
                'Where we store the Altered Table's Name   
                Dim User As String 
                'Where we will store the Database Username   
                Dim iRow As DataRow  
                'DataRow to hold the inserted values   
                Dim dRow As DataRow  
                'DataRow to how the deleted/overwritten values   
                Dim aRow As DataRow  
                'Audit DataRow to build our Audit entry with   
                Dim rwNum As Integer 
                'Row number from inserted table  
                Dim PKString As String 
                'Will temporarily store the Primary Key Column Names and Values here   
     
                Using conn As New SqlConnection("context connection=true")  
                    'Our Connection   
                    conn.Open()  
                    'Open the Connection   
     
                    'Build the AuditAdapter and Mathcing Table   
                    Dim AuditAdapter As New SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn)  
                    Dim AuditTable As New DataTable()  
                    AuditAdapter.FillSchema(AuditTable, SchemaType.Source)  
                    Dim AuditCommandBuilder As New SqlCommandBuilder(AuditAdapter)  
                    'Populates the Insert command for us   
     
                    'Get the inserted values   
                    Dim Loader As New SqlDataAdapter("SELECT * from INSERTED", conn)  
                    Dim inserted As New DataTable()  
                    Loader.Fill(inserted)  
     
                    'Get the deleted and/or overwritten values   
                    Loader.SelectCommand.CommandText = "SELECT * from DELETED" 
                    Dim deleted As New DataTable()  
                    Loader.Fill(deleted)  
     
                    'Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)   
                    Dim cmd As New SqlCommand("SELECT object_name(resource_associated_entity_id) FROM " & _  
                                              "sys.dm_tran_locks WHERE request_session_id = @@spid and " & _  
                                              "resource_type = 'OBJECT'", conn)  
                    TName = cmd.ExecuteScalar().ToString()  
                    'Retrieve the UserName of the current Database User   
                    'Here we need to find workaround to obtain application user  
                    Dim curUserCommand As New SqlCommand("SELECT system_user", conn)  
                    User = curUserCommand.ExecuteScalar().ToString()  
     
                    'Retriewing primary key columns for the current table  
                    Dim PKTableAdapter As New SqlDataAdapter("SELECT c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ," & _  
                                                             "INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '" & TName & "' " & _  
                                                             "and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME " & _  
                                                             "and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn)  
                    Dim PKTable As New DataTable()  
                    PKTableAdapter.Fill(PKTable)  
     
                    Select Case tcontext.TriggerAction  
                        'Select Case on the Action occuring on the Table   
                        Case TriggerAction.Update  
                            'For each row in the inserted table  
                            For rwNum = 0 To inserted.Rows.Count - 1  
                                'Get the inserted values in row form   
                                iRow = inserted.Rows(rwNum)  
                                'Get the overwritten values in row form   
                                dRow = deleted.Rows(rwNum)  
     
                                'the the Primary Keys and There values as a string   
                                If PKString Is Nothing Then PKString = PKStringBuilder(PKTable, iRow)  
     
                                For Each column As DataColumn In inserted.Columns  
                                    'Walk through all possible Table Columns   
                                    If Not iRow(column.Ordinal).Equals(dRow(column.Ordinal)) Then 
                                        'If value changed   
                                        'Build an Audit Entry   
                                        aRow = AuditTable.NewRow()  
                                        aRow("ActionType") = "UPDATE" 
                                        aRow("TableName") = TName  
                                        aRow("PK") = PKString  
                                        aRow("FieldName") = column.ColumnName  
                                        aRow("OldValue") = dRow(column.Ordinal).ToString()  
                                        aRow("NewValue") = iRow(column.Ordinal).ToString()  
                                        aRow("ChangeDateTime") = DateTime.Now.ToString()  
                                        aRow("ChangedBy") = User  
                                        'Insert the entry   
                                        AuditTable.Rows.InsertAt(aRow, rwNum)  
                                    End If 
                                Next 
                            Next 
                            Exit Select 
                        Case TriggerAction.Insert  
                            For rwNum = 0 To inserted.Rows.Count - 1  
                                iRow = inserted.Rows(rwNum)  
                                If PKString Is Nothing Then PKString = PKStringBuilder(PKTable, iRow)  
                                For Each column As DataColumn In inserted.Columns  
                                    'Build an Audit Entry   
                                    aRow = AuditTable.NewRow()  
                                    aRow("ActionType") = "INSERT" 
                                    aRow("TableName") = TName  
                                    aRow("PK") = PKString  
                                    aRow("FieldName") = column.ColumnName  
                                    aRow("OldValue") = Nothing 
                                    aRow("NewValue") = iRow(column.Ordinal).ToString()  
                                    aRow("ChangeDateTime") = DateTime.Now.ToString()  
                                    aRow("ChangedBy") = User  
                                    'Insert the Entry   
                                    AuditTable.Rows.InsertAt(aRow, rwNum)  
                                Next 
                            Next 
                            Exit Select 
                        Case TriggerAction.Delete  
                            For rwNum = 0 To deleted.Rows.Count - 1  
                                dRow = deleted.Rows(rwNum)  
                                If PKString Is Nothing Then PKString = PKStringBuilder(PKTable, dRow)  
                                For Each column As DataColumn In inserted.Columns  
                                    'Build and Audit Entry   
                                    aRow = AuditTable.NewRow()  
                                    aRow("ActionType") = "DELETE" 
                                    aRow("TableName") = TName  
                                    aRow("PK") = PKString  
                                    aRow("FieldName") = column.ColumnName  
                                    aRow("OldValue") = dRow(column.Ordinal).ToString()  
                                    aRow("NewValue") = Nothing 
                                    aRow("ChangeDateTime") = DateTime.Now.ToString()  
                                    aRow("ChangedBy") = User  
                                    'Insert the Entry   
                                    AuditTable.Rows.InsertAt(aRow, rwNum)  
                                Next 
                            Next 
                            Exit Select 
                        Case Else 
                            'Do Nothing   
                            Exit Select 
                    End Select 
                    'Write all Audit Entries back to AuditTable   
                    AuditAdapter.Update(AuditTable)  
                    'Close the Connection   
                    conn.Close()  
                End Using  
                SqlContext.Pipe.Send("Audit Trigger FIRED")  
            Catch ex As Exception  
                Debug.Print(ex.Message)  
            End Try 
        End Sub 
     
        'Helper function that takes a Table of the Primary Key Column Names and the modified rows Values   
        'and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"   
        Public Shared Function PKStringBuilder(ByVal primaryKeysTable As DataTable, ByVal valuesDataRow As DataRow) As String 
            Dim temp As String = [String].Empty  
            For Each kColumn As DataRow In primaryKeysTable.Rows  
                'for all Primary Keys of the Table that is being changed   
                temp = [String].Concat(temp, [String].Concat("<", kColumn(0).ToString(), "=", valuesDataRow(kColumn(0).ToString)))  
            Next 
            temp &= ">" 
            Return temp  
        End Function 
     
    End Class 
     

    Thanks in advance.

    Regards,
    Prabi
    martes, 30 de diciembre de 2008 6:12
  • Any updates on this?
    jueves, 01 de enero de 2009 4:43
  • No solution to this problem?

    has anybody tried this?

    lunes, 05 de enero de 2009 4:52
  • Hi guys,

    Please tell me if there is any solution. Its really frustrating now.

    l am expecting reply.

    regards,
    Prabi
    • Propuesto como respuesta ajweav jueves, 18 de febrero de 2010 0:27
    lunes, 19 de enero de 2009 4:30
  • You are using an object (DataTable) that is inherently synchronized.  There are resources configured at the CLR runtime level that are explicitly protected for a reason.  MS obviously doesn't want you touching the threads of the CLR inside the DB (the host), so they explicitly protected it.  Use a type that doesn't rely on thread safety/synchronization.
    • Propuesto como respuesta ajweav jueves, 18 de febrero de 2010 0:27
    jueves, 18 de febrero de 2010 0:27
  • When in a CLR, you cannot call WriteLine or Print functions.  If you look in your stack, the second-highest level shows that the exception is being called from within Debug.Print().  If you look in your code:

      Catch ex As Exception  
                Debug.Print(ex.Message)  
            End Try 
        End Sub 


    Your catch statement contains this very function call.  Remove this, and it should solve your problem.

    And as a quick comment, I have used DataTable objects in CLRs before without incident.  I doubt that they are the issue.
    • Propuesto como respuesta richmintz viernes, 27 de abril de 2012 21:10
    miércoles, 17 de marzo de 2010 18:08
  • Great answer,

    Thanks

    viernes, 27 de abril de 2012 21:11
  • Would having a Throw in the catch do the same thing?
    miércoles, 13 de noviembre de 2013 15:00