SQL Server : configurer et dépanner Database Mail
Requêtes pour inspecter, configurer, autoriser, tester et dépanner Database Mail et son utilisation par SQL Server Agent.
Un ensemble de requêtes et de procédures pour configurer, autoriser, tester et dépanner Database Mail dans SQL Server.
Inspecter la configuration
select *
from msdb.dbo.sysmail_profile p
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id
join msdb.dbo.sysmail_server s on a.account_id = s.account_id
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
Donner accès à Database Mail
Ajouter l'utilisateur au rôle DatabaseMailUserRole de msdb, puis lui donner accès à un profil privé. Ne pas oublier de rendre le profil public si tous les utilisateurs doivent pouvoir l'utiliser.
use msdb
go
-- Add user to the Database Mail role
exec sp_addrolemember 'DatabaseMailUserRole', 'sqlUser'
-- Grants permission for a database user or role
-- to use a private Database Mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'sqlUser',
@profile_name = 'sqlMailProfileName',
@is_default = 0
Pour un login Windows, le membre du rôle s'écrit DOMAIN\app_user.
Vérifier l'état des messages
| Vue | Contenu |
|---|---|
sysmail_faileditems |
Messages en échec |
sysmail_allitems |
Tous les messages traités par Database Mail |
sysmail_unsentitems |
Messages non envoyés |
sysmail_sentitems |
Messages envoyés |
sysmail_mailattachments |
Pièces jointes |
sysmail_event_log |
Messages Windows ou SQL Server renvoyés par Database Mail |
État de la file d'attente :
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';
Envoyer un mail de test
USE msdb
GO
EXEC sp_send_dbmail @profile_name='sqlmail',
@recipients='user@example.com',
@subject='Test message',
@body='This is the body of the test message.
Database Mail was received successfully.'
Vérifier la configuration du compte et du profil
DECLARE @DatabaseMail VARCHAR(255);
SELECT
ProfileName = smp.name
,AccountName = sma.name
,AccountFromAddress = sma.email_address
,AccountReplyTo = sma.replyto_address
,SMTPServer = sms.servername
,SMTPPort = sms.port
FROM msdb.dbo.sysmail_account sma
INNER JOIN msdb.dbo.sysmail_profileaccount smpa ON sma.account_id = smpa.account_id
INNER JOIN msdb.dbo.sysmail_profile smp ON smpa.profile_id = smp.profile_id
INNER JOIN msdb.dbo.sysmail_server sms ON sma.account_id = sms.account_id;
Vérifier que SQL Server Agent utilise Database Mail
DECLARE @DatabaseMail VARCHAR(255);
DECLARE @res TABLE
(
Value VARCHAR(255)
, Data VARCHAR(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail';
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile';
IF (
SELECT COUNT(*)
FROM @res r
WHERE r.Value = 'UseDatabaseMail' AND r.Data = 1
) = 1 AND
(
SELECT COUNT(*)
FROM @res r
WHERE r.Value = 'DatabaseMailProfile' AND r.Data IS NOT NULL
) = 1
SET @DatabaseMail = 'Configured'
ELSE
SET @DatabaseMail = 'Not Configured';
select @DatabaseMail
Variante qui renvoie les valeurs brutes du registre :
DECLARE @res TABLE
(
Value VARCHAR(255)
, Data VARCHAR(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE'
, @key = N'Software\Microsoft\MSSQLServer\SQLServerAgent'
, @value_name = N'DatabaseMailProfile';
INSERT INTO @res
EXEC sys.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE'
, @key = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, @value_name = N'UseDatabaseMail'
SELECT *
FROM @res;
Script de configuration complet
Active Database Mail, crée un profil et un compte SMTP, les associe, puis crée un opérateur de l'Agent.
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
sp_configure 'show advanced options',0
go
reconfigure
go
-- Profile
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = 'sqlmail')
BEGIN
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'sqlmail',
@description = '';
END
-- Account
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'SQL Server Mail')
BEGIN
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL Server Mail',
@email_address = 'user@example.com',
@display_name = 'sqlserver01',
@replyto_address = 'user@example.com',
@description = '',
@mailserver_name = 'smtp.example.com',
@mailserver_type = 'SMTP',
@port = '25',
@username = NULL,
@password = NULL,
@use_default_credentials = 0,
@enable_ssl = 0;
END
-- Associate the account to the profile
IF NOT EXISTS(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 = 'sqlmail'
AND a.name = 'SQL Server Mail')
BEGIN
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'sqlmail',
@account_name = 'SQL Server Mail',
@sequence_number = 1;
END
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'sql_operator',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'user@example.com',
@category_name=N'[Uncategorized]'
GO
Dépannage
Pour l'erreur « Profile name is not valid » (msg 14607), voir cet article.