BizTalk 2013 cannot send BAM Email Alerts when using SQL 2012 Database Mail

BizTalk 2013 cannot send BAM Email Alerts when using SQL 2012 Database Mail

If you configure BizTalk 2013 BAM Alerts service to run with a non-SQL admin user(not SysAdmin) with SQL 2012 DB Mail functionality to send alerts mails, you may notice the email alerts function does not work but file alerts are working well. Furthermore, all email alerts items are dropped into the table:

[BAMAlertsApplication].[dbo].[bam_Metadata_Distribution_FailedItems]

 

This behavior should be caused by the non-SysAdmin doesn't have proper permission to call sp_send_dbmail (Transact-SQL) https://technet.microsoft.com/en-us/library/ms190307.aspx

 

Accroding to the document, DatabaseMailUserRole role is required to call this stored procedure. Use the following SQL commands to grant the role to the BAM Alerts Service user and test if this resolves the problem.

 

use msdb

go

create user '%YourBAMAlertServiceAccount%'

go

exec sp_addrolemember 'DatabaseMailUserRole','%YourBAMAlertServiceAccount%'

go

 

 

Best regards,

WenJun Zhang