I'm fighting with strange problem that that most probably is connected with Windows / MS SQL Server security/privileges setup. When external app (that runs with administrator privileges) polls the IIS server(it does login to database with already opened connection, checks for data) and when there is data to process(with the same connection when it then tries to get data) fails from time to time with this error:
        Cannot open database "TheDatabase" requested by the login. The login failed. Login failed for user 'DatabaseUser'.
        Even worse, when this happens - every activity that involves database fails with same error message. So for a few minutes the database and thus whole website is down. But I'm not able to recreate this no matter what I do and I'm only able to see this in exception logs. Database user,that public login is mapped to, is 'dbowner' and nothing points to why it happens. This issue seem to appear after update of website code - ie this happened due to some particular changes in either Windows security or SQL Server config or code, but review of changes in sources gives no clues as everything seems 100% unrelated to this. Thanks in advance for helping with this.

  • Maybe an open connection that you forget to close, maybe a backup program lock your database.
    – Aristos
    Commented Feb 14, 2012 at 13:00
  • My guess would be that your application is not closing connections, and keeps just creating new ones. See how many SPIDs you have throughout the day (or however long it takes for this to happen) by executing sp_who2. Commented Feb 14, 2012 at 13:01
  • Check the "login failed state" of the failed login attempts. They can be seen in the Server's log (which is, the correct permissions implied, accessible via SQL Server Management Studio). That might provide more inside about the actual cause. Commented Feb 14, 2012 at 13:14
  • to clarify this: backups work ok and there was no conflicts before, the connection is managed by IIS with connection pool, it always worked ok, connection string is constant and comes from web.config file, the app polls IIS - not the sql server directly
    – vertique
    Commented Feb 14, 2012 at 14:07
  • and it appears to happen randomly
    – vertique
    Commented Feb 14, 2012 at 14:16

2 Answers 2


Check the Auto Close property on the database you're trying to connect to.

From MSDN:

If True, the database is closed, and its resources are freed when no user connection accesses the database. If False (default), the server maintains the database in an open and ready state regardless of user activity.

Take a look at this article: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-auto-close

  • this may be the solution as I had Auto-Close set to True, though I have to test it first.
    – vertique
    Commented Feb 14, 2012 at 15:10
  • If you had this option set to True, than this is probably it. I had this problem once too, but unfortunately at the time I wasn't able to figure out who or what enabled it.
    – ivanmp
    Commented Feb 14, 2012 at 15:18
  • there is another issue, however with Auto-close set to false it seems it's not blocking database anyway
    – vertique
    Commented Feb 15, 2012 at 10:40
  • @vertique If the answer did help you, why not upvote it?
    – ivanmp
    Commented Feb 15, 2012 at 11:14

The clue is this

Cannot open database "TheDatabase" requested by the login

This means that some connection string has either a non-existent database mentioned, or the login used does not have access. It is also possible that no database is mentioned, and the default database for the login is missing/no permission.

One of these 3 options is usually the answer...

Edit: you don't have auto-close on the database, do you?

  • Sorry, maybe I wasn't clear enough - it works, but fails from time to time, often enough to cause real troubles.
    – vertique
    Commented Feb 14, 2012 at 13:58
  • @vertique: is some code overriding or modifying the connection string?
    – gbn
    Commented Feb 14, 2012 at 13:59
  • no, it's not modified anywhere
    – vertique
    Commented Feb 14, 2012 at 14:08
  • @vertique: see my update about autoclose then please
    – gbn
    Commented Feb 14, 2012 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.