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

Question

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

Answer 1

 

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

Answer 2

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

Answer 3

Any updates on this?

Answer 4

No solution to this problem?

has anybody tried this?

Answer 5

Hi guys,

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

l am expecting reply.

regards,
Prabi

Answer 6

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.

Answer 7

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.

Answer 8

Great answer,

Thanks