How to Configure Email Alerts for Prism Suite

The information in this article applies to Prism Suite v. 11.0 and newer.

One of the new features in Prism Suite 11.0 is the ability to receive email alerts on two events: when a deployment task fails, and when a computer joins or leaves a configuration group. This KB article provides two options for configuring email.

Note: In order to use the new email feature, your channel must be secured (meaning a username and password is required to log in), and the users who wish to receive alerts must configure their email address in the Console. To secure the channel, open the Prism Console and go to Security|Configure Security. Check the Secure this channel box. Next create usernames and passwords and assign roles for the users who will have access to the channel. For each user who wants to receive email alerts, fill in their email address.

Option 1 – Database Mail (full SQL 2005 or 2008 only)

Full versions of SQL 2005 and 2008 (not SQL Express) use Database mail. The following KB article has instructions for configuring it. How to Configure Full SQL Server to Send Email Alerts Using SQL Database Mail

Option 2 – NBTSendMail (SQL 2005 or 2008, either full SQL or Express)

Note: If you are using SQL 2005 Express or SQL 2008 Express, you must install the SQL Management Studio Express component in order to configure NBTSendMail.

There are three steps to install and configure NBTSendMail: 1) running the SQL commands to install the required NBTSendMail stored procedure and NBTEncryptPassword function to the msdb system database, 2) giving the Prism Server account the necessary permissions to run the email components, 3) running SQL commands to insert your email settings into the EmailSettings table.

Step 1: Run SQL commands to install the required email components in the msdb system database.

If the SQL Server used for Prism is not on the same box as the Prism Server, copy the NbtSendMail.dll file from the Prism Server to the SQL server. By default, NbtSendMail.dll is installed to C:\Program Files (x86)\New Boundary\Server\NbtSendMail on the Prism Server.

In SQL Management studio, open a New Query window and run the following (see the note below – first set the local path to correct location of NBTSendMail.dll on the SQL Server):

EXEC sp_configure 'show advanced options' , '1';

reconfigure;

EXEC sp_configure 'clr enabled' , '1' ;

reconfigure;

EXEC sp_configure 'show advanced options' , '0';

reconfigure;

USE msdb

GO

--Note: if necessary, change the path to the location where the NbtSendMail.dll was copied.

CREATE ASSEMBLY NBTSendMail FROM 'C:\Program Files (x86)\New Boundary\Server\NbtSendMail\NBTSendMail.dll'

WITH PERMISSION_SET = UNSAFE

GO

CREATE PROCEDURE [dbo].[pr_NBTSendMail]

@server [nvarchar](128),

@port [int],

@timeout [int],

@enableSSL [int],

@serverUser [nvarchar](128),

@serverPass [nvarchar](128),

@recipients [nvarchar](4000),

@subject [nvarchar](4000),

@from [nvarchar](4000),

@body [nvarchar](4000)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [NBTSendMail].[NBTSendMail.NBTsproc].[pr_NBTSendMail]

GO

CREATE FUNCTION [dbo].[pf_NBTEncryptPassword]

(@password [nchar](20)) RETURNS nchar(128)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [NBTSendMail].[NBTSendMail.NBTudf].[pf_NBTEncryptPassword]

GO

To allow the Prism server to run pr_NBTSendMail, pf_NBTEncryptPassword

Step 2: Give the Prism Server account the necessary permissions for email functionality.

In SQL Management Studio:

1. Expand Security|Logins. Right-click the Prism server account (usually <computername>\PD_<computername>) and open its Properties.

2. In User-Mapping, check the box next to the msdb database (if it isn’t already checked). This maps the user into the msdb system database. Click OK.

3. Go back out to the main level. Expand Databases|System Databases|msdb|Programmability|Stored Procedures.

4. Right-click dbo.pr_NBTSendMail and open its Properties.

5. In Permissions, click the Add (or Search), then Browse, then check the box next to the name of your Prism Server account. Click OK twice.

6. Still in Permissions, click the checkboxes to Grant two permissions: “Execute” and “View Definition” to the Prism Server account. Click OK.

7. Go back to the main level, then expand Databases|System Databases|msdb|Programmability|Functions|Scalar-valued Functions.

8. Right-click dbo.pf_NBTEncryptPassword and open its Properties.

9. Perform the same actions as in steps 5-6 above to set the permissions for pf_NBTEncryptPassword. Click OK.

Step 3: Insert email settings into the EmailSettings table of the Prism channel database.

To add email settings to the database, you will execute the pr_SaveEmailSettings stored procedure. It accepts the following six parameters:

To add the email settings, open a New Query window SQL Management Studio and run the SQL EXEC command shown below .

*Important: Make sure you are running this query against your Prism Channel database, not the msdb database. For example, if you are evaluating Prism Suite and you installed the Quick Start Channel, the channel database name will be Prism_Deploy_Sample_<GUID>. If you create a new channel and you wish to receive email alerts for that channel, you will have to run the EXEC command again against that channel database.

Example 1 (SMTP server requires authentication)

EXEC dbo.pr_SaveEmailSettings 'smtpserver.mycompany.com', 'smtpuser@mycompany.com', 'smtppassword', 'fromuser@mycompany.com', 25, 1

Example 2 (SMTP server allows anonymous authentication)

EXEC dbo.pr_SaveEmailSettings 'smtpserver.mycompany.com', '', '', 'fromuser@mycompany.com', 25, 0

Note: If the EmailSettings table is empty, or no row has Enabled=1, the stored procedure will first try to Database Mail (as detailed in Option 1 above for full SQL only), and then optionally SQLMail (consult Microsoft’s documentation to configure SQLMail).

* Prism supports the SMTP Service Extension for Secure SMTP over Transport Layer Security as defined in RFC 3207. In this mode, the SMTP session begins on an unencrypted channel, and then a STARTTLS command is issued by the client to the server to switch to secure communication using SSL. See RFC 3207 published by the Internet Engineering Task Force (IETF) for more information. If the SMTP mail server does not advertise STARTTLS in the response to the EHLO command, then the call will fail.

An alternate connection method is where an SSL session is established up front before any protocol commands are sent. This connection method is sometimes called SMTP/SSL, SMTP over SSL, or SMTPS and by default uses port 465. This alternate connection method using SSL is not currently supported.