I recently set up a new SQL Server 2005 + service pack 2 installation in a Windows 2003 Server environment. The services were configured to start using the network service account (NT AUTHORITY\NetworkService). All of the services started successfully, except for SQL Server Agent.
Symptoms
A warning dialog will appear with the following error message:
The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
In Event Viewer, an error event is logged with the following description:
SQLServerAgent could not be started (reason: Error creating a new session).
The following appears in the SQL Server Agent log file (located at C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT on my system, but this will depend on the particulars of your installation):
2008-01-17 11:04:40 - ! [298] SQLServer Error: 15247, User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)Cause
2008-01-17 11:04:40 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-01-17 11:04:40 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_get_startup_info', database 'msdb', schema 'dbo'. [SQLSTATE 42000]
2008-01-17 11:04:40 - ! [298] SQLServer Error: 229, The INSERT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. [SQLSTATE 42000]
2008-01-17 11:04:40 - ! [000] Error creating a new session
2008-01-17 11:04:41 - ? [098] SQLServerAgent terminated (normally)
The account under which the SQL Server Agent runs does not have the permissions required to start the service successfully.
Solution
On my system, SQL Server Agent is configured to run as NT AUTHORITY\NetworkService. You will need to identify the user that runs SQL Server Agent on your system. To do this, open the Services administrative tool, right click on the SQL Server Agent (MSSQLSERVER) service, and choose properties. Click on the Log On tab, and note the user:
I gave this account the sysadmin server role in SQL Server. To do this, open SQL Server Management Studio and log in to the database engine. Drill down into Security, and then Logins. Right click on the user that runs SQL Server Agent, and choose properties. Under Select a page in the top left corner of the properties window, click on Server Roles. Enable the sysadmin role by clicking on the check box in the Server Roles pane, and then click on OK.
You should now be able to start SQL Server Agent successfully.
23 comments:
This is a good explanation.
Im getting the same error with SQL 2008 Express edition.
Finally found the reason!!
Agent log says...
This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.
Cheers
-- Eranga Priyakara
Spot on Don. Great little piece of advice - thanks heaps !
For work with sql files advise use-repairing sql server,tool helped me not once and as far as i know it is free,program can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.
Looking for this post very badly.
Thanks a lot .
I have got the same problem in SQL 2008 Express edition and I have check my settings. the settings is already as what you describe here.
i noticed that :
1. Not able to start SQL Agent when SQL server is started
2. trigger SQL Agent to start will start SQL server also, however, the started SQL Agent will then stopped automatically after SQL server is started.
could it be SQL agent service is disabled for SQLExpress 2008?
any comment?
To my mind sql files are very important in my PC.Because I work very often with it.And there is tool which works with it very good-mdf repair.It is free as far as I know,besides that it helped me twice.In addition program can too compatible with all supported operating systems, designed by Microsof.
Hi VoonChong,
SQL Server Express edition does not come up with SQL Server agent..
this solution in not valid for win 7 x64 and sql server 2008 sp1..
Really appreciate..
Thanks a lot.
Actually, I think the Agent is expected to run as the Local System Account. This already is already a member of the sysadmin role.
Thanks for pointing me in the right direction.
Thanks for this. Very helpful!
I was struggling to start SQL Server Agent for past 2 days browsing thru blogs and, finally, I resolved this issue thank to your your posting!
I simply checked the check box for 'sysadmin' role in SQL Server Agent Property window and it worked fine!
Thanks a lot!
double-check that you are trying to connect to the correct db type (SQL vs SQL Express. When you create a new database and you select server in drop down, type \SQLExpress after server name. If db is created then you are using SQLExpress and so can ignore disabled agent service as not required.
Thanks..Setting the sysadmin role for SQL agent logon account has worked. thanks a lot for this post.
Excellent work..
Thanks alot for the help
I had the same settings in my sql 2012. but this solution didnt help
Thank you VV Much for this explanation
sp_sqlagent_update_agent_xps
Seo tool: http://iclick.skyit/vn
thanks, you save my life :D
Solution
go to control panel , administrative tools , serivces
or
start - run
write services.msc
and
search for Error Reporting Service - double click and set startup type to automatic , and start it
search for Event Log - double click and set startup type to automatic , and start it
OMG. I looking for this answer for a lot time. Thanks bro.
ThanX, geat explanation! Solved the problem.
Post a Comment