SQL Certificate / 跨 DB 信任

SQL Certificate 跨 DB 信任

1. 跨DB 使用Stored Procedure執行msdb.dbo.sp_send_dbmail傳送郵件時,常會有如下的錯誤訊息:

image

除了設定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;

image

 

-- 備份憑證至檔案

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

image

 

/*

-- 解除簽章

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';

 

-- 未匯入私鑰,簽章時會有以下錯誤

image

 

-- 刪除憑證

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警示,並不影響憑證匯入。

clip_image010

clip_image012

Alter語法僅能對憑證新增或移除私鑰,憑證的其它屬性無法修改。另外可以 Drop Certificate [Certificate_Name]刪除憑證,但前提是該憑證沒有拿來對其它物件簽章或加密。

clip_image014