早上巡檢的的時候，發現一資料庫的作業報如下錯誤（作業名等敏感信息已經替換），該作業的OWNER為一個域賬號： JOB RUN: 'JOB_NAME' was run on 2016-6-1 at 7:00:00 DURATION: 0 hours, 0 minutes, 1 seconds STAT... ...
早上巡檢的的時候，發現一資料庫的作業報如下錯誤（作業名等敏感信息已經替換），該作業的OWNER為一個域賬號：
JOB RUN: 'JOB_NAME' was run on 2016-6-1 at 7:00:00
DURATION: 0 hours, 0 minutes, 1 seconds
STATUS: Failed
MESSAGES: The job failed. Unable to determine if the owner (Domain\UserName) of job JOB_NAME has server access (reason: Could not obtain information about Windows NT group/user 'Domain\UserName', error code 0x2095. [SQLSTATE 42000] (Error 15404)).
在SQL Server Agent錯誤日誌裡面，你會看到如下錯誤信息：
Date 2016/6/1 7:10:01
Log SQL Server Agent (Current - 2016/6/1 8:40:00)
Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'Domain\UserName', error code 0x2095. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
關於15404的錯誤的官方介紹如下所示。具體參考下麵鏈接信息
Details
| Product Name | SQL Server |
| Event ID | 15404 |
| Event Source | MSSQLSERVER |
| Component | SQLEngine |
| Symbolic Name | SEC_NTGRP_ERROR |
| Message Text | Could not obtain information about Windows NT group/user 'user', error code code. |
Explanation
15404 is used in authentication when an invalid principal is specified. Or, impersonation of a Windows account fails because there is no full trust relationship between the SQL Server service account and the domain of the Windows account.
User Action
Check that the Windows principal exists and is not misspelled.
If this error is the result of a lack of a full trust relationship between the SQL Server service account and the domain of the Windows account, one of the following actions can resolve the error:
-
Use an account from the same domain as the Windows user for the SQL Server service.
-
If SQL Server is using a machine account such as Network Service or Local System, the machine must be trusted by the domain containing the Windows User.
-
Use a SQL Server account.
具體來說，是因為當SQL Server實例不能訪問AD Server，因為AD Server出現了異常，導致SQL Server服務（本地Windows用戶帳戶）試圖驗證AD中的用戶“域\用戶名”，但是無法驗證，因為它沒有正確的/許可權訪問AD資源。
The SQL Server service (a local Windows user account) was trying to authenticate the user "domain\userName" in AD, which it could not do because it does not have the right/permission to access AD resources.
另外關於15404錯誤，其實有多種可能性，根據具體的代碼有所不同（error code 0x5、error code 0x2095 等），以前我遇到一個error code為0x5的案例，總結在這一篇MS SQL Could not obtain information about Windows NT group/user 'domain\login', error code 0x5. [SQLSTATE 42000] (Error 15404)。
15404 is the exception when EXECUTE AS context cannot be impersonated. Reasons for these error are plenty. The most common reasons are:
- when the SQL Server instance does not have access to the AD server because is running as a local user or as 'local service' (this would have an error code 0x5,
ACCESS_DENIED) - when the SQL Server is asked to impersonate an unknown user, like an user from a domain the SQL Server has not idea about (this would have the error code 0x54b,
ERROR_NO_SUCH_DOMAIN)
The proper solution is always dependent on the error code, which is the OS error when trying to obtain the impersonated user identity token: one searches first for the error code in the System Error Codes table (or fires up windbg, does a loopback non-invasive kernel debug connection and goes !error, which is what I prefer cause is faster...).
解決方法一般也很簡單，將作業的ower改為sa或將SQL Agent服務改為擁有sysadmin角色的賬號即可解決問題。
參考資料：
https://msdn.microsoft.com/en-us/library/dn205134(v=sql.110).aspx
