SQL Server on Linux之Database Mail設定及測試(使用postfix套件設定Mail Server)

在本篇介紹SQL Server on Linux的Database Mail設定,其實設定方式跟在Windows的版本差不多,只是這裡將自行額外安裝postfix套件來設定Mail Server,擔任MTA(Mail Transfer Agent)的角色,並嘗試設定兩種郵件設定檔(Profile),即"公開性質"跟"私人性質"兩種,另外,將在sqllnx1及sqllnx2分別使用【UI介面】及【T-SQL】兩種方式分別建置Database Mail。

為了方便對照,再次說明下圖是對部門內部介紹SQL Server on Linux時的實作環境,安裝完CentOS 7.x後,記得先執行 #yum update -y  ,以更新所有使用yum下載安裝的套件。

 

<前置作業-設定postfix郵件伺服器>

--請在sqllnx1 & sqllnx2上設定
--(1)安裝套件
#mail    --找不到命令
#yum install -y postfix mailx            --安裝相關套件套件,安裝mailx套件是為了在client端收發郵件
#less /etc/postfix/main.cf               --檢視postfix的設定檔
#grep -n '^[^#]' /etc/postfix/main.cf    --或 #cat /etc/postfix/main.cf | grep -n '^[^#]'    --檢視postfix設定檔中的"非註解部分"的內容

--(2)修改postfix設定檔
#vi /etc/postfix/main.cf
-------------------------------------
--在sqllnx1的設定內容
myhostname = sqllnx1.dba.com  --郵件伺服器主機名稱
mydomain = sqllnx1.dba.com    --郵件伺服器負責的網域名稱,與DNS的MX設定有關
myorigin = $mydomain          --由postfix寄出的郵件,其寄件人的欄位,所要顯示的網域名稱
inet_interfaces = all         --設定想要接收主機裡哪些網路介面傳來的郵件
mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain    --定義這台郵件伺服器自家人的網域
-------------------------------------
--在sqllnx2的設定內容
myhostname = sqllnx2.dba.com
mydomain = sqllnx2.dba.com
myorigin = $mydomain
inet_interfaces = all
mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain
-------------------------------------

--(3)設定防火牆
#firewall-cmd --list-all
#firewall-cmd --permanent --add-service=smtp
#firewall-cmd --reload
#firewall-cmd --list-all

--(4)啟用服務並重新開機
#systemctl enable postfix
#reboot
#systemctl status postfix

--(5)以root權限,建立測試用帳戶sqlalert(後面設定Database Mail時會用到此帳戶),並設定其密碼
#adduser sqlalert
#passwd sqlalert

--(6)測試電子郵件的傳送與接收
--在sqllnx2上,以root登入,輸入以下內容:
#mail sqlalert@[192.168.56.131]     --@後面的郵件網域名稱,系統會到DNS解析,但目前環境沒有架DNS,所以直接用#mail sqlalert@sqllnx1.dba.com會執行錯誤(查/var/log/maillog)
Subject: Test send mail from sqllnx2
Hello! Test mailer!!
.    --輸入一個點號,代表本文結束
#tail /var/log/maillog    --檢查電子郵件傳送記錄
--切換至sqllnx1,以sqlalert帳戶登入(記得退出所有以su指令切換的帳戶),並執行以下指令,確認是否有收到mail
#mail

--(7)反向測試,在sqllnx1上,以root登入,輸入以下內容:
#mail sqlalert@[192.168.56.132]     --@後面的郵件網域名稱,系統會到DNS解析,但目前環境沒有架DNS,所以直接用#mail sqlalert@sqllnx2.dba.com會執行錯誤(查/var/log/maillog)
Subject: Test send mail from sqllnx1
Hello! Test mailer!!
.    --輸入一個點號,代表本文結束
#tail /var/log/maillog    --檢查電子郵件傳送記錄
--切換至sqllnx2,以sqlalert帳戶登入(記得退出所有以su指令切換的帳戶),並執行以下指令,確認是否有收到mail
#mail

 

<Database Mail的服務架構>

