SQL Certificate 跨 DB 信任
1. 跨DB 使用Stored Procedure執行msdb.dbo.sp_send_dbmail傳送郵件時,常會有如下的錯誤訊息:
除了設定trustworthy(ALTER DATABASE DB_Name SET TRUSTWORTHY ON)之外,較安全的作法是利用憑證(Certificate)建立SQL Login,然後賦予該SQL Login所需要的最小權限.
-- 建立測試 DB
CREATE DATABASE [TestDBMail_DB]
GO
-- 切換至測試 DB
USE [TestDBMail_DB]
GO
-- 建立測試預儲程式 sp_SendEmail
CREATE PROC [dbo].[sp_SendEmail]
(
@v_Profile NVARCHAR (20),
@v_TO NVARCHAR(50),
@v_CC NVARCHAR(50),
@v_Subject NVARCHAR(100),
@v_Body NVARCHAR(MAX)
)
WITH EXECUTE AS OWNER
AS
EXEC msdb.dbo.sp_send_dbmail -- 使用 msdb.dbo.sp_send_dbmial 寄發郵件
@profile_name = @v_Profile,
@recipients = @v_TO,
@copy_recipients = @v_CC,
@body = @v_Body,
@body_format = 'HTML',
@importance = 'High',
@subject = @v_Subject ;
GO
-- 建立憑證
CREATE CERTIFICATE [DBMail_Certificate]
ENCRYPTION BY PASSWORD = 'P@ssw0rd' -- 此處以密碼加密,也可用Master Key加密
WITH SUBJECT = 'Certificate for signing SendEmail SP';
GO
-- 檢視憑證
SELECT * FROM sys.certificates;
-- 備份憑證至檔案
BACKUP CERTIFICATE [DBMail_Certificate]
TO FILE = 'c:\SQL_Certificate\DBMailCertificate.CER';
GO
-- 使用憑證對 sp_SendEmail 簽章 (若 Stored Procedure 重新 compile 則簽章即失效,需重新簽章。)
ADD SIGNATURE TO OBJECT::[sp_SendEmail]
BY CERTIFICATE [DBMail_Certificate]
WITH PASSWORD = 'P@ssw0rd'; -- 若該憑證以 Master Key 加密,則此處不需提供解密密碼
GO
-- 檢視簽章
SELECT * FROM sys.crypt_properties
/*
-- 解除簽章
DROP SIGNATURE FROM OBJECT::[sp_SendEmail]
BY CERTIFICATE [DBMail_Certificate];
GO
-- 刪除憑證私鑰(刪除後該憑證將無法再對其它 object 作簽章動作)
ALTER CERTIFICATE [DBMailCertificate]
REMOVE PRIVATE KEY;
GO
*/
-- 在 Master DB 裡載入憑證
USE [master]
GO
CREATE CERTIFICATE [DBMail_Certificate]
FROM FILE = 'c:\SQL_Certificate\DBMailCertificate.CER';
GO
-- 以憑證建立 SQL Login
CREATE LOGIN [DBMailLogin]
FROM CERTIFICATE [DBMail_Certificate];
GO
-- 賦予 SQL Login 存取系統物件之權限
GRANT AUTHENTICATE SERVER TO [DBMailLogin]
GO
-- 賦予 SQL Login 使用 DB Mail 之權限
USE [msdb]
GO
CREATE USER [DBMailLogin] FROM LOGIN [DBMailLogin];
EXEC msdb.dbo.sp_addrolemember
@rolename = 'DatabaseMailUserRole',
@membername = 'DBMailLogin';
GO
-- 測試發送email
USE [TestDBMail_DB]
GO
CREATE LOGIN Ellie WITH PASSWORD = 'P@ssw0rd'; -- 建立測試帳號
CREATE USER Ellie;
Grant EXEC ON sp_SendEmail TO [Ellie];
EXEC AS USER = 'Ellie'; -- 模擬指定的 user
EXEC [sp_SendEmail]
@v_Profile = 'SQL_Profile',
@v_TO = 'ellie@gmail.com',
@v_CC = '',
@v_Subject = 'Test Mail From TestDBMail_DB',
@v_Body = 'This is an auto-generated email from TestDBMail_DB';
-- 清除測試物件
REVERT; -- 恢復成原先登入的 user
USE [master]
GO
DROP LOGIN [DBMailLogin]
DROP CERTIFICATE DBMail_Certificate
DROP LOGIN [Ellie]
DROP DATABASE TestDBMail_DB
2. 若該憑證欲跨DB對物件作簽章,需將私鑰一併匯入才能執行簽章動作。
CREATE DATABASE Test_Certificate_DB
GO
USE Test_Certificate_DB
GO
CREATE CERTIFICATE [Test_Certificate]
ENCRYPTION BY PASSWORD = 'P@ssw0rd'
WITH SUBJECT = 'Certificate for testing'
GO
-- 備份憑證及私鑰至檔案
BACKUP CERTIFICATE [Test_Certificate]
TO FILE = 'c:\SQL_Certificate\DBMailCertificate.CER'
WITH PRIVATE KEY
(
FILE = 'c:\SQL_Certificate\DBMailCertificate.Pvk' ,
DECRYPTION BY PASSWORD = 'P@ssw0rd', -- 對私鑰解密
ENCRYPTION BY PASSWORD = '123456' -- 寫至檔案前先對私鑰加密
);
CREATE DATABASE Test_Certificate_DB_2
GO
USE Test_Certificate_DB_2
GO
CREATE PROC SP_Test
AS
SELECT @@version;
-- 匯入憑證(不匯入私鑰)
CREATE CERTIFICATE [Test_Certificate]
FROM FILE = 'c:\SQL_Certificate\DBMailCertificate.CER';
-- 簽章
ADD SIGNATURE TO OBJECT::[SP_Test]
BY CERTIFICATE [Test_Certificate]
WITH PASSWORD = 'P@ssw0rd';
-- 未匯入私鑰,簽章時會有以下錯誤
-- 刪除憑證
DROP CERTIFICATE [Test_Certificate]
GO
-- 匯入憑證時連同私鑰一併匯入
CREATE CERTIFICATE [Test_Certificate]
FROM FILE = 'c:\SQL_Certificate\DBMailCertificate.CER'
WITH PRIVATE KEY
(FILE = 'c:\SQL_Certificate\DBMailCertificate.Pvk',
DECRYPTION BY PASSWORD = '123456',
ENCRYPTION BY PASSWORD = 'P@ssw0rd')
GO
ADD SIGNATURE TO OBJECT::[SP_Test]
BY CERTIFICATE [Test_Certificate]
WITH PASSWORD = 'P@ssw0rd';
GO
-- 清除測試物件
USE [master]
GO
DROP DATABASE Test_Certificate_DB
DROP DATABASE Test_Certificate_DB_2
3. 建立憑證時可以指定START_DATE及EXPIRY_DATE。預設START_DATE為系統日;EXPIRY_DATE為START_DATE + 1年。SQL Server Service Broker會檢查EXPIRY_DATE是否到期,但其它加密或簽章的內建函數並不會檢查到期日。如以下欲匯入已到期的憑證僅出現Warning警示,並不影響憑證匯入。
Alter語法僅能對憑證新增或移除私鑰,憑證的其它屬性無法修改。另外可以 Drop Certificate [Certificate_Name]刪除憑證,但前提是該憑證沒有拿來對其它物件簽章或加密。