TechTips - SharePoint SQL Error 18456, Login Failed for User "domain\user" Client: Local Machine

This is an error that can be very common for SharePoint administrators that occurs on the SQL server and happens every minute on the minute to the second. This can fill up a log file very quickly and can cause serious problems, the error itself isn't that big of a deal but full log files can cause problems. Luckily the error is a relatively easy one to fix.

This error is caused by the Shared Service Provider in MOSS 2007 SharePoint, it shouldn't happen but there is a job in SQL that runs to get rid of junk sessions within the Shared Service Provider every minute. This is where your error comes from, if you look on the net you will probably find all sorts of people telling you that you need to put the service account in the sysadmin group on your SQL server (like this Microsoft article). This wasn't the case for me; my account was already in that group so that fix wouldn't have worked.

The reason you are getting the error is that you have probably created an SSP (Shared Service Provider) and then deleted it and recreated it for some reason. When this happens the original SSP keeps a job in the SQL agent as SharedServices1_DB_Job_DeleteExpiredSessions (or whatever you named your SSP database upon creating it). It is still running every minute with the service account you specified but cannot complete because it doesn't really exist in SharePoint any more, so it gives you the 18456 login failed error.

So to fix the error all you have to do is find the job and disable it, verify that once it's disabled the errors are gone and then once you know it was the cause of the problem, delete the job. The reason you have to be careful and verify that this job is the right one to delete is there will be another similar job that is actually running to delete expired sessions in your new SSP on your SharePoint server.

For instance in my SQL instance I had two of these running, the first was named

SharedServices1_DB_Job_DeleteExpiredSessions

The second job was named

SSP1_DB_Job_DeleteExpiredSessions

The reason there were two, I created my first SSP1 and there was a power failure mid configuration. I had to start over because my DB was corrupted, but this job stayed in the SQL jobs and caused my error.

So to determine which job was causing the problem here is what I did.

1 - Go to MOSS 2007 Central Admin page

2 - Clicked on Application Management Tab

3 - Click on Content Databases (under SharePoint Web Application Management)

4 - Click Web Application drop down

5 - Click Change Web Application

6 - Click the name of your SSP (mine was SSP1)

7 - Check the Column Database Name

8 - Go to SQL server and open management studio

9 - Login to instance running SharePoint

10 - Double Click on SQL Server Agent (to expand it)

11 - Double Click on Jobs folder (to expand it)

12 - Right click on Job named SharedServices1_DB_Job_DeleteExpiredSessions (or whichever isn't your proper DB name)

13 - Click Disable

14 - Reboot server

15 - See if the error is gone (also make sure your SSP is still working)

16 - If both of those statements are true you can delete the job

17 - Done!

Once you have tested your SSP and see that the error is gone then you can delete the job forever. This should fix your problem with every minute getting the Error 18456, Login failed for user "domain\user" that was filling your log files.

Hope this helps you with your problem.