Cannot open database "xx" requested by the login. The login failed.

Hi,
we are running a webapplication, using IIS6, Windows 2003 64 bit in classic Asp.
In the asp pages we connect to our SQL server (SQL Server 2005 64bit,windows 2003 64 bit), mirrored on a third sql server (same software setup as the first sql server).

The mirroring is without witness.

This works perfectly but sometimes we get the following error :

Description: [Microsoft][SQL Native Client][SQL Server]Cannot open database

"OBJECTS" requested by the login. The login failed.

In the logs of sql, we see that the offending connection does not go the principal but to the mirror. Which would cause the problem of course..

The name of the database varies, and there is no consistent error. Most of the time there are no problems (95%), but sometimes we get these errors.

Connection from the asp to sql is done using ADO, using following driver in the connectionstring :

Driver="{SQL Native Client}"

Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?

Greets
Tom

Looks like your application attempts to login using "OBJECTS" as the default database and the database does not exist. Is that database supposed to always exist or is it occasionally dropped and recreated? Your application should not attempt to connect to the database if it might not exist.

Thanks
Laurentiu

|||Hi Laurentiu,

the database always exists. So it must always be available for connection.
But for some reason SQL fails to see the database.

Also, it's not one and the same database that has this problems. We have more than 100 databases on the sql server, and the connection error popups randomly with other databases.

Greets
Tom|||

Can you check your server errorlog and post the errors that show up there? Also, does this happen during server startup by any chance? Is your database on a special volume, such that it would take longer to start up, for example, is it on an EFS volume?

Thanks
Laurentiu

|||Hi Laurentiu,

I'll check the correct SQL serverlogs when this error occurs.

The server is not starting up but running as normally. Remember, this is a production server.
No special volumes.

Additional information :

As told the connection is made in our ASP pages , using an ADO connectionstring.

Now it seems that the initial connection setup works :
e.g

set pCn = Server.CreateObject("ADODB.Connection")
pCn.connectionstring=lstr
pCn.Open

And the first executes always seem to work :
e.g.
pcn.Execute ("select * from thetable")

But in the same page, more executes are performed with the same connection (pC).
And after some executes, it fails with the above error.

Again, this does not happen always, and not with the same database !
And we are not starting up the server, or doing special tasks (aka backups,etc) which would add an extra load on the server.
The moments that this error occures are random, as are the databases with which the error occurs.

Greetings
Tom

|||

If you hit the error again, can you please provide the errorlog entries?

Thanks
Laurentiu

|||

Hi Laurentiu,

sorry for the delay in getting back to you.

There are no error entries in the primary sql server error logs
Only an error pops up in the secondary (mirror) server, stating the login failed.
And this of cours makes sense, as the connection does not go to the primary but to the secondary, which is acting as mirror. And therefore can not be accessed.

The question is : why does the connection sometimes jumps in stead from the primary server, to the mirror server ? Nowhere in my code I give a secondary server as reference.

The connection is made through ODBC in an ASP web application (IIS6) , simelar to :

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.CommandTimeout = 180
oCn.Open lconstirng lconstring

where lconstring is a dsn param : e.g. "filedsn=c:\data\dsn\mastercon.dsn"
where the dsn file contains following info :

[ODBC]
DRIVER={SQL Native Client}
UID=myuserid
pwd=mypwd
Address=xxx.yyy.zzz.qqq,myport
Network=DBMSSOCN
DATABASE=MyDatabaseName
WSID=xxx.yyy.zzz.qqq
APP=Microsoft Open Database Connectivity
SERVER=xxx.yyy.zzz.qqq
Description=blabla

I tried the above without the DSN, so a dsnless connection string. Nothing changed.
I tried following drivers :{SQL Native Client} and SQLNCLI
Nothing changed

Any ideas ?

Greets
Tom|||

Hi Tom,

For these questions, I suggest trying the Database Mirroring forum or the SQL Server Data Access forum. These are the forums that address issues related to mirroring and server connections.

Thanks
Laurentiu