見上圖,我們可以使用sp_send_dbmail系統預存程序來傳送電子郵件,若要使用屬於私人性質的郵件設定檔(Profile),該電子郵件傳送的帳戶(User),必須是msdb資料庫中的DatabaseMailUserRole資料庫角色的成員才能傳送郵件。而DatabaseMail90.exe外部程序,可視為一個SMTP用戶端,即MUA ( Mail User Agent )郵件使用者代理人的角色,是幫忙使用者進行郵件收發工作的軟體。SMTP郵件伺服器,即MTA ( Mail Transfer Agent )郵件傳送代理人的角色,主要的功能就是將所收受的信件,依照信件的Flow( 送到哪裡去 ) 來決定將該信件放置到本機帳戶下的郵件信箱中 ( Mailbox ),或者是再將這個信件送到下個MTA 去。另外,Database Mail有支援叢集架構。

 

<Database Mail的運作架構>

見上圖,[使用者] vs [設定檔],以及[設定檔] vs [帳戶],都可以是1對多的關係,即同一個[使用者],可以對應至不同的[設定檔],同一個[設定檔],亦可以指定不同的[帳戶]。依據[使用者]為"msdb資料庫的public角色"或是屬於"在msdb資料庫自訂的user",郵件設定檔(Profile)可分為"公開性質"跟"私人性質"。上圖中每個[帳戶],指的都是一個郵件伺服器的設定。

 

<練習1: 設定Database Mail - 使用UI介面> 請連上sqllnx1操作 (僅設定"公用設定檔")

使用Host上的SSMS連結至sqllnx1(192.168.56.131),啟用Database Mail後,開始新增設定檔。給定「設定檔名稱」後,開始設定第一個帳戶,即”sqllnx1alert”。因為sqllnx1alert是透過sqllnx1自己本身的postfix服務來寄送Email,所以「伺服器名稱」記得設定為”localhost”,可使用匿名驗證。

緊接著設定第二個帳戶,即”sqllnx2alert”。因為sqllnx2alert是透過sqllnx2的postfix服務來送Email,所以「伺服器名稱」設定為”192.168.56.132”,用之前在sqllnx2上建立的sqlalert帳戶來做驗證。

將該設定檔設定為「公用設定檔」,會自動使用msdb資料庫的public角色來提供處理Email資料的權限

測試Database Mail,從sqllnx1寄Email給sqllnx2上的sqlalert帳戶,注意「收件者」欄位,若要使用IP,則要加上中括弧[ ]

以sqlalert帳戶登入sqllnx2,用mail指令查看信件,確實有收到sqlalert@sqllnx1.dba.com寄來的Email

以root帳戶登入sqllnx1,用tail指令查看/var/log/maillog

 

<練習2: 設定Database Mail - 使用T-SQL> 請連上sqllnx2操作 (設定"公用設定檔"及"私人設定檔")

--請在sqllnx2(192.168.56.132)上執行
--create login 'sql2admin1'
USE [master]
GO
CREATE LOGIN [sql2admin1] WITH PASSWORD=N'xxxxxx'  --密碼請自行變更
GO

--create user 'sqladmin1' for private profile
use msdb
go
create user sqladmin1 for login sql2admin1
go
exec sp_addrolemember 'DatabaseMailUserRole','sqladmin1'  --加入msdb資料庫中的DatabaseMailUserRole資料庫角色的成員
go

-- Create DB Mail Account
EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'sqllnx1alert',  --第一個帳戶名稱
@description = 'This Mail Account is set for SQLLNX1', 
@email_address = 'sqlalert@sqllnx1.dba.com',   --寄件者
@replyto_address = 'sqlalert@sqllnx1.dba.com', 
@display_name = 'SQL Agent on SQLLNX1', 
@mailserver_name = '192.168.56.131',   --因為指令是在sqllnx2上執行,且指定mail server為sqllnx1
@port = 25, 
@enable_ssl = 0,         --當設定為1時,表示此服務可以使用TLS
@username = 'sqlalert',  --指定Linux的sqlalert帳戶來進行驗證
@password = 'xxxxxxxx'   --密碼請自行變更
GO

EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'sqllnx2alert',  --第二個帳戶名稱
@description = 'This Mail Account is set for SQLLNX2', 
@email_address = 'sqlalert@sqllnx2.dba.com',    --寄件者
@replyto_address = 'sqlalert@sqllnx2.dba.com', 
@display_name = 'SQL Agent on SQLLNX2', 
@mailserver_name = 'localhost',     --因為指令是在sqllnx2上執行,且指定mail server為sqllnx2自己本身,故採匿名驗證
@port = 25, 
@enable_ssl = 0  
GO

