SQL 2005 CLR trigger and TCPClient [modified]
-
Hi, I´m trying ot get a CLR-Trigger running. I Created the trigger, its insert correctly in the database, but when the SQLTrigger tries to open a tcpclient Connection, I get a Security Exception: "{System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port) at Triggers.Trigger1() The action that failed was: Demand The type of the first permission that failed was: System.Net.DnsPermission" How / Where can I give the Trigger the needed Permissions? -- modified at 10:58 Wednesday 28th February, 2007
-
Hi, I´m trying ot get a CLR-Trigger running. I Created the trigger, its insert correctly in the database, but when the SQLTrigger tries to open a tcpclient Connection, I get a Security Exception: "{System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port) at Triggers.Trigger1() The action that failed was: Demand The type of the first permission that failed was: System.Net.DnsPermission" How / Where can I give the Trigger the needed Permissions? -- modified at 10:58 Wednesday 28th February, 2007
The permissions referred to are Code Access Security permissions. If you created your assembly with the default settings in Visual Studio then the assembly is set to SAFE. In order to access external resources you need to increase the assembly to EXTERNAL_ACCESS. If you are using Visual Studio this is done using the project properties. Otherwise, if you are scripting the creation of your assembly use WITH EXTERNAL_ACCESS instead of WITH SAFE (or nothing - default)
-
The permissions referred to are Code Access Security permissions. If you created your assembly with the default settings in Visual Studio then the assembly is set to SAFE. In order to access external resources you need to increase the assembly to EXTERNAL_ACCESS. If you are using Visual Studio this is done using the project properties. Otherwise, if you are scripting the creation of your assembly use WITH EXTERNAL_ACCESS instead of WITH SAFE (or nothing - default)
I´m using standard .Net assembly... the Exception occures when the trigger tries to execute the line
TcpClient client = new TcpClient("127.0.0.1",13000);The TcpClient is Part of the System.dll assembly, and this assembly should be usaly registered correctly? -
I´m using standard .Net assembly... the Exception occures when the trigger tries to execute the line
TcpClient client = new TcpClient("127.0.0.1",13000);The TcpClient is Part of the System.dll assembly, and this assembly should be usaly registered correctly?The system.dll assembly isn't "registered" with Sql Server, it's part of the hosted CLR. Certain classes w/in the CLR have attributes called Host Protection attributes which indicate the type of functionality the class and it's methods expose. SQL Server restricts access to some these classes based upon how your assembly is created. In order for your assembly to access (it will compile fine, but it won't run) these classes you must create (register) your assembly using WITH EXTERNAL_ACCESS instead of WITH SAFE.