Saturday, February 15, 2014

SQL Server 2008: An exception occurred while enqueueing a message in the target queue

Problem

During routine check of the farm database server event logs, you see the following Application event occuring at the rate of about 77 repetitions per minute:
Log Name:      Application
Source:        MSSQLSERVER
Date:          [date/time]
Event ID:      28005
Task Category: Server
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      [databasename]
Description:
An exception occurred while enqueueing a message in the target queue. 
Error: 15404, State: 19. Could not obtain information about Windows NT 
group/user '[domain\accountname]', error code 0x5.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQLSERVER" />
    <EventID Qualifiers="49152">28005</EventID>
    <Level>2</Level>
    <Task>2</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="[date/time]" />
    <EventRecordID>1127225</EventRecordID>
    <Channel>Application</Channel>
    <Computer>[databasename]</Computer>
    <Security />
  </System>
  <EventData>
    <Data>15404</Data>
    <Data>19</Data>
    <Data>Could not obtain information about 
Windows NT group/user '[domain\accountname]', error code 0x5.</Data>
    <Binary>656D000010000000100000004F00430053002D00
560053002D004E0041005600530051004C00440031000000070000006D00610073007400650072
000000</Binary>
  </EventData>
</Event>
Troubleshooting
  1. Reviewed server Application log to find start of issue:
    • Results: 33,000 repetitions of event completely flooded log through beginning - log was overwriting itself.  Estimated duration of event thus far: (33K total occurrences in log) / (77 occurrences per cycle) / (~ 63 seconds per cycle) > = 7 hours.
  2. Checked farm user content sites:
    • Result: no issues; users able to access content
  3. Checked Central Administration Health Analyzer Reports:
    • Result: no related issues.
  4. Launched SQL Server Configuration Manager and reviewed log on for SQL Server service: local system account.  Changed this to standard domain account per discussion in this reference.
    • Result: no resolution.
  5. Stopped and restarted SQL Server service
    • Result: no resolution.
  6. Restarted the server:
    • Result: no resolution.
  7. Reviewed the DBOs for all farm databases by using this statement:
    SELECT name, suser_sname(owner_sid) FROM sys.databases
    as discussed in this reference.
    • Result: did not find any NULL values as discusses in the reference, thus not helpful
  8. Checked locked status by executing this statement,
    SELECT LOGINPROPERTY('sa','IsLocked')
    according to this reference:
    • Result: not locked, thus not helpful
  9. Tested ability of problematic account to impersonate using this statement,
  10. EXECUTE AS LOGIN = ‘[yourdomain]\me’
    Go
    as discussed in this reference:
    • Results: failed. Tried again on problematic account: succeeded.  Tried on other service accounts: failed.
  11. Checked databases, service accounts and service account roles on database server
    • Results: found three databases created under (and thus owned by) the farm account, which was unusual, as these are usually created by the admin account. Databases involved the securestore and web analytics services. Neither the admin nor the farm account were mapped to these databases (this accounted for an unrelated error involving failed database upgrade - psconfig).
  12. Checked with systems admins regarding patching
    • Results: servers had been patched with latest Windows server CUs the previous day.  Found some older discussion associating eventID 28005 with patching.
  13. Again checked the farm databases against the error details
    • Result: again noted that the account identified in the error, the farm service account, was identified as the DBO for three particular databases; and that these databases were all created recently.  Also noted the owner for a new content database, created recently, also used the farm account.
  14. Changed the DBO for these three databases to my own administrator account using this TSQL statement:
    ALTER AUTHORIZATION ON DATABASE::NewContentDb TO "MyAdministratorAccount";
    • Result: no resolution, but improved my understanding of issue.  28005 error now presented my administrator account in its error message, rather than the farm service account.  This help bound the scope of the issue by demonstrating that I could affect the error outcome, even in a minor way.
  15. Changed the DBO for the new content database to the SA using the same TSQL statement:
    • Result: no resolution.
  16. Changed the DBO for these three databases to the SA.
    • Result: success.  EventID 28005 stopped appearing in the server Application log.
Solution
  1. Changed the DBO for the three particular databases to the SA (using the same TSQL statement shown above).
    • Result: success. 24 hours later, the error message still has not appeared.
Summary

The references pointed the way to the a solution, this being changing the DBO for databases to the SA.  This is not the optimal solution, but it at least resolves the immediate problem.  Note that not all databases had to have their DBO changed.  Only those associated with the domain account presented in the error event.  There remain databases in the farm, after implementing the solution above, for which the owner remains the farm administrator account.  And no error is being generated for them. So, it's unclear what the root cause here is.

One remaining task to be performed is to obtain a domain account for which the 'Allowed to authenticate' security setting has been enabled, as suggested by this reference (scroll to the bottom of the page).  This will require sysadmin assistance and will thus be implemented at a later date.

References
Notes
  • Farm topology: Windows Server 2008 R2, SQL Server 2008 R2 Standard, SharePoint 2010 Server Enterprise.
  • Methodology: the troubleshooting steps were wide ranging in order to more definitively bound the scope of the issue and improve understanding of it.

No comments: