LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon