The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

伺服器被 sysprep 一般化後導致 SQLAgent 無法啟動的問題

昨天試著準備一台含有 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

相關連結