Today, we will discuss a real-world scenario that everyone should be aware of – how to determine who changed the password of your SQL Login. This knowledge can be invaluable in situations where security is compromised or auditing is required.
When working with customers in my consulting role, I often come across challenging questions and requirements. One such requirement arose when a client noticed that the SQL “SA” account password was being randomly reset. They needed to identify who was responsible for these changes.
Initially, I checked the SQL Server error log, but unfortunately, it does not record login password change events. My next thought was to explore the “Default trace” as it should have the necessary data. However, I discovered that the SQL Server default trace only records the Audit Login Change Property Event Class and not the Audit Login Change Password Event Class.
With the default trace not providing the required information, the client had no choice but to run a custom trace to track password changes. Another approach that came to mind was using the undocumented command “fn_dblog” to read the transaction log. However, I informed the client that this method is not officially documented and has limitations, such as data being flushed in the transaction log if a checkpoint is fired.
Nevertheless, I provided the client with a script to troubleshoot the issue. The script involved creating a login for demonstration purposes and then changing the password. By querying the transaction log using “fn_dblog”, they were able to identify the login name associated with the password change. Additionally, they could determine which account’s password was modified by querying the “sys.server_principals” table.
It is important to note that the use of undocumented commands should be approached with caution as they are not officially supported. Careful consideration should be given to their usage to avoid any unintended consequences.
I hope you found this blog post helpful in understanding how to determine who changed the password of your SQL Login. If you have any interesting ways in which you have used similar techniques, I would love to hear about them. Please feel free to share your experiences and insights.