I had some time to research SQL Server 2005 this weekend and ran across the HostProtectionAttribute (HPA).
The ability to write stored procedures and such with your favorite .NET language opens up a dangerous number of APIs, like Process.Start, which can kill a server’s throughput and reliability. The HostProtectionAttribute lets SQL Server look out for dangerous code.
Say we write the following stored procedure:
<SqlProcedure()> _
Public Shared Sub PureEvil2()
SyncLock (GetType(StoredProcedures))
' do work
End SyncLock
End Sub
The above code will compile and deploy into the SAFE bucket of SQL Server without complaint. Execute the proc however, and the host protection kicks in:
Msg 6522, Level 16, State 1, Procedure PureEvil2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'PureEvil2': System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
SQL Server doesn’t like our code trying to block a thread. Underneath the covers, SyncLock (VB) and lock (C#) use System.Threading.Monitor. Using Reflector on a v2.0 framework install we can see the Monitor class decorated with a custom HostProtectionAttribute.
public sealed class Monitor
{
// ...
}
SQL Server is looking for the attribute at execution time. Since the HPA can target a struct, class, method, constructor, delegate, or assembly, there must be a bit of overhead involved. I wonder why the verification can’t take place during the CREATE ASSEMBLY deployment?
It will also be interesting to see if the ASP.NET team can take advantage of HPA in the future to keep killer code out of ASP.NET.
OdeToCode by K. Scott Allen
Subscribe
Twitter
Search
About
Learn C#