Wysył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 :
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' |
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 |
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 |
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 |
W przypadku pojawienia się problemów z wysyłaniem wiadomości sprawdź przede wszystkim :
- Informację o błędach, odpowiedziach serwera w widoku sysmail_event_log.
- 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 ;)).
- Sprawdź czy usługa brokera jest aktywna.
- 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
- 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.
- 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 - 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