Password management options for the SQL Server sa login

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
In earlier tips from the sa series we outlined 'When not to use the sa password' and 'When was the last time the sa password changed?'.  In this installment of the sa series we will be outlining options for password management.  In a nutshell, depending on the security needs of the organization dictates how the sa password should be managed.  Although at a certain level, the sa login needs to be protected even in environments without specific legal or regulatory requirements.  As the security needs increase, then it is necessary to implement additional measures to manage and protect the most privileged (out of the box) login in SQL Server, the sa login.

Solution
As a DBA\Developer it is necessary to handle any privileged account's password with great care.  As such, here are some techniques to do so:

• Do not use the sa login unless necessary and when you think it is necessary research other options to validate no other options exist
  • When not to use the sa password
• Do not let any applications get promoted to the production environment if they use the sa login
• Use an electronic or physical password safe to manage the passwords to ensure they are stored in a secure location as opposed to a sticky note or some other easily accessible location
• Create a password with 20+ characters (mixed case), numbers, symbols
• Have a limited number of DBAs\Developers know the password or have access to the password to limit the potential exposure
• Audit the login usage to the SQL Server error log or capture the usage with Profiler or a third party tool

• Change the password on a regular basis whether that is monthly, quarterly, semi-annually
• Ensure that changing the password is not a chore
• Change the password when a DBA\Developer who knows the sa password leaves the organization
• If you are in a secure environment, split the password between 2 DBAs so 1 DBA knows the first half of the password and another DBA knows the second half of the password
• If you are using SQL Server 2005, leverage the new password options of 'Enforce password policy' and 'Enforce password expiration'

• Setup different sa passwords for each server, so that if the sa password is compromised on 1 server all of the servers are not compromised
  • If that is not feasible, setup different sa passwords between environments i.e. development, test and production or setup different sa passwords based on the application environment i.e. financial, legal, manufacturing, human resources, etc.
• Move to mixed mode authentication model and rely on the Windows capabilities to manage the user name and passwords in SQL Server
• Know the portions of SQL Server (i.e. Reporting Services, Analysis Services, etc.) that do not use the sa password and have a separate administrative access paradigm
  • <-- SQL Server Security Note --> Windows Groups to Support SQL Server 2005 Applications

Next Steps

• Evaluate how the sa password or any login\account password is handled in your environment to determine if it is secure enough or if changes should be made for how the passwords are managed.
• Take a good hard look at your environment to determine how the passwords should be managed in a reasonable manner.  What works for 1 environment may not work for another, but at some level the passwords should be managed to offer a viable means for protecting the most privileged login in SQL Server.
• Check out these related tips on MSSQLTips.com:
  • When not to use the sa password
  • When was the last time the sa password changed?
  • <-- SQL Server Security Note --> Windows Groups to Support SQL Server 2005 Applications
  • <-- Security Bulletin --> How do I enforce SQL Server 2000 password changes?
  • All Security Tips
  • All Compliance Tips

Related Tips: More | Become a paid author

Last Update: 1/10/2007

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. Need SQL Server help and not sure where to turn? Reach out to the Edgewood experts for a Health Check starting at $995. Get SQL Server Tips Straight from Kevin Kline. Join the over million SQL Server Professionals who get their issues resolved daily. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...