-- Create DB Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'publicProfile',
@description = 'Public Profile for sending Automated DBA Notifications'
GO

EXECUTE msdb.dbo.sysmail_add_profile_sp 
@profile_name = 'privateProfile',
@description = 'Private Profile for sending Automated DBA Notifications'
GO

-- Add Mail Account to Mail Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name ='publicProfile',
@account_name = 'sqllnx2alert',
@sequence_number = 1
go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name ='privateProfile',
@account_name = 'sqllnx1alert',
@sequence_number = 1
go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name ='privateProfile',
@account_name = 'sqllnx2alert',
@sequence_number = 2
go

--設定公開設定檔
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public',       --msdb資料庫的public角色
@profile_name = 'publicProfile',  --此profile設為公開
@is_default = 1
go

--設定私人設定檔
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
@principal_name = 'sqladmin1',     --msdb資料庫的使用者帳戶,有加入msdb資料庫中的DatabaseMailUserRole資料庫角色的成員
@profile_name = 'privateProfile',  --此profile設為私人
@is_default = 1
go


--可使用SSMS查看sqllnx2的Database Mail設定


--測試
EXECUTE msdb.dbo.sp_send_dbmail 
@profile_name = 'publicProfile',  --此profile設為公開
@recipients = 'sqlalert@[192.168.56.131]',  --指定[收件者]
@Subject = 'Testing DBMail from SQLLNX2',   --此郵件由sqllnx2發送
@Body = 'This message is a test for DBMail' 
GO
--以sqlalert帳戶登入sqllnx1,並檢視mail

EXECUTE msdb.dbo.sp_send_dbmail 
@profile_name = 'privateProfile',  --此profile設為私人
@recipients = 'sqlalert@[192.168.56.132]',  --指定[收件者]
@Subject = 'Testing DBMail from SQLLNX2 by SQLLNX1', --此郵件由sqllnx1(為第一順位)或sqllnx2(為第二順位)發送
@Body = 'This message is a test for DBMail' 
GO
--以sqlalert帳戶登入sqllnx2,並檢視mail

 

<補充說明: 當Lab環境有安裝DNS Server時的參考>

--若在centos(192.168.56.121)架設DNS Server後,[寄件者]就可以使用email address來寄信,而不用IP
--請自行安裝BIND(Berkeley Internet Name Domain)相關套件及調整設定檔,此部分不細談

