5

I have a very annoying problem on my Windows 2008 R2 server. Each time it's restarted, either planned or unplanned, one specific site is not able to connect to the database server, a MSSQL Server 2008 R2 Express instance on the same machine. It will give the following error:

Cannot open database "SiteDb" requested by the login. The login failed.
Login failed for user 'SiteDbUser'.

This error will continue, until I manually recycle the application pool for this specific site - a simple website restart doesn't seem to do the trick. As soon as I recycle the app pool, the site connects just fine and will continue to do so until the next reboot. Now, the strange thing is that I have other sites running on this webserver, which doesn't seem to suffer from this problem - they will connect just fine after a reboot. All my websites have different MSSQL users, but I have compared the ones working with the one not working, and I can't seem to find any differences. I have also tried creating a new user for the problematic website, but that doesn't seem to make no difference. I have tried specifying both the instance name and "127.0.0.1" in the connection string, and both work for the other sites, but none of them makes a difference for the problematic site.

I have tried a few other things as well, but since I have to reboot the server to test it (or at least so it seems?), testing this is a bit "expensive", because the server is running sites with quite a bit of daily visitors which matters for my business.

Have any of you guys experienced something like this? I will appreciate any ideas :)

Caleb
  • 11,813
  • 4
  • 36
  • 49
Kasper
  • 51
  • 1
  • We are experiencing this exact issue. To add to your info, we actually have several independent ASP.Net MVC applications each in their own AppPool that use the exact same connection string (sql server authentication), but still only one of the sites experiences this issue (it is always the same site, and that site is the most frequently accessed - 1 mil. hits/day). And, similarly, our app is asking for a new connection every request but yet it continues to fail until we recycle the AppPool. – quentin-starin May 09 '11 at 21:21

1 Answers1

1

It's possible that on reboot IIS becomes available before the SQL Server database (i.e. it's in recovery and it not available). Do you have lot of databases? This could mean a longer start up time.

I'm guessing this by "Cannot open database "SiteDb" requested by the login" which means login is successful but can not connect to the database specified.

Ideas:

  • Can you change the IIS services to delayed start?
  • Set a start up job that waits 30 seconds then issues iisreset
  • Add retry logic to the web code?
gbn
  • 6,079
  • 1
  • 18
  • 21
  • Thank you for your good reply. The server is running 8 active databases and has a couple of inactive databases as well. I have considered the fact that the SQL server might not be fully available when the first request comes in, but why only for this specific site? I have another site which is just as busy, where it works fine. And why doesn't it start working again when SQL Server IS ready? :) – Kasper May 03 '11 at 07:32
  • I agree, our code requests a new connection every time but yet it still requires an AppPool recycle to clear the error. – quentin-starin May 09 '11 at 21:22