SQLSERVERAGENT service fails to start , SQLServer Error: 229


Issue

The SQL agent services fail to start and give the following error when we try starting it from the  services console :

 

When we attempt to start SQL agent services we get the following error :

---------------------------

Services

---------------------------

The SQLSERVERAGENT 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.

---------------------------

OK

--------------------------

 

 

Environment

Windows 2003

Service Pack 2

SQL Version: SQL2000 Enterprise Edition

SQL Service Pack: RTM

SQL Build: 8.00.2039

SQL Platform: x86

SQL Server Name: HRSERVER

SQL Instance Name: default

 

 

 

·         We collected the SQL agent logs and saw the following errors

2010-10-24 16:51:19 - ! [298] SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_has_server_access', database 'msdb', owner 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

2010-10-24 16:51:19 - ! [298] SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_get_startup_info', database 'msdb', owner 'dbo'. [SQLSTATE 42000]

2010-10-24 16:51:19 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID

2010-10-24 16:51:19 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID 3696

2010-10-24 16:51:19 - ? [101] SQL Server  version 8.00.2039 (0 connection limit)

2010-10-24 16:51:19 - ? [102] SQL Server ODBC driver version 3.86.3959

2010-10-24 16:51:19 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is

2010-10-24 16:51:19 - ? [310] 8 processor(s) and 2048 MB RAM detected

2010-10-24 16:51:19 - ? [339] Local computer is HRSERVER running Windows NT 5.2 (3790) Service Pack 2

2010-10-24 16:51:19 - ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role

2010-10-24 16:51:19 - ? [098] SQLServerAgent terminated (normally)

 

·         Just a test changed the startup account from the local system to a  domain account and subsequently added it to the sysadmin server role which enabled SQL agent services to start up

 

·         SQL server services inspite of having local system account as its startup , was working fine but SQL Agent on the other hand failed to startup with local system account

 

·         We then  Checked the following permissions for the service account under secpol.msc (local security policy)

                • Act as Part of the Operating System

                • Bypass Traverse Checking

                • Lock Pages In Memory

                • Log on as a Batch Job

 • Log on as a Service

 • Replace a Process Level Token

 

 

·         We added the "SYSTEM" account to all these permissions and we were able to start it up successfully

Regards,

Amrutha

Comments (0)

Skip to main content