Allow broadcast from CLR Stored Procedure without using permission set 'Unsafe'

Hello,

I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:

using System;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Net.Sockets;

public partial class UserDefinedFunctions

{

[SqlProcedure]

public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)

{

System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();

byte[] datagram = message.GetUnicodeBytes();

client.Send(datagram, datagram.Length, (string)address, (int)port);

}

};

I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:

Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.Net.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)

at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)

at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)

at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)

I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.

Anyone who has something like this ?

No, you can not alter any of the existing permission sets. What you can do is, by using CAS, further restricting what a permission set can do by setting (for that assembly) specific CAS policies. However, you still have to create the assembly with whatever permission set, that is required.

Niels
|||You can't modify the built-in SQL CLR permission sets, but you can restrict your assembly's permission grant by using assembly-level permission attributes. However, your assembly would still need to be deployed at the UNSAFE level in order to be granted the SecurityPermission\UnmanagedCode permission that it needs.