While setting up my development machine for the coming weeks, I needed to make a number of tough decisions. The first decision was whether to go with SQL Server 2005 or SQL Server 2008. I figured it was not a bad thing to go with the lastest version for new development. I remember there was an issue with Visual Studio 2008 integrating with SQL Server 2008, but that seemed to be fixed in Visual Studio 2008 SP1.

The next decision was whether to go with SQL Server Express or Standard. I chose the latter, simply because it's a different environment and if I'm going to test my app with a "real" server I might as well start to use it in development as well.

The final decision to make was whether to use the development web server included with VS2008 or go with IIS7. Again, I chose the latter for the aforementioned reasons: if I'm going to deploy to IIS7, I might as well debug it in IIS7 as well. That will tell me about the kind of problems I might run into before I go into "production".

Seems that a number of those problems already found me while trying to use the simplest ASP.NET MVC project possible: File>Create New. Compile. Run. Fail.

The problem was that it was impossible to register with the new website. ASP.NET MVC out of the box uses the ASP.NET MembershipProvider to offer signup/login functionality. Which is cool. But of course, it won't work out of the box when you're using the SQL Server 2008 Standard edition.

The first issue is that the Standard edition doesn't support attached databases. So creating a new database file (.mdf) won't work. That also means that no such database is ready when you're running for the first time. You'll have to make it yourself. That one is easy: ASP.NET comes with a nice tool that does this for you on any database of your choosing. I knew that! So I opened SQL Server Management Studio, created a new database (I called it "aspnetdb". Aptly. Very aptly.) and used the aspnet_regsql tool to create the required schema in there.

Then, I changed the connection string in Web.config to use this database. I kept the integrated security on (anyone still using "mixed mode security" should be dragged onto the street and shot). So then it worked. But only when using the development webserver. Because that uses my personal account to connect to SQL Server, and I happen to be database administrator (at least, that's what SQL Server tells me) so it can connect and do all of its stuff just fine.

When you use IIS 7, it uses the identity of the app pool of your web app to connect to SQL Server. That's also an easy guess. The problem is that in Windows 7, there's some weirdness about what user account is used exactly to that purpose. SQL Server will report a failed login for "IIS APPPOOL\DefaultAppPool". But I couldn't find such a user. While I was googling, I came across a number of sites advising me to switch it back to using Network Service for the DefaultAppPool, but I knew there had to be a better way. The weirdness ended when I took a leap of faith: I added a login named IIS APPPOOL\DefaultAppPool without checking the name. That worked.

After that, I made it dbo of the aspnetdb database (which is probably too much, but it'll do for now) and then everything went smoothly. This is exactly the sort of problems I was hoping to encounter before actually deploying. So my plan worked!