SQL Server 2005 SP2 killed my web sites

A few days ago I was visiting my web server (running Windows 2003). Windows Update prompted me to install the SQL Server 2005 Service Pack 2 update so I went ahead and clicked ok to do that.

Yesterday I was surfing thru my web sites and realized that some of my web sites running on that server did not work as they were supposed to do, ie they only gave an error screen 🙁

It turns out the update had stopped my SQLServer service and some of my web sites had been down or partially down for several days.

I know this is my own fault, that I should have checked the server after running a major update, but I was so sure that anything coming thru Windows Update would not stop anything critical on a server. Well, lesson learned… (I hope)

Failed to update database “XXX.MDF” because the database is read-only

After a few hours of Googling and testing things I finally solved this problem so I thought I’d write the solution down both for my own and other’s benefit.

What I did to get the error:
I had just done some changes to one of my web sites using Visual Studio 2005 and ASP.NET 2.0 with VB.NET. I have previously used MySQL as a database for this site but thought I would test using SQL Server 2005 Express Edition for a new feature I just added.
As usual I did Build – Publish Web Site and then copied and pasted the published directory to my web server. When browsing any of the web pages that use the SQL Server 2005 database I get this error message: Failed to update database “XXX.MDF” because the database is read-only.

From what I found on Google this seems to be a confirmed bug in the beta releases of Visual Studio but should be fixed in the released version, but obviously it is not. There seems to be many ways and suggestions on how to fix this, some very complicated.

This worked for me:
1) Make sure App_Data directory or any contained files does not have file system attribute Read-only set. (I had some files marked as Read-only)
2) Give user ASPNET and NETWORK SERVICE Modify control over the App_Data directory. (I had to add both these)
3) Run IISRESET to restart IIS to refresh its permissions.

Main part of this solution was found in this MSDN forum thread, specifically posts by Thongtap and Justin. Thanks guys!