Recently, one of my clients contacted me to understand why they are seeing such messages in their application event log. Where it says The Description for Event ID 18456 From Source MSSQLSERVER Cannot be Found.
When I searched in ERRORLOG, I did see below message at the exact same time.
2017-10-29 19:27:44.87 Logon Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: ]
It looks like due to some missing file event viewer is enabled to “decode” the message and showing only the placeholders like below.
- sa
- Reason: Password did not match that for the login provided.
- [CLIENT: <local machine>]
If we check sys.messages for error 18456, we can see below.
Login failed for user ‘%.*ls’.%.*ls%.*ls
As we can see above, there are three placeholders which are shown above. It looks like there is some DLL or file which is used to decode the value. Here is the correct message in the event log.
WORK DONE / SOLUTION
Based on my search on the internet, its “decoded” by files present in a registry key.
Here is the path because mine is default instance. “EventMessageFile” under
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\MSSQLSERVER
The file was there but looks like it was of incorrect version. I copied it from another machine. Then I reopened event viewer and the same message was now shown as below.
Log Name: Application
Source: MSSQLSERVER
Date: 29-Oct-17 7:27:44 PM
Event ID: 18456
Task Category: Logon
Level: Information
Keywords: Classic,Audit Failure
Description:
Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: ]
Have you seen this behavior earlier?
Reference: Pinal Dave (https://blog.sqlauthority.com)
