Password expired and Deactivate logon Id

Question

Password expired and Deactivate logon Id

kommagoni

SSCommitted

Points: 1898
More actions
February 9, 2011 at 11:06 am

#231637

I have a Question.
As part of security guidelines, it is recommended that SQL Logon ID passwords be changed once a year.
The process would be manual in nature, we do not want to have a password expire and deactivate a Logon ID. That would cause a disruption to the application.
What I would like you to do is to research if there is a way to determine when a password was last changed. Focus only on SQL 2005 or SQL 2008 server. Do not worry about SQL 2000 servers.
Let me know anyone have a solution above issue

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

There's probably a way to do it through policy management.

You might be able to get "good enough" by just querying sys.sql_logins. The modify date updates if the data is changed. So if the modify date is more than a year ago, the password definitely hasn't been changed in that time.

I'd say it'll probably be easier to just change all the passwords on a scheduled basis. That way, you get it all done all at once. Do that as a quarterly or annual process, and you're good to go.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kommagoni SSCommitted Points: 1898 More actions · Answer 2

Can please explain clearely because in my server having around 225 databases, so many logins at the same time I dont want disruption to the application users , what i would like to know if there is anyway to determine when a password was last changed.Focus only on SQL 2005 and SQL 2008.

GSquared SSC Guru Points: 260824 More actions · Answer 3

You could set up a DDL trigger that tracks the Alter Login event, and logs any change in the password. Then you could query that log table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SumLasty SSCommitted Points: 1923 More actions · Answer 4

you can use loginproperty in sql 2008.

http://msdn.microsoft.com/en-us/library/ms345412(v=SQL.100).aspx

select loginproperty('sunsql','DaysUntilExpiration') as Daysexpiration

select loginproperty('sunsql','badpasswordtime') as badtime

select loginproperty('sunsql','badpasswordcount') as badcount

select loginproperty('sunsql','PasswordLastSetTime') as PasswordLastSetTime;

there is alimitations here -> will be used only for sql authenticated logins with check policy and expiration on.