SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error 18456 Severity 14 State 38


Error 18456 Severity 14 State 38

Author
Message
learningguy
learningguy
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 32
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
NJ-DBA
NJ-DBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1546
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
learningguy
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 32
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
NJ-DBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1546
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
learningguy
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 32
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
NJ-DBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1546
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
learningguy
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 32
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
NJ-DBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1546
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search