SQL-Server login-error of SQL-Server Agent Account

Question

I have to manage a configured SQL-Server version 2005 and have setup Database Mail successfully - test mail works.

Also I've changed the agent service to a domain user with (currently) domain admin and db admin privileges.

Now I'm getting numerous login errors for this user

[298] SQL-Server error:18456 error logging on user domain\sqlagent, [SQLSTATE 28000]

when running jobs with notification. Any idea how that can happen, when the agent service can be restarted without problem?

I've searched the web extensively but the only answers I can find are related to wrong passwords.

Answer 1

The user account that is running the SQL Server agent might not have permissions to use the database mail profile you can check that with EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

But you are having some issues with the agent account security, please try the following steps.

• Remove all extra privileges from the Agent user account (or create a new domain account) Running the SQL Server agent with domain admin privs is just terrifying.
• Change the user for the Agent service using the SQL Server Configuration Manager. You should never change it any other way as it will set all the necessary permissions. Change the account to network service and then back again and make sure beforehand that the agent user is neither a local or domain administrator.
• Check the mail profile on the SQLAgent settings and make sure that it's not private and that the agent user account can use it (The user account will be made sysadmin by the Configuration manager and should be able to use all accounts)

Answer 2

Possible cause you may be seeing this error is:

Either The BUILTIN\Administrators has been removed from the Logins (Security-> Logins) section or BUILTIN\Administrators does not have sufficient priviliges to access the SQL server.

As per MS

The issue does not occur if the SQLAgent service is starting under a domain or computer account that is listed separately in SQL Server Enterprise Manager and that has System Administrators (sysadmin) privileges.

So, if per above BUILTIN\Administrators does not exist, you need to add:- Go to \Servername\Security\Logins and grant access to the BUILTIN\Administrators user

Again referring from MS article for such error :

If you really want to deny access to SQL Server by Windows NT administrators, you can remove the BUILTIN\Administrators account. First add the login account you are using for the SQL Agent Service as a SQL Server login and assign the System Administrator (sa) role to that account. Then either revoke the System Administrators (sysadmin) role permissions from the BUILTIN\Administrators account, or delete the BUILTIN\Administrators account completely from the Logins in SQL Server Enterprise Manager.

Check if above helps!

Answer 3

I had the same problem, and after hours spent I realized that somehow some of the logins were not created. I know the error is misleading, but I compared a working instance of an SQL Server and the one with this error and indeed there were missing logins.

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO 

CREATE LOGIN [NT Service\MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO


CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

CREATE LOGIN [NT SERVICE\SQLWriter] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

CREATE LOGIN [NT SERVICE\Winmgmt] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT Service\MSSQLSERVER]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\SQLSERVERAGENT]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\SQLWriter]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\Winmgmt]
GO

This will create the necessary logins, and then tries starting the SQL server agent again.