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.
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 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
-
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 Feb 14 '12 at 13:58
-
-
-
sp_who2
. – Thomas Stringer Feb 14 '12 at 13:01