Database Mail on SQL Azure

As requested by some people, here is the English version of my previous post on Database Mail in Italian.

Among the SQL Server 2008 features not available in SQL Azure, as shown in the MSDN, there is the absence of all those related to SQL Agent.
Database Mail, which lets you send emails from the database engine using some stored procedures in msdb, is one of these.
Creating a mechanism to queue e-mail messages in a SQL Azure database and send them through a worker role is easy, but I would like to go further, making the existing code using Database Mail compatibile with SQL Azure.
In this post we will see how we can change the system stored procedures to make them run on SQL Azure. We will consider only the most common stored procedures. All others, if necessary, may be ported in the same way.

Obtaining the original SQL Server 2008 scripts
Obtain the original code of the stored procedure is quite simple. Just open the msdb database with SSMS, left click on the object you want to change and choose Modify on the context menu. For almost all items it was possible to obtain the code to be modified this way, except for the dbo.get_principal_id and dbo.get_principal_sid functions and the xp_sysmail_format_query extended procedure. The first two have been easily rewritten, while for the extended procedure xp_sysmail_format_query, given the purpose of the post, it was decided to not to proceed. This procedure is used to append to the body or attach a file with the result of a query. If you really need it, you will need to write the code that formats the output of a query as a string representing the output table.

Managing profiles
Messages are sent using profiles, which contain all the account information and the SMTP server address. They also allow for a redundancy mechanism that ensures that your message is sent, no matter if a SMTP is down or your account has expired. Typical operations for the creation of profiles are:

— Creating a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘AdventureWorks2008R2 Public Account’,
@description = ‘Mail account for use by all database users.’,
@email_address = ‘db_users@Adventure-Works.com’,
@replyto_address = ‘danw@Adventure-Works.com’,
@display_name = ‘AdventureWorks2008R2 Automated Mailer’,
@mailserver_name = ‘smtp.Adventure-Works.com’ ;

— Creating a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘AdventureWorks2008R2 Public Profile’,
@description = ‘Profile used for administrative mail.’ ;

— Adding an account to profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘AdventureWorks2008R2 Public Profile’,
@account_name = ‘AdventureWorks2008R2 Public Account’,
@sequence_number =1 ;

— Granting profile access to users
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘AdventureWorks2008R2 Public Profile’,
@principal_name = ‘public’,
@is_default = 1 ;

Stored procedures involved, along with their dependencies are:

• sysmail_verify_accountparams_sp
• sysmail_verify_profile_sp
• sysmail_verify_account_sp
• sysmail_verify_principal_sp
• sysmail_add_profile_sp
• sysmail_add_profileaccount_sp
• sysmail_add_principalprofile_sp
• sysmail_add_account_sp
• sysmail_create_user_credential_sp

These stored procedures can be created in SQL Azure starting from SQL Server 2008 scripts. In general, for stored procedures not involved in the creation of credentials (sysmail_create_user_credential_sp and sysmail_add_account_sp) requires no changes other than the removal of any explicit reference to the msdb database.
The sysmail_add_account_sp stored procedure stores the username and password as a credential. This mechanism, since is not possible to retrieve the credential secret, it should be changed to allow a worker role to get the password for SMTP authentication.
An easy way is to create a new table for storing the SMTP username and password and change the sysmail_create_user_credential_sp stored procedure in order to use that table instead of server credentials.
The password field needs to be adequately protected, but because of SQL Azure current limitations we can not use the EncryptBy * statements. For sake of simplicity we will store passwords in clear text (The password field has the name “ciphertext” so we don’t forget to protect it).

CREATE TABLE sysmail_account_credential(
credential_id int IDENTITY(1,1) NOT NULL,
username nvarchar(256) NULL,
cyphertext nvarchar(256) NULL,
CONSTRAINT [SYSMAIL_ACCOUNT_CREDENTIALIDMustBeUnique] PRIMARY KEY CLUSTERED
(
credential_id ASC
)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF))

CREATE PROCEDURE [dbo].[sysmail_create_user_credential_sp]
@username nvarchar(128),
@password nvarchar(128),
@credential_id int OUTPUT
AS
— Le porzioni commentate fanno riferimento al codice originale

