Cannot start any SQL server services

Question

Post reply

Cannot start any SQL server services

office 18838

SSChasing Mays

Points: 625
More actions
March 15, 2012 at 6:13 am

#253060

Hello,
Due to problems on our network we have had to demote a Domain Controller that was running our SQL sever in a Windows domain. This server is now a standard domain server but one knock on effect is that it seems to have changed the configuration settings for the SQL server and none of the services can be started so the server will not run.
Please can anyone help with the settings required (TCP/IP and logon) to get the SQL server services all running again? We have queues of people trying to access the applications that this server runs so am desperate to get this running again. I am an SQL novice so please take this into account when replying.
Thanks,
Matthew

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

was this the only domain controller in the domain or do you have a replicated AD?

if its the only DC then you cant use AD credentials as there is no where for SQL to authenticate the user tokens

office 18838 SSChasing Mays Points: 625 More actions · Answer 2

Thanks for your reply Anthony. No, there are two other DCs on the network and they have taken over the role, along with DNS. All other users are authenticating fine and I have checked the network connections. This definitely seems to be a service logon issue because the event errors are referring to this. I have tried changing the service logins but this does not help.

I can't work out what has changed in making this a standalone server on the domain instead of a DC and why the existing login is being refused to start up any of the services.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

can the sql server successfully ping the other two DC's? do the logins which your using as service accounts exist on the other two DC's

can you post the details from eventvwr for the startup of the services so we can see if anything else is at play?

office 18838 SSChasing Mays Points: 625 More actions · Answer 4

I have checked network connections and can ping both DCs from the SQL server command prompt. I can also connect to file shares on the 2 DCs from the SQL server itself. The account being used for the SQL server services is up and running and working fine. I'm pasting below a number of errors on this from the system event log. They all refer to this logon error.

Log Name: System

Source: Service Control Manager

Date: 15/03/2012 15:32:23

Event ID: 7041

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: phaedrus.CES.LOCAL

Description:

The MSSQLSERVER service was unable to log on as CES\SQLServerDataEngine with the currently configured password due to the following error:

Logon failure: the user has not been granted the requested logon type at this computer.

Service: MSSQLSERVER

Domain and account: CES\SQLServerDataEngine

This service account does not have the required user right "Log on as a service."

User Action

Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.

If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.

Event Xml:

<Channel>System</Channel>

<Computer>phaedrus.CES.LOCAL</Computer>

</System>

<Data Name="param1">MSSQLSERVER</Data>

<Data Name="param2">CES\SQLServerDataEngine</Data>

</EventData>

</Event>

==============================================================

Log Name: System

Source: Service Control Manager

Date: 15/03/2012 15:32:23

Event ID: 7000

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: phaedrus.CES.LOCAL

Description:

The SQL Server (MSSQLSERVER) service failed to start due to the following error:

The service did not start due to a logon failure.

Event Xml:

<Channel>System</Channel>

<Computer>phaedrus.CES.LOCAL</Computer>

</System>

<Data Name="param1">SQL Server (MSSQLSERVER)</Data>

</EventData>

</Event>

======================================================================

Log Name: System

Source: Service Control Manager

Date: 15/03/2012 15:49:43

Event ID: 7024

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: phaedrus.CES.LOCAL

Description:

The SQL Active Directory Helper Service service terminated with service-specific error %%-1073741724.

Event Xml:

<Channel>System</Channel>

<Computer>phaedrus.CES.LOCAL</Computer>

</System>

<Data Name="param1">SQL Active Directory Helper Service</Data>

</EventData>

</Event>

=============================================

Log Name: System

Source: Service Control Manager

Date: 15/03/2012 15:27:43

Event ID: 7001

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: phaedrus.CES.LOCAL

Description:

The SQL Server Agent (MSSQLSERVER) service depends on the SQL Server (MSSQLSERVER) service which failed to start because of the following error:

The service did not start due to a logon failure.

Event Xml:

<Channel>System</Channel>

<Computer>phaedrus.CES.LOCAL</Computer>

</System>

<Data Name="param1">SQL Server Agent (MSSQLSERVER)</Data>

<Data Name="param2">SQL Server (MSSQLSERVER)</Data>

</EventData>

</Event>

========================================================================

Log Name: System

Source: Service Control Manager

Date: 15/03/2012 11:50:04

Event ID: 7041

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: phaedrus.CES.LOCAL

Description:

The SQLBrowser service was unable to log on as CES\Timaeus with the currently configured password due to the following error:

Logon failure: the user has not been granted the requested logon type at this computer.

Service: SQLBrowser

Domain and account: CES\Timaeus

This service account does not have the required user right "Log on as a service."

User Action

Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.

If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.

Event Xml:

