SQL Agent cannot start

Question

SQL Agent cannot start

Vedran Kesegic

SSCrazy Eights

Points: 9302
More actions
October 3, 2012 at 9:13 am

#263397

After clean install of SQL Server 2012 RTM Developer edition, SQL Agent fails to start with "access denied" error.
The default user setup has set is "NT SERVICE\SQLSERVERAGENT".
When I change the agent user to LOCALSYSTEM, it can start without problems.
I checked windows permission in local security policy for "NT SERVICE\SQLSERVERAGENT", and they are ok. Also, that account is sysadmin in sql. Obviously, some permission is not set correctly by installer, but I don't know what else to check.
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is the computer part of an AD network that might deny permissions, even if they're okay locally? I think that can be done.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 2

Thanks. Computer is on a domain, but none of the required privileges is denied from AD.

I also checked folder permission, as stated in doc here: http://msdn.microsoft.com/en-us/library/ms143504%28v=SQL.110%29.aspx

And found that this permissions were missing (for some reason, they were NOT set by the installer):

Instid\MSSQL\Log - Delete

110\com - Read, Execute

110\shared - Read, Execute

110\shared\Errordumps - Read, Write

ServerName\EventLog - Full control - CANNOT FIND THAT FOLDER - DOES NOT EXISTS?

Where that "EventLog" map should be ?

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

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

NT SERVICE\SQLSERVERAGENT is the virtual group which the account running the SQL Agent Service is granted access to so that you dont have to mess with SQL permissions.

From what I remember you cannot set a service to run as this virtual account.

What account is running the service?

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 4

After the initial setup, virtual account "NT SERVICE\SQLSERVERAGENT" was set to run sql agent service, as default.

But, under that account sql agent would not start (access denied).

After I changed to "LOCALSYSTEM", sql agent service started successfully.

If I try to change back to "NT SERVICE\SQLSERVERAGENT" I get "Access denied" as soon as I click on "Apply" button in the service "Log On" dialog.

Even though I got "access denied" the account indeed was changed from "LOCALSYSTEM" to "NT SERVICE\SQLSERVERAGENT",

but cannot start (Access denied).

Just for the test, i tried to give "Full control" on all drive letters to "NT SERVICE\SQLSERVERAGENT",

and still got "Access denied" from the sql agent service.

Although I could not give full control to Program Files, Program Files (x86), and Windows - "Access denied".

I am logged-in windows with my domain account which has local admin rights.

I even tried with SUBINACL:

C:\Program Files (x86)\Windows Resource Kits\Tools>Subinacl /service eventlog /grant=SQLSERVERAGENT=S

eventlog : new ace for nt service\sqlserveragent

eventlog : 1 change(s)

Elapsed Time: 00 00:00:00

Done: 1, Modified 1, Failed 0, Syntax errors 0

Last Done : eventlog

C:\Program Files (x86)\Windows Resource Kits\Tools>

But still cannot start agent service. Note "1, Failed" in the suinacl output.

Do you know where the map "ServerName\EventLog" should be, or is on your server?

Maybe that would help, because it seems I don't have that folder at all (Win7 64bit).

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

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

Please attach a screen shot of how you are managing to assign nt service\sqlserveragent to run the service within SQL config manager or services.msc as I cannot replicate it.

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 6

Click "Browse", and type a user. Change location to local host, then "Check names":

Click OK. Leave blank password:

If I click "OK" or "Apply", I receive "Access denied" immediately.

Service account is changed, but service cannot start.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

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

Yes that is the problem, NT SERVICE\SQLSERVERAGENT your referencing is not an account is a virtual group, you need to specify an account not a group to run a service.

What ever account you specify in the service gets added to that group.

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 8

Virtual account NT Service\SQLSERVERAGENT is the default account installer sets for sql agent, out of the box.

There is no reason that this account should not work.

For example, NT Service\MSSQLSERVER is default virtual account for sql engine and it is happily running under it,

as you can see on this screenshot:

Agent cannot start, and does not even generate SQLAGENT.OUT file.

That account has appropriate permission on the SQLAGENT.OUT file and the LOG map.

Could it be something with AD ?

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

a.ho SSC Journeyman Points: 89 More actions · Answer 9

All,

I have the same problem. Can anyone help ?

Angus

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 10

Try uninstalling completely and installing some other edition/version of SQL2012.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

tstewar Valued Member Points: 53 More actions · Answer 11

First of all, please disregard everything anthony.green wrote. It is all wrong. He may be referring to groups set up for SQL Server 2005, it isn't clear. In any case, SQL Server and SQL Agent both run under NT Service accounts unless you change them (say because you need to access network items within the SQL Server or SLQ Agent umbrella) during install or afterwards in service properties.

For some reason, by default, SQL Server 2012 installs so that the NT Service\SQLAgent$<Instance> account does not have access to the BINN folder. The only means that I have found to fix the issue is to hack through the default permissions down to the binn folder (see the executable path in the properties for the service) and grant read/execute permissions to the NT Service ID (you may have to change file ownership in this folder to make this happen). This install program seems to set them for the corresponding MSSQL$<Instance> ID.

Chuck Lathrope Mr or Mrs. 500 Points: 568 More actions · Answer 12

I had same issue on windows 2012 R2 server and SQL 2012 SP2 install base. I had to manually grant the nt service\SQLServerAgent rights as shown in this article: https://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx I didn't have to give special rights to Event log (I tested job failure writing to app log and it did without any custom changes). I wish the installer would just work. I did get an error on install and tried to have sql config manager update to same account again, but it didn't work.

HTH someone in future,

Chuck