SQL Server - Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail)

Muitas pessoas me perguntam: “É possível enviar e-mails pelo SQL Server?” ou “Como enviar e-mails pelo SQL Server?”. Neste post irei lhes mostrar como habilitar o envio de e-mails no seu servidor SQL Server e como enviar e monitorar os e-mails enviados.

Um recurso que é muito utilizado na maioria dos sistemas, é o envio de e-mails automáticos contendo notificações, promoções, newsletter e muito mais. Sendo assim, seria muito interessante enviar esses e-mails diretamente de nossas SP’s no banco de dados, que podem ser agendadas pelo SQL Server Agent para automatizar sua execução.

Habilitando o envio de e-mails no 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 = '[email protected]',
    @replyto_address = '[email protected]',
    @display_name = 'Sua Empresa',
    @mailserver_name = 'smtp.seudominio.com.br',
    @mailserver_type = 'SMTP',
    @port = '587',
    @username = '[email protected]',
    @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

Habilitando o envio de e-mails no servidor pelo SSMS

Uma outra forma de habilitar e configurar o Database mail no SQL Server é pela interface do SQL Server Management Studio.

1) Abra o Object Explorer no Management Studio, clique com o botão direito do mousse na opção Management > Database mail e selecione a opção “Configure Database Mail”

2) Você verá a tela abaixo, que é apenas uma apresentação do Database Mail. Se você preferir, pode até marcar a opção “Skip this page in the future”, para que essa tela não apareça mais.

3) Nesta tela você verá 3 opções. Inicialmente, selecione a primeira opção (Set up Database Mail by performing the following tasks)

4) Por padrão, o recurso de Database Mail vem desabilitado na instância SQL Server. Ao selecionar a opção “Set up Database Mail by performing the following tasks” na tela anterior, você verá esse aviso perguntando se você deseja habilitar o Database Mail. Clique no botão “Yes”

5) Nesta tela, você poderá definir o nome do perfil de e-mail que será necessário para enviar os e-mails e adicionar as contas de SMTP que você irá utilizar para realizar o envio. Clique no botão “Add” para adicionarmos uma conta de SMTP.

6) Aqui você irá adicionar os dados da sua conta de e-mail, lembrando sempre de conferir o endereço SMTP do seu provedor de e-mail, número da porta, se a conexão requer conexão segura (SSL) e outros detalhes de autenticação.

7) Agora sua conta SMTP está configurada e você já pode visualizá-la na tela de contas SMTP do Perfil.

8) Nesta tela vamos configurar a segurança do perfil de e-mail criado, ou seja, definir quem poderá utilizá-lo. Você pode marcar que esse perfil seja público ou privado, onde apenas alguns usuários tem acesso. Eu geralmente deixo o perfil público, mas limito o acesso dos usuários na procedure sp_send_dbmail via GRANT.

9) Aqui você pode visualizar e alterar os parâmetros do Database Mail, como tamanho máximo dos anexos, extensões de anexo proibidas, e o retry de e-mail (que tenta enviar o e-mail novamente em caso de falha)

10) E por fim, você verá um resumo das alterações que você configurou e que serão realizadas na instância após clicar no botão “Finish”.

11) Alterações realizadas com sucesso.

Testando o envio de e-mail

Após configurar o Database Mail, vamos testar se o servidor está permitindo o envio normalmente. Para isso, abra o Object Explorer no Management Studio, navegue até Management > Database Mail e clique com o botão direito na opção “Send Test E-mail…”

Basta selecionar o perfil que você criou (No exemplo, é o MSSQLServer), preencher o e-mail do destinatário que irá receber o teste e clicar no botão “Send Test E-mail”

Após enviar o teste, será aberta uma tela confirmando que o e-mail foi para a fila do Database Mail.

