How do I enforce SQL Server 2000 password changes?

By: Jeremy Kadlec | Updated: 2006-09-05 | Comments | Related: > Auditing and Compliance

Problem

One of the simplest security best practices is changing passwords on a regular basis. For some organizations that could be quarterly while others may have a more aggressive policy. Regardless of the policy, the issue remains the same, how can I enforce SQL Server 2000 password changes for my logins?

Solution

Scoping out the environment is the first place to start. What will probably be found is that you have user (logins for specific users), application (logins for an application to access SQL Server), system logins (the sa login) and administrative logins (logins used by DBAs). Of those logins, SQL Server 2000 standard and Windows (Domain\UserName) authenticated logins probably exist. Each of these require a different approach and offer a various level of password automation. Let's see what options are available to address these needs.

ID	Login Purpose	Login Type	Approach to Change Passwords
1	User Login - Login a business user would enter with their password to access the web based or desktop application	SQL Server Standard Login	If you know that logins do not normally have login changes (i.e. additional server defined roles or removal of server defined roles, change to the default database, etc.) then in the application during the login process, query the updatedate column of the master.dbo.syslogins table to find out when the password was last changed If the password has exceed your organization's password duration policy, issue prompt the user for a new password and commit to SQL Server 2000 with the sp_password system stored procedure
		Windows Login	With Windows based authentication to SQL Server, default Windows policies can be setup to enforce the number of days when passwords should be changed
		Custom Account and Password Solution	Record the date and time of the password changes, check to see if the password change threshold has changed and prompt the user to change their password

2	Application Login - Login an application would use from a connection string	SQL Server Standard Login	Need to coordinate password changes with developers in order to ensure the correct password is in all connection strings
		Windows Login	If you have the luxury to leverage Windows authentication on an Intranet or desktop application, once again leverage the native Windows policies for password changes

3	System Logins - The notorious system login in SQL Server is 'sa' which has the highest level of rights in the environment	SQL Server Standard Login	Ensure the sa login is not used in any applications, DTS Packages or scripts to be able to change this password at any point in time Many options are available to secure this password to include: Do not share the password outside of the DBA group Do not have any more than 1 DBA know this password and secure the password in an electronic or physical safe Split the password between two DBAs requiring 2 individuals to perform any password change to the sa login
		Windows Login	Not applicable

4	Administrative Logins - Administrative logins for DBAs and System Admins	SQL Server Standard Login	Avoid a situation where DBAs and System Admins use SQL Server Standard Logins rather than Windows logins
		Windows Login	Ensure the Windows policy is setup as specified above

Next Steps

If you are unfamiliar with your organization's password policies, revisit them and ensure the SQL Server's you manage are making the grade.
If your organization does not have password policies, review the type of work your organization conducts and assess the sensitivity of the data to determine:
- How frequently the passwords should be changed i.e. 30, 60, 90 days?
- Should the passwords have a minimum length, letters, numbers, capitalization, special characters, etc.?
- Should the passwords be passwords or pass phrases?
- Should the passwords become inactive after a finite number of login failures?
Stay tuned for new options available with SQL Server 2005 to improve standard login password complexity and expiration capabilities.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2006-09-05

How do I enforce SQL Server 2000 password changes?

Problem

Solution

Next Steps

About the author

Comments For This Article