none
SQLSERVERAGENT service started manually but then stopped

    Question

  • The following is what I found in SQLAgent.out:

    2018-03-09 10:41:41 - ? [100] Microsoft SQLServerAgent version 13.0.1728.2 Process ID 16432

    2018-03-09 10:41:41 - ? [495] The SQL Server Agent startup service account is NT Service\SQLSERVERAGENT.
    2018-03-09 10:41:41 - ! [150] SQL Server does not accept the connection (error: 0). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
    2018-03-09 10:41:41 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
    2018-03-09 10:41:41 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
    2018-03-09 10:41:41 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)
    2018-03-09 10:41:41 - ? [098] SQLServerAgent terminated (normally)

    I have installed msodbcsql. 

    Looking forward to your help.

    Thanks & Best,

    Michelle

    Friday, March 09, 2018 3:47 AM

All replies

  • Hi MichelleMei,

    When did you meet this problem? What about this instance? A named instance or default instance? Which version of SQL Server did you use?

    Please try to use another account which has admin permission for SQL Server agent, or trying to repair the installation.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 12, 2018 8:43 AM
  • Hi Teige,

    Thank you for the reply. I have just completed an upgrade to SQL Server 2017. But actually, before the upgrade I have also had the same problem. It happened every time when I start the SQL Server Agent.

    And yes, it's the default instance. NT Service\SQLSERVERAGENT is the default account with admin permission for SQL Server Agent. I changed once through Configuration Manager its password which was set already when I changed it for the first time. Not sure if that is the reason why I cannot get connect. I have tried also to use administrator as the account for log on, but I don't know which password to be put into the text box for password. And it seems that the SQL Server Configuration Manager rejects for me to make a change like this.

    By the way, the version for ODBC Driver is 14.0.1000.169, which is the same as SQL Server Agent. I search on line and find a solution which says that the problem may caused by the mismatch of ODBC Driver then I installed ODBC 13.1 after uninstallation of 14.0.1000.169. However, the problem still exists. 

    Other features of SQL 2017 installed work well.

    Thanks again for your help and looking forward to your help.Best Regards,

    Michelle


    • Edited by MichelleMei Tuesday, March 13, 2018 8:20 AM
    Tuesday, March 13, 2018 8:18 AM
  • Some more information added...

    whether I add virtual server's name as value in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent\ServerHost, the problem remains.

    Tuesday, March 13, 2018 1:35 PM
  • Checked again the Errorlog and found the following message...

    Error: 18456, Severity: 14, State: 5.
    Login failed for user 'NT Service\SSISScaleOutMaster140'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

    Really looking forward to your help.

    Thanks,

    Michelle

    Wednesday, March 14, 2018 10:09 AM
  • What is output of select @@version

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, March 14, 2018 11:07 AM
    Moderator
  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
    Wednesday, March 14, 2018 6:29 PM
  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)

    This is NOT the complete output I was expecting complete result. Could you also apply Latest CU that is CU4. Plus some more errorlog information would also help to understand why agent is failing to come online. Is this SQL Server cluster ?

    Plus what is service account of Agent, can you change service account to local system and try restarting it for timebeing.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, March 15, 2018 6:12 AM
    Moderator
  • Hi Shashank, 

    Thank you so much for the reply and suggestions. I have installed CU4 for SQL 2017. Although the problem still exists, I think the update of CU4 is really helpful.

    For the account I used for Sql Server Agent, NT Service\SQLSERVERAGENT is the default one with admin permission. Actually, I have used SQL Server Configuration Manager to change it into some other different account, for example, local system, every time I received an error message which says that the process terminated unexpectedly.[0x8007042b]. When I exited and reopened Configuration Manager, I found always the change had been completed. Even so, I cannot start Sql Server Agent successfully. 

    And below is what I found in Errorlog:

    2018-03-15 23:28:34.62 Logon       Error: 18456, Severity: 14, State: 5.
    2018-03-15 23:28:34.62 Logon       Login failed for user 'NT Service\SSISScaleOutMaster140'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

    I also attached here something from the masterservice log.

    [Master Service][{null}][Error][DESKTOP-........][24][{null}][{null}]
    [2018-03-15T15:29:17.8168418+00:00]  Failed to keep alive worker agent '83b1dabf-........-e617980c081e' in SSISDB
    System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT Service\SSISScaleOutMaster140'.

    at Microsoft.SqlServer.IntegrationServices.ScaleOut.Utilities.SQL.SqlConnectionWrapper.

    Execute(Action`1 action, Boolean waitForBlock)
    at Microsoft.SqlServer.IntegrationServices.MasterService.ClusterManagement.<>c__DisplayClass2.

    <KeepAlive>b__0()

    at Microsoft.SqlServer.IntegrationServices.MasterService.MasterServiceHelper.TryHandleRequestAction[T](Func`1 action, Int32 errorCode, String errorMessage, String source)
    ClientConnectionId:dcae1be4-421d-4a96-b9e4-97165fe0fddc
    Error Number:18456,State:1,Class:14

    Thank you again for your help. Have a great day.

    Michelle 

    Thursday, March 15, 2018 4:12 PM
  • You still did not gave me the output 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, March 15, 2018 5:28 PM
    Moderator
  • Here is...

    Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) - 14.0.3022.28 (X64)   Feb  9 2018 19:39:09   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 16299: )

    Thanks again,

    Michelle 

    Thursday, March 15, 2018 7:16 PM

  • Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) - 14.0.3022.28 (X64)   Feb  9 2018 19:39:09   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 16299: )

    Thanks again,

    Michelle 

    You have enterprise edition of SQL Server 2017 and it is not supported on windows 10 OS. This is a client OS with lot of limitations having said that you can install SQL Server 2017 ent on windows 10 but issues may pop every now and then. Again what is point in wasting such a costly license on windows 10.

    From the logs it seems like some windows 10 bug. Because when you are trying to change service account with SQL Server configuration manager it is failing and still at the end it is able to change.



    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 16, 2018 7:10 AM
    Moderator