How to Configure Full SQL Server to Send Email Alerts Using SQL Database Mail



The information in this article applies to Prism Suite 11.0 (and newer), Policy Commander 2.0 (and newer), and PwrSmart 3.2 (and newer) and it requires full SQL 2005, 2008, or 2012 with Database Mail enabled. SQL Express does not support Database Mail.


1.       On your Standard or Enterprise SQL Server 2005, 2008, or 2012 system, run Microsoft SQL Server Management Studio.

2.       Connect to and expand the SQL instance that Prism Suite, Policy Commander, or PwrSmart is using.

3.       Expand Management, right-click on Database Mail, and choose Configure Database Mail.
 

4.       Make sure the "Set up Database Mail by performing the following tasks:" radio button is selected and click Next >.  If you get a message about the Database Mail feature not being available, click Yes to enable it.

5.       For Prism Deploy or PwrSmart, enter a profile name of "PrismAlerts" and then click the Add... button to add a SMTP account. Note: If you have Policy Commander, you will need to repeat steps 5 through 9 to create a second Profile named "PolicyAlerts".

6.        a.       If you already have an SMTP account set up on that SQL Server that you wish to use, simply select it and click OK.
 b.      If you do not have an SMTP account already setup, click the New Account… button.  Enter a name, description, and attributes for an SMTP account that you wish to use and click OK.

 

7.       Click the Next > button.

 

8.       Put a check mark in the box in the Public column for the Profile that you just created and click Next >.

9.       Click Next >, Finish, and then Close when the SQL operations complete.

10.   Right-click on Database Mail and choose 'Send Test E-Mail...'  If you do not get the test email, edit the attributes of the SMTP account until the test email succeeds

11.   Expand Security and then Logins, view the properties of the login that the Policy Commander or New Boundary Channel Server is using. Typically it will be named DOMAIN\PD_COMPUTERNAME, COMPUTERNAME\PD_COMPUTERNAME, or COMPUTERNAME_PD_COMPUTERNAME.
 

12.   Select the User Mapping page.  

13.   In the 'USERS MAPPED TO THIS LOGIN' grid, check the box in the Map column for the msdb Database.

14.   In the 'DATABASE ROLE MEMBERSHIP FOR: MSDB' grid, check the box for DatabaseMailUserRole and click OK.

 

Your server is now configured to send email alerts.