Failed to open the explicitly specified database

  • Hello experts,

    Users are reporting this error for their logins:

    Message

    Error: 18456, Severity: 14, State: 38.

    Message

    Login failed for user 'foo'. Reason: Failed to open the explicitly specified database 'bar'.

    The databases and logins are in an Availability Group, not sure if that is relevant. But it is an odd error because the login in question is in a different AG and the login should NOT have access to the database anyway. Yet when the login tries to log in, it triggers the above error.

    I checked the logins on the AG replicas, and they exist and have the same SIDs, so it doesn't seem to be a login synchronization issue.

    Does anyone know how to troubleshoot this? I already know the database in question, I just don't know why the login is trying to access it. Even the users don't want to access the database; it's just the login failures they want to stop.

    Thanks for any help!

    -- webrunner

     

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Script the creation of one of the troublesome logins and check the DEFAULT_DATABASE for it.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Thanks, Phil.

    I checked one of the logins and its DEFAULT_DATABASE is [master].

    Do you know why a login would try to access the other database during login if it isn't the default database?

    Also, maybe this is irrelevant, but the database causing the error happens to be the first listed, alphabetically, on the server.

    Thanks again.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • I'm with Phil.  Whenever I've seen this error it relates to the DEFAULT db specific for the user (or group).

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Or - it is the default database set in the connection string.  How are these users connecting to the system - and what application are they using?  Are they attempting to connect to the system using the listener or directly to the node?

    If they are connecting through the listener - are they using ApplicationIntent to route to a read-only secondary?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks all. I will check with the clients because it could be something with the connection string. Thanks again.

     

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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