Thanks to fellow DevelopMentor "Brain" Kevin Jones (RSS) for pointing this one out.
These problems left me stranded for days, then today, on another machine, I hit a different set of problems I kept getting the following "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." and if I set "User Instance" to false the following "An attempt to attach an auto-named database for file C:\[app_path]\ASPNetDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
..
If you don't want to follow the link, the short answer is that SQLExpress creates a directory per user in "c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" that it uses to store information. Deleting this directory has fixed both of my problems.
Sorry, I couldn't resist. And if you're a Perl Monger like me, you shouldn't either. SQL Server MVP Erland Sommarskog has just spun out MSSQL::OlleDB, a mod for working with MSSQL Server using OLE-DB (thus the name). There's a ton of documentation provided too, with examples.
Enjoy!