Wednesday, January 27, 2016

How to Manage SQL Server Service Account Error

SQL database is full of extraordinary functions and these functions make user’s life easy and more comfortable. Sometimes it’s easy to find errors and corruption but sometimes it’s like a nightmare. Few errors come up with the starting process and greed the SQL server by their interruption. Here, I will discuss an issue related with the SQL Server.

Incorrect password of the service account or account locked disabled

I got the following error when I tried to start MS SQL Server:


The request failed or the service did not respond in timely fashion. Consult the event log or other applicable error logs for details.

 Reasons for this error:

  • Changing the location of master database
  • Anti-virus can lock the SQL Server
  • Insufficient permission for SQL Server Service account
  • Server side protocols issue
  • Missing important files due to disk failure or accidental deletion
  • Changing the Password of the service account but not updated on the Server where SQL instance installed
  • SQL Server is disabled from the Service Control Manager
  • Incorrect location on the startup parameters etc

So, these are the few responsible cases for this error.

At the time of startup error, try to open the SQL Server by command prompt and I do so. Simply type,net startmssqlserver to start the SQL Server.One more error message that you may get after executing this command.

“A service specific error occurred”

Check the SQL server error log or the Windows Application Event Log to find the main issue for this problem. Personally, I prefer the SQL Server error log which is a text file and I can see all the error messages easily. To know the location of error log file, go to the SQL Server Service in the SQL Server configuration Manager and check the Starting Parameters.

I highlighted the location of the error log in the above figure and it is default location of the error log file. With this, you can easily check the current location of the master.mdf and masterlog.ldf files.

If the SQL Server starts at least once before failure then it is easy to find the reason of failure by the error log report. In some cases, the error log will not show any entry and these cases are:

Invalid password of Service Account or Account locked
Incorrect location of the ERRORLOG file or the permission issue of the SQL account

In the first case, The SQL Server won’t start so there is no chance for any entry in the log file. In the second case, the SQL Server starts but you cannot find the log entry in the log file. To check this issue, examine the Windows Event Log. Go to administrative Tools-> Event Viewer and filter for errors.

Check the main reason for this problem. I got the following reason:

Logon failure: unknown user name or bad password.

From the above error, it is clear that an incorrect password causing the error. I updated the password of SQL Server account and fix it.

Other possible errors are:
Logon failure: account currently disabled.
Login failed: Account locked out.

To enable the account, go to your database by using SSMS. Security->Logins->sa. Right click on the sa and go to the properties. Now select the Status from the left pane. Enable the login status and click on OK button.

You can also use the script to enable sa account:

  1. Use [master]
  2. GO
  4. GO
  6. GO

Note: enter the correct password. It is just an example.

The best way to recover SQL server password is to contact with the concern person (Example: DBA). If you are unable to contact the related person so in this case, you can try a professional tool to recover SQL password. This is also a way to recover your database.

Conclusion: So this is a very common problem with SQL Server database. If you are a DBA and have changed any password of the SQL account then always save on a safe place. If needed then share it with concern person. 

No comments:

Post a Comment