Login Failed to open specified DB Error in Sql Log

Question

Login Failed to open specified DB Error in Sql Log

pdsqsql

Old Hand

Points: 358
More actions
January 30, 2019 at 10:04 am

#376883

Hi,
I am keep getting following error in my sql serror log:
Login failed to user "DbUSer" Reason: Failed to open the explicitly open the specified Database 'MyDB' [Client: <Local Machine>]
I tried to troubleshoot as it's a Error: 18456 and State:38.
I found that one of my database is is not exists that which user is trying to connect it means database was dropped earlier when that user was associated, but this user is associated with other database also when i see from the mapping DB in security.
Database is not exists so it's not showing under mapping but somewhere is's trying connect.
How can i find that this user from where trying login as it says Local MAchine?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

poratips Hall of Fame Points: 3994 More actions · Answer 1

If any one has any input, really appreciated.
One of account looks like is a service account but that one also associated with other databases so i can't remove or disable it.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

poratips - Thursday, January 31, 2019 9:00 AM
If any one has any input, really appreciated.
One of account looks like is a service account but that one also associated with other databases so i can't remove or disable it.

If it's a service account and it's logging on locally then check the services for anything running under that account.
You can also change the default database for the login if you want to eliminate the errors but keep the account.

Sue

poratips Hall of Fame Points: 3994 More actions · Answer 3

Sue_H - Thursday, January 31, 2019 9:49 AM
poratips - Thursday, January 31, 2019 9:00 AM
If any one has any input, really appreciated.
One of account looks like is a service account but that one also associated with other databases so i can't remove or disable it.
If it's a service account and it's logging on locally then check the services for anything running under that account.
You can also change the default database for the login if you want to eliminate the errors but keep the account.
Sue

Thanks Sue.
Actually this account associated with other databases when i see under user mappings and default database is MASTER. i am not finding somehow root application/program or from where is trying to connect as error log says [CLIENT: LOCAL MACHINE] State:38 and Error: 18456.
I have two user keep throwing similar errors and one of the database is OFFLINE and another database is not exists.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

poratips - Thursday, January 31, 2019 1:12 PM
Sue_H - Thursday, January 31, 2019 9:49 AM
poratips - Thursday, January 31, 2019 9:00 AM
If any one has any input, really appreciated.
One of account looks like is a service account but that one also associated with other databases so i can't remove or disable it.
If it's a service account and it's logging on locally then check the services for anything running under that account.
You can also change the default database for the login if you want to eliminate the errors but keep the account.
Sue
Thanks Sue.
Actually this account associated with other databases when i see under user mappings and default database is MASTER. i am not finding somehow root application/program or from where is trying to connect as error log says [CLIENT: LOCAL MACHINE] State:38 and Error: 18456.
I have two user keep throwing similar errors and one of the database is OFFLINE and another database is not exists.

Have you tried an extended events session or trace to see more of what is going on when the accounts try to log in? I'd probably try something like that to narrow it down.
Other than checking services on the local machine, other things I can think of would be did you try checking jobs and job owners? Or any other applications that run from that server? Any batch jobs or packages on that server?

Sue

poratips Hall of Fame Points: 3994 More actions · Answer 5

Sue_H - Thursday, January 31, 2019 1:29 PM
poratips - Thursday, January 31, 2019 1:12 PM
Sue_H - Thursday, January 31, 2019 9:49 AM
poratips - Thursday, January 31, 2019 9:00 AM
If any one has any input, really appreciated.
One of account looks like is a service account but that one also associated with other databases so i can't remove or disable it.
If it's a service account and it's logging on locally then check the services for anything running under that account.
You can also change the default database for the login if you want to eliminate the errors but keep the account.
Sue
Thanks Sue.
Actually this account associated with other databases when i see under user mappings and default database is MASTER. i am not finding somehow root application/program or from where is trying to connect as error log says [CLIENT: LOCAL MACHINE] State:38 and Error: 18456.
I have two user keep throwing similar errors and one of the database is OFFLINE and another database is not exists.
Have you tried an extended events session or trace to see more of what is going on when the accounts try to log in? I'd probably try something like that to narrow it down.
Other than checking services on the local machine, other things I can think of would be did you try checking jobs and job owners? Or any other applications that run from that server? Any batch jobs or packages on that server?
Sue

I tried to check the jobs, linked server but couldn't find that user login with this DB although this account used by other DB too.
I also tried to see SSIS packages into sql job but didn't have that user and DB in connection string.

poratips Hall of Fame Points: 3994 More actions · Answer 6

poratips

Hall of Fame

Points: 3994

February 3, 2019 at 3:32 pm

#2020980

Any other tips?

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 7

poratips - Sunday, February 3, 2019 3:32 PM
Any other tips?

Do the errors in the log contain a TCPIP address?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

poratips Hall of Fame Points: 3994 More actions · Answer 8

Jeff Moden - Sunday, February 3, 2019 5:22 PM
poratips - Sunday, February 3, 2019 3:32 PM
Any other tips?
Do the errors in the log contain a TCPIP address?

I couldn't see any TCPIP address.
I think i will be trying to look into EventLog and from that will cross check the PID/SPID through PRocess and which application using to get the root.

Johnson Welch SSCoach Points: 18773 More actions · Answer 9

This may help you out. The blog covers the causes of getting this error and the resolution around them.
https://www.systoolsgroup.com/updates/fix-microsoft-sql-server-error-18456-login-failed-for-user/

poratips Hall of Fame Points: 3994 More actions · Answer 10

poratips

Hall of Fame

Points: 3994

February 19, 2019 at 9:41 pm

#2022448

Thank you!

poratips Hall of Fame Points: 3994 More actions · Answer 11

Thank you everyone for your help and response.
I was able to trace using EventLog and through TAsk Manager and it helps me out.