昨天試著準備一台含有 SQL Server 的 VHD 基礎磁碟，在主機與資料庫都安裝設定好且都測試無誤後使用 sysprep 一般化處理過，試圖準備出一個非常乾淨且內含 SQL Server 的基礎磁碟，不過在開機完並設定伺服器基本參數後發現了我的 SQL Server 2005 的 SQL Server Agent 服務無法啟動，花了些時間才釐清問題，因此留下筆記。
執行一般化的工具在 C:\Windows\System32\sysprep\sysprep.exe
執行完後會是一個全新的作業系統，所以所有電腦、使用者與群組的 SID 都會變更，不過在我伺服器裡 SQL Server 2008 與 SQL Server 2008 R2 的 SQL Server Agent 都可以正常啟動，唯獨 SQL Server 2005 的 SQL Server Agent 服務無法啟動。
我進入 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG 目錄查看是否有任何蛛絲馬跡，如下圖示，每次 SQL Server Agent 被重新啟動（或啟動失敗）都會產生一個新的記錄檔，因此你可以找 SQLAGENT.OUT 這個最新的紀錄，或依據數字大小來尋找近期的錯誤，基本上數字越大的記錄檔是越舊的紀錄檔。
以下是錯誤訊息內容：
- [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [SQLSTATE 28000]
- [000] Unable to connect to server 'SQL2008R2\SQL2005'; SQLServerAgent cannot start
- [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [SQLSTATE 28000]
- [382] Logon to server 'SQL2008R2\SQL2005' failed (DisableAgentXPs)
- [098] SQLServerAgent terminated (normally)
如果錯誤訊息要對照 SQL Server 本身的錯誤記錄檔(ERRORLOG)也可以在同目錄下找到相關檔案：
我查到的錯誤也類似：
- Logon Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: <local machine>]
- Logon Error: 18456, Severity: 14, State: 11.
基本上看到這裡已經可以斷定是 SQL Server Agent 的服務帳戶 NT AUTHORITY\NETWORK SERVICE 無法成功登入 SQL Server 2005 才會導致服務無法啟動。
接著我分析資料庫的登入(Logins)，發現也有 SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005 這個群組，而且該群組也已經授與 sysadmin 伺服器角色，應該沒有權限問題才對：
接著我再查 SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005 這個本機群組是否包含 NT AUTHORITY\NETWORK SERVICE 這個系統帳戶，結果發現也沒問題：
最後，我再查出這個群組與系統帳戶的 SID （使用 PsTools 工具包裡的 PsGetSid 程式來查），基本上確認 NT AUTHORITY\NETWORK SERVICE 這個系統帳戶的 SID 是固定的 (S-1-5-20)，然而 SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005 這個群組的 SID 會與 SQL Server 有關嗎？
我回到 Management Studio 查看該 登入 在系統中註冊的 SID 看有無任何關連，但卻看不出異狀：
查到最後已經從作業系統層面排除了帳號、群組與 SID 的關係，最後一步就是考慮將 SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005 這個群組登入從 SQL Server 2005 裡移除並重新建立，結果就真的修復 SQL Server Agent 無法正常啟動的問題了。
登入重建的方法如下圖示，先由 SSMS 幫你產生重建的指令碼
然後再執行該指令碼即可
不過在 SQL Server 2005 中有三個內建的群組，其中有兩個群組必須授予 sysadmin 伺服器角色才能正常運作，因此還要手動進行設定以下兩個群組：
- SQLDEV\SQLServer2005SQLAgentUser$SQLDEV$SQL2005
- SQLDEV\SQLServer2005MSSQLUser$SQLDEV$SQL2005
最後我的完整 T-SQL 語法如下：
注意：以下語法請勿照抄，因為每台電腦名稱與SQL實體名稱都不太一樣，記得先改過再用！
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005')
DROP LOGIN [SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005]
GO
CREATE LOGIN [SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQL2008R2\SQLServer2005MSSQLUser$SQL2008R2$SQL2005')
DROP LOGIN [SQL2008R2\SQLServer2005MSSQLUser$SQL2008R2$SQL2005]
GO
CREATE LOGIN [SQL2008R2\SQLServer2005MSSQLUser$SQL2008R2$SQL2005] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC master..sp_addsrvrolemember @loginame = N'SQL2008R2\SQLServer2005SQLAgentUser$SQL2008R2$SQL2005', @rolename = N'sysadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'SQL2008R2\SQLServer2005MSSQLUser$SQL2008R2$SQL2005', @rolename = N'sysadmin'
GO
相關連結