2

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.

  • Please mention the complete error message – KASQLDBA May 21 '15 at 9:15
  • Also, is the user you mentioned : added to the logins in security tab when you expand SSMS? – KASQLDBA May 21 '15 at 9:19
  • Sorry for the translation: [298] SQL-Server error:18456 error logging on user domain\sqlagent, [SQLSTATE 28000]. The user is db admin and enabled – Crossover May 21 '15 at 9:24
  • So is there a issue in starting sql agent? or jobs failing with error you mentioned? – KASQLDBA May 21 '15 at 9:33
  • Agent can be started and restarted without problems. A test job is running successfully with notification set to 'Email operator when the job completes'. Operator e-mail address, account, profile etc. set correctly, but no mail is sent, while 'Send Test E-Mail...' succeeds. – Crossover May 21 '15 at 9:45
3

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)
  • FYI - it was the last step. The mail profile was private. Also removed the dom admin privilege. (UPVOTE HERE, reputation too low) – Crossover May 21 '15 at 10:18
  • I did the Upvote on you're behalf for Sporri: Nice catch! – KASQLDBA May 21 '15 at 10:37
0

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!

  • Thanks for your patience. BUILTIN\Administrators (German: VORDEFINIERT\Administratoren) exist, have sa and are enabled. – Crossover May 21 '15 at 9:52
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.