Wednesday, April 27, 2011

Fun with SQL Server DatabaseMail and Service Accounts

If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.

Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).

a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.

b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB

Above was the solution to our issues.

Cheers – J

Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx

Other hints:

+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?

+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;

Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp

What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';

Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'

What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'

SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems

No comments: