Error 18456 Severity 14 State 38

Question

Error 18456 Severity 14 State 38

learningguy

SSChasing Mays

Points: 615
More actions
November 3, 2011 at 6:53 am

#390750

I used to have SSRS installed on this SQL box and I already removed SSRS by going to "program and features" (proper way to uninstall SSRS). Any way, it's already been removed and I removed SSRS DBs afterward.
Now, I kept getting error"
"Login failed for user 'domain\blah' Reason: Failed to open the explicity specified databas. [Client: <local machine>}.
Based from some research, this error is related to SSRS and I already cleaned it up and its DBs, now I just want to clean up the errors.
Can someone tell me how to get rid of this error?
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

Matthew Nguyen (11/3/2011)
I used to have SSRS installed on this SQL box and I already removed SSRS by going to "program and features" (proper way to uninstall SSRS). Any way, it's already been removed and I removed SSRS DBs afterward.
Now, I kept getting error"
"Login failed for user 'domain\blah' Reason: Failed to open the explicity specified databas. [Client: <local machine>}.
Based from some research, this error is related to SSRS and I already cleaned it up and its DBs, now I just want to clean up the errors.
Can someone tell me how to get rid of this error?
thanks

This error means some user is trying to connect, but cant connect to the database it's looking for. Based on what you said, it probably doesnt exist anymore. Also, client: local machine is telling you that the connection is coming from the same server that hosts the database. Take a look at the user in question- what is it's default database- does it exist? What application or service is running under that user? it's still trying to connect to your database server. One way to find out more detail is to run a profiler trace and capture failed logins (under security audit)... this will show you the "client process id". With that you can identify what process is trying to log in to your database server and access a database which it does not have permission to (or does not exist).

learningguy SSChasing Mays Points: 615 More actions · Answer 2

Thanks for your prompt reply.

default DBs for this user is 'master' and this acct does have admin privs, indeed it's svc acct that run SQL.

I completely agreed with you on everything you mentioned in your post; however, it still didn't make any sense to me and maybe there is something that I missed.

thoughts?

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

Yes. If it has admin rights, then it is trying to access a databse which no longer exists. Look at the default trace for the process id of the login failure. Run taskmanger to see what process that is and you have your offender. By chance is there a reporting services service still running?

learningguy SSChasing Mays Points: 615 More actions · Answer 4

it came out from SQL agent. SQL agent service running as this act. To be more specific, I think the error came out from either update job activity, job manager or TSQL jobstep.

NJ-DBA SSChampion Points: 13834 More actions · Answer 5

So you have some job out there looking for a db that doesnt exist. Should be pretty easy to compare the login failure times to job history and track down the offender

learningguy SSChasing Mays Points: 615 More actions · Answer 6

ok, I found out the job came from one of the incremental backup job and I have no idea which could trigger the log backup to run every 5 min which triggered the errors. I even disable the log backup job for this DB and still got the errors.

I am stumped......

Any ideas?

Thanks

NJ-DBA SSChampion Points: 13834 More actions · Answer 7

If you have a log backup job or an incremental backup job which explicitly mentions a database which does not exist, then that's a problem. Disabling the job is great, unless something else is enabling it. For example at one environment I worked, there were custom script in place where "driver jobs" enabled other jobs based upon table values. So there was a "config table" which listed databases and which jobs to run against it. If you deleted a database, but didnt update some routine which generates maintenance jobs based on a list of database, you could encounter the scenario you are seeing.

So if your job is disabled, but it's running, you need to figure out what is enabling it. Take a look at the errorlog- is some other job running immediately before this particular job runs? Is that job enabling the job you disabled?