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.

W celu skonfigurowania usługi Database Mail należy najpierw sprawdzić ustawienia konfiguracyjne:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
GO

Database Mail bazuje na usłudze Brokera i musi on być również aktywny w bazie MSDB. W celu sprawdzenia usługi należy wykonać następujący kod:

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

W przypadku wartości 0 dla kolumny is_broker_enabled należy usługę włączyć kodem:

USE master
GO
ALTER DATABASE msdb SET  ENABLE_BROKER WITH NO_WAIT
GO  

Poniższymi skryptami można sprawdzić aktualną/istniejącą konfigurację usługi Database Mail:

-- collect data 
SELECT * from msdb.dbo.sysmail_profile
/*
profile_id	name	        description	last_mod_datetime	    last_mod_user
1	        Internal_Alerts	NULL	    2022-07-10 10:17:44.760	domena\dariusz.brejnak
*/
SELECT * from msdb.dbo.sysmail_account
/*
account_id	name			description	email_address					display_name			replyto_address					last_mod_datetime		last_mod_user
1			Internal_Alerts				internal-alerts@konto.com	SQL_Alerts	internal-alerts@konto.com	2022-07-10 10:16:11.363	domena\dariusz.brejnak
*/

SELECT * from msdb.dbo.sysmail_configuration
/*
paramname						paramvalue	description										last_mod_datetime	last_mod_user
AccountRetryAttempts			1			Number of retry attempts for a mail server		2019-09-24 14:21:47.197	sa
AccountRetryDelay				60			Delay between each retry attempt to mail server	2019-09-24 14:21:47.200	sa
DatabaseMailExeMinimumLifeTime	600			Minimum process lifetime in seconds				2022-07-10 10:12:17.937	sa
DefaultAttachmentEncoding		MIME		Default attachment encoding						2019-09-24 14:21:47.197	sa
LoggingLevel					2			Database Mail logging level: normal - 1, 
											extended - 2 (default), verbose - 3				2019-09-24 14:21:47.200	sa
MaxFileSize						1000000		Default maximum file size						2019-09-24 14:21:47.197	sa
ProhibitedExtensions			exe,dll,vbs,js	Extensions not allowed in outgoing mails	2019-09-24 14:21:47.197	sa
*/

--SELECT * from msdb.dbo.sysmail_principalprofile

SELECT * from msdb.dbo.sysmail_profileaccount
/*
profile_id	account_id	sequence_number	last_mod_datetime	last_mod_user
1			1			1				2022-07-10 10:17:44	domena\dariusz.brejnak
*/

SELECT * from msdb.dbo.sysmail_server
/*
account_id	servertype	servername	port	username	credential_id	use_default_credentials	enable_ssl	flags	timeout	last_mod_datetime	last_mod_user
1			SMTP		10.10.10.19	587		NULL		NULL			0						0			0		NULL	2022-07-10 10:16:11	domena\dariusz.brejnak
*/

SELECT * from msdb.dbo.sysmail_servertype
/*
servertype	is_incoming	is_outgoing	last_mod_datetime	last_mod_user
SMTP		0			1			2019-09-24 14:21:47	sa
*/

SELECT * 
            FROM msdb.dbo.sysmail_profileaccount pa 
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
            WHERE p.name = 'Internal_Alerts' 
              AND a.name = 'Internal_Alerts'

Przystępujemy więc do konfiguracji usługi korzystając z procedur wbudowanych:

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'Internal_Alerts'
  , @description = ''
  , @email_address = 'internal-alerts@konto.com'
  , @display_name = 'SQL_Alerts'
  , @replyto_address = 'internal-alerts@konto.com'
  , @mailserver_type = 'SMTP'
  , @mailserver_name = '10.10.10.19'
  , @port = 587
Go

-- Create a Database Mail profile
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Internal_Alerts'
  , @description = ''
Go

-- Add the account to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Internal_Alerts'
  , @account_name = 'Internal_Alerts'
  , @sequence_number = 1
Go

Po skonfigurowaniu Database Mail można wysłać e-mail testowy. Na koniec należy dla bezpieczeństwa zmienić ustawienia w konfiguracji 'show advanced options'

EXEC sp_configure 'show advanced options', '0';
RECONFIGURE
GO

Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl