Login failed for user 'sqluserid'

Question

Login failed for user 'sqluserid'

GonnaCatchIT

SSCrazy

Points: 2797
More actions
March 7, 2016 at 7:36 am

#146026

Hi Team,
We have windows app net 2.0 c# which connects to SQL Server 2005 r2.
I get strange errors during certain time of the day
SqlWorkflowPersistenceService caught exception from OpenConnection: System.Data.SqlClient.SqlException:
Cannot open database "WorkflowPersistence" requested by the login. The login failed.
But I am certain the sql user id I am using is not being tampered.
My Question:
Does this error hiding the actual error , can this mean something else than the obvious?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No, the error means exactly what it says. At the time the login was attempted, the database WorkflowPersistence was not available.

A common cause of this is having auto_close set on the database, if it is on, SQL will close the DB when the last user disconnects and have to reopen it when someone requests access. It's hugely wasteful in terms of resources to do that.

p.s. There's no such thing as SQL Server 2005 R2.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 2

So if Auto_Close is not in SQL Server 2005 r2, then this is not the cause in my case.

The reason I ask this is - the sql user ID is being used for all the major DB's as well an it's really strange I get this error for only one particular DB 'WorkflowPersistence'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

GonnaCatchIT (3/7/2016)
So if Auto_Close is not in SQL Server 2005 r2, then this is not the cause in my case.

I never said that.

What I said was that there is no such version of SQL Server as 2005 R2. It does not exist. There's 2005, 2008 and 2008 R2.

Autoclose has been in every version of SQL from 7.0 or earlier.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 4

Sorry , my bad. It's SQL Server 2005 , in the middle migrating from 2005 to 2008 r2 :).

I will check if this is set on my DB and will explore more on this. Thanks for the pointer