• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • Last Modified:

SQL Server agent & maintenance task issue ?

Hi All,

I'm currently running SQL Server 2012 Standard SP1 on my Windows Server 2008 R2 and have login as SA user, somehow I cannot start the SQL Agent from SSMS and cannot edit the SQL maintenance job with this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

'Agent XPs' component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (ObjectExplorer)

------------------------------
BUTTONS:

OK
------------------------------

Open in new window


Any help would be greatly appreciated.

Thanks,
0
Senior IT System Engineer
Asked:
Senior IT System Engineer
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
bbaoIT ConsultantCommented:
it is by design, because Agent XPs component has been disabled.

what to do? basically all info and action you need to know have been provided by the error message. :)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That error doesn't says that SQL Agent service can't be started. It says that a group of extended stored procedures (XPs) couldn't be enabled because of a security configuration.

You can verify if SQL Server Agent is running by checking the services in SQL Server Configuration Manager.
1
 
bbaoIT ConsultantCommented:
1
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
It's not SQL Server express, if it is, the agent is disabled and can not be turned on. This is by MS design. It doesn't come whit the free edition.
0
 
Daniel JonesData Research AnalystCommented:
Enable the SQL Server Agent extended stored procedures by running the following query:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Open in new window

1
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks guys, it seems the SQL Agent Service was stopped because of the service wa using Service account.
Not sure why, but when I change it as the local account it works again ?

Note: the service account is not locked.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
The agent account does require instance and database access (jobs are stored in msdb database). This is not the case for the SQL Server Service account.

Just to clarify.

Regards Marten
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thanks guys, it seems the SQL Agent Service was stopped because of the service wa using Service account.
Did you check if the SQL Agent account had permissions in SQL Server instance?
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now