SQL Server 2012 can't start because of a login failure

I recently installed Microsoft SQL Server 2012 on a fresh Windows 7 installation, but whenever I want to run the server, I get the following error:

Error 1069: The service did not start due to a logon failure.

The following user is configured to start the service: NT Service\MSSQL$SQLEXPRESS

How can I fix this problem?

113111 次浏览
小开

While ("run as SYSTEM") works, people should be advised this means going from a minimum-permissions type account to an account which has all permissions in the world. Which is very much not a recommended setup best practices or security-wise.

If you know what you are doing and know your SQL Server will always be run in an isolated environment (i.e. not on hotel or airport wifi) it's probably fine, but this creates a very real attack vector which can completely compromise a machine if on open internets.

This seems to be an error on Microsoft's part and people should be aware of the implications of the workaround posted.

小开

The answer to this may be identical to the problem with full blown SQL Server (NTService\MSSQLSERVER) and this is to reset the password. The ironic thing is, there is no password.

Steps are:

  • Right click on the Service in the Services mmc
  • Click Properties
  • Click on the Log On tab
  • The password fields will appear to have entries in them...
  • Blank out both Password fields
  • Click "OK"

This should re-grant access to the service and it should start up again. Weird?

NOTE: if the problem comes back after a few hours or days, then you probably have a group policy which is overriding your settings and it's coming and taking the right away again.

小开

This happened to me. A policy on the domain was taking away the SQL Server user account's "Log on as a service" rights. You can work around this using JLo's solution, but does not address the group policy problem specifically and it will return next time the group policies are refreshed on the machine.

The specific policy causing the issue for me was: Under, Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignments: Log on as a service

You can see which policies are being applied to your machine by running the command "rsop" from the command line. Follow the path to the policy listed above and you will see its current value as well as which GPO set the value.

小开

One possibility is when installed sql server data tools Bi, while sql server was already set up.

Solution:- 1.Just Repair the sql server with the set up instance

if solution does not work , than its worth your time meddling with services.msc

小开

I had a similar issue that was resolved with the following:

  1. In Services.MSC click on the Log On tab and add the user with minimum privileges and password (on the service that is throwing the login error)
  2. By Starting Sql Server to run as Administrator

If the user is a domain user use Domain username and password

小开

Short answer:
install Remote Server Administration tools on your SQL Server (it's an optional feature of Windows Server), reboot, then run SQL Server configuration manager, access the service settings for each of the services whose logon account starts with "NT Service...", clear out the password fields and restart the service. Under the covers, SQL Server Config manager will assign these virtual accounts the Log On as a Service right, and you'll be on your way.

tl;dr;

There is a catch-22 between default settings for a windows domain and default install of SQL Server 2012.

As mentioned above, default Windows domain setup will indeed prevent you from defining the "log on as a service" right via Group Policy Edit at the local machine (via GUI at least; if you install Powershell ActiveDirectory module (via Remote Server Administration tools download) you can do it by scripting.

And, by default, SQL Server 2012 setup runs services in "virtual accounts" (NT Service\ prefix, e.g, NT Service\MSSQLServer. These are like local machine accounts, not domain accounts, but you still can't assign them log on as service rights if your server is joined to a domain. SQL Server setup attempts to assign the right at install, and the SQL Server Config Management tool likewise attempts to assign the right when you change logon account.

And the beautiful catch-22 is this: SQL Server tools depend on (some component of) RSAT to assign the logon as service right. If you don't happen to have RSAT installed on your member server, SQL Server Config Manager fails silently trying to apply the setting (despite all the gaudy pre-installation verification it runs) and you end up with services that won't start.

The one hint of this requirement that I was able to find in the blizzard of SQL Server and Virtual Account doc was this: https://msdn.microsoft.com/en-us/library/ms143504.aspx#New_Accounts, search for RSAT.

小开

I don't know how good of a solution this is it, but after following some of the other answer to this question without success, i resolved setting the connection user of the service MSSQLSERVER to "Local Service".

N.B: i'm using SQL Server 2017.


提交答案