We help IT Professionals succeed at work.

SQL service stopped after reboot

Hi Experts,

I have a problem with a new SQL2016 server. Its a VM on VMWARE.
Inside the Configuration Manager the service is set to "automatic".
After a reboot, the service is stopped.
The service is configured with a managed account in AD.
After we readd the account, the service is running, until the next reboot.

Do you have an idea ?
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Please verify whether the account has the privilege to "logon as service". This needs to be granted using a domain GPO.
Pete LongTechnical Consultant
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Start > Run Regedit > Navigate to HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Windows NT\CurrentVersion\Winlogon\

Locate/create "REG DWORD 32 value: bit called SyncForegroundPolicy

Set its value to 1

Reboot.

Or in services.msc change the startup type to 'automatic delayed'.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Afree with mcknife, the user you are using as the service accounts lacks the requisite rights, including login as service, lock pages in memory, etc. This should be done through a GPO and rights assignement.
The suggestion Pete included is also managed/handled through a GPO. rather than going through a registry or local policy that will be overridden by GPO of the domain once refreshed.
Eprs_AdminSystem Architect

Author

Commented:
hey mcknife,

how to check this ?
When I click the managed account, I cannot see any priviledge.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
when you reset the account, it tells you that the account was added to the login as a service.
If you are using an AD account, you have to grant it these rights. presumably you did not setup a service account for the sql server that you then added to the administrators (domain admins, etc. group)

open GPMC and see which GPOs you have. assigned and see if one of them has a user rights assignment .
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
on the server in question, open an elevated command prompt and run:
gpresult /h %temp%\result.html

Open in new window

Now upload that file. The answer is in there.
Eprs_AdminSystem Architect

Author

Commented:
inside the result.html I cannot find anything with logon as a service.
Eprs_AdminSystem Architect

Author

Commented:
I have checked the local security policy, here is my service account listed.
Thats ok.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Please upload the results file. You may use notepad to redact it, before.
Eprs_AdminSystem Architect

Author

Commented:
I cannot upload it, it is private and too long
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
It's a few Kilobytes. Not too long, you have to upload it as attachment. Privacy is possible after redacting it (search and replace within notepad). Done that many times here, it was never a problem.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
lets try this, grant this ad account rights:
lock pages in memory
login as a service

The common theme often it to set the account as admin and then pare down which I am not in favor of.
Make sure the account is a member of the local MSSQL server group. the login is part of the security in the DB?
You have a rights issue whether it is access to the file system where the system and user DBs are.

without seeing the error log or you looking at the error log and relaying what the error is that prevents sql from starting we can only provide you with possible option that might explain your situation.... Your comment that when you add the user into the login, it runs, this means the user that is being used does not have inherent login as service rights. and on reboot, fails.
Eprs_AdminSystem Architect

Author

Commented:
Hi mcKnife,

sorry for the delay. Here is the file.
output.txt
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
With all the gpos you have, most are being blocked for one reason or another.

I may have missed, but my question is whether you have a rights assignment policy that grants this ad user rights to lock pages in memory and logon as service

The user against whom the results were run is a member of the administrators group.
It is unlikely the same user as used to run ms SQL.

I use the group policy management console to get this info (group policy wizard) select computer then select user
Eprs_AdminSystem Architect

Author

Commented:
Hi Arnold,
I dont understand. sorry.

How to proceed here now ?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Your first issue is the login SQL uses to start lacks logon as service rights unless and until you reentered the credentials in the service.
Using a GPO that assigns, grams logon as service, lock pages in memory to the service account used to start SQL and under which it is running.
It is possible that once you address this, the other issues might go away.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
@EPRS_Admin

Sorry, I must have missed that you already posted the result file.
I looked at it and I wonder if you could recreate it using the command I gave you.
What you supplied does not have the html code that I need to properly view it.

I looked at it in a text editor, though, and I couldn't see that setting I was looking for (logon as batch job).
Please use my command.
Eprs_AdminSystem Architect

Author

Commented:
Hi McKnife,

yes it was reproducable. After each reboot of the SQL server the SQL service was stopped.
We used here a managed account from windows.

After each reboot, the service was stopped.
Just to start the service wasn't able.

After readding the service account to the logon page, then the service started.
Until the next reboot, then it stopped again.

Any ideas why the managed service accounts not work, after a reboot ?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
The GPO potentially granting rights to this account to logon as service no longer applies or the account initially was part of the administrators group and through a recent audit it is no longer.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
As said: use a domain GPO that allows "logon as batch job" for that account.
Eprs_AdminSystem Architect

Author

Commented:
Hi McKnife,

do you have a link for me to quickly find the settings for this GPO ?
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
See the following.
https://social.technet.microsoft.com/wiki/contents/articles/5752.how-to-grant-users-rights-to-manage-services-start-stop-etc.aspx

Though to grant rights you would look at account policies/local policies to grant defined users/groups in this policy additional rights including ......
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
In GPMC, create a policy (or use an existing one) and go to computer configuration - policies - windows settings - Security Settings, open Local Policies and highlight User Rights Assignment. There, locate Log on as a batch job. Open the properties and add any users that need this right.
Eprs_AdminSystem Architect

Author

Commented:
ok,

and this is always needed when I use a group managed account ?
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Stop, over the weeks (old thread!), I have confused something.
The user right needed is, as said in the beginning, "logon as service", set in the same part of the GPO.

Yes, it needs to be set if you want this account to run a service. If you find that complicated, you may entitle a (previously to be created) group "serviceaccounts" to which you would add all your future service accounts.
Eprs_AdminSystem Architect

Author

Commented:
Thanks a lot.
We switched back to a regular service account.
This is working fine.
Also after the reboot of the server.