SET NOCOUNT ON
–DECLARE @rc int
–DECLARE @credential_name UNIQUEIDENTIFIER
DECLARE @credential_name_as_str varchar(40)
–DECLARE @sql NVARCHAR(max)

—- create a GUID as the name for the credential
–SET @credential_name = newid()
SET @credential_name_as_str = convert(varchar(40), @username) –@credential_name)
–SET @sql = N’CREATE CREDENTIAL [‘ + @credential_name_as_str
— + N’] WITH IDENTITY = ‘ + QUOTENAME(@username, ””)
— + N’, SECRET = ‘ + QUOTENAME(ISNULL(@password, N”), ””)

–EXEC @rc = sp_executesql @statement = @sql
–IF(@rc 0)
— RETURN @rc

INSERT INTO dbo.sysmail_account_credential (username,cyphertext) VALUES (@username, @password)

–SELECT @credential_id = credential_id
–FROM sys.credentials
–WHERE name = convert(sysname, @credential_name)

SELECT @credential_id = credential_id FROM dbo.sysmail_account_credential WHERE credential_id = @@IDENTITY

IF(@credential_id IS NULL)
BEGIN
RAISERROR(14616, -1, -1, @credential_name_as_str)
RETURN 1
END

RETURN(0)
GO

The following tables are used by the stored procedures:

• sysmail_account
• sysmail_server
• sysmail_servertype
• sysmail_profile
• sysmail_profileaccount
• sysmail_principalprofile

Again the tables can be created using the scripts generated by SSMS connected to SQL Server 2008. To fix some incompatibilities with SQL Azure we need to remove all references to filegroups and the ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON and PAD_INDEX = OFF options from the scripts.
You can use the stored procedures on SQL Azure as usual, with the exception that they do not reside in the msdb system database but on our user database.

The sp_send_dbmail stored procedure
The stored procedure sp_send_dbmail enqueues a mail message for sending. The message can be either in plain text or HTML and have files in attachment. The body of the message or the attachment may also contain the result of a query.

A glance at the stored procedures’s source code reveals that some changes are needed to make it run on SQL Azure. Firstly it is necessary to comment out the code that checks for the service broker and the ExternalMailQueue queue:

–Check if SSB is enabled in this database
–IF (ISNULL(DATABASEPROPERTYEX(DB_NAME(), N’IsBrokerEnabled’), 0) 1)
–BEGIN
— RAISERROR(14650, 16, 1)
— RETURN 1
–END

–Report error if the mail queue has been stopped.
–sysmail_stop_sp/sysmail_start_sp changes the receive status of the SSB queue
–IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N’ExternalMailQueue’ AND is_receive_enabled = 1)
–BEGIN
— RAISERROR(14641, 16, 1)
— RETURN 1
–END

we also need to remove the call to sp_SendMailQueues

— Create the primary SSB xml maessage
–SET @sendmailxml = ”
— + CONVERT(NVARCHAR(20), @mailitem_id) + N”

— Send the send request on queue.
–EXEC @rc = sp_SendMailQueues @sendmailxml
–IF @rc 0
–BEGIN
— RAISERROR(14627, 16, 1, @rc, ‘send mail’)
— GOTO ErrorHandler;
–END

and, of course, all references to the msdb database.

The original stored procedure calls dbo.sp_validate_user. This stored procedure is used to identify, if not specified, the default profile and checks if the user has rights to use it. At first it, this process is carried out using the information stored in sysmail_principalprofile. If the default profile is not identified, the stored procedure tries a profile lookup based on Windows Group membership. Of course, since we only have SQL authentication, this does not makes sense in SQL Azure and we have to comment out the few lines related.

The security check makes use of the functions dbo.get_principal_id and dbo.get_principal_sid, not present on SQL Azure. The function code is not available, but you can easily write it:

CREATE FUNCTION dbo.get_principal_id (@sid varbinary(85))
RETURNS int
AS
BEGIN
DECLARE @id int
SELECT @id = principal_id FROM sys.database_principals WHERE sid=@sid
RETURN @id
END
GO

CREATE FUNCTION dbo.get_principal_sid (@id int)
RETURNS varbinary(85)
AS
BEGIN
DECLARE @sid varbinary(85)
SELECT @sid = sid FROM sys.database_principals WHERE principal_id=@id
RETURN @sid
END

GO

Through the parameter @file_attachments of sp_send_dbmail you can specify a list of files to be attached to the message. The attachments are specified as a semicolon separated list of absolute file paths. The stored procedure sp_GetAttachmentData, using the extended procedure xp_sysmail_attachment_load, stores the file content in sysmail_attachments_transfer. This mechanism cannot work in SQL Azure, so you must remove the code.
Since the attachment names are stored in sysmail_mailitems they can be used for sending. Of course the files will be on the cloud storage and consequently the file path will be an absolute URL or the container/file name pair of a known storage account.

Also, the stored procedure sp_send_dbmail allows to append to the message body (or as file in attachment) the result of a query. This feature relies on the xp_sysmail_format_query extended procedure, which is called by sp_RunMailQuery. xp_sysmail_format_query deals with query and builds the string that represents the output in tabular format. For sake of simplicity this feature is removed. You can port it to SQL Azure by adding to sp_RunMailQuery the code required to execute the query and format the output table as a string.

The original definition of the stored procedures uses an impersonation as dbo (CREATE PROCEDURE [dbo]. [Sp_send_dbmail] … WITH EXECUTE AS ‘dbo’). SQL Azure does not allow the use of SNAME_SUSER() in impersonation contexts, and since it is used in the code and as a default field value, the WITH EXECUTE AS clause is removed.

Sending an e-mail
The stored procedure sp_send_dbmail relies for its operation on the sysmail_mailitems table. The sent_account_id field is set to the account id of the actually used profile, the sent_status value indicates the message status (1 sent, 2 sending failed, 3 not sent) and sent_date is set to the date and time of message sending.
With a join between sysmail_mailitems, sysmail_profile, sysmail_profileaccount, sysmail_account, sysmail_server and sysmail_account_credential you can retrieve all information needed for sending:

SELECT sysmail_mailitems.recipients, sysmail_mailitems.copy_recipients, sysmail_mailitems.blind_copy_recipients, sysmail_mailitems.subject, sysmail_mailitems.body,
sysmail_mailitems.body_format, sysmail_mailitems.importance, sysmail_mailitems.sensitivity, sysmail_mailitems.file_attachments, sysmail_mailitems.sent_status,
sysmail_account.email_address, sysmail_account.display_name, sysmail_account.replyto_address, sysmail_server.servername, sysmail_server.port,
sysmail_server.username, sysmail_profileaccount.sequence_number, sysmail_account_credential.cyphertext AS [Password]
FROM sysmail_profileaccount INNER JOIN
sysmail_account ON sysmail_profileaccount.account_id = sysmail_account.account_id INNER JOIN
sysmail_mailitems INNER JOIN
sysmail_profile ON sysmail_mailitems.profile_id = sysmail_profile.profile_id ON sysmail_profileaccount.profile_id = sysmail_profile.profile_id INNER JOIN
sysmail_server ON sysmail_account.account_id = sysmail_server.account_id LEFT OUTER JOIN
sysmail_account_credential ON sysmail_server.credential_id = sysmail_account_credential.credential_id
WHERE (sysmail_mailitems.sent_status = 0)
ORDER BY sysmail_profileaccount.sequence_number

The complete script for creating tables and stored procedures can be downloaded here . Of course, before you use this code in a production environment, you should test it thoroughly and add tighter security checks.

To create a worker role for sending messages, you can refer to the excellent MSDN blog posts I Miss You SQL Server Agent: Part 1 and I Miss You SQL Server Agent: Part 2.
The worker role needs to check if there are messages waiting on queue (sent_status = 0) and send them using an SMTP Client, trying with several accounts if needed. After sending the message, it should update the sysmail_mailitems table setting the fields sent_status, sent_date, from_address and reply_to. The values for the latter two are from the actually used account.
As mentioned earlier, the attachment file should be stored on the cloud storage, the worker role transfers it on local storage and instructs the SMTP client accordingly.

I hope I’ve provided some guidance to ease the porting process of your solution on SQL Azure, limiting the need for changes. If you need clarification, you can also contact me directly using the form on “Contatti”.