Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

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

Visualizações: 31.742 views
Tempo de Leitura: 11 minutos

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.

Não se esqueçam que para o usuário conseguir enviar e-mails pelo SQL Server, ele precisará estar na database role DatabaseMailUserRole do banco msdb (ou permissões elevadas, como owner da msdb, sysadmin, etc)

Habilitando o envio de e-mails no servidor usando T-SQL

Visualizar como ativar e configurar o Database mail via linha de comando (usando T-SQL)
Por padrão, o SQL Server vem com as opções de envio de e-mail desabilitadas como medida de segurança. Vou mostrar abaixo, como habilitar essas opções:


Habilitando o envio de e-mails no servidor pelo SSMS

Visualizar como configurar o Database Mail pelo Management Studio
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

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

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

Enviando e-mail com 2 arquivos em anexo

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:

Monitoramento-Email

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):

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:

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:

Verificar o status da fila de mensagens:

Verificar os logs do Database Mail:

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

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:

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