2015-09-22

Unable to connect/restart SQL Server service when deploying Log on as a service policy on SQL Server


Managed Service Account (MSA) is a special kind of domain account managed by a domain controller and is assigned to a single member computer and used for running services. The MSA password is managed by the domain controller. MSAs can register a Service Principle Name (SPN) with Active Directory. MSAs use a $ name suffix; for example, CONTOSO\SQL-A-MSA$.

If you use an MSA as the SQL Server service account, you need to grant the service account the Log on as a service right within the Group Policy Object (GPO) in the Group Policy Management. After configured the Log on as a service right, if you execute command (gpupdate /force) to force server to apply the updated GPO, you might receive error message – “The service did not start due to a logon failure” when connecting to SQL Server service, or “The request failed or the services did not respond in a timely fashion” when restarting SQL Server service. Check System event log in Windows Logs, you will find more details:
Logon failure: the user has not been granted the requested logon type at this computer.
This service account does not have the required user right Log on as a service.

The issue happens on the server instances starting SQL Server service with default virtual account. If you check Local Group Policy, you will find that NT SERVICE\ALL SERVICES is already added into Log on as a service. You can change Startup type of those SQL Server services to Automatic (Delayed Start) to minimize the conflict issue caused by Group Policy, or manually run the following steps as an alternative solution:
1. Open Services console.
2. Double-click the specific server instance you are trying to start.
3. In Properties dialog box, switch to Log On tab.
4. Clear value in the Password field, click Apply, click OK when prompting message “Passwords mismatch”, then click OK.
5. Retry starting the service.

Note that you must execute the steps above via Services console as it doesn't work in SQL Server Configuration Manager.

To execute the steps in Windows Server Core environment, the easiest way is to use Microsoft Management Console(MMC) because it can be used to access SQL Server Configuration Manager of remote instances:
1. Log on to the server that can connects to Server Core server.
2. Start an MMC snap-in such as Computer Management under Administrative Tools.
3. In the left pane, right-click the top of the tree and click Connect to another computer.
4. In Another computer field, type the computer name of the server that is in Server Core mode and click OK.

If you encounter error messages when execute above steps, please refer to Access SQL Server Configuration Manger of remote instances via Microsoft Management Console.

No comments:

Post a Comment