I haven't really been paying much attention to SQLEXPRESS and the APP_DATA directory in the past but over the last couple of days of finalizing my slides for the ASP.NET Connections conference I realized that it's one heck of a lot easier to ship a database in APP_DATA then to have scripts or even something like Red Gate's Sql Packager generated database. Sql Packager BTW, is an excellent tool if you need to shuttle around databases frequently between machines as it packages databases into an EXE that you can move and simply install on another machine. It's much faster than anything native including attaching/detaching.

Anyway, using APP_DATA and shipping a detached database is also very easy and it works great if you have SQL Express installed and you're running with the built-in ASP.NET 2.0 Web server.

However, I've not been able to get APP_DATA to work with IIS. I get security errors which basically point at the ASPNET account not having rights to the database. I'm not sure exactly how SQLExpress deals with permissions in this scenario, but it's not recognizing the ASPNET account as a valid user even though on this demo machine it is set up as an Admin account (yeah I know bad idea, but for demos this is lets me use integrated security so I don't have to expose passwords to the world). I also made sure I added ASPNET to APP_DATA ACL list and gave it full permissions.

I figured that SQLEXPRESS must be triggering off the impersonated account in some way, but in that case ASPNET should work the same as my login account both of which are Admin at this point. And it's not…

The workaround I suppose is to attach the database and then administer the security policy properly through SQL Security, but given that the expectation is SQLEXPRESS this is not so transparent since SQL Express doesn't come with Administration tools (although you can download a beta version of the Express Management Studio).

Is there an easier way?