Failed to open the explicitly specified database

Question

Post reply

Failed to open the explicitly specified database

webrunner

SSC-Dedicated

Points: 31879
More actions
July 8, 2021 at 7:38 pm

#3905850

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

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

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

Phil Parkin SSC Guru Points: 246974 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

webrunner SSC-Dedicated Points: 31879 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 101117 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

webrunner SSC-Dedicated Points: 31879 More actions · Answer 5

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