SQL Server - Cómo activar y configurar Database Mail para enviar y monitorear emails por la base de datos (sp_send_dbmail)

Mucha gente me pregunta: “¿Es posible enviar correos electrónicos a través de SQL Server?” o “¿Cómo enviar correos electrónicos a través de SQL Server?”. En esta publicación, le mostraré cómo habilitar el envío de correos electrónicos en su SQL Server y cómo enviar y monitorear los correos electrónicos enviados.

Una característica muy utilizada en la mayoría de los sistemas es el envío de correos electrónicos automáticos que contienen notificaciones, promociones, newsletters y mucho más. Por tanto, sería muy interesante enviar estos correos electrónicos directamente desde nuestros SP’s en la base de datos, los cuales pueden ser programados por el Agente SQL Server para automatizar su ejecución.

Habilitar el envío de correos electrónicos en el servidor usando T-SQL

-----------------------------------------------------------------------------------------
-- Habilita o envio de e-mail no servidor
-----------------------------------------------------------------------------------------

sp_configure 'show advanced options', 1;
GO

RECONFIGURE
GO

sp_configure 'Database Mail XPs', 1;
GO

RECONFIGURE
GO


-----------------------------------------------------------------------------------------
-- Cria uma conta de envio de e-mail no banco de dados
-----------------------------------------------------------------------------------------

DECLARE
    @Account_Name SYSNAME = 'ContaEnvioEmail',
    @Profile_Name SYSNAME = 'ProfileEnvioEmail'
    

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_account WHERE name = @Account_Name) > 0)
    EXEC msdb.dbo.sysmail_delete_account_sp @account_name = @Account_Name


EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = @Account_Name,
    @description = 'Conta de e-mail para ser utilizada por todos os usuários do banco',
    @email_address = 'usuario@seudominio.com.br',
    @replyto_address = 'naoresponder@seudominio.com.br',
    @display_name = 'Sua Empresa',
    @mailserver_name = 'smtp.seudominio.com.br',
    @mailserver_type = 'SMTP',
    @port = '587',
    @username = 'usuario@seudominio.com.br',
    @password = 'senha',
    @enable_ssl = 1,
    @use_default_credentials = 0



-----------------------------------------------------------------------------------------
-- Cria o profile de e-mail
-----------------------------------------------------------------------------------------

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_profile WHERE name = @Profile_Name) > 0)
    EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = @Profile_Name



EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = @Profile_Name,
    @description = 'Profile Público para Envio de E-mail' ;


-----------------------------------------------------------------------------------------
-- Adiciona a conta ao perfil criado
-----------------------------------------------------------------------------------------

DECLARE 
    @profile_id INT = (SELECT profile_id FROM msdb.dbo.sysmail_profile WHERE name = @Profile_Name), 
    @account_id INT = (SELECT account_id FROM msdb.dbo.sysmail_account WHERE name = @Account_Name)
    

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_profileaccount WHERE account_id = @account_id AND profile_id = @profile_id) > 0)
    EXEC msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = @Profile_Name, @account_name = @Account_Name


EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @Profile_Name,
    @account_name = @Account_Name,
    @sequence_number = 1;


-----------------------------------------------------------------------------------------
-- Libera acesso no perfil criado para todos os usuários
-----------------------------------------------------------------------------------------

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_principalprofile WHERE profile_id = @profile_id) > 0)
    EXEC msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = @Profile_Name


EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = @Profile_Name,
    @principal_name = 'public', -- Aqui você pode dar acesso para um usuário específico, se quiser
    @is_default = 1;


-----------------------------------------------------------------------------------------
-- Define o tamanho máximo por anexo para 5 MB (O Padrão é 1 MB por arquivo)
-----------------------------------------------------------------------------------------

EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize', '5242880'; -- 1024 x 1024 x 5

Habilitar el envío de correos electrónicos en el servidor vía SSMS

Otra forma de habilitar y configurar el correo de la base de datos en SQL Server es a través de la interfaz de SQL Server Management Studio.

1) Abra el Explorador de objetos en Management Studio, haga clic derecho en la opción Administración > Correo de base de datos y seleccione la opción “Configurar correo de base de datos”

