Replicare le funzionalità di Database Mail su SQL Azure

As requested by some people, I published an english version of this post.

Tra le funzionalità di SQL Server 2008 non presenti in SQL Azure, dando un’occhiata all’MSDN, spicca l’assenza delle funzionalità legate a SQL Agent. Tra queste c’è Database Mail, che permette di gestire l’invio di messaggi di posta elettronica direttamente dal database engine, eseguendo una serie di stored procedure in msdb.
Realizzare un meccanismo che accodi i messaggi all’interno di un database SQL Azure e li invii attraverso un worker role è abbastanza semplice, ma vorrei andare oltre, realizzando la compatibilità con il codice già scritto per Database Mail, trasferendo su Azure le sue funzionalità.
In questo post vedremo come è possibile adattare le stored procedure di sistema in modo da farle eseguire su SQL Azure. Considereremo solo le stored procedure più comuni. Le altre, se necessario, potranno essere adattate in modo analogo.

Ricavare il codice originale
Ricavare il codice originale delle stored procedure è piuttosto semplice. Basta andare con SSMS sull’oggetto interessato nel database msdb e scegliere di modificarlo. Per quasi tutti gli oggetti è stato possibile procedere alla modifica del codice in questo modo. Le function dbo.get_principal_id e dbo.get_principal_sid sono state riscritte (ma sono banali), mentre per l’extended procedure xp_sysmail_format_query si è deciso, viste le finalità del post, di non procedere alla replicazione della funzione che permette di allegare o accodare al corpo del messaggio il risultato di una query. Se questa funzionalità è per voi strategica sarà necessario scrivere il codice che formatta l’output di una query in una stringa rappresentante la tabella di output.

Gestione dei profili
I messaggi sono inviati utilizzando i profili, che contengono tutte le informazioni sull’account ed il server SMTP da utilizzare per l’operazione e permettono di realizzare un meccanismo di ridondanza che garantisce l’invio del messaggio. Le operazioni tipiche per la creazione dei profili sono:

— Creazione di un account Database Mail
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’ ;

— Creazione di un profilo Database Mail
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘AdventureWorks2008R2 Public Profile’,
@description = ‘Profile used for administrative mail.’ ;

— Aggiunta dell’account nel profilo
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘AdventureWorks2008R2 Public Profile’,
@account_name = ‘AdventureWorks2008R2 Public Account’,
@sequence_number =1 ;

— Concessione agli utenti dell’accesso al profilo
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘AdventureWorks2008R2 Public Profile’,
@principal_name = ‘public’,
@is_default = 1 ;

Le stored procedure coinvolte, con le relative dipendenze sono:

• 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

Queste stored procedure possono essere create in SQL Azure facilmente, utilizzando degli script estratti dal proprio server SQL 2008. In generale, per le stored procedure non utilizzate nella creazione dell’account e delle relative credenziali (sysmail_create_user_credential_sp e sysmail_add_account_sp), non sono necessarie particolari modifiche, eccettuata la rimozione di qualche riferimento esplicito al database msdb.

La stored procedure sysmail_add_account_sp memorizza la username e la password dell’account come credenziale all’interno del server. Questo meccanismo, non essendo possibile recuperare in un secondo momento il secret della credenziale, va modificato al fine di permettere ad un worker role di ottenere la password per l’autenticazione SMTP.
Il modo più semplice è quello di creare una nuova tabella per la memorizzazione delle credenziali e modificare in modo opportuno la stored procedure sysmail_create_user_credential_sp. Ovviamente le password andranno protette in modo adeguato, ma a causa delle attuali limitazioni di SQL Azure non è possibile utilizzare gli statement EncryptBy*. Per semplicità memorizzeremo le password in chiaro (non rabbrividite, vi prego! Il campo per la password ha come nome “cyphertext” per ricordarvi di proteggerlo.).

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

Le stored procedure, a loro volta dipendono dall’esistenza delle seguenti tabelle:

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

Anche in questo caso le tabelle possono essere create utilizzando gli script generati con SSMS connesso al server SQL 2008. Gli script prodotti, per risolvere delle incompatibilità con SQL Azure, vanno modificati rimuovendo i riferimenti ai filegroup e le opzioni ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF.
Fatto questo è possibile utilizzare le stored procedure in modo usuale, con l’unica eccezione che queste non risiederanno nel database di sistema msdb ma sul nostro database utente.

La stored procedure sp_send_dbmail
La stored procedure sp_send_dbmail permette di accodare un messaggio di posta elettronica per l’invio. Il messaggio può essere in formato plain text o HTML e avere dei file in allegato. Il corpo del messaggio o l’allegato può contenere anche il risultato dell’esecuzione di una query.

Scorrendo il sorgente della stored procedure sp_send_dbmail appare evidente che necessita di alcune modifiche per poter essere utilizzata su SQL Azure. Innanzi tutto è necessario commentare il codice relativo alla verifica dello stato del service broker e della coda ExternalMailQueue:

