Last week on one of the SQL Server 2000 instances, the SQL Server Agent service was not starting. This server was on the same domain in which the SQL Server Agent was connecting to SQL Server using sa login and jobs were not running as scheduled. DBA checked if the same issue had happened here. But on this server, SQL Server Agent was connecting to the SQL Server instance using Windows Authentication. The SQL Server Agent error log had the following messages.
2011-01-11 15:01:42 - !  SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_has_server_access', database 'msdb', owner 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2011-01-11 15:01:42 - !  SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_get_startup_info', database 'msdb', owner 'dbo'. [SQLSTATE 42000]
2011-01-11 15:01:42 - ?  Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID
2011-01-11 15:01:42 - ?  Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID 220
2011-01-11 15:01:42 - ?  SQL Server version 8.00.2055 (0 connection limit)
2011-01-11 15:01:42 - ?  SQL Server ODBC driver version 3.86.3959
2011-01-11 15:01:42 - ?  NetLib being used by driver is DBMSSHRN.DLL; Local host server is
2011-01-11 15:01:42 - ?  8 processor(s) and 2048 MB RAM detected
2011-01-11 15:01:42 - ?  Local computer is ServerName running Windows NT 5.2 (3790) Service Pack 2
2011-01-11 15:01:42 - !  SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role
2011-01-11 15:01:43 - ?  SQLServerAgent terminated (normally)
Some days back we had implemented a Change Control to revoke sysadmin privileges for the BUILTIN\Administrators login on this instance. It usually happens that the SQL Server service accounts are part of the Local Administrators group on the Operating System and the DBA forgets to add those logins explicitly while revoking access for Builtin\Administrators. However on this instance, the Domain group which had the SQL Server service accounts as its members was granted sysadmin privileges on the instance and SQL Server Agent was correctly configured to start using the same account.
Even though the service account had sysadmin privileges, the SQL Server was not able to validate it. Moreover from the error message it looked as if a null value was passed for the login name while connecting to the instance.
SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role
As a knee-jerk reaction we planned to restart the SQL Server service assuming that the instance was not able to connect to the Active Directory to validate the service account login. Since this is a Production instance restarting the SQL Server service required several approvals for the Change Control. Hence it was decided to temporarily change service account of SQL Server Agent and enter the correct service account again.
After entering the same Service Account details again in the SQL Server Agent properties, the following error messages were displayed.
It was now confirmed that the SQL Server Agent service was not able to authenticate the service account details with Active Directory. On all the servers on this domain, the service accounts were entered as email@example.com. The second error message indicated that the service account details in this format could not be verified and it also suggested try changing the Service Account to domian\serviceaccount format. This domain was part of a Domain Forest which had several child domains in it.
Going by the error message, we entered the service account details as domain\serviceaccount and the SQL Server Agent service started successfully!