Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

How do I enforce SQL Server 2000 password changes?


By:   |   Read Comments   |   Related Tips: More > 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.



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools