Monday, March 19, 2012

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 /hardware setup as the first sql server).

The mirroring is without witness.

This works but sometimes we get the following ODBC error in our ASP application :

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

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

No error can be seen in the principal SQL error logs.
But a failed authorization can be found on the mirror server..
And this of course this 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 the code I give a secondary server as

reference.

Most of

the time there are no problems (97%), but sometimes we get these errors.

The moments that this error occures are random, as are the databases with which the error occurs.

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 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


Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?
Before we started using SQL 2005 and mirroring this problem did not occur.

Greets

Tom

This will occur if you client has intermittent problems accessing the principal. The mirroring principal will send the name of the mirror server to the client after the first connection. If the client loses connection, it does not know if the principal has failed (it could have been destroyed by a natural disaster for example) so it will try to connect to the mirror to see if there has been a failover to the mirror. Since there was no server side failover in this case, you will get the connection failure logged on the mirror server. I recommend you monitor your network to see if there are intermittent problems causing this behavior.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||At this point we can't find any immediate problems.

What information could help us to identify the reason causing the 'intermittent problems accessing the principal' ?

Are there logs I can consult ?

Greetings

Tom|||

I found an interesting link, which could be the reason for our problem.

But we are unaware that we disbled pooling :

http://groups-beta.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/9c79f6f4e8adce7b/f99b340d2fd76aef

Would this fix help even when pooling is enabled ?

Greetings,
Tom

|||Any solution for this with SP2 for 2005 ?

No comments:

Post a Comment