How do I enforce SQL Server 2000 password changes?

By:   |   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.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec 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


Comments For This Article

















get free sql tips
agree to terms