–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

va inoltre commentata la chiamata a 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

e rimossi i riferimenti espliciti al database msdb.

Nel codice della stored procedure originale vengono effettuate delle chiamate a dbo.sp_validate_user. Questa stored procedure viene utilizzata, nel caso in cui questo non viene specificato, per identificare il profilo di default e per verificare se l’utente ha accesso al profilo. Nel caso in cui una verifica su sysmail_principalprofile non abbia dato risultati, la ricerca e la verifica vengono effettuati nel contesto di un gruppo di utenti del sistema operativo o del dominio. Questi controlli, essendo l’autenticazione SQL l’unica disponibile, vanno commentati.

Per la verifica su sysmail_principalprofile vengono utilizzate le function dbo.get_principal_id e dbo.get_principal_sid, non presenti su SQL Azure. Il codice delle funzioni non è disponibile, ma è possibile riscriverle facilmente:

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

Attraverso il parametro @file_attachments di sp_send_dbmail è possibile specificare una lista di file da allegare al messaggio. Gli allegati sono specificati indicando il percorso assoluto al file. La stored procedure sp_GetAttachmentData, usando l’extended procedure xp_sysmail_attachment_load, carica il contenuto del file nella tabella sysmail_attachments_transfer. Questo meccanismo non può funzionare nel contesto di SQL Azure, pertanto è necessario rimuovere il codice relativo.
I nomi degli allegati saranno comunque memorizzati nella tabella sysmail_mailitems e potranno essere utilizzati in seguito per l’invio. Ovviamente i file non saranno nel file system locale dell’istanza SQL Azure, ma sul cloud storage e di conseguenza il percorso del file sarà l’URL assoluta o semplicemente la coppia container/nome file all’interno di uno storage noto a priori.

La stored procedure sp_send_dbmail permette anche di accodare al corpo del messaggio o inserire come allegato il risultato dell’esecuzione di una query. Questa funzionalità si basa sull’extended procedure xp_sysmail_format_query richiamata da sp_RunMailQuery. xp_sysmail_format_query si occupa di eseguire la query e di costruire la stringa che rappresenta l’output in formato tabellare. Per semplicità non ci occuperemo di questa funzione, ma è possibile ricostruire questa caratteristica modificando in modo opportuno sp_RunMailQuery.

Nella definizione della stored procedure originale è prevista l’impersonazione come dbo (CREATE PROCEDURE [dbo].[sp_send_dbmail] … WITH EXECUTE AS ‘dbo’). SQL Azure non permette l’utilizzo di SNAME_SUSER() in un contesto di impersonazione e, visto che questa è utilizzata sia esplicitamente nel codice che come valore di default nelle tabelle, la clausola EXECUTE AS è stata rimossa.

Ricavare le informazioni per l’invio
La stored procedure sp_send_dbmail si appoggia per il suo funzionamento sulla tabella sysmail_mailitems. Il campo sent_account_id viene impostato all’id dell’account del profilo utilizzato effettivamente per l’invio, il campo sent_status indica lo stato del messaggio (1 inviato, 2 invio fallito, 3 non inviato), mentre il campo sent_date viene impostato alla data ed ora di invio.
Con una join tra le tabelle sysmail_mailitems, sysmail_profile, sysmail_profileaccount, sysmail_account, sysmail_server e sysmail_account_credential è possibile recuperare tutte le informazioni necessarie per effettuare l’invio:

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

Lo script completo per la creazione delle tabelle e delle stored procedure può essere scaricato da qui.
Ovviamente, prima di utilizzare le stored procedure in un ambiente di produzione è bene testarle a fondo e, se non viene implementato il meccanismo di esecuzione delle query, aggiungere dei controlli più restrittivi.

Per la creazione di un worker role per l’invio dei messaggi è possibile fare riferimento agli ottimi post del blog MSDN I Miss You SQL Server Agent: Part 1 e I Miss You SQL Server Agent: Part 2.
Il worker role dovrà controllare se esistono dei messaggi in attesa di invio (sent_status=0) e tentare di trasmetterlo con un SMTP Client, eventualmente riprovando con account differenti. Una volta inviato il messaggio dovrebbe aggiornare, sulla tabella sysmail_mailitems, i campi sent_status e sent_date ed inserire i valori corretti nei campi from_address e reply_to sulla base dell’account utilizzato.
Come detto prima i file degli allegati dovrebbero essere custoditi sul cloud storage, ad esempio su un container predefinito. Quest’ultimo potrebbe essere sia pubblico che privato.
Il worker role si occuperebbe di trasferire i file sul local storage e istruire l’SMTP Client ad allegarne il contenuto.

Spero di avervi fornito delle indicazioni utili al porting della vostra soluzione su SQL Azure limitando la necessità di modifiche. Se doveste avere necessità di chiarimenti, potete anche contattarmi direttamente, utilizzando la pagina Contatti.