SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Konfiguracja Database Mail w SQL Server

Mail_smallWysyłanie powiadomień mailowych z poziomu serwera bazodanowego, jest niezwykle przydatną funkcjonalnością. Typowe zastosowanie to automatyczne przekazywanie informacji o statusie zadań administracyjnych, procesów utrzymaniowych czy ETL (zazwyczaj błędach lub ostrzeżeniach).

Możesz je wykorzystać równie dobrze w typowych aplikacjach klienckich. Informować użytkowników o pojawiających się zdarzeniach np. zleceniach, zmianach statusów czy przekroczeniu określonych wskaźników, bezpośrednio z poziomu SQL Server.

W artykule tym, znajdziesz opis koncepcji Database Mail w SQL Server. Zaprezentuję także przykładową konfigurację za pomocą procedur wbudowanych. Co istotne, Database Mail jest dostępny także w darmowej wersji SQL Server Express, choć bez graficznego wizzarda i widocznej osobnej gałęzi obiektów w Management Studio.


Architektura Database Mail

Konfiguracja, widoki systemowe oraz procedury składowane dotyczące Database Mail, znajdują się są w bazie systemowej MSDB. Działanie oparte jest na usłudze Brokera (Service Broker), czyli wbudowanemu w SQL Server mechanizmowi do przesyłania wiadomości, kolejkowania i obsługi zdarzeń.

Wysyłanie maili, z punktu widzenia użytkownika, sprowadza się do wywołania procedury składowanej sp_send_dbmail, która wrzuca wiadomość do kolejki. Pojawienie się w niej nowego rekordu, wyzwala proces odpowiedzialny za fizyczne wysłanie maila. Tym procesem jest zewnętrzny program DatabaseMail.exe, uruchamiany automatycznie przez proces SQL Server.

Z konfiguracją Database Mail, związane są obiekty kont pocztowych i profili – przedstawione na poniższym schemacie :

Database_Mail_00

Profile – mogą mieć przypisane jedno lub wiele kont pocztowych z których będą wysyłane wiadomości. Jest to proste zwiększenie niezawodności, na wypadek gdy podstawowy serwer (konto) SMTP jest nie dostępny.

W momencie przypisania konta do profilu (procedura sysmail_add_profileaccount_sp), określany jest parametr sequence_number (priorytet) zgodnie z którym, następuje próba wysłania wiadomości. Najpierw przez konto z najniższym sequence numer. W przypadku niepowodzenia, podejmowana jest próba wysłania przez kolejne.

Konta pocztowe, mogą być przypisane do jednego lub wielu profili (współdzielone) np. konto backupowe.

Każdemu użytkownikowi, możemy nadać uprawnienia (proc. sysmail_add_principalprofile_sp
) do wielu profili pocztowych. Jeden z nich, może być określony jako domyślny – wtedy nie trzeba będzie podawać jawnie nazwy profilu w momencie wywoływania procedury wysyłającej maila. Nadanie uprawnień do profilu nie jest jednoznaczne z tym, że już użytkownik będzie mógł wysyłać maile. Musi mieć także uprawnienia do wykonywania procedury msdb.dbo.sp_send_dbmail czyli dostęp do bazy MSDB ze stosownymi uprawnieniami.

Do zarządzania uprawnieniami w tym zakresie, istnieje dedykowana rola w bazie MSDB DatabaseMailUserRole. Najwygodniej jest dopisać do niej każdego użytkownika (grupę), która ma mieć możliwość wysyłania i dostępu do widoków związanych z mailami (np. sysmail_allitems). Widoki te są o tyle bezpieczne, że użytkownik będzie widział tylko te wiadomości, których jest autorem.

Wprowadzona architektura zależności pomiędzy kontami, profilami a użytkownikami daje elastyczność i jest łatwo skalowalna.


Konfiguracja Database Mail w SQL Server

Pierwszym krokiem, jest przygotowanie instancji SQL Server, czyli włączenie obsługi Database Mail.

sp_configure 'show advanced options',1
reconfigure
go
 
sp_configure 'Database Mail XPs',1;
reconfigure
go

Jak już wspominałem, Database Mail bazuje na usłudze Brokera i musi on być również aktywny w bazie MSDB. Jego status możemy sprawdzić za pomocą :

select name ,   is_broker_enabled 
from sys.databases
where name = 'msdb'

Database_Mail_01
W przypadku gdy nie jest aktywny (0), należy go włączyć :

USE master
GO
ALTER DATABASE msdb SET  ENABLE_BROKER WITH NO_WAIT
GO

Teraz, możemy zabrać się za właściwą konfigurację profili i skojarzonych z nimi kont pocztowych. Od razu podpowiem, że większość problemów z wysyłaniem maili, jest związane właśnie z błędami w tym kroku. Należy upewnić się co do parametrów konta SMTP , poprzez które chcemy wysyłać maila.

Na początek dodam dwa nowe konta – na serwerach home.pl oraz Gmail. Konfiguracja portów, konieczność uwierzytelniania SSL są z reguły identyczne dla większości providerów.

