Sharepoint and SQL Server error 18456 (Severity: 14, State: 16) at Virtual Generations

Jan

Sharepoint and SQL Server error 18456 (Severity: 14, State: 16)

By Jaap Steinvoorte

Categories: Microsoft, SharePoint and search

We have a up and running demo environment with MOSS 2007. One of my colleagues told me that the search wasn’t working well. So I turned to the servers (virtual) to find out what was wrong. The first thing was to check the event viewer where I found this message:

[photopress:eventviewer.jpg,full,pp_image]

Doubleclick showed me this message, and now???

[photopress:event_properties.jpg,thumb,pp_image]

I started with a search on the internet where I found this post on MSDN, it tells me to start SQL Server with a startup parameter, -y 18456. So I did, and checked the logs in the program files/SQL Server folder.

The log file tells me this:

2007-01-28 19:59:02 - ! [298] SQLServer Error: 18456, Login failed for user ‘DEMO\administrator’. [SQLSTATE 28000]
2007-01-28 19:59:02 - ! [298] SQLServer Error: 4060, Cannot open database “SharedServices_DB” requested by the login. The login failed. [SQLSTATE 42000]
But hey… wait a minute, I don’t have a database with this name, yes, I had one, when I set up the MOSS environment, but after some trial and error(fail) I dumped the whole Shared Service Provider and setup a new one which functions pretty well.

[photopress:ssp.jpg,thumb,pp_image]

Also I had a Eventlog with Error: 18456, Severity: 14, State: 16. stated that NT authority\system was denied access. After restarting the SQL Server service the above mentioned errors came up, this one was gone.

But then, the problem which it initially started with. There where a lot of access denied errors in the search log file that came up: “Access is denied. Check that the Default Content Access Account has access to this content, or add a crawl rule to crawl this content. (The item was deleted because it was either not found or the crawler was denied access to it.)”

Where did they came from? The first things I checked was the content crawler account, bad as I am I installed everything under the administrator account, I checked and nothing was wrong. I wanted to build over the Shared Service Provider, but suddenly I realized it could be the IIS authentication settings. Everything was configured as basic authentication so that our business consultants can work on our demo environment from customerlocations which are behind a proxy. I checked the windows integrated authentication and everything works like a charm now.

This leaves me with a couple of questions:

how to get rid of thos event logs which states that access to a (non existing) SSP database is denied.
Where came the very first event log (NT authority\system) from, it happened after I rebooted the SQL server and the MOSS 2007 server and before I restarted the SQL Server service
Why do I have to check Windows Integrated Authentication while I defined a content crawler account with the same credentials as the target site.

Update, another alert: “Crawling this document could not be completed because the remote server did not respond within the specified timeout. Try crawling the server later, or increase the timeout value. You may also try scheduling crawls during off-peak usage times.”. And I have a state-of-the-art machine, dual processor and 4 GB of memory. Yes it is virtualized, one SQL server, one Domain Controller and one MOSS 2007, but the SQL server gets two GB!!!! Ok, because there is a demo tomorrow I will divide the “Local Office SharePoint Server sites” content source in more content sources with their own schedule… To be continued

Update 2: What helps in my case (for the 3rd time I ran into the 18456 error) is restarting the SQL Server servic

Feel free to contact me at Life (@) virtual-generations.com in case the errors won’t be solved. I’m very willing to help. There’s not much information to find about this error. So, with your input I want to make this post a qualitative good one

Update 3: Peter Johnson left a comment about this issue:

I had a similar issue to this one with MOSS after i’d installed then re-installed onto the same box without flattening it down completely.

I ran sql profiler and realised that the error is coming from the SQL server Agent trying to login to a database that doesn’t exist.

Basically MOSS uses SQL Server Jobs to delete expired sessions on a timed basis, to keep the system resource usage at a minimum.

So that said, you need to go into sql server management studio and disable the job called SharedServices_DB_job_deleteExpiredSessions.

Once this is done you should no longer see those error messages in the event log.

If you disable the right job and the error message no longer appears then delete the job. (safety first!)

41 Responses to “Sharepoint and SQL Server error 18456 (Severity: 14, State: 16)”

Feed for this Entry Trackback Address

1 Peter Johnson Mar 13th, 2007 at 7:41 pm

