Get AD Last password change date from a SQL SP

Question

Post reply

Get AD Last password change date from a SQL SP

Justin-14543

SSCrazy

Points: 2017
More actions
December 4, 2009 at 9:01 am

#137408

I am using sp_OACreate to query AD using ADsDSOObject. I am able to get the following info with no problem.
SELECT cn, mail, sAMAccountName, userAccountControl, adspath
FROM ''LDAP://server.com''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
order by mail
I need to get the last password set date for each login. I do not know the column name to pass in the SELECT clause to get the last password change date. Can anyone enlighten me with this.
I have tried passwordExpires, pwdLastSet etc and it does not work in this query. I need to know the column name that can give me the last password change date.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stueyc Valued Member Points: 63 More actions · Answer 1

you are looking for "PasswordLastChanged"

example vbscript code would be

Set objUser = GetObject _

("LDAP://CN=user1,OU=userAccounts,DC=company,DC=com")

lastpwd = objUser.PasswordLastChanged

WScript.echo "PasswordLastChanged is: " & lastpwd

it's the same ldap object query you are trying to do.

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

thanks

Stuart Coney

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 2

When I last looked at this myself it was in some crazy format that I had to do some conversions to get it into a date/time that was usable..

CEWII

Justin-14543 SSCrazy Points: 2017 More actions · Answer 3

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.

Stuart - I tried the PasswordLastChanged attribute in my Select statement and it does not work. This attribute works only from VB script. It does not work from my select statement within sql server.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Here is a pretty good read on it.

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

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.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events