Hi, I have a problem.
I am performing a CLR triggers in which it is triggered when an insert operation is performed on a certain table. The operation that I must perform from the triggers would be to execute an insert on a table that is on another server. The connection they gave me to get to this table from the other server is an ODBC connection.
What happens is that when I go to open the connection within the Trigger CLR it gives me an error.
The connection is correctly configured, because I have made a windowsform application and it connects correctly.
The trigger is declared with EXTERNAL ACCESS permissions.
public static void SqlTrigger1 ()
{
SqlContext.Pipe.Send("comienzo");
OdbcConnection conO = new OdbcConnection("DSN=xxx;UID=xxx;PWD=xxx");
SqlContext.Pipe.Send("DECLARO CONEXION");
conO.Open();
SqlContext.Pipe.Send("ABRO CONEXION ");
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "INSERT INTO .........";
SqlContext.Pipe.Send("DEFINO TEXTO COMANDO ");
cmd.Connection = conO;
SqlContext.Pipe.Send("INDICO CONEXION AL COMANDO");
cmd.ExecuteNonQuery();
SqlContext.Pipe.Send("EJECUTO COMANDO ");
conO.Close();
SqlContext.Pipe.Send("PROCESO FINALIZADO ");
}
I do not understand why the connection is not working from the trigger and if it works from the windowsform application if they are using the same code.
Error that it returns, when executing withO.Open ();
Msg 6522, Level 16, State 1, Procedure SqlTrigger1, Line 1 [Batch Start Line 11]
Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario 'SqlTrigger1':
System.Security.SecurityException: Error de solicitud de permiso de tipo 'System.Data.Odbc.OdbcPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
System.Security.SecurityException:
en System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
en System.Security.PermissionSet.Demand()
en System.Data.Common.DbConnectionOptions.DemandPermission()
en System.Data.Odbc.OdbcConnectionFactory.PermissionDemand(DbConnection outerConnection)
en System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
en System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
en System.Data.Odbc.OdbcConnection.Open()
en Triggers.SqlTrigger1()