Five Ways to Manage your SQL Server Passwords [T-SQL Tuesday #58 – Passwords]

If you are responsible for several SQL Server installations, a question you might have is how to best manage the SA passwords. In this post I am going to explore a handful of different options.

T-SQL Tuesday #58

This post is my contribution to this month T-SQL Tuesday #58, which I have the honor of hosting: sqlity.net/en/2559/tsql2sday-58-invite/

The Line-Up

While there are many areas in which we have to deal with passwords, this article is looking at the particular case of handling the passwords for multiple SQL Server installations that give access to the SA account. As the SA account is the most powerful account (and also the most often attacked account) in SQL Server, it deserves special attention.

There are all the good old password rules that you really should follow anyways, but for SA in particular. The following list does not claim to be complete and I am not going into more detail, but you SA passwords should be: random, complex, long and most important not used for multiple instances.

If you follow that advice, you probably need a way to keep track of all these passwords, particularly if you are managing multiple servers. There are certainly many ways to skin this cat; I am going to cover the following:

• The Safe
• The Cloud
• Locally Encrypted
• Enterprise Password Manager
• Don't Have a Password

The Safe

If your instance count is "manageable", you can just print out a list of the passwords and store that list in some form of secure location like an actual safe. If you do not have an electronic version of this document floating around, this is certainly one of the more secure ways to manage your passwords.

It however gets cumbersome when it comes to changing those passwords. Things will likely get out of sync quickly. You might find yourself in a situations that requires immediate access to this list. That time is probably not the best time to discover that your list has become incomplete or just plain wrong over time.

However, if you have a reliable and diligent person taking care of this list, this method might be an option for you.

The Cloud

If you have a larger or a distributed team, "the safe" might not cut it. In that case, there are several cloud based password management systems available that also allow you to share passwords with the entire team. One option that I have worked with for a long time is Passpack. It is usable through the website interface or as a standalone program. Passwords are encrypted locally (even on the web-site version), so no one at Passpack can get access to your precious passwords.

A tool like Passpack makes it easy to share passwords across a distributed team. While they still can get out of sync, any person that has access to the store can also change the stored passwords easily making the maintenance less of a burden.

Locally Encrypted

If you do not trust "the cloud", a local password store might make more sense to you. The most prominent member in this category is probably KeePass. KeePass offers several features that make it one of the most secure ways to manage your personal passwords. That includes advanced obfuscation mechanisms to swart key loggers.

The passwords are stored in encrypted form in a file on the local file system. The encryption is designed to be "slow" by using multiple rounds of common encryption and hashing algorithms making brute force attacks infeasible. The passwords are also protected while handled in memory so that the system page file will never contain an unencrypted password.

However, KeyPass does not offer multi-user capabilities. You can attempt to implement you own, e.g. by checking the encrypted key file into you source control system, but at this point it becomes cumbersome again.

Enterprise Password Managers

If you are required to be PCI or HIPAA compliant or just have a really large number of servers to manage, an enterprise password management tool might be what you need. PasswordManager Pro by ManageEngine and CA ControlMinder are two examples of this type of tool.

An enterprise password manager not only stores the passwords, it can also automatically manage temporary access and access auditing. This includes the implementation of a Break-Glass process to give people quick access to the resource in emergency situations while creating a "loud" audit entry and sending out notifications to the appropriate people.

Enterprise password managers can even automate the required regular password change. You could for example use this to change all you SA passwords on a monthly basis without any user interaction.

Don't Have a Password

SQL Server allows us to disable SQL Authentication. In that case, the entire authentication and password management burden lies on Active Directory. In this operation mode, you can control someone's access to a particular instance just by adding the account in question to the appropriate AD group, an action that can be easily audited. You could also have a simple process running that automatically takes accounts back out of those groups after a set interval.

There is one undeniable advantage of this approach: If you do not have any passwords to manage, those passwords cannot leak either, so this is likely the most secure way to handle sysadmin access to your SQL Server instances. Not in all cases can you easily disable SQL Authentication, but it is an option you should seriously consider.

Summary

This article showed several ways you can use to manage your SQL Server SA passwords. All methods presented have their unique set of advantages and disadvantages and you have to figure out which one is the right one for you based on the requirements and the budget.

An enterprise password manager is most likely the most secure and most effective solution. However, you should also consider disabling SQL Authentication altogether.