So far, the articles in this years 12 Days of Christmas series first and second days) have shown some relatively easy fixes for some rather annoying problems. In this article, I will continue that same pattern. In this article, I will share another frustrating problem related to both Kerberos and Managed Service Accounts (MSAs).
What is an MSA you might ask? That is a good question. If you are unacquainted with this special type of service account, you have been missing out. An MSA is a special account managed by the domain controller. It is an account that is used to run a service on a single computer (note a Group Managed Service Account, or gMSA, is another type of MSA that can be used on multiple computers). Since the password is managed by the domain and is frequently changed, humans won’t know the password and will not be able to use it to log on to a computer. This type of service account is preferred because of the improved security on the account.
That sounds great, right? It is, until you encounter some of the oddball failures and randomness of the service not starting as you might expect. Wayne Sheffield addressed some of the problems relating to the service starting in his article here. As luck would have it, there is a similar problem that needs just a little more action on our part in order to nudge the service to be able to start consistently.
Fix SQL Service not Starting after Reboot when using an MSA
As a general rule of thumb, I recommend adding the service dependencies to your SQL Server Service. These service dependencies are (as mentioned in Wayne’s article): Netlogon, KEYISO, and W32Time. Additionally, I recommend that you double check the dependency for the SQL Server Agent to ensure it is dependent upon SQL Server Service.
Most of the time, the agent service will already have the SQL Server Service dependency and the SQL Server Service will have the KEYISO dependency. However, that is not a foregone conclusion. Occasionally, those dependencies are missing. Netlogon and W32Time are almost always missing so you will need to add those dependencies most of the time.
I won’t go into further detail about setting these dependencies today. Wayne showed how to use regedit to set the dependencies. That said, these dependencies should be set prior to setting the “fix” I will mention shortly in this article. In addition, stay tuned because I will be showing in the near future how to set those dependencies via PowerShell.
Occasional Errors after Reboot
The problem of SQL Server Service not starting after a reboot when using an MSA is ridiculously frustrating. Why? Well, you get very little information as to what the problem is. The event viewer just shows (most of the time) that the service stopped and doesn’t give an error code. In fact it just shows it as an informational event. Every once in a while, you may get a gold nugget of a clue with a message similar to the following:
NETLOGON error:
This computer was not able to set up a secure session with a domain controller in domain TurdWilligers due to the following:
There are currently no logon servers available to service the logon request.
This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.
ADDITIONAL INFO
If this computer is a domain controller for the specified domain, it sets up the secure session to the primary domain controller emulator in the specified domain. Otherwise, this computer sets up the secure session to any domain controller in the specified domain.
Despite the service dependency on Netlogon, you can still see SQL Server try to start before the Netlogon service is fully functional. This is disastrous, right? Not exactly. Despite the flawed attempt to start a bit quick there, we still have our methods to avoid this problem in the future. You will laugh at how ridiculously easy this fix is. Are you ready for it?
And now the Fix
The fix is (after setting the service dependencies mentioned previously) to set the SQL Server Service to “Automatic (Delayed Start)” instead of “Automatic” as shown in the following image.
All that needs to be done is this quick and simple change in the services control panel (not SQL Server Configuration Manager). Once applied, reboot the server to validate that it worked.
TADA – problem solved. Now, take this fix and make your environment consistent. Or not. The choice is yours. Personally, I prefer to have my environment as consistent as absolutely possible. There are times when that is not plausible and it is these times that it is very highly recommended to DOCUMENT what is different and the reasons as to why it is different. This makes your job significantly easier and gives you back a significant amount of time that you will grow to appreciate more and more over time.
Put a bow on it
Running into startup issues with your services could be a nightmare on some days. Couple those issues with an MSA and you may be looking at a bald spot on your head before long. Never fear, this article demonstrates that startup issues involving MSAs can be an extremely simple condition to resolve. The most benign of changes affecting your service startup can be the difference between SQL Server starting normally after an unexpected reboot or SQL Server being down until somebody “notices” the problem.
As mentioned previously in this article, please stay tuned for future tips on how to simplify adding the service dependencies. It should prove a useful tool in your arsenal, helping you to become more efficient and elite.
Enjoyed reading some of these security related articles? Security is a fantastic topic and there is plenty to learn in regards to security. Try this article for more related to Kerberos issues. Or maybe you want something a little more in your face about security? Try this back to basics article about public role permissions.
This is the third article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post Here is an Easy Fix for SQL Service Startup Issues When Using an MSA first appeared on SQL RNNR.