Tuesday, December 9, 2014

SharePoint 2013: Search Service not working due to lost permissions

I was delivering a training session in our London office 2 weeks ago, while I was interrupted for an urgent issue for a client that could not wait as this was their live system and the current support provided had no success in resolving it. Briefly - Search not working. SharePoint 2013. I jumped in and there were some actions already tried by the support provider, but mostly restarting the Search Services on all servers. That didn't help. The client production configuration is (the issue did not occur on any of their dev/test environments):


  • 1 WFE running the Query Processing Component 
  • 2 Application servers running all other Search components
  • 1 SQL server (dedicated and only one instance on it)
  • All databases and service applicatoins provisioned through PowerShell (AutoSPInstaller)


The user experience looks like this on any query, executed from anywhere (and that just happened to occur out of the blue):


The Search Service Administration page in Central Admin was showing "All Errors".

The event logs on the APP servers were all flooded with EventID 1357 containing this:

A database error occurred. Source: .Net SqlClient Data Provider Code: 229 occurred 0 time(s) Description:  Error ordinal: 1 Message: The EXECUTE permission was denied on the object 'proc_MSS_GetStatusChangeRequest', database 'Search_Service_Application_DB', schema 'dbo'., Class: 14, Number: 229, State: 5    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)
   at Microsoft.Office.Server.Search.ManagedSqlSession.ExecuteNonQuery()

.... and a lot more errors related to Search as a consequence of this.

That's SP2013 + SP1 + only 2 security updates released in May this year.  No updates applied after that and everything was working as a charm for 6 months after we've built it. I was given info that there were some network issues experienced just prior to that issue, but I did not have enough data to correlate both.

I've seen the same behavior a couple of months ago when a SQL box was rebooted manually without any actions on the SharePoint farm like stopping services or IIS at all. Then the Search stopped working even when SQL was operational. The reason was lost service account permissions for the Search service account. The same thing happened now for some (unknown yet) reason - the Search service account losts its permission - SPSearchDBAdmin for the Search Service Application database.

I know that's not the best solution, but the only one I could think of in the limited amount of time available to fix this production system - add the permission to the account manually in SQL Server Management Studio. Go to your Search Service Application database (the one referenced above in the application error from the event log, then go to Security -> Users and select the account you use for running your Search Service Application pool, it is also shown in the event log under the error.

In my case that's a dev system on which I've reproduced the issue for the purpose of this post:



On the Membership section, verify the SPSearchDBAdmin role is selected for the account, if it's not (like it was in our case), add it and that should fix your problem. Might need to restart the Search Service again on all the servers running it - we had to do that in our case to get the Admin Component up and running in addition to the Query Processing Component which started working properly immediately after the database permissions were restored.



We still do not know the root cause of that as the environment was handed over long time ago, initially thought that Search in 2013 is very sensitive to connectivity to the SQL Server as I've seen the issue with lost permissoins twice, and the first time was a reboot of SQL definitely causing it. Then I stopped the SQL server on my dev environment for a while to see if that will happen and it didn't, it must  be something else then. And yes, we do use SQL aliases, but we only had one SQL server in that production environment. Hopefully that helps you solve such critical issues quickly.