2) Verá la pantalla a continuación, que es solo una presentación de Database Mail. Si lo prefieres, incluso puedes marcar la opción “Omitir esta página en el futuro”, para que esta pantalla ya no aparezca.

3) En esta pantalla verás 3 opciones. Inicialmente, seleccione la primera opción (Configurar el correo electrónico de base de datos realizando las siguientes tareas)

4) De forma predeterminada, la función Correo electrónico de base de datos está deshabilitada en la instancia de SQL Server. Cuando selecciona la opción "Configurar el Correo de base de datos realizando las siguientes tareas" en la pantalla anterior, verá esta advertencia que le preguntará si desea habilitar el Correo de base de datos. Haga clic en el botón "Sí"

5) En esta pantalla, puede definir el nombre del perfil de correo electrónico que será necesario para enviar los correos electrónicos y agregar las cuentas SMTP que utilizará para enviarlos. Haga clic en el botón "Agregar" para agregar una cuenta SMTP.

6) Aquí agregará los detalles de su cuenta de correo electrónico, recordando siempre verificar la dirección SMTP de su proveedor de correo electrónico, el número de puerto, si la conexión requiere una conexión segura (SSL) y otros detalles de autenticación.

7) Ahora su cuenta SMTP está configurada y puede verla en la pantalla Perfil de cuentas SMTP.

8) En esta pantalla configuraremos la seguridad del perfil de correo creado, es decir, definiremos quién puede utilizarlo. Puedes hacer que este perfil sea público o privado, donde solo algunos usuarios tienen acceso. Generalmente dejo el perfil público, pero limito el acceso de los usuarios al procedimiento sp_send_dbmail mediante GRANT.

9) Aquí puede ver y cambiar los parámetros de Database Mail, como el tamaño máximo de los archivos adjuntos, las extensiones de archivos adjuntos prohibidas y el reintento de correo electrónico (que intenta enviar el correo electrónico nuevamente en caso de falla).

10) Y finalmente, verás un resumen de los cambios que configuraste y que se realizarán en la instancia luego de hacer clic en el botón “Finalizar”.

11) Cambios realizados con éxito.

Prueba de envío de correo electrónico

Después de configurar Database Mail, probemos si el servidor permite el envío normalmente. Para hacer esto, abra el Explorador de objetos en Management Studio, navegue hasta Administración > Correo de base de datos y haga clic derecho en la opción "Enviar correo electrónico de prueba...".

Simplemente seleccione el perfil que creó (en el ejemplo, es MSSQLServer), complete la dirección de correo electrónico del destinatario que recibirá la prueba y haga clic en el botón "Enviar correo electrónico de prueba".

Después de enviar la prueba, se abrirá una pantalla confirmando que el correo electrónico pasó a la cola de Correo de base de datos.

