SQL Server外部作業時的權限問題探討
前幾天在玩SQL複寫功能時，在建立發行集時發生權限上的問題。就是SQL的快照集代理程式
(Snapshot Agent)要在repldata\unc資料夾建立快照資料時有權限上的問題，導致無法正常執
行複寫功能。
當時我SQL的服務帳戶跟SQL Server Agent是同一帳戶(本機User帳戶，不是Administrators)。
而我在設定發行集時，快照集代理程式的帳戶是勾選使用SQL Server Agent帳戶。
因此我做了一個測試，我用SSMS來備份一個資料庫到repldata\unc資料夾。卻發現可以將
資料庫正常的備份到該資料夾中，沒有遇見權限不足的問題。
此時一個問題困擾著我，SQL服務帳戶 = SQL Server Agent服務帳戶 = Snapshot Agent帳戶。
但是Snapshot Agent用該帳戶做事有權限問題，而SQL Server Agent用該帳戶做事卻不會。
下圖中我建了一個一般USER帳戶叫StartSQL。
在SQL的組態管理員中我們可以看見SQL的服務帳戶跟SQL Server Agent都是StartSQL這一個帳戶。
我們先檢視一下unc這一個資料夾的安全性設定。下圖中的設定StartSQL帳戶應該是無權限存取。
設定快照集代理程式是使用SQL Server Agent帳戶來做。
如下圖所示快照集代理程式作業失敗，狀態是拒絕存取路徑。而下圖中背景Process Monitor
也顯示Snapshot.exe程式用StartSQL這一個帳戶在CreateFile時發生Access Denied的情形
(下圖紅框圈選處)。
發生上圖狀況後，我利用SSMS執行一個Backup Database的動作到unc資料夾中。我們可以
在下圖中看見備份是成功的。下圖背景Process Monitor也有資訊顯示sqlservr.exe程式利用
StartSQL這一個帳戶CreateFile到unc資料夾其狀態是SUCCESS。
如下圖，我們到unc資料夾中也看見備份檔案了。
上述幾個步驟中我們所有的作業都是使用StartSQL這一個帳戶來Access unc資料夾。
但是同一個帳戶對同一個資料夾卻是有兩種權限。StartSQL可以寫入備份檔到unc中，
卻無法建立快照檔案到unc中。
因此我重新安裝一台Win2012R2+SQL2014來做實驗，並且將SQL服務帳戶及SQL
Server Agent帳戶切開，方便我們釐清問題。
如下圖所示SQL服務帳戶為StartSQL，SQL Server Agent帳戶為SQLAgent。皆為一般USER權限。
開啟SQL組態管理員，如下圖所示，我們可以確認各服務所使用的帳戶為何。
在開始建立發行集之前，我們先看一下該unc資料夾目錄的預設權限為何。
其預設權限如下圖所示，其中下圖紅色圈選處MSSQLSERVER群組的完整名稱為
NT SERVICE\MSSQLSERVER。如果搜尋時沒打上完整名稱而只是打上
MSSQLSERVER是會搜尋不到的。
下圖是我們執行快照代理程式發生錯誤的畫面，錯誤訊息為拒絕路徑存取。
而下圖背景為我們利用Process Monitor來看unc資料夾被存取的資訊。由下
圖紅色圈選處我們可以知道Snapshot.exe程式用SQLAgent這一個帳戶來存
取unc資料夾發生Access Denied的情形。
由上面取得的資訊，可以確認快照集代理程式的確是用SQLAgent這一個帳戶在Work。
下面這一個步驟為建立一個backup database的JOB，並觀察SQL是使用甚麼帳戶來備份資料庫到unc資料夾。
如下圖所示，備份排程定在上午10:45分。
在10:45分時Process Monitor記錄到(下圖紅色圈選處)。Sqlservr.exe程式利用StartSQL帳戶
寫資料到unc資料夾，狀態是SUCCESS。因此我們可以知道備份作業SQL是使用SQL服務帳戶
來做，而不是SQL Server Agent帳戶來做。
但StartSQL這一個帳戶在repldata\unc中並沒有權限啊。為何該帳戶就可以寫檔案進unc資料夾呢?
因此我懷疑是MSSQLSERVER這一個群組讓StartSQL有權限，所以我LAB過程中有將MSSQLSERVER
從unc中移除權限，備份作業也就失敗了，這裡就沒截圖了。
NT SERVICE\MSSQLSERVER到底是甚麼樣的帳戶呢 ?
我們可以在Windows服務帳戶與權限這一篇MSDN文章中找到相關說明。
虛擬帳戶
Windows Server 2008 R2 和 Windows 7 中的虛擬帳戶是「受管理的本機帳戶」
(Managed Local Account)，會提下列功能來簡化服務管理工作。 虛擬帳戶是自
動管理的，而且虛擬帳戶可以在網域環境中存取網路。 如果在 Windows Server
2008 R2 或 Windows 7 上安裝 SQL Server 的期間針對服務帳戶使用預設值，則
會使用以執行個體名稱做為服務名稱的虛擬帳戶，其格式為 NT SERVICE\<SERVICENAME>
。 以虛擬帳戶執行的服務，會利用電腦帳戶的認證，存取網路資源，格式為
<網域名稱>\<電腦名稱>$。
由上述的說明中我們可以知道NT SERVICE\MSSQLSERVER是SQL Server服務的虛擬帳戶。
這個虛擬帳戶在安裝SQL2012時是預設的SQL服務帳戶(如下圖所示)
因此我們即使在上圖步驟中更改SQL的服務帳戶，但完成安裝後我們可以去檢視SQL
相關資料夾目錄安全性設定，會發現NT SERVICE\MSSQLSERVER這虛擬帳戶被加入
可存取的權限，反倒是我們指定的SQL服務帳戶沒有被加入(如下圖所示)。
下圖則是NT SERVICE\MSSQLSERVER在SQL Server安裝後相關目錄的預設權限。其中可以看見
relpdata這一個資料夾也在表列中。下表來源
而在查詢文件過程中看見NT SERVICE\SQLSERVERAGENT虛擬帳戶，這個虛擬帳戶是預設當
SQL Server Agent服務帳戶的。因此我就想也許將NT SERVICE\SQLSERVERAGENT這一個帳
戶加入unc中，那也許SQL Server Agent服務帳戶(SQLAgent)也會因為這一個設定而擁有unc
存取權限。
如下圖所示我們將NT SERVICE\SQLSERVERAGENT這一個帳戶加入到repldata這一個資料夾中。
而unc資料夾會繼承repldata權限設定。
我們開啟unc資料夾的安全性設定。可以看見unc繼承了repldata的權限設定，因此多了一個
SQLSERVERAGENT群組。
當我們將NT SERVICE\SQLSERVERAGENT這一個虛擬帳戶加入unc後，重新執行快照集代理程式。
代理程式執行成功(如下圖所示)。而從Process Monitor看到Snapshot.exe一樣使用SQLAgent這一
個帳號來執行作業，而這一次就SUCCESS了。但我們並沒有將SQLAgent帳戶加入到unc安全性內，
所以可以判斷應是SQLAgent繼承了NT SERVICE\SQLSERVERAGENT權限而讓該帳戶有權限存取unc
資料夾。
而當我們再將NT SERVICE\SQLSERVERAGENT從repldata及unc資料夾的安全性中移除後
(如下圖所示)。
我們可以發現快照集代理程式又無法存取unc資料夾了。
LAB到這邊，似乎會發現一個規則，就是如果該帳戶當下是被SQL Server服務使用就
會被賦予繼承NT SERVICE\MSSQLSERVER 的權限。而當下如果是被SQL Server Agent
服務使用就會被賦予繼承NT SERVICE\SQLSERVERAGENT的權限。那該如何驗證這個規
則是否正確呢?
我打算建立兩個複寫發行集，一個發行集的快照代理程式帳戶會直接勾選UI中
【SQL Server Agent帳戶】。而另一個發行集的快照代理程式帳戶則是直接鍵
入SQL Server Agent帳戶(SQLAgent)的帳密，來看看相同的帳戶在兩種設定方
式下會有不同的權限嗎?
開始設定前，我們先將NT SERVICE\SQLSEEVERAGENT虛擬帳戶及SQLAgent
帳戶都設定可存取unc資料夾，如下圖紅色圈選處。
下圖中我們建立兩個發行集，rpSqlAgent發行集的快照代理程式是勾選UI中
【SQL Server Agent帳戶】。而rpNotSqlAgent發行集則是我們直接鍵入
SQL Server Agent帳戶(SQLAgent)的帳密。
首先我先執行rpSqlAgent的發行集的快照代理程式，並且利用Process Explorer程式
擷取快照代理程式執行時該Process的相關資訊。
如下圖所示，我們可以看見該Process是執行Snapshot.exe程式。而執行帳戶為
W12R2_SQL2014\SQLAgent。而這一個帳戶所屬的Group也都被表列出來，其
中值得我們注意的是W12R2_SQL2014\SQLAgent帳戶在這個Process中是
NT SERVICE\SQLSERVERAGENT虛擬帳戶的Owner，因此也繼承了該虛擬帳戶
的權限。
接下來我們執行rpNotSqlAgent的發行集的快照代理程式，並且利用Process Explorer程式
擷取快照代理程式執行時該Process的相關資訊。
如下圖所示，我們可以看見該Process也是執行Snapshot.exe程式。而執行帳戶也是
W12R2_SQL2014\SQLAgent。帳戶所屬的Group也都被表列出來，而
W12R2_SQL2014\SQLAgent帳戶在這一個Process中並不是NT SERVICE\SQLSERVERAGENT
虛擬帳戶的Owner，因此也不會繼承虛擬帳戶權限。
從上面兩個步驟中我們發現兩個發行集的快照代理程式是執行同一支程式Snapshot.exe
並且使用同一個帳戶W12R2_SQL2014\SQLAgent，但該帳戶在兩個Process中卻有兩種權限。
繞了一大圈後似乎找到我們一開始的問題解答。我之前的觀念認為一樣的帳戶在系統資源的存
取權限應該都是一樣的啊，看來並不是如此簡單啊。
小結
※ Process是指定由【SQL Server服務帳戶】執行，則當下的執行帳戶會被授予繼承
NT SERVICE\MSSQLSERVER虛擬帳戶權限。
※ Process是指定由【SQL Server Agent服務帳戶】執行，則當下的執行帳戶會被授予繼承
NT SERVICE\SQLSERVERAGENT虛擬帳戶權限。
※【服務帳戶】的權限 = 一般帳戶(實驗中StartSQL或SQLAgent)權限 + 虛擬帳戶權限。
※ 這樣的設計目的也許是方便權限管理，相關系統資源如針對虛擬帳戶授權，這樣日後就算
我們更換SQL的服務帳戶，新帳戶也會繼承虛擬帳戶權限，因此造成的影響也沒那麼大。
至於系統如何依據服務運作相關權限賦予，可參考James大哥搜尋到並提供給我參考的文章
SQL Server Service Account and Per-Service SID
參考資料來源
SQL Server Service Account and Per-Service SID
我是ROCK
rockchang@mails.fju.edu.tw