Use MSDB
GO
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'Gmail',
     @email_address = 'mojekonto@gmail.com',
     @display_name = 'Gmail Public', 
     @description = 'Konto Main', 
     @mailserver_name = 'smtp.gmail.com',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'mojekonto@gmail.com',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'SQLPEDIA',
     @email_address = 'adresemail@sqlpedia.pl',
     @display_name = 'backup', 
     @description = 'Konto Main', 
     @mailserver_name = 'domena.home.pl',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'adresemail@sqlpedia.pl',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
-- weryfikacja kroku
select * from sysmail_server

Kolejnym krokiem, jest utworzenie profili. Zróbmy dwa – jeden będzie wykorzystywany jako publiczny, drugi prywatny dla procesów utrzymaniowych.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Public',
@description = 'Dla wszystkich userów' ;
 
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Priv',
@description = 'Tylko admin' ;
 
-- weryfikacja kroku
select * from sysmail_profile

Database_Mail_02
Teraz trzeba powiązać konta pocztowe z profilami. Profil publiczny będzie posiadał jedno konto a prywatny dwa, podstawowe i backupowe.

-- Public
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Public', -- nazwa profilu
@account_name = 'Gmail',  -- nazwa konta
@sequence_number =1 ;
 
-- Priv
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'SQLPEDIA',
@sequence_number =1 ;
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'Gmail',
@sequence_number =2 ;
 
-- weryfikacja kroku
select * from sysmail_profileaccount

Database_Mail_03

Ostatnią rzeczą która trzeba wykonać aby zwykły użytkownik mógł wysyłać maile, jest nadanie stosownych uprawnień. Nadajmy więc uprawnienia do profilu publicznego dla wszystkich (Principal_id = 0 to rola public w bazie MSDB).

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Public',
@principal_id = 0,
@is_default = 1 ;

Poza uprawnieniami do profilu, zwykły użytkownik musi mieć jeszcze dostęp do bazy MSDB oraz przypisanie do roli DatabaseMailUserRole

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
USE [msdb]
GO
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [UserName]
GO

I to już wszystko. Teraz możemy na wysłać maila testowego.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Public',
@recipients = 'JakisAdresEmail@wp.pl',
@subject = 'SQL Server Databse Mail - Test',
@body = 'Dzieńdobry i dowidzenia ...';

Weryfikacja poprawności działania i typowe problemy

Do weryfikacji poprawności wysłania maili mamy dostępnych kilka widoków systemowych. Szczególnie użyteczne są dwa :

  • msdb.dbo.sysmail_allitems, pokazujący aktualny status i zawartość wiadomości w kolejce.
  • msdb.dbo.sysmail_event_log – tu znajdziemy szczegółowe informacje o błędach, odpowiedziach serwera SMTP.
USE msdb
go
 
SELECT sent_status, mailitem_id, profile_id, recipients,
	subject, body
FROM sysmail_allitems
ORDER BY mailitem_id desc
 
SELECT * FROM sysmail_event_log
ORDER BY log_id  desc

Database_Mail_04
W przypadku pojawienia się problemów z wysyłaniem wiadomości sprawdź przede wszystkim :

  1. Informację o błędach, odpowiedziach serwera w widoku sysmail_event_log.
  2. Wiem, że to banał ale literówki, nieprawidłowa konfiguracja konta to naprawdę najczęstsza przyczyna problemów (bywa że username to pełna nazwa adresu email ;)).
  3. Sprawdź czy usługa brokera jest aktywna.
  4. Sprawdź status kolejki – powinna być STARTED.

    EXECUTE msdb.dbo.sysmail_help_status_sp

    Jeśli nie jest, możesz uruchomić ją za pomocą

    EXECUTE msdb.dbo.sysmail_start_sp

  5. Ustawienia firewall lub co bardziej kłopotliwe – szczególnie w korporacjach, blokowanie portów pocztowych 587. Łatwo to zweryfikować, telnetując się na np. na port 578 serwera SMTP. Jeśli się nie powiedzie, to znaczy że ruch na dany port jest wycinany.
    Database_Mail_06
  6. Szczególne ustawienia serwerów pocztowych SMTP, np. GMAIL, w których podstawowa autentykacja jest traktowana jako niebezpieczna.

    W Gmail, wprowadzono standard Oauth 2.0 i jeśli chcemy korzystać z Basic Authentication musimy jawnie określić, aby z naszego konta mogły korzystać tzw. mniej bezpieczne aplikacje.
    Więcej na ten temat znajdziesz tutaj : https://support.google.com/accounts/answer/6010255?hl=en
    Akceptację aplikacji mniej bezpiecznych, możesz ustawić po zalogowaniu do GMail tutaj : https://www.google.com/settings/security/lesssecureapps

  7. Sprawdź kto jest ownerem bazy systemowej MSDB. Jeśli jakiś „zwykły” user – warto zmienić na SA :)

    select name, suser_sname(owner_sid) as Owner  
    from sys.databases
     
    USE msdb
    GO
     
    EXEC sp_changedbowner [sa]
    GO


Źródła MSDN :
http://technet.microsoft.com/en-us/library/ms175887(v=SQL.105).aspx

Powyższy artykuł dotyczy wersji SQL Server 2008 R2 – 2014

One thought on “Konfiguracja Database Mail w SQL Server

Leave a comment

Twój adres email nie zostanie opublikowany.

Uzupełnij równanie (SPAM protection) *