Making Passwords expire

Question

Making Passwords expire

Jeremy Kemp

SSCertifiable

Points: 6754
More actions
December 1, 2003 at 6:39 am

#59508

Our site is 'encouraging' us to improve some of the security aspects of our application. One area they are particularly keen on is the use of non-expiry passwords.
We have a small number of developers they have all had their own SQL Server login id and password. We can change this so that they log into SQL Server with their Windows User Id and password which meets the company's dictat.
However, we have a few SQL Server user accounts which are not real people. For example, we have an account for our batch processing and another one for our web site (the site is protected by a login screen where users enter their windows user id and password but the web site logs on to SQL Server with its own account). There is also the SA account!!
Is there a way within SQL Server to force passwords to exipre after 90 days? At the moment we are thinking of manually changing the passwords every month but are not sure whether this will satisfy the company security requirements.
Thanks
Jeremy

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

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

cppwiz SSCarpal Tunnel Points: 4036 More actions · Answer 1

I would create a SQL Agent job to run a password-changing script every 90 days. The script could loop through the syslogins table and call sp_password for every SQL login. The new passwords could be constructed using parts of the time-stamp. A login with securityadmin or sysadmin rights does not need to know the old password to set the new password. Then the script could email the new password to the owners of the SQL accounts so they could change the passwords themselves if they don't like it.

You might also consider replacing the sp_password proc with your own proc to force complex passwords. Check out:

http://www.sqlservercentral.com/products/enforcepass/

grhollis Mr or Mrs. 500 Points: 578 More actions · Answer 2

Jeremy,

Check the updatedate field in the syslogins table. It is updated every time the password is updated. you could setup a job that does a datediff. If you really wanted to do it, you could send e-mail to them when it is getting ready to expire in X number of days and them reset it after 90 days.

Note, I am not sure what else updates the updatedate field in the syslogins table.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 3

Thanks for the ideas. I was thinking about a datediff function as well but it does need more investigation.

However, we going to try and get everything using Windows login ids, whose passwords do expire, and this will solve the majority of our problems.

I'm not sure about the SA password but as we are handing the application over to another department (i.e. outsourcing) I will leave them that little problem.

Jeremy

buddy__a Hall of Fame Points: 3222 More actions · Answer 4

For web servers you can set the web service process user to a domain account (much like the you set the SQL Server service user account). Of course you'd then have to change the web code's connections string to use trusted connections instead of UID and PWD.