SQLCLR and Serialization

I was playing around with SQLCLR and found a scenario that confused me for a long time:

I created a small class library that contained two classes. One class instantiates the other class, which is marked as Serializable. A public method of the parent class serializes the child class using a BinaryFormatter, and returns the resulting byte array. Simple, right?

Well, I imported this class library as an assembly into my SQL Server, and was able to use the Safe permission set without a problem.

I then proceded to create a CLR Stored Procedure using Visual Studio.NET 2005 that would instantiate the parent class from my previous library, and invoke the method that would serialize the child class. I built the project, had VS deploy it to my SQL Server, and then I tested the stored proc in Management Studio.

It blew up with an error resembling the following (some names were changed to protect the innocent):

Msg 6522, Level 16, State 1, Procedure Z_Start, Line 0A .NET Framework error occurred during execution of user defined routine or aggregate 'Z_Start':

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(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 checkFrames, Int32 unrestrictedOverride)

at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission cap, StackCrawlMark& stackMark, PermissionType permType)

at System.Security.CodeAccessPermission.DemandInternal(PermissionType permissionType)

at System.Runtime.Serialization.Formatters.Binary.ObjectWriter..Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck)

at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStream, Object graph, Header[] headers, Boolean fCheck)

at Namespace.ParentObject.Serialize()

I played with everything that I could think of, changing the permission set of my first class library from SAFE to EXTERNAL_ACCESS to UNSAFE. Nothing worked. To me, it seemed like a bug (and I even filed a bug report).

Turns out to be a problem with this developer. Though I played around with the permission set of the one class library, I was totally forgetting that the stored procedure's assembly has its own permission set too. I was using VS to deploy the assembly, so I wasn't concerning myself, per se, with what VS was doing with it. Come to find out, that by default, VS marks SQLCLR projects as SAFE. In order to use Reflection (which the BinaryFormatter uses when serializing objects), you need at least EXTERNAL_ACCESS permissions on ALL assemblies in the call stack, not just one or the other.

A quick trip into the project's property page allowed me to change the stored procedure assembly to EXTERNAL_ACCESS, and then my test worked as expected. Credit to Erland Sommarskog (who reviewed my bug report and posted that EXTERNAL_ACCESS worked for him).

Now onto more playing around....