Si recibió el correo electrónico de prueba, simplemente haga clic en el botón "Aceptar". Si no lo recibió, haga clic en el botón "Solución de problemas" para ir a la página de ayuda de Microsoft (https://technet.microsoft.com/pt-br/library/ms187540(v=sql.105))

Ejemplo de correo electrónico de prueba enviado por SQL Server Database Mail:

Para ver el registro de Database Mail y comprobar si sus correos electrónicos se enviaron o el mensaje de error al intentar enviarlos, consulte más en la parte "Supervisión del envío de correos electrónicos" de esta publicación, a continuación.

Enviando el correo electrónico

Ahora que hemos configurado el servidor, estamos listos para enviar correos electrónicos. El sp que utilizaremos es sp_send_dbmail, del propio SQL Server, que permite enviar correos electrónicos en formato texto o HTML y archivos adjuntos.

Envío de correo electrónico en formato HTML

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br'

Envío de un correo electrónico concatenando el resultado de una consulta al cuerpo del mensaje

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br',
    @query = 'SELECT TOP 10 * FROM sys.sysobjects'

Envío de un correo electrónico con el resultado de una consulta como archivo adjunto (CSV)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br',
    @query = 'SET NOCOUNT ON; SELECT TOP 10 * FROM sys.sysobjects',
    @query_attachment_filename = 'anexo.csv',
    @attach_query_result_as_file = 1,
    @query_result_header = 1,
    @query_result_width = 256,
    @query_result_separator = ';',
    @query_result_no_padding = 1

Envío de correo electrónico con 2 archivos adjuntos

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br',
    @file_attachments = 'C:\Imagem.jpg;C:\Teste.txt'

Monitoreo del envío de correos electrónicos

Monitorear las acciones siempre es importante en TI. El envío de correos electrónicos no es una excepción. Aunque es posible monitorear los correos electrónicos a través del servidor de correo electrónico, no siempre es tan rápido poder validarlo, principalmente porque esto está algo fuera del alcance del DBA/Programador, donde este control generalmente lo controla el equipo de Infraestructura.

Además, el servidor de correo electrónico mantiene registros de todo el tráfico de correo electrónico, de todas las cuentas, lo que hace que sea más difícil extraer esta información que simplemente consultar las tablas del catálogo de SQL Server, que son msdb.dbo.sysmail_mailitems y msdb.dbo.sysmail_event_log.

Para que sea más fácil extraer información, normalmente creo esta vista y luego simplemente la consulto:

CREATE VIEW [dbo].[vwMonitoramento_Email] AS
SELECT
    A.send_request_date AS DataEnvio,
    A.sent_date AS DataEntrega,
    (CASE    
        WHEN A.sent_status = 0 THEN '0 - Aguardando envio'
        WHEN A.sent_status = 1 THEN '1 - Enviado'
        WHEN A.sent_status = 2 THEN '2 - Falhou'
        WHEN A.sent_status = 3 THEN '3 - Tentando novamente'
    END) AS Situacao,
    A.from_address AS Remetente,
    A.recipients AS Destinatario,
    A.subject AS Assunto,
    A.reply_to AS ResponderPara,
    A.body AS Mensagem,
    A.body_format AS Formato,
    A.importance AS Importancia,
    A.file_attachments AS Anexos,
    A.send_request_user AS Usuario,
    B.description AS Erro,
    B.log_date AS DataFalha
FROM 
    msdb.dbo.sysmail_mailitems                  A    WITH(NOLOCK)
    LEFT JOIN msdb.dbo.sysmail_event_log        B    WITH(NOLOCK)    ON A.mailitem_id = B.mailitem_id

Otra forma es utilizar la interfaz de SQL Server Management Studio.

Después de seleccionar la opción “Ver registro de correo de la base de datos”, verá la pantalla de registros de correo de la base de datos, en una interfaz similar a la del Monitor de actividad de SQL Server.

Ayuda rápida para solucionar problemas (Solución de problemas de correo electrónico de base de datos)

Si tiene problemas para configurar o enviar correos electrónicos, aquí tiene algunos consejos que pueden ayudarle a resolverlos.

Validar datos de acceso

Este tema puede parecer tonto, pero asegúrese de haber escrito correctamente la dirección, el puerto, el nombre de usuario y la contraseña del servidor SMTP y de haber marcado la opción para usar SSL si su servidor SMTP lo solicita.

Recientemente tuve una gran batalla con Database Mail para configurar una cuenta de Yahoo y otra alojada en Hostgator. Aunque la documentación indica que debo usar el puerto 465 (SSL) en ambos casos, utilicé un cliente de correo electrónico (Outlook) configurado usando el puerto 465 y envié correos electrónicos normalmente y probé con éxito el envío usando el puerto 465 en VBscript, en Database Mail solo pude enviar cuando configuré el puerto 587 (TLS). En el puerto 465, el correo electrónico no se envió en absoluto, probablemente porque utiliza el protocolo SSL.

Por lo tanto, consulte la documentación de su proveedor de correo electrónico y en caso de problemas al configurar Database Mail, intente usar otros puertos alternativos (los ISP generalmente proporcionan dos puertos para enviar SMTP seguro, uno SSL y otro TLS).

Compruebe si Service Broker está activado para la base de datos msdb (debe devolver 1):

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

Si el resultado de la consulta es 0, significa que Service Broker no está activo. Como resultado, los mensajes enviados no se pondrán en cola y no se realizará el envío. Para solucionar este problema, ejecute el siguiente comando:

USE master;
GO

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

He visto varios informes de personas que intentaron activar Service Broker en MSDB, pero el proceso falló o simplemente no se activó porque el Broker no pudo obtener los bloqueos en MSDB para realizar los cambios necesarios. Si esto le sucede, elimine todas las conexiones MSDB o reinicie el servicio SQL e inténtelo nuevamente.

Compruebe si Database Mail se está ejecutando en la base de datos MSDB:

EXECUTE msdb.dbo.sysmail_help_status_sp

-- Se não retornar "STARTED", executar o comando abaixo para iniciar o Database Mail:
EXECUTE msdb.dbo.sysmail_start_sp

Verifique el estado de la cola de mensajes:

EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail'

Verifique los registros de correo de la base de datos:

SELECT * FROM msdb.dbo.sysmail_event_log

Comprueba información sobre elementos enviados y errores de mensajes:

SELECT * FROM msdb.dbo.sysmail_allitems

Cortafuegos y antivirus

Muchas veces, el envío del correo electrónico puede no realizarse debido a que el Firewall/Antivirus bloquea el intento de envío de mensajes a través del protocolo SMTP por parte del proceso de Database Mail. Una forma de asegurarse de que el problema no sea el Firewall es liberar el puerto utilizado por la conexión (normalmente 25, 465 o 587) o incluso desactivar el Firewall/Antivirus temporalmente, sólo para probar si están bloqueando el envío o es otra cosa.

Envíe un correo electrónico de prueba utilizando el script VBscript:

Otra alternativa para comprobar si el problema es de SQL Server o del servidor es intentar enviar un correo electrónico de otra forma. Para hacer esto, puede utilizar el siguiente script VBscript:

Const cdoAnonymous = 0 'Sem autenticação
Const cdoBasic = 1 'Autenticação Básica (Base64)
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message") 
objMessage.Subject = "Teste de E-mail" 
objMessage.From = """Me"" <seu_email@seudominio.com>" 
objMessage.To = "destinatario@email.com" 
objMessage.TextBody = "Teste de Mensagem.." & vbCRLF & "Foi enviada utilizando autenticação Base64 e SSL."

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 

' Endereço do servidor SMTP
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.seudominio.com"

' Tipo de autenticação
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

' Usuário de autenticação
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "usuario@seudominio.com"

' Senha de autenticação
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "sua_senha"

' Porta do SMTP
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 

' Utiliza SSL ?
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

' Timeout
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update
objMessage.Send

If err.number = 0 then Msgbox "Email enviado com sucesso"

Después de crear el script, simplemente ejecútelo a través del símbolo del sistema (CMD.exe):

Validar protocolos de red

Otra validación que se puede realizar es si los protocolos de red Named Pipes y TCP/IP están habilitados para su instancia. Si no es así, habilite y reinicie el servicio SQL Server para su instancia.

Analizar la cuenta de servicio de SQL Server

Otro punto que debes evaluar si no puedes enviar tus correos electrónicos es verificar si la cuenta de servicio que se utiliza para ejecutar SQL Server tiene permisos para acceder a la red externa. No es necesario que sea una cuenta de administrador local o de administrador de dominio, pero debe tener permisos básicos de acceso a la red para poder enviar correo electrónico SMTP.

Para obtener más detalles sobre las cuentas de servicio, accede a este enlace.

Instale la última versión del Service Pack y las actualizaciones acumulativas

Microsoft SQL Server 2016 SP1
Última actualización acumulativa de Microsoft SQL Server 2016
Para otras ediciones, accede a este enlace.

Instale la última versión del Service Pack y las actualizaciones acumulativas

Si está utilizando SQL Server 2016 RTM o SP1, tenga en cuenta un problema común con Database Mail, que es la necesidad de instalar Microsoft .NET Framework 3.5 en su servidor para evitar que sus mensajes se queden atascados en la cola de Service Broker y siempre tengan un estado de no enviado. De lo contrario, Database Mail no funcionará debido a un error del producto, que se solucionó en la Actualización acumulativa 2 (Enlace de referencia), volvió a ocurrir en el Service Pack 1 CU1 y se solucionó nuevamente en el Service Pack 1 CU2 (Enlace de referencia).

Enlace de Microsoft para ayudar a solucionar problemas de Database Mail
Solución de problemas de correo de base de datos

Cómo enviar correo electrónico desde el servidor SQL, cómo enviar correo electrónico desde el servidor SQL

Cómo enviar correo electrónico desde el servidor SQL, cómo enviar correo electrónico desde el servidor SQL