2

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.

|improve this question

migrated from stackoverflow.com Feb 14 '12 at 13:30

This question came from our site for professional and enthusiast programmers.

  • Maybe an open connection that you forget to close, maybe a backup program lock your database. – Aristos Feb 14 '12 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. – Thomas Stringer Feb 14 '12 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. – Christian.K Feb 14 '12 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 Feb 14 '12 at 14:07
  • and it appears to happen randomly – vertique Feb 14 '12 at 14:16
0

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

|improve this answer
  • this may be the solution as I had Auto-Close set to True, though I have to test it first. – vertique Feb 14 '12 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 Feb 14 '12 at 15:18
  • there is another issue, however with Auto-close set to false it seems it's not blocking database anyway – vertique Feb 15 '12 at 10:40
  • @vertique If the answer did help you, why not upvote it? – ivanmp Feb 15 '12 at 11:14
2

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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