<Channel>System</Channel>

<Computer>phaedrus.CES.LOCAL</Computer>

</System>

<Data Name="param1">SQLBrowser</Data>

<Data Name="param2">CES\Timaeus</Data>

</EventData>

</Event>

I should add that, in response to the login failure info I tried granting the logon as a service right to the SQL server services acccount objects using local security policy on the SQL machine. In order to do this I followed the instructions given in this post: http://www.sqldbadiaries.com/2010/12/22/the-sql-server-service-did-not-start-due-to-a-logon-failure/ , but this did not help - the DC demotion seems to have caused a deeper issue.

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

looks like the account has been removed from logon as a service,

run secpol.msc on the server and add in the accounts for the engine and agent to the logon as a service section (think its under the local settings)

also check with your domain admins and see if there are any domain wide policies which would remove this access, if so modify them to include the accounts

office 18838 SSChasing Mays Points: 625 More actions · Answer 6

I tried granting the logon as a service right to the SQL server services acccount objects using local security policy on the SQL machine. In order to do this I followed the instructions given in this post: http://www.sqldbadiaries.com/2010/12/22/the-sql-server-service-did-not-start-due-to-a-logon-failure/ , but this did not help - the DC demotion seems to have caused a deeper issue.

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

in secpol, when you look at the log on as a service section, what is the icon showing at the begining? is it a page with 10010 or is it two servers with a little script icon in the bottom left?

if its the latter then AD is forcing out a group policy update which is overwriting what is set manually.

office 18838 SSChasing Mays Points: 625 More actions · Answer 8

Sounds like we might be getting somewhere. Here is a shot of the secpol tab - single server with a script in bottom left:

Two things:

1. Only NT SERVICES/ALL SERVICES is listed with this right. Also it is only a single sever rather than two. This does not seem correct to me. I think this might have something to do with the way in which this server was demoted and some manual configuration is needed.

2. Can you help with where I can find the settings in AD's group policy so that I can modify that for this server. I can set up a new GPO just for this if required although it looks like an existing one needs editing.

To add to the above, I have been digging around in GP in one of the DCs and I notice a couple of Domain Member policies that are not being applied to the newly demoted DC (i.e. now just a Domain Member). One of these is not to display the last logged on user name on the logon screen - this policy is not being applied. It is not directly relevant to the SQL logon but it shows that the group policy is not being applied to this domain member - the SQL server machine.

This makes me ask, rather than a GPO trumping local policies could it be that the GPO from the domain is not being applied to this SQL server that is preventing the logon?

Ant-Green SSC Guru Points: 113445 More actions · Answer 9

Ant-Green

SSC Guru

Points: 113445

March 16, 2012 at 5:28 am

#1460434

sorry i ment at the root level

office 18838 SSChasing Mays Points: 625 More actions · Answer 10

Don't follow. Root level? Where do I need to look?

I see. I have found it now. It is just a page.

Does this mean that if I edit this correctly, the logon will work? Not sure exactly how to do it.

office 18838 SSChasing Mays Points: 625 More actions · Answer 11

Also, I cannot find anything like the "Log on as a service policy" that is on the local machine in the Domain Group Policy snap-in on the DC.

Ant-Green SSC Guru Points: 113445 More actions · Answer 12

if its just a page then its controlled locally and GP will not override any settings that you add in manually unless the folders in the tree are locked like they are on the screen shot attached as you wont be able to modify the settings

you should be able to just click on the add user or group button and add in the accounts

office 18838 SSChasing Mays Points: 625 More actions · Answer 13

Anthony,

Thanks so much for your help - your pointers helped us diagnose this and solve it with around only 30 hours total downtime in the end.

I managed to get the SQL server back on-line late on Friday by adding the accounts to the "Log on as a service" local security policy in the User Rigts Assignment section. However, there are several things to note for anyone else encountering this problem.

1. I tried setting up new accounts for these services to run in and also adding these to the local security policy but that did not work. It only worked by adding the existing login accounts (the ones that were in use before demotion) to the Security Policy on the local machine.

2. It was also necessary to add a number of other generic accounts to the "Log on as a service policy", such as LOCAL SERVICE, NETWORK SERVICE and a couple of other accounts like MSSQLServer. I found that these were required by checking the local secpol on another machine on the network.

3. While we managed to get the server back on-line it is rather limping along because 2 of the services are still not startable - the SQL Server Agent and the Fulltext daemon search service. I am sure we will eventually get these working but we still need to find the exact accounts used to launch and run these. If not we may migrate the server to another machine.

Overall it should be noted that this is not supported by Microsoft and the state clearly that before demoting a DC, SQL should be uninstalled (unfortunately this is not mentioned in any of the posts I have seen on demoting a DC).