Persistent Database Mail Queue

Persistent Database Mail Queue

February 5, 2017

So we ¬†have this ancient SQL Server 2005 instance sitting in some high security environment. I want to have mails fire daily with details of health checks but I find my mails simply queue, reporting ‘unsent’.

I do the regular checks and they look OK. Mail queue is started and inactive. Database Mail XPs is enabled.

  • sysmail_help_status_sp
  • sysmail_help_queue_sp @queue_type = ‘Mail’ ;
  • exec sp_configure ‘Database Mail XPs’

Further checks on OS level show the following error in Event Viewer:

/*
Database Engine Instance=XXXXXXXXX;Mail PID=6792;Error Message:
The update to the database failed. Reason: The EXECUTE permission was denied on the object
‘sysmail_logmailevent_sp’, database ‘msdb’, schema ‘dbo’.
*/

/*

1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: There was an error on the connection. Reason: Login failed for user ‘<DOMAIN>\sqlserverpost’., connection parameters: Server Name: <SERVER-NAME>, Database Name: msdb
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)

2) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 14
LineNumber: 65536
Number: 18456
Procedure:
Server: <SERVER-NAME>
State: 1
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: Login failed for user ‘<DOMAIN>\sqlserverpost’.
Data: System.Collections.ListDictionaryInternal
TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)
HelpLink: NULL

StackTrace Information
===================
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
*/

So guess what, I did it the ‘quick’ way. I gave the SQL Server Service Account sysadmin! Yeah, I know, you thought I was going to say the SQL Server Agent Account. Well NO, because databasemail.exe is an external process which logs in as the service account to do its job.

Igiri Books © Copyright 2016. All Rights Reserved. Site Credits: AppWorld