Just came back from TechEd, where I had a bit of time to think about training vs speaking. When I'm teaching, I'm not just speaking facts and doing neat demos (ta da!) at folks. I look at their eyes to make sure they "get it" and aren't satisfied until they do. During the presentation, I'd noticed that the lights were turned down low, and I couldn't do that. This manifested itself in my saying "Does everybody get that?". Maybe once too often… In a classroom, you also have more time to talk to individuals about their specific issues. I've heard this called "bring your own problem".
Many of my blog posts come from thinking about and solving (even after the class) problems that are brought up during classes. So I thought it would be worthwhile to present a series of them (without identifying info, naturally) that I haven't posted about yet.
Here's one on SQLCLR assemblies. I'm working on a payroll system and want to expose a set of calculations in a SQLCLR assembly. But, if all assemblies are loaded into the same .NET appdomain, I won't be able to keep a programmer that "knows better" from invoking my methods through reflection, thereby circumventing security. Having a different appdomain (you get one per assembly owner per database) means I won't be able to call to the other appdomain (for users that need to call my methods from other appdomains) without using a remoting technology. This seems like a bit of overhead, to use web services to call one SQLCLR appdomain from another. Didn't there used to be an "EXECUTE" permission on assemblies? (BTW, that permission did exist in 2005 beta 1, but didn't do anything, it was removed pre-RTM).
Well, how about using SQL? When you expose an algorithm (say, the pension calculation algorithm) as a T-SQL function/procedure, rather than just having your function available in an assembly to call via other SQLCLR .NET code, security is enforced through SQL permissions, the same as with other SQL objects.
This brings up another misconception about SQLCLR (.NET assemblies in SQL Server) code. Programmers sometimes think there is a special pipeline when calling from a .NET client to a SQLCLR object (e.g. a stored procedure) and that the rules (like type usage) of .NET apply. The only way to call from a .NET client to a SQLCLR stored procedure is by using SQL; that is, ADO.NET SqlCommand objects. And of course, you must define "entry points" into your .NET code using SQL DDL, such as CREATE PROCEDURE foo… EXTERNAL NAME [AssemblyName].[ClassName].[MethodName]. There is no .NET client-to-.NET sproc pipeline.
Get that?
@bobbeauch