ASPNETDB + Production deployment --> Practical Tips

Posted on 6/30/2006 @ 8:45 PM in #Vanilla .NET by | Feedback | 7018 views

One of the biggest curse (in my eyes) and blessing, in ASP.NET 2.0 is that little cute web server that runs in your system tray.

It's a blessing because it makes development and sending and receiving web projects, and source control rather easy. It's a curse because it screws up everytime there is anything to do with security or permissions, because that web server runs as YOU (usually admin), and IIS will run as IUSR_xxx.

One of the connected issues with that problem are the issues that you may run into, when using ASP.NET 2.0 membership services or the webparts framework. This post borders talksa bout ASPNETDB.MDF works, what issues you may run into in the real world, and how to get around them.

So you create the ASPNETDB.MDF in your webapp by running aspnet_regsql.exe. This will create an MDF and LDF, which are auto-attached when you run your website by hitting F5. What is cool however is that they are auto-attached in SQLExpress.

If you dive into your machine.config, you will see an entry as shown below -

    <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

(as you can tell, I have a named instance running SQLExpress. Another practical tip - when installing 2.0 crap, first install SQL Server 2005 developer edition, THEN install Visual Studio. That way, you won't have to connect to a named instance for 99% of your work).

Note the AttachDBFilename verb, it uses relative paths. So at runtime, the |DataDirectory| is replaced by your ASPNET application path\App_Data and bingo, SQLExpress auto attaches that database, and your membership API, or Webpart persistence framework starts working.

Well, thats kinda cool, except when you take your application to production, and now start running on a webserver, in a controlled environment (probably SQL Server is installed on a different database), you will start getting an error informing you that "An Attempt to attach ASPNETDB.MDF failed". (or something on those lines).

Well that blows.

The quick and easy solution that *may* work is that you simply install SQLExpress on your Web Server. That may work since now SQLExpress will try and attach the database to it's main instance through IIS, and it may erratically fail. (please see this discussion for more details)

There are other reasons you may not want to do that. Why? Because usually in a production environment, your database anything would sit inside a DMZ. Not only that, you may be deploying to a webfarm. And you want to minimize your surface area, so you want the least stuff running on the public face of your site.

Then what do you do? At this time, you have two choices.

a) You could create an application based firewall setting to allow SQLExpress to communicate. I wouldn't recommend this, since this would require a windows based firewall solution. If your IIS box is hacked, by definition your firewall is hacked as well. What a bad idea !! Not to mention, this won't work with a webfarm. Ideally your firewall solution on a high demand public website should be on an OS different from your webserver. It just raises the bar for the hacker.

b) Here is the recommended choice (per me) - Move the ASPNETDB.MDF/LDF to a real SQL Server instead of SQLExpress. Modify the LocalConnectionString (or simply override the personalizationprovider connection string etc.). And everything will work merrily.

... So to make the long story short....

Your life will be a lot easier if you had a step between development and production to move the aspnetdb database to a real SQL Server. This will make your development easier (no more fighting SQLExpress), and your production deployment cleaner.

The exception to the rule is simplistic websites not running on a webfarm, where the licensing cost of SQL Server enterprise is prohibitive.


Sound off but keep it civil:

Older comments..