Sql Server Password Last Change Date

[Tabita Knezevic]

Usethe Net User command to display the date and time you last set your Windows 10 user account password. Check the Password last set output of the net user %username% command.

The AD Pro Toolkit includes a built in schedular so you can automate running a report on when users last changed their password. Click on Scheduler and then click the Add button to create a schedule. This report can be emailed daily, weekly or monthly.

In a development environment I want to modify the 'password last set' date of my AD accounts so they won't begin to expire during development phase, but as soon as the environment becomes a production environment.

All you need to do to reset the pasword clock is open ADusers and computersfind the user/users in question (you can do a bulk change by highlighting several users)On the account tab - tick the change at next login and click apply and then untick the same box and apply again

Passwords need to be changed regularly. This is one of the most basic password security best practices as it protects user accounts from hackers, scammers, and other security threats. Therefore, a strict password change policy is required in every organization.

This native way to find the last password changed for an AD User Account is complex and requires knowledge of how to write a PowerShell script. A more straightforward solution to this is to use Lepide Active Directory Auditor.

Lepide Active Directory Auditor overcomes the complexity of the native method by providing a straightforward way to list all passwords older than a specified number of days by using the Password Older than n Days Report:

As you can see, Lepide Active Directory Auditor provides a far simpler method of finding the data a password was last changed for an AD User Account. Start a free trial of Lepide Active Directory Auditor to get instant access to this Active Directory audit report.

We are looking at methods to identify whether a user has changed their Windows account password (Local or Domain account). Is there any foolproof way we can determine whether password has been changed?

We got to know that using Windows NT Authority command 'net user' and checking the 'password last set' value we will get this information. However we have also found that there are tools through which it is possible to modify these values and manipulate the output of the command, which makes using the password last set value as unreliable. Also can we reliably compare the windows account password hashes of old and new passwords and determine that password has been changed ? i.e. can we assume administrator cannot revert back to old password hash temporarily after logging to disguise the password change?

Also can we reliably compare the windows account password hashes of old and new passwords and determine that password has been changed ? i.e. can we assume administrator cannot revert back to old password hash temporarily after logging to disguise the password change?

On domain members and workstations, local user account password hashes are stored in a local Security Account Manager (SAM) Database located in the registry. They are encrypted using the same encryption and hashing algorithms as Active Directory.

We have already found out that the value of 'PwdLastSet' can be checked using 'net user' command (has been mentioned in our question). What we were looking for was whether it can be manipulated using any malicious tool/hacker - that is, whether someone can actually change the password, yet leave the 'PwdLastSet' value unchanged to indicate that there has not been any change in the password.

Scroll to the pwdLastSet field. Modify it by entering 0 (zero) in the value field. Click OK. This sets the value to (Never) as in the password has never been set. Click OK on the User Account Properties box.

Try this ADManager Plus - Reports based on User management that should help you to Reset Password Expiration Date and more than 150+ Report based on user management in AD

Link: Windows AD password reporting software - ADManager Plus

Hi Twon,

Thanks for posting the powershell for this. Do you know why just doing $user.pwdlastset does not modify the value for that parameter?

Also - is it necessary to set it to 0 first, then to -1?

It is necessary to set it to 0 first, then to -1. If you try just setting -1, then it reverts to the date that was initially set. If you set 0 first, it clears out that initial date, then -1 will set the current date.

Requiring regular user account password changes is one of the most basic password security best practices. It protects user accounts from being accessed using old credentials gleaned from phishing or other attacks on a Microsoft Windows machine or Windows server. Therefore, a password change policy is required in every organization.

Netwrix Auditor for Active Directory makes it easy to review all password changes for a certain user account, providing details such as who changed the password, when the change occurred, which workstation it was done from, and how many times the password was changed within a given period of time. Reviewing this report will help you investigate both account lockout issues and attacks. You can also easily create an alert that notifies you in real time whenever the password for a particular user account is changed.

I dont think the date is stored in a human readable form though it may need some post processing to concert to something you can use....I cant check this right now as I am not near an AD! - but please feedback what you get out of the query value wise

You are right. The password last change date is in some crazy format. Everything i get out of the ADODsObject thru sp_OAMethod is a big integer value. Although when you query it using the SELECT from ''LDAP://server.com'', it gives appropriate values for each column except that i cannot find the column name for password last change date.

In the comments section there is a series of comments by Dave Naples covering User Information Retrieval. In that section he covers UserAccountControl, whenChanged, and whenCreated. The field whenchanged will be either the password last changed field you are looking for, or a field describing the last time info in the account was updated.

I need to check when function was changed last time. I know how to check creation date (it is in function properties window in SQL Server Management Studio).

I found that in SQL Server 2000 it wasn't possible to check modify date ( look at this post: Is it possible to determine when a stored procedure was last modified in SQL Server 2000?)

With the rights that the sa login has by default in SQL Server, it is imperative to change this password on a regular basis whether it is monthly, quarterly or semi-annually. In addition, as DBAs move on to other opportunities, it is wise to change the sa password as well. Changing the sa password should be a relatively easy process requiring little to no impact on the organization. Unfortunately, changing the sa password on a regular basis is not a common practice at most organizations, because the impacts of changing the password are unknown.

The first step in the process is to find out when the sa password was last changed. If this timeframe is unacceptable to your organization, then steps need to be taken to understand where the sa login is used and how the application can be modified to use another login.

In SQL Server 2000 a documented process does not exist to determine when the sa password was changed. The best means to determine if the sa password has changed is based on the value from the updatedate column in the master.dbo.syslogins table. This value seems to be the only possible column to determine if any property (default database, default language, etc.) for the sa login has changed. Since the sa properties do not change frequently, the value for this column should be a reasonable, but not an absolute indicator of when the sa password was last changed. Reference the code below to determine the value for the sa login's updatedate column.

Unfortunately, SQL Server 2005 suffers from the same self documenting issue as SQL Server 2000 as it pertains to the last time the sa password has changed. The modify_date of the sys.sql_logins catalog view can be used as an indicator of when the last property (default database, default language, etc.) for the login has changed. Just as is the case with SQL Server 2000, this value does not guarantee the date\time stamp of the password change, but rather any property change. Since the properties do not change frequently under normal circumstances this value can serve as a reasonable, but not an absolute indicator of when the sa password was last changed. If other properties have changed, the modify_date is not a true indicator and other research/documentation is needed to determine the last sa password change date.

For this specific exercise, we can't depend on the information Alteryx Server provides for change management. I'm wondering if there is some other way to see the last modified date. For example, for designer we can check directory that the YXMD files are saved in and windows explorer shows the last modified date.

In this thread it mentions you can see the "files" in a mongoDB format, which would work for our purposes, but the naming convention doesn't make sense to me and I'm not sure if these files are 11 with workflows.

Hi @LanceFace, if you make any changes to a workflow and publishes it to the Server, this will be a new version of the workflow. You can see the Latest Version date on the Server UI, on the workflow page.

Thanks both for your responses. Unfortunately we are not able to rely on the Alteryx Server app at all for this audit, so the UI and the API are not going to work. To be honest, I'm not sure there's an answer.

As the others are hinting at, your colleague must be talking gibberish, or referring to something else. The on-disk structure for a record, or page for that sake, does not contain any references to the time of the last update. While you can find info regarding the last update at the object level, no such info is available at the record/row level.

3a8082e126