Tips for troubleshooting SQL Agent Error: 15281
We encountered an interesting problem the other day I thought I would post on ReplTalk. Customer call when they noticed the SQL Server Agent resources in their SQL 2008 clustered installation would not start. The following error appeared in the Windows Application and System Event log.
Windows Application Event Log
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect
to SQLServer as SysAdmin, but ‘(Unknown)’ is not a member of the SysAdmin role).[sqagtres] StartResourceService: Failed to start SQLAgent$SQL2K5 service. CurrentState: 1
[sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435)
[sqagtres] OnlineThread: Error 435 bringing resource online.
Windows System Event Log
The Cluster service failed to bring clustered service or application ‘SQL Server (SQL2K5 )’ completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application.
Cluster resource ‘SQL Server Agent (SQL2K5 )’ in clustered service or application ‘SQL Server (SQL2K5 )’ failed.
We also looked in the MSSQL\LOG folder for SQL Server to see what error messages SQL Server Agent was reporting. Here is where we noticed Error: 15281.
Sqlagent Log File
================
2010-08-17 14:16:32 – ! [298] SQLServer Error: 15281, SQL Server blocked access to procedure ‘dbo.sp_sqlagent_has_server_access’ of component ‘Agent XPs’ because this 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. [SQLSTATE 42000] (ConnIsLoginSysAdmin)2010-08-17 14:16:32 – ! [298] SQLServer Error: 15281, SQL Server blocked access to procedure ‘dbo.sp_sqlagent_get_startup_info’ of component ‘Agent XPs’ because this 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. [SQLSTATE 42000]
2010-08-17 14:16:32 – ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but ‘(Unknown)’ is not a member of the SysAdmin role <<<< permissions ? >>>>>
2010-08-17 14:16:33 – ? [098] SQLServerAgent terminated (normally)
From the SQL Agent output log it looks like the SQL Server Agent account doesn’t have the correct permissions to connect to SQL Server. We logged on using the SQL Server Agent service account and we we’re able to connect to SQL Server using the SQL Server Management Studio utility. When connecting to SQL Server using the SQL Server Agent account we also wanted to verify the account had access rights to the MSDB system databases. In particular, we wanted to see if the SQL Agent account could SELECT from the sys.JOBS table which contains the listing of SQL Server Agent Jobs to be executed on this server.
Well this turned out to be a dead-end. The SQL Service Agent account was able to access the MSDB database. Next we happen to look in the SQL Server Errorlog and noticed the following message:
Recovering only master database because traceflag 3608 was specified.
Looking at the SQL Server Configuration Manager we discovered the SQL Server had been started with Trace Flag –T3608 which bypasses the recovery of all databases except Master. During started, the MSDB database would not have been available for SQL Server Agent.
This trace flag was added for troubleshooting an unrelated problem. Once we removed this trace flag SQL Server started, recovered all databases, and SQL Agent was able to connect.
