Sunday, July 6, 2008

Windows Server 2008, IIS, ASP, ADO and Access 2007

Ah, the joys of walking into a mine field alone. For those of you who don't know me, I've spent years writing web apps using ASP and a bit less (but way more often) on PHP+MySQL. Most of my ASP work was done using MS-SQL or Oracle, but lately these lines have blurred somewhat. I just finished up an application for a customer that uses ASP with MySQL. Talk about weird. It works fine though.

For those of you that do know me, how about a phone call once in a while? Eh? :(

So I'm building a web app to interface with Active Directory, which is a scaled down version of one I wrote for a previous employer. Instead of managing users, groups, computers, contacts and tracking all the crap that goes with that (for what else but SOX), this one is for home use. And while the big corporate-y version allowed me to control AD operations securely from my Blackberry, and provided our Deloitte and Douchebag auditors with more than enough reporting to make them grab a kleenex, I don't need that level of power at home.

I wanted to be able to empower my wife with the ability to inflict the ultimate torture on kids of cutting off their computer access. She's not up to dealing with ADUC or cryptic CMD shell commands. Scripts would be nice, but I don't want to dole out permissions like that (not that I don't trust my wife, but she might hit a malicious web site during her crazy surfing and it might leverage her elevated privs to do more damage than I would like - sorry honey). I also considered desktop shortcuts to scripts (one for each child) to stuff a record into the database or write a file to be picked up by another process, either by event sinks or by scheduling, but ah, yeck, too messy.

No, this little project will simply provide all the kids' user accounts with their ENABLED/DISABLED status and the option to toggle those individually. The site runs on ASP using IIS7 (Windows Server 2008) and only allows for toggling user accounts between the two given states. The site runs under Windows Authentication to allow my wife and I access. The toggle requests are queued in a database which is then queried on a schedule by another job that runs with elevated privs to execute the toggle requests and mark the database records as completed and timestamped. Very simple stuff - Nothing Earth-shattering here.

The challenge came with choosing a database. I thought about SQL 2005, but my AD DC server (my lone wolf) has three legs and piling another sack of goods on might break it's back. So I decided to go lightweight. I looked for SQL Express 2005. But when I get to the Microsoft site it goes right to SQL Express 2008 CTP/RC or whatever. Now is where the fun begins!

One of the pre-reqs for SQLX08 is to have .NET Framework 2.0 SP2 installed. But there is no .NET Framework 2.0 SP2. There is only .NET 2.0 SP1. The docs took me on a wild goose chase to discover a small post that .NET Framework 3.5 is supposed to rectify that, and allow a .NET 2.0 SP2 pre-req audit to pass. Not so! It does not! I have 3.5 installed and it still chokes during the SQLX install saying it needs .NET 2.0 Sp2 installed and stops there.

Frustrated, I did a Google search for SQL Express 2005 and found the download link on the Microsoft web site (funny how it still seems to be easier to find things on Microsoft's web site using Google). I installed SQLX 2005 and setup the table and permissions. I ran into so many stupid problems trying to get ASP to not only just read it (aka "SELECT"), but try to modify (aka "UPDATE") table rows. Gnashing my teeth again, I opted for MS-Access 2007.

More fun.

Google is the best. How can we live without it? I created an Access 2007 database with a table and all the required fields and datatypes, constraints, etc. Then I copied the .accdb file up to the ASP folder on my web server/domain controller/file server/print server/music and media server/foot-rest server, and linked it up to my ASP page using ADO and standard connectionless stuff. It failed with an expected 500 error. This page describes why and what to do about it.

Google led me to find the required Access Runtime Engine for MS-Access 2007. Even though there's no mention of it being required for WS08 and it's not on the system requirements list, it worked! Best of all, I didn't even have to reboot my server after installing it. And when it finished, I simply hit refresh on my ASP web page and it cranked up fine.

Now, my wife can go to our internal web site and lock the kiddies out and continue on with her chores without having to call me at work and whine about having her hands tied. I may scale this up if I have time and a need, but for now it's fine.
Post a Comment