22 comments:

  1. Thank you for posting this. Solved my problem :-)

    ReplyDelete
    Replies
    1. Hi thool, glad you found this post useful. I am hoping we stop seeing this with SP 2016... :)

      Delete
  2. This is Good information about this topic..I like it.. wordpress database fix ..Keep it Up!

    ReplyDelete
  3. This was helpful thank you :)

    ReplyDelete
  4. Attractive, post. I just stumbled upon your weblog and wanted to say that I have liked browsing your blog posts. After all, I will surely subscribe to your feed, and I hope you will write again soon! wow powerleveling

    ReplyDelete
  5. The monetary motivators of this program are profitable to businesses. Actually, the program has numerous different advantages as well. Email Extractor Software

    ReplyDelete
  6. Failure is not an option, starting out on the right path is. A vast majority of business failures are a result of not knowing the simple steps you can take to steer clear of inevitable obstacles we all face as entrepreneurs. Don't start your journey in the wrong direction. Follow these simple steps towards SUCCESS! Top Local Business In New York

    ReplyDelete
  7. In the realm of business advancement and marking, going computerized is all the buzz. So exactly what is advanced promoting and how might we use it to develop our organizations? CBT Mass Email Sender Desktop Software

    ReplyDelete
  8. Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post. שרת וירטואלי

    ReplyDelete
  9. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. nemzetközi raklapos szállítás Europa-Road Kft

    ReplyDelete
  10. In 1776, Scottish economist and philosopher, Adam Smith wrote the masterpiece, 'The Wealth of Nations'. In this piece, I explain the new rule of Technology of Nations. http://cruxcreative.com/how-to-produce-great-digital-learning-content/

    ReplyDelete
  11. Nice post. I was checking constantly this blog and I am impressed! Extremely helpful information specially the last part I care for such info a lot. I was seeking this particular information for a very long time. Thank you and good luck. pop corn time descargar

    ReplyDelete
  12. Every good business must have a roadmap that tells exactly how the company is, its potentials and makeup. This important document serves as a guarantee for assistance by any technical or financial institution. It is thus imperative that young entrepreneurs consider developing their own business plan for their enterprises. home insurance claims

    ReplyDelete
  13. The dream of business ownership is alive and well these days, especially in Colorado. As of 2015, the state is home to nearly 600,000 small businesses which employ almost 1,000,000 people! Every day you hear about a new start up being launched or a company taking its headquarters to Colorado. Business is good! So for today's discussion, we thought it would be important to address the benefits of buying an established business over starting a business مصور جلسات تصوير حفلات

    ReplyDelete
  14. Failure is not an option, starting out on the right path is. A vast majority of business failures are a result of not knowing the simple steps you can take to steer clear of inevitable obstacles we all face as entrepreneurs. Don't start your journey in the wrong direction. Follow these simple steps towards SUCCESS! biden

    ReplyDelete
  15. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. link

    ReplyDelete
  16. Great with detailed information. It is really very helpful for us.
    Village Talkies a top-quality professional corporate video production company in Bangalore and also best explainer video company in Bangalore & animation video makers in Bangalore, Chennai, India & Maryland, Baltimore, USA provides Corporate & Brand films, Promotional, Marketing videos & Training videos, Product demo videos, Employee videos, Product video explainers, eLearning videos, 2d Animation, 3d Animation, Motion Graphics, Whiteboard Explainer videos Client Testimonial Videos, Video Presentation and more for all start-ups, industries, and corporate companies. From scripting to corporate video production services, explainer & 3d, 2d animation video production , our solutions are customized to your budget, timeline, and to meet the company goals and objectives.
    As a best video production company in Bangalore, we produce quality and creative videos to our clients.

    ReplyDelete
  17. Social media for small businesses is a great way for emerging businesses to generate lead and build a reputation. If regularly updated, social media can deliver more results as compared to traditional mediums. Social media for small businesses gives brands an edge of control over the content that they want to post.Also, since social media is a two-way dialogue process, it helps businesses to instantly identify what is benefitting them. Social media for small businesses also helps generate Word of Mouth, which is one of the best tools for emerging businesses. Virtual assistant services

    ReplyDelete
  18. I was surfing net and fortunately came across this site and found very interesting stuff here. Its really fun to read. I enjoyed a lot. Thanks for sharing this wonderful information. Atlanta Limousine

    ReplyDelete
  19. I havent any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us. fix your credit report

    ReplyDelete
  20. So you have an affiliate business but it's not really delivering. You persist anyway out of stubbornness to all those people who told you to give up! In the back of your mind you catch yourself thinking "Am I wasting my time with this?". Going out of business

    ReplyDelete
  21. The Home Business industry is a multi-billion dollar industry, but unfortunately the vast majority of people (more than 95%) never achieve any level of success with it. Why? Because they don't know the 6 keys to home business success, and they give up before they see results. Freedom Insurance

    ReplyDelete