SQL Agent starts then stop after SQL 2008 installation - by Philippe_R

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 624656 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/25/2010 9:53:08 AM
Access Restriction Public


Hi all,
Once SQL server installed we experience troubles while starting the SQL agent which starts then immediatly stops if we do not put it in the server local Administrators group. We use only AD service account (simple user, no special rights).  This may be caused by the FILESTREAM bug (not sure).

As a workaround to avoid putting the SQLagent in the local Administrators groups we put the AD service account in the SQLServerMSSQLUser$%COMPUTERNAME%$InstanceName group then restart the agent. Once restarted we remove the AD service account from the SQLServerMSSQLUser$%COMPUTERNAME%$InstanceName group and it therefore works fine without error. All this is done using PowerShell to automate installations (moreover 150 instances).

Is there another (better) way to solve this issue?
Sign in to post a comment.
Posted by Alex [MSFT] on 12/6/2010 at 10:08 AM

I understood from Yuhong, and based on your comments as well, that the issue was resolved. So I will go ahead and close it. You can always reactivate it if you need.

Thank you
Posted by Philippe_R on 12/6/2010 at 10:00 AM
In fact, issue was fully solved because of the ACL script fix plus the SP2 which fix another issue (the ! [298] SQLServer Error: 22022, Usage: EXECUTE xp_sqlagent_notify 'A', NULL, NULL,).
Posted by Philippe_R on 12/6/2010 at 5:39 AM
You're welcome Yuhong,

I stopped the SQLAgent then removed the SQL agent service domain account from local administrators group then renamed the Agent log file.

Starting the agent fails then and no new log file named SQLAGENT.OUT is created. For sure there's a file permission issue there which is definitively fixed by putting the agent service domain user account in the local SQLServerMSSQLUser$%COMPUTERNAME%$InstanceName group then starting the service then removing the account from the local group. Restarting then SqlAgent service works definitively fine afterwards.

Staing that point, I analysed twice the PowerShell code used to automate the command prompt installation and detected that we were only changing the registry keys to a new folder without setting the ACL rights afterwards.
Doing a get-ACL "$oldFolderSQLAGENTERROR" | set-ACL "$newFolderSQLAGENTERROR" solves the issue.

Thanks for your time and support you provided to help solving this case.

Posted by Microsoft on 12/3/2010 at 1:24 PM
Thank you for providing more information. Somehow the file "SQLAGENT.OUT" didn't reach to us, sorry.

So your SQL Agent service account is in sysadmin indeed, which is good. I noticed the timestamp "2010-02-25" in SQLAGENT.OUT from your previous post.

2010-02-25 09:25:22 - ? [131] SQLAgent$KMSX

It looks like an old SQLAGENT.OUT. I suspected some file permission on SQLAGENT.OUT prevents Agent from overwriting the old SQLAGENT.OUT, which may cause Agent unable to start.

Can you rename SQLAGENT.OUT to saying SQLAGENT_OLD.OUT? Then try to start Agent again and see what happens. If Agent still doesn't start successfully, please post the entire context of SQLAGENT.OUT here.



Posted by Philippe_R on 12/3/2010 at 12:42 AM
Hello Li,

1) I confirm that login is in sysadmin role. Here's an extract of the result of the query:
EXEC sp_helpsrvrolemember @srvrolename = 'sysadmin';

ServerRole    MemberName    MemberSID
sysadmin    sa    0x01
sysadmin    NT AUTHORITY\SYSTEM    0x010100000000000512000000
sysadmin    NT SERVICE\MSSQL$KMSX    0x0106000000000005500000003935797D1CA98ABDD4A6709F26B48D64BE6F0731
sysadmin    NT SERVICE\SQLAgent$KMSX    0x0106000000000005500000001A31BE9E7F5E6C93F58D5918AB4A563EE32ECA3C
sysadmin    dba    0x367708FDE11FD446B5E24BFC49201E57
sysadmin    DOMAIN\SVC_SQLAgent    0x0105000000000005150000008FE6C3645433CFB55080265A480B0100

2) I attached the complete SQLAGENT.OUT file.

Best regards,
Posted by Microsoft on 12/2/2010 at 3:33 PM
After you run restart-service, can you send us the file or the entire context of "SQLAGENT.OUT"?

It's true that there is no more context after "SQLServerAgent terminated". What we need is all the context before the last line of "terminated".

Also, can you check "NT SERVICE\SQLAgent$KMSX" is in sysadmin role? Or you can just run the following T-SQL and tell us the result.

EXEC sp_helpsrvrolemember @srvrolename='sysadmin'

"NT SERVICE\SQLAgent$KMSX" is expected to be in sysadmin.


Yuhong Li

Posted by Philippe_R on 11/30/2010 at 1:43 AM
Hi Alex,

1) result of the query:
name    type

2) Executed the following (RunAs Admin)
PS C:\> restart-service 'SQLAgent$KMSX'
WARNING: Waiting for service 'SQL Server Agent (KMSX) (SQLAgent$KMSX)' to finish stopping...
Restart-Service : Service 'SQL Server Agent (KMSX) (SQLAgent$KMSX)' start failed.
At line:1 char:16
+ restart-service <<<< 'SQLAgent$KMSX'
     + CategoryInfo         : OpenError: (System.ServiceProcess.ServiceController:ServiceController) [Restart-Service]
     , ServiceCommandException
     + FullyQualifiedErrorId : StartServiceFailed,Microsoft.PowerShell.Commands.RestartServiceCommand

And here's the related SQLAGENT.OUT content. No log after the SQLServerAgent terminated
2010-02-25 09:25:22 - ? [131] SQLAgent$KMSX service stopping due to a stop request from a user, process, or the OS...
2010-02-25 09:25:26 - ? [098] SQLServerAgent terminated (normally)

And trying to run the agent in verbose mode returns the following:
C:\Program Files\Microsoft SQL Server\MSSQL10.KMSX\MSSQL\Binn>SQLAGENT.EXE -c -v -i "MSSQL$KMSX"

Microsoft (R) SQLServerAgent 10.0.2531.0
Copyright (C) Microsoft Corporation, 1995 - 2008.

2010-11-30 10:27:30 - ? [098] SQLServerAgent terminated (normally)

Posted by Alex [MSFT] on 11/29/2010 at 1:12 PM

First of all, the service account that is used to start Agent is not necessary to be in the local Administrators group. Users are fine by not having the Agent service account in the local Administrators group.

We need more information to find the root cause why Agent service cannot be started on that machine.

1). Check SQL Server logins. We expect that Agent Service SID should be added in SQL Server logins. The Agent Service SID would be shown in SQL Server login as follows:

NT SERVICE\SQLSERVERAGENT    (if SQL server is a default instance)

Can you tell us if SQL Server logins contains Agent Service SID? Or you can send us the result of the following query and we can check it:

SELECT name,type FROM sys.server_principals

2)Check Agent log file “SQLAgent.OUT”. The log may report something why Agent service cannot be started. Usually the log file is located at:

%ProgramFiles%\Microsoft SQL Server\MSSQL10.<instancename>\MSSQL\Log\SQLAgent.OUT

Can you send us the Agent log file?

Thank you
Posted by Alex [MSFT] on 11/29/2010 at 10:34 AM
Thank you for reporting this issue - we are investigating and we will get back to you shortly.


Alex Grach