Lately I’ve been extending and improving the ASP.NET Portal Starter Kit. One update I really wanted to make was to move the configuration storage from the XML file to our back-end SQL Server database. The only thing that was holding me back was the inability to cache the configuration data. I didn’t want the round-trip of retrieving infrequently-changing config data every time a page was loaded, but until ASP.NET 2.0 and SQL Server 2005 there’s no built-in mechanism to set up a database cache dependency.

After a little Googling, I ventured across this article describing a potential solution. It involves using a built-in stored procedure to write out to a file based on a trigger. At first it seemed great! Network shares were allowed, which supports our server configurations, and it was easy to implement. So I spent a short while “upgrading” the guts of the configuration storage to use this new architecture. (Of course, I simply extended the existing architecture so that now I can either call PersistToSql() or WriteXml() and it will handle either mechanism.) That’s when I ran into a few “snafu’s” that make me still wonder if it’s worth it:

1. Executing the sp_makewebtask sproc requires the caller to be a member of the sysadmin role. Excuse me, say again? Suddenly any defense-in-depth security goes out the window. And it’s not like I’m just removing the wall around the innards of my “castle”, it’s like I’m digging a tunnel through all interior walls and leaving just an inch of wall between me and the outside world! If I (or any portal module creator for that matter) slip up and allow a SQL injection, well, you can kiss that baby good-bye! This is mitigated (slightly) by the simple knowledge that it’s possible, and being extra-extra-extra-thorough when it comes to testing all database interactions.

2. Writing to a network share requires either SQL Server or my ASP.NET application to be impersonating a user with network privileges. This isn’t such a big deal because it’s fairly common for an ASP.NET application to impersonate a network user. But if that wasn’t an option, I would have to have SQL Server impersonate a network user. That’s not unheard of (it’s required if you want to set up Active Directory as a linked server) but again, undesirable.

While these two problems are potentially bad, there are workarounds for each. In the first case, creating a custom extended stored procedure should be able to satisfy the requirement as that xp_sproc can be set up not to require sysadmin role-level access. And in the second case, it is definitely acceptable to set up ASP.NET to impersonate a domain account–a nice side effect is not having to store database credentials anywhere, integrated security is now available.

Despite my own objections, I’d have to say I’m pleased with the solution, and I’m definitely looking forward to ASP.NET 2.0 and SQL Server 2005!

1. aaron Says:
   August 22nd, 2007 at 11:30 pm

   Don’t worry, I’m still that same friend!

   My other blog went away due to lack of usage (by me and others) and generally a lack of anything interesting to say…
   This one doesn’t matter how much it gets used, since its primary purpose is to be my “tech knowledge repository”.

   Thanks for visiting and commenting!

2. Joshus Says:
   August 22nd, 2007 at 11:30 pm

   Aaron – I am out of my league here. I liked you better when you were just my friend who I could call and ask simple HTML questions.

   But seriously, where’s that “random thoughts according to Aaron Lerch re: culture and religon” blog you used to run?