Caso você tenha recebido o e-mail de teste, basta clicar no botão “OK”. Se você não recebeu, clique no botão “Trobleshooting” para ser direcionado para a página de ajuda da Microsoft (https://technet.microsoft.com/pt-br/library/ms187540(v=sql.105))

Exemplo de e-mail de teste enviado pelo SQL Server Database Mail:

Para visualizar o log do Database Mail e verificar se os seus e-mails foram enviados ou a mensagem de erro ao tentar enviar, veja mais na parte “Monitorando o envio de e-mails” deste post, logo abaixo.

Enviando o e-mail

Agora que configuramos o servidor, estamos prontos para realizar nossos envios de e-mails. A sp que iremos utilizar é a sp_send_dbmail, do próprio SQL Server, que permite o envio de e-mails no formato texto ou HTML e anexos.

Enviando e-mail no formato HTML

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = '[email protected]',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = '[email protected]'

Enviando e-mail concatenando o resultado de uma query ao corpo da mensagem

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

Enviando e-mail com o resultado de uma query como anexo (CSV)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = '[email protected]',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = '[email protected]',
    @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

Enviando e-mail com 2 arquivos em anexo

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

Monitorando o envio de e-mails

Monitorar ações sempre é importante na TI. O envio de e-mails não foge à regra. Por mais que seja possível monitorar os e-mails pelo servidor de e-mails, nem sempre é tão rápido conseguir validar isso, principalmente porque isso foge um pouco do escopo do DBA/Programador, onde esse controle geralmente é controlado pela equipe de Infraestrutura.

Além disso, o servidor de e-mails guarda logs de todo o tráfego de e-mails, de todas as contas, sendo mais difícil extrair essas informações do que simplesmente consultando tabelas de catálogo do SQL Server, que são a msdb.dbo.sysmail_mailitems e a msdb.dbo.sysmail_event_log.

Para facilitar a extração das informações, eu geralmente costumo criar essa view, e depois apenas faço consultas nela:

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

Uma outra forma é utilizando a interface do SQL Server Management Studio

Após selecionar a opção “View Database Mail Log”, você verá a tela dos logs do Database Mail, em uma interface parecida com o do SQL Server Activity Monitor

Ajuda rápida para resolver problemas (Troubleshooting Database Mail)

Caso você esteja com problemas para configurar ou enviar e-mails, seguem algumas dicas que podem te ajudar a resolver esses problemas.

Valide os dados de acesso

Esse tópico pode parecer bobo, mas garanta que você digitou corretamente o endereço do servidor SMTP, porta, usuário, senha e se você marcou a opção de utilizar SSL caso seu servidor SMTP solicite isso.

Recentemente tive uma grande batalha com o Database Mail para configurar uma conta do Yahoo e outra hospedada na Hostgator. Apesar da documentação indicar que eu deveria utilizar a porta 465 (SSL) em ambos os casos, eu ter utilizado um cliente de e-mail (Outlook) configurado utilizando a porta 465 e enviado e-mail normalmente e ter feito um teste de envio utilizando a porta 465 em um VBscript com sucesso, no Database Mail eu só consegui enviar quando coloquei a porta 587 (TLS). Na porta 465 o e-mail não era enviado de jeito nenhum, provavelmente por ser do protocolo SSL.

Sendo assim, consulte a documentação do seu provedor de e-mail e em caso de problemas ao configurar o Database Mail, tente utilizar outras portas alternativas (Geralmente os provedores disponibilizam duas portas para envio de SMTP seguro, uma SSL e outra TLS).

Verificar se o Service Broker está ativado para o database msdb (deve retornar 1):

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

Se o resultado da query for 0, isso significa que o Service Broker não está ativo. Com isso, as mensagens enviadas não serão enfileiradas por ele e o envio não ocorrerá. Para corrigir isso, execute o comando abaixo:

USE master;
GO

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

Já vi vários relatos de pessoas que tentaram ativar o Service Broker no MSDB, mas o processo travou ou simplesmente não ativou porque o Broker não conseguiu obter os locks no MSDB para realizar as alterações necessárias. Se isso acontecer com você, elimine todas as conexões do MSDB ou reinicie o serviço do SQL Service e tente novamente.

Verificar se o Database Mail está executando no database 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

Verificar o status da fila de mensagens:

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

Verificar os logs do Database Mail:

SELECT * FROM msdb.dbo.sysmail_event_log

Verifica as informações sobre os itens enviados e erros das mensagens:

SELECT * FROM msdb.dbo.sysmail_allitems

Firewall e Antivírus

Muitas vezes o envio do E-mail pode acabar não acontecendo pelo fato do Firewall/Antivírus bloquear a tentativa de envio de mensagens pelo protocolo SMTP por parte do processo do Database Mail. Uma forma de se garantir que o problema não seja o Firewall é liberar a porta utilizada pela conexão (Geralmente 25, 465 ou 587) ou mesmo desativar o Firewall/Antivírus temporariamente, apenas para testar se eles que estão bloqueando o envio ou é alguma outra coisa.

Enviar um teste de e-mail utilizando script VBscript:

Uma outra alternativa de verificar se o problema está no SQL Server ou no servidor é tentando enviar um e-mail por uma outra forma. Para isso, você pode utilizar o script VBscript abaixo:

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"" <[email protected]>" 
objMessage.To = "[email protected]" 
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") = "[email protected]"

' 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"

Após criar o script, basta executá-lo pelo Prompt de Comando (CMD.exe):

Valide os protocolos de rede

Uma outra validação que pode ser feita, é se os protocolos de rede Named Pipes e TCP/IP estão habilitados para a sua instância. Caso não estejam, habilite e reinicie o serviço do SQL Server da sua instância.

Analise a conta de serviço do SQL Server

Um outro ponto que você deve avaliar caso não esteja conseguindo enviar seus e-mails é verificar se a conta de serviço que está sendo utilizada para executar o SQL Server possui permissões para acessar a rede externa. Não precisa necessariamente ser uma conta de Administrador local ou Domain Admin, mas precisa ter as permissões básicas de acesso à rede para conseguir enviar o e-mail SMTP.

Para mais detalhes sobre contas de serviço, acesse este link.

Instale a última versão do Service Pack e Cumulative Updates

Microsoft SQL Server 2016 SP1
Microsoft SQL Server 2016 Latest Cumulative Update
Para outras edições, acesse este link.

Instale a última versão do Service Pack e Cumulative Updates

Caso você esteja utilizando o SQL Server 2016 RTM ou SP1, fique atento a um problema comum do Database Mail, que é a necessidade de ter que instalar o Microsoft .NET Framework 3.5 no seu servidor para evitar que as suas mensagens fiquem presas na fila do Service Broker e fiquem sempre com o status de unsent (não enviadas). Caso contrário, o Database Mail não irá funcionar devido a um bug do produto, que foi corrigido no Cumulative Update 2 (Link de referência), voltou a ocorrer no Service Pack 1 CU1 e foi novamente corrigido no Service Pack 1 CU2 (Link de referência).

Link da Microsoft para ajudar a solucionar problemas do Database Mail
Solucionando problemas de Database Mail

Como enviar e-mail mail email pelo sql server, how to send email mail from within sql server

Como enviar e-mail mail email pelo sql server, how to send email mail from within sql server