--(1)在centos(192.168.56.121)執行
# less /etc/named.conf
----------------------------------------------
--擷取部分內容
options {
        listen-on port 53 { any; };    --有調整為"any"
        listen-on-v6 port 53 { ::1; };
        directory       "/var/named";
        dump-file       "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
        recursing-file  "/var/named/data/named.recursing";
        secroots-file   "/var/named/data/named.secroots";
        allow-query     { any; };    --有調整為"any"
.
.
.
(中間省略)
.
.
.
include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";
----------------------------------------------

# less /etc/named.rfc1912.zones    --查看內容,有自行增加一個zone,即"dba.com"
----------------------------------------------
zone "dba.com" IN {
        type master;
        file "named.dba";    --此檔案需自行建立
        allow-update { none; };
};
----------------------------------------------

# ll /var/named/
# less /var/named/named.dba    --查看內容,有簡單地指定了幾筆A紀錄、NS(Name Server)記錄、MX(Mail Server)記錄
----------------------------------------------
$TTL 1D
@       IN SOA  @ rname.invalid. (
                                        0       ; serial
                                        1D      ; refresh
                                        1H      ; retry
                                        1W      ; expire
                                        3H )    ; minimum
        NS      @
        A       192.168.56.121
sqllnx3 A       192.168.56.133
sqllnx2 A       192.168.56.132
sqllnx1 A       192.168.56.131
@       IN      MX      10      @
----------------------------------------------

--(2)在sqllnx1(192.168.56.131)執行
# dig @192.168.56.121 sqllnx2.dba.com    --指定使用192.168.56.121為DNS Server,查sqllnx2的主機IP
----------------------------------------------
; <<>> DiG 9.11.4-P2-RedHat-9.11.4-16.P2.el7_8.3 <<>> @192.168.56.121 sqllnx2.dba.com
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 44666
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 2

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;sqllnx2.dba.com.               IN      A

;; ANSWER SECTION:
sqllnx2.dba.com.        86400   IN      A       192.168.56.132

;; AUTHORITY SECTION:
dba.com.                86400   IN      NS      dba.com.

;; ADDITIONAL SECTION:
dba.com.                86400   IN      A       192.168.56.121

;; Query time: 0 msec
;; SERVER: 192.168.56.121#53(192.168.56.121)
;; WHEN: 四  5月 21 16:38:39 CST 2020
;; MSG SIZE  rcvd: 90
----------------------------------------------

# dig @192.168.56.121 dba.com    --指定使用192.168.56.121為DNS Server,查Domain所在的IP
----------------------------------------------
; <<>> DiG 9.11.4-P2-RedHat-9.11.4-16.P2.el7_8.3 <<>> @192.168.56.121 dba.com
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 12126
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;dba.com.                       IN      A

;; ANSWER SECTION:
dba.com.                86400   IN      A       192.168.56.121

;; AUTHORITY SECTION:
dba.com.                86400   IN      NS      dba.com.

;; Query time: 0 msec
;; SERVER: 192.168.56.121#53(192.168.56.121)
;; WHEN: 四  5月 21 16:41:20 CST 2020
;; MSG SIZE  rcvd: 66
----------------------------------------------

# dig @192.168.56.121 dba.com NS    --指定使用192.168.56.121為DNS Server,查詢dba.com網域的NS記錄
----------------------------------------------
; <<>> DiG 9.11.4-P2-RedHat-9.11.4-16.P2.el7_8.3 <<>> @192.168.56.121 dba.com NS
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 3616
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 2

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;dba.com.                       IN      NS

;; ANSWER SECTION:
dba.com.                86400   IN      NS      dba.com.

;; ADDITIONAL SECTION:
dba.com.                86400   IN      A       192.168.56.121

;; Query time: 0 msec
;; SERVER: 192.168.56.121#53(192.168.56.121)
;; WHEN: 四  5月 21 16:45:05 CST 2020
;; MSG SIZE  rcvd: 66
----------------------------------------------

# dig @192.168.56.121 dba.com MX    --指定使用192.168.56.121為DNS Server,查詢dba.com網域的MX記錄
----------------------------------------------
; <<>> DiG 9.11.4-P2-RedHat-9.11.4-16.P2.el7_8.3 <<>> @192.168.56.121 dba.com MX
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 18426
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 2

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;dba.com.                       IN      MX

;; ANSWER SECTION:
dba.com.                86400   IN      MX      10 dba.com.

;; AUTHORITY SECTION:
dba.com.                86400   IN      NS      dba.com.

;; ADDITIONAL SECTION:
dba.com.                86400   IN      A       192.168.56.121

;; Query time: 0 msec
;; SERVER: 192.168.56.121#53(192.168.56.121)
;; WHEN: 四  5月 21 16:52:21 CST 2020
;; MSG SIZE  rcvd: 82
----------------------------------------------

--(3)在sqllnx1(192.168.56.131)執行
--若要暫時讓sqllnx1(192.168.56.131)使用centos(192.168.56.121)當作DNS Server(但是重開機後會回復原設定)
# vi /etc/resolv.conf     --修改檔案,在檔案前面增加一筆"nameserver 192.168.56.121"
# ping sqllnx2.dba.com    --成功

--測試電子郵件的傳送與接收
--在sqllnx1上,以root登入,輸入以下內容:
#mail sqlalert@sqllnx2.dba.com
Subject: Test send mail from sqllnx1
Hello! Test mailer!!
.    --輸入一個點號,代表本文結束
#tail /var/log/maillog    --檢查電子郵件傳送記錄

--切換至sqllnx2,以sqlalert帳戶登入(記得退出所有以su指令切換的帳戶),並執行以下指令,確認是否有收到mail
#mail

 

參考資料:

1. CENTOS6.4中修正錯誤訊息error (network unreachable) resolving