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:

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.

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.

Sometimes I wonder what I’d be doing if it weren’t for software development.

I’d like to think of myself as being a craftsman in some other profession.

Then the time comes when I need to attach a small block of wood to a wall using two screws. The tricky part is to accomplish the task without destroying the wood, the wall, the screws, or any of the tools.

So much for craftsmanship with my hands. Perhaps I would be a goat herder.

This weekend I noticed patterns & practices superhero Ron Jacobs has made the jump into podcasting. His first two shows include an interview with Billy Hollis and a discussion about configuration contexts in Enterprise Library.

A bit later I was looking for local events in the area and discovered Steve Vai is bringing his superhuman guitar playing skills to town. Billy Sheehan will play bass on the tour, and believe me - having this much talent on stage at the same time should be illegal.

You might be wondering why I bring up these seemingly unrelated events.

Well, one of the photos on the right hand side is of Ron Jacobs, and the other photo is of Steve Vai. Both of these photos appear prominently on their respective web sites. One of these guys is known as PAG Daddy, and the other started his career with Frank Zappa.

Eerily similar - don’t you think?

Almost every article ever written about database transactions includes the example of an ATM machine dispensing money. The articles will describe how the bank uses a transaction to prevent people from overdrawing the account during simultaneous access, or how the bank can rollback a transaction if an error occurs at the ATM.

They are all wrong.

About 7 years ago I went to a local ATM, punched in my PIN, and asked the ATM to withdraw $80 from my account. The machine thought about the request for a bit, and then started to make the happy fluttering noise ATM machines will do just before delivering cash. The happy fluttering noise was followed by a fateful POP sound in the distance, and then a total blackout. Power failure.

When power came back seconds later I still had no money, and the ATM had an OS/2 boot screen.

Cool! I remember thinking. The bank’s database just rolled back my transaction! I drove off in search of a working machine. Of course I was a bit younger then and had not learned the secret mission statement of every major financial institution: “Customer inconvenience is our first priority”. When my bank statement arrived the next month I was genuinely surprised to see the bank had committed the $80 transaction against my account.

One might think ATM machines would be equipped with some sort of UPS to keep them running in the event of a power failure during an open transaction, but one would be forgetting the second mission statement of all financial institutions: “Mistakes are acceptable, as long as we screw the customer and not ourselves”.

For the curious, here is how the story ends.

A month later I went to the bank, approached a teller, and told her if she gave me $80 in unmarked bills nobody would get hurt.

Well, not really, but it felt that way.

I showed the teller my statement and told her my story. She asked me to stand by a door to the left and walked through a back door behind the counter.

While waiting in the lobby I thought about the possibility of the teller returning to tell me the bank records were never wrong. Not only would I not get my $80 back, but they’d deduct a $10 service fee for seeing me in person. I’m always optimistic like that when dealing with banks. Thieves.

I was surprised when a woman burst out from the door beside the counter, ran over and practically hugged me. The bank had noticed an $80 discrepancy in the ATM records and this lady’s sole purpose was to find out what happened. I made her day and got my money back.

The End.

One of the thorny areas in writing a distributed application is keeping the logical thread of execution authenticated and authorized as calls hop from server to server. If you want to flow the original client’s identity across these servers you’ll quickly run into the “single network hop” restriction of NTLM (sometimes called the “double hop” issue). A client’s identity can only make a single hop. The first hop happens from the web browser to the web server. The web server can impersonate the client when accessing local resources, but it make a second hop to a third machine. Larry Osterman has details on this behavior.

The single hop issue turns up a lot these days as more products (Reporting Services, SharePoint) rely on Windows authentication, but we rarely see these applications on the same server as our ASP.NET applications.

One solution to the problem is the trusted subsystem model. However, the trusted subsystem model does not flow the original client’s identity automatically, and it becomes your application’s responsibility to perform authorization checks. Tricky.

Another solution is to use Kerberos delegation. If you want to enlighten yourself on the subject, I’d recommend the following roadmap.

Start with David Chappell’s “Exploring Kerberos, the Protocol for Distributed Security in Windows 2000”, and chase this article with Keith Brown’s “Exploring S4U Kerberos Extensions in Windows Server 2003”.

The next step is to watch delegation in action. A two part webcast walks through every detail of setting up delegation in a typical ASP.NET application environment: “Getting Delegation to Work with IIS and ASP.NET: The Ins and Outs of Protocol Transition” (Part 1 and Part 2).

At this point it’s time to take the IT department out to lunch, or perhaps send fruit baskets to their house. You’ll need their sign-off and support to pull it all off.

Two documents that can help during the implementation phase are “HOW TO: Configure an ASP.NET Application for a Delegation Scenario” and “Troubleshooting Kerberos Delegation”.

Just imagine how popular you’ll be at the next neighborhood social event if you can hang out at the punch bowl and explain the nuances of S4U2Self and S4U2Proxy.

Karl Seguin’s site (OpenMyMind.net) has some good articles for ASP.NET developers. The two articles on creating multilingual websites (Part I, Part II) jump out at me, and the “Mastering Page-UserControl Communication” is a must read if you are starting to write user controls which interact with the parent page. (Hint: using Request.Form or FindControl to pull values from a user control is path filled with peril).  

Maxim Karpov has a nice post about his VSLive! experience. Max is a bright guy and a blast to hang out with.

I’m still tying up lose ends and requests for information from my presentation on SQL Server Reporting Services Integration.

If you have any performance or exporting problems, make sure you have Service Pack 1 in place. To determine the version of Reporting Services, go to the base URL for the report server (typically http://machinename/reportserver/). At the bottom of the browser page will be the version number - 8.00.878.00 is SP1.

To see what is the future holds with Service Pack 2, see Geoff Snowman’s webcast: “New Features in Reporting Services Service Pack 2”. We are still waiting for the blog to update, Geoff.

In ASP.NET we are used to sorting data in a DataGrid by clicking on a column header. This same functionality is difficult to achieve in SSRS, but see MSFTie Bruce Johnson’s newsgroup post to see one approach (copy out the inline RDL and paste into a new report). I believe sorting will be much easier to implement in the 2005 version of Reporting Services.

If you have the Enterprise edition of Reporting Services and want to look at custom authentication schemes, try the article “Using Forms Authentication in Reporting Services” for some background information and a code to build on. Also, there is a data processing extension to read XML files on GotDotNet.

Finally, there are some articles here on OdeToCode to help with learning the web service API:

• Introduction To Role-Based Security In SQL Server Reporting Services
• Authentication, Authorization, and SQL Server Reporting Services
• Using GetReportParameters
• Using CreateSubscription
• Tree Navigation Sample

For anyone looking on information on setting up Kerberos delegation with ASP.NET, I have a post in the works for that topic…