2

On my localhost, I am running 2014 (not express). I need to create a job, and for that, I need the Agent service to run. But I can't get it started.

I have been googling the snot out of this, but so far no luck.

In the Windows Logs, under System, I have only this:

The SQL Server Agent (SQL2014) service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.

and

A timeout was reached (30000 milliseconds) while waiting for the SQL Server Agent (SQL2014) service to connect.

and

The server {1B1F472E-3221-4826-97DB-2C2324D389AE} did not register with DCOM within the required timeout.

Not very helpful. I found the SQLAGENT.OUT file, but there is nothing recent in it. The last entry is from over a month ago. None the less, this is the contents of the file:

2017-10-26 15:35:51 - ? [100] Microsoft SQLServerAgent version 12.0.5207.0 (X64 unicode retail build) : Process ID 4980
2017-10-26 15:35:51 - ? [495] The SQL Server Agent startup service account is NT Service\SQLAgent$SQL2014.
2017-10-26 15:35:56 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2017-10-26 15:35:57 - ? [000] 
2017-10-26 15:35:57 - ? [101] SQL Server EBDEVELOPER\SQL2014 version 12.00.5207 (0 connection limit)
2017-10-26 15:35:57 - ? [102] SQL Server ODBC driver version 11.00.2100
2017-10-26 15:35:57 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is EBDEVELOPER\SQL2014
2017-10-26 15:35:57 - ? [310] 8 processor(s) and 16315 MB RAM detected
2017-10-26 15:35:57 - ? [339] Local computer is EBDEVELOPER running Windows NT 6.2 (9200) 
2017-10-26 15:35:57 - ? [432] There are 13 subsystems in the subsystems cache
2017-10-26 15:35:58 - ? [129] SQLAgent$SQL2014 starting under Windows NT service control
2017-10-26 15:35:58 - + [475] Database Mail is not enabled for agent notifications.
2017-10-26 15:35:58 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2017-10-26 15:37:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:38:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:39:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:40:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:41:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:41:33 - ? [131] SQLAgent$SQL2014 service stopping due to a stop request from a user, process, or the OS...
2017-10-26 15:41:35 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2017-10-26 15:41:35 - ? [098] SQLServerAgent terminated (normally)

(The xxxxx was the name of the former developer who used to use this laptop. I am his replacement. For his sake, I removed his name.)

Following some advice I found on other threads, I ran this against the master database:

sp_configure 'show advanced options','1'
RECONFIGURE
go

sp_configure 'Agent XPs','1'
RECONFIGURE
go

It returned this:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.

Service still would not start (although it looks like nothing changed, so I wasn't surprised).

I've tried to start it from within the normal Windows Services, and from within SQL Server Configuration Manager.

In the Properties, I have tried to logon as all of the options on the Built-in account dropdown. I've also tried the admin account I use to connect to the DB from within SSMS.

The service is set to Start Type automatic.

I'm not sure what else to try. Any suggestions?

12
  • 1
    Change the sql server service & Agent account to use local system.
    – Kin Shah
    Nov 29 '17 at 15:40
  • @kin, sorry, I wasn't clear in my OP. I said, "In the Properties, I have tried to logon as all of the options on the Built-in account dropdown." I should have been more specific and said: "I have tried Local System, Local Service, and Network Service." Nov 29 '17 at 15:41
  • @Kin, the SQL Server service is running with this account: NT Service\MSSQL$SQL2014. I would try that for the Agent service, but I have no idea what the password is. Should I stop the SQL Server agent and re-start it with the Local System account? Nov 29 '17 at 15:44
  • When you change the account, it will stop and restart the service.
    – Kin Shah
    Nov 29 '17 at 15:56
  • So just to be clear, I should change the account for the SQL Server service. Is that correct? (I'm nervous to try this.) Nov 29 '17 at 16:02
2

Possibly the SQL Server Agent service was previously running under the old developer's account 'xxxxx'.

The developer left and his/her account was deleted in Active Directory.

The service will no longer start.

Solution

  1. Start the SQL Server Configuration Manager
  2. Right-click the SQL Server Agent (INSTANCE_NAME) service to bring up the properties.
  3. In the properties change the service account to start with either your Windows account or switch to "Local System".
  4. Close the properties
  5. Start the service in Configuration Manager.

Note:
It is important to change these settings using the SQL Server Configuration Manager and not with the normal Windows Services Management Console. The reason being that SQL Server Configuration Manager will ensure that all the directory/file permissions and SQL Server permissions are set correctly.

4
  • In the SQL Server Configuration Manager and from step 1 to step 5? Because if you switched between Windows Services management console and SQL Server Configuration Manager, then you will have issues.
    – John K. N.
    Nov 29 '17 at 15:57
  • Yes, I only changed the "Log on as" in the SQL Server Configuration Manager. Not in Windows Services. From Windows Services I only tried to start the service, and nothing more. Nov 29 '17 at 15:59
  • ...or the simple way. 1. Reboot Laptop. 2. Login. 3. Open SQL Server Configuration Manager and nothing else. 4. proceed with solution (and start service in SQL Server Configuration Manager). Seeing as you might have already half changed stuff, consider changing the account to your Windows Account, finalizing and the going back to the "Local System" account and all the while using the SQL Server Configuration Manager.
    – John K. N.
    Nov 29 '17 at 16:00
  • Nope. Same result. Nov 29 '17 at 16:13
0

Try this, and it will most likely resolve your issue. I bet your security settings of Component Services, Computers, and properties of My Computer on the COM Security tab most likely doesn't include a group or user needed to start the service.

  1. Enter dcomcnfg in run, and it will open up Component Services
  2. Expand component services, expand computers, expand My Computer
  3. Right-click My Computer, and then click Properties
  4. Click the COM Security tab, and confirm that the Access Permissions and Launch and Activition Permissions properties are set properly.
2
  • Thanks! Can you help me understand what "set properly" would be? In both cases, EVERYONE has full control. Nov 30 '17 at 16:56
  • So it has the Everyone default group in there? OK, I would then try to add the AD service account or even a group that the service account belongs to. Then make sure the Allow check box is selected.
    – H.79
    Nov 30 '17 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.