Hi,

I had a similar issue to this one with MOSS after i’d installed then re-installed onto the same box without flattening it down completely.

I ran sql profiler and realised that the error is coming from the SQL server Agent trying to login to a database that doesn’t exist.

Basically MOSS uses SQL Server Jobs to delete expired sessions on a timed basis, to keep the system resource usage at a minimum.

So that said, you need to go into sql server management studio and disable the job called SharedServices_DB_job_deleteExpiredSessions.

Once this is done you should no longer see those error messages in the event log.

If you disable the right job and the error message no longer appears then delete the job. (safety first!)

Hope this helps

Pete
2 Jaap Steinvoorte Mar 13th, 2007 at 11:28 pm

Thanks Peter! Added it to the article, hope we can help somebody with this.
3 Dakini May 18th, 2007 at 12:17 pm

You certanly did help someone. I spent so much time trying to figure out why i was keep on getting this error, changing permissions all the time.

My SQL access denied is now gone, I just have to sort out my Sharepoint Search acsess now.

Thank you so much for your help.

Kind Regards

Dakini
4 Kevin Clark May 30th, 2007 at 11:36 pm

Thank you so much for posting your solution to the 18456 error. I disabled SharedServices_DB_job_deleteExpiredSessions and it fixed my issue! We were trying to figure this out for the past 2 months
5 Jaap Steinvoorte May 30th, 2007 at 11:51 pm

Hi Kevin,

As always, you’re welcome! It took me also a long, long time.
6 Neeraj Jun 29th, 2007 at 9:21 am

Hi Pete,

Even I also says thanks.I was too struggling to fix NT AUTHORITY\SYSTEM. Error: 18456, Severity: 14, State: 16.
error.

Thanks,
Neeraj
7 PC Jul 21st, 2007 at 1:46 am

Folks

Thanks for the advice this really helped me out… Nothing like trying to fix somehting on a Friday after 5pm ugh…..
8 Robert D Sep 6th, 2007 at 3:25 am

Well, you can add another greatful person to the list. This error has been plaguing my SharePoint SQL Server for over 3 months! So strange that SharePoint does not do a better job of cleaning these sorts of things up once a Shared Services DB is removed via the Central Admin site.

In any case, thanks again!
-Rob
9 Jaap Steinvoorte Sep 7th, 2007 at 2:43 pm

Your welcome! Glad I could help you with this one
10 marco pistone Sep 12th, 2007 at 6:58 pm

Say thanks is not enough…
11 Rich Rockwell Oct 3rd, 2007 at 9:57 pm

I also struggled to fix the NT AUTHORITY\SYSTEM error 18456 serverity 14 state 16 and fixed it by disabling the DB_Job_DeleteExpiredSessions.

THANKS!
12 Casey Oct 9th, 2007 at 3:12 am

Excellent post! This error popped up today after we removed an SSP. Been really nagging me! Thanks!
13 Farouk Nov 15th, 2007 at 7:35 pm

Thanks Peter for your fix, worked well for us!
14 Robin Nov 20th, 2007 at 8:20 pm

Thanks so much for the information on disabling the job for the obsolete search database. This has been plaguing me for over a month!!!!
15 Richard Nov 29th, 2007 at 2:26 am

This note was extremely helpful !!!!!
16 Elsa Nov 30th, 2007 at 1:56 am

I had the same error on the SQL server… i had no idea where to turn and found your blog. Thank you so much for posting it, it saved me a lot of trouble!
17 Paul Feb 26th, 2008 at 5:34 pm

Hi, in the referenced article I cannot see anywhere where it tells you to start the DB up with -y 18456.

I’ve had a look around and can’t see anywhere that defines it as a valid flag http://msdn2.microsoft.com/en-us/library/ms190737.aspx

I’m having the same problem but there is no scheduled job to stop.
18 Jaap Steinvoorte Feb 26th, 2008 at 10:45 pm

Hi Paul,

Check this blog, recent article: http://www.chandima.net/Blog/Lists/Posts/Post.aspx?List=466e85c0-8506-4dad-b674-b825d88a30bc&ID=115
Let me hear if this will solve your problems.
19 TG2K Feb 27th, 2008 at 5:08 pm

Thanks for helping me pointing this error. Occured after writing documentation about adding/deleting SharedServices.

Deleting a Shared Services Provider (created for demo) does not delete the SQL’s “DeleteExpiredSessions” associated job. Easy to find in the SQL Agent’s jobs list when you know the name of the deleted SSP.
20 Destry Mar 3rd, 2008 at 10:46 am

Great post! We have the same problem and this post solves the problem. Thanks a lot!
21 Harish Mathanan Mar 27th, 2008 at 6:53 am

Thank you so much for this piece of info. It has truly helped me, spent so much time trying to find the cause of the errors and equal ammount of time going through rubbish suggestions from sites that I googled up.
22 Jim Apr 14th, 2008 at 3:48 pm

I can’t add anything that hasn’t been said. This really helped me out as well.
23 Dan May 14th, 2008 at 11:31 pm

Yep, this works great. Thanks for your help.
24 Mike Jun 4th, 2008 at 3:56 pm

Peter Johnson, I was pulling my hair. Your simple solution (disable xxxxxxxxxx_job_deleteexpiredsessions) resolved my issue. THANK YOU !!
25 Yoong Jun 5th, 2008 at 6:55 am

Peter Johnson and Jaap Steinvoorte thanks so much for this, it’s been a week of me hunting for a solution but this is the one! Many thanks guys! I’m sure so many ppl will be appreciative of this solution….it surely helped me!!

THANKS GUYS!
26 Chadd Warwick Jun 6th, 2008 at 3:26 pm

Thank you so much for the help!! I found the error the other day and with a quick Google search you have saved me a lot of time.
27 Brian Parker Jun 24th, 2008 at 7:54 pm

Holy Cow! Thanks Pete! My SQL log has been generating that error every 60 seconds for the last NINE MONTHS!!! What a relief to have that cleaned up!
28 Colin Cochrane Jul 25th, 2008 at 5:43 am

Thanks so much! This has been a thorn in my side for about a week now.
29 helge lehmann Jul 25th, 2008 at 8:51 am

Thx a lot man!
30 Doug Lubey of Louisiana Jul 27th, 2008 at 7:27 pm

Thanks, I really appreciate the help…..I have had the same problem for awhile.

search reference:
[298] SQLServer Error: 4060, Cannot open database
sql error logs [298] SQLServer Error 18456, Login failed for user
31 Andy G Jul 28th, 2008 at 2:42 pm

Great addition to this post Peter. Saved me hours of head scratching!
32 Santiago Aulestia Aug 25th, 2008 at 4:54 pm

You did help me! I had this problem and I was not finding any reason why…
Well thanks to our “Delete SSP” process in MOSS!
33 Mai Omar Oct 28th, 2008 at 2:08 pm

thanks…. it worked here too
34 Rick CAin May 22nd, 2009 at 3:53 pm

Thank you! I had uninstalled-reinstalled MOSS twice, ran through the registry trying to find out why it kept generating errors like this. I love simple fixes like this.
35 Reddy Kiran Jun 1st, 2009 at 10:04 pm

Thanks Jaap it worked, deleting “SharedServices_DB_job_deleteExpiredSessions” fixed the issue
36 William Jun 26th, 2009 at 10:11 pm

Have the same problem like Paul. There is no scheduled job to delete in SQLServer Agent.

Anyone can advise???
37 Jason Jul 1st, 2009 at 6:41 pm

Awesome find! Thanks a bunch!
William, make sure the SQLServer Agent service is started.
38 Franklin Sep 9th, 2009 at 9:02 pm

Thanks, resolved my issue, thought I’d never get rid of that error!
39 Renata Sep 25th, 2009 at 10:17 am

Guys, that is what I was looking for! Excelent job, thanks.
40 Cindy Nov 17th, 2009 at 5:51 pm

One way to figure out which are the old jobs and which ones are valid for the new MOSS install:
run SCRIPT JOB AS..CREATE TO..NEW QUERY EDITOR WINDOW in SQL Server Management Studio. That way, you can see the script and which database a particular job is trying to access. Then you’ll know which jobs are legit and which you can delete.
41 Sudhir Kesharwani Mar 4th, 2010 at 2:09 pm

Guys,

Many thanks for such a useful post.

I had been struggling to find some resolution to this… and your post really helped me.

You are rocking…

Sudhir