Tag: administration

I wrote recently about my philosophy for service accounts, and wanted to add a few more thoughts.

Security is important for our database servers. One of the loopholes that everyone should be aware of is that the service running SQL Server has complete control over the service and potentially if this account were compromised, the security of our installation would be at risk.

In this post I wanted to address two things related to service account passwords. The mechanics of building and working with these passwords and the ongoing maintenance in terms of changing the passwords.

Creating Passwords

One of the tools I recommend for anyone administering computer systems, including my parents on their personal computers, is a password manager. There should be a way for you to create and store complex passwords that are not easily guessed. I use Password Safe, but 1Password, KeyPass, and others are just as good.

Typically I’ve used these to store the administrative passwords for various systems for all DBAs, sysops, etc. to use. However I haven’t used these for service accounts.

Why not?

Mostly because I don’t think any of us should be logging in as services. Apart from initial setup and testing, we shouldn’t use service accounts for anything.

I always recommend long, complex, random passwords for services. The password should be created and written down long enough for someone to enter it twice in the areas reserved for credentials, and then the paper should be destroyed.

I write these down because I want extremely long (20+), random strings that aren’t memorable and are really a one-time use string. Used just long enough to enter into the Services applet or as a credential in a PoSh (or other) script.

If you use groups for your account rights, and you should even for service accounts (SQL Server makes this easy), you can always use another account to test access. Grant it the same permissions and groups, and perform your tests.

Changing Passwords

I don’t worry about changing service account passwords. Yes, I know this isn’t recommended, but services rarely change or are used to log on, we can limit the access of an account to a particular machine, and since the password isn’t stored, it’s not very vulnerable to cracking.

If you are worried, then create a new, long, random string for the particular service(s) that are suspected to be vulnerable.

I don’t allow expiration of service account passwords, though in a few organizations that have required yearly service account password changes, we’ve scheduled the changes for slow periods, not waiting until the expiration occurred. I can almost guarantee that accounts will expire during a critical time when machines should not go down.

One caution. I know that changing passwords to long, complex strings is hard, and that there’s a temptation to set services to the same password or use some pattern to build passwords.

Don’t.

Patterns are poor security, and coupling services together with the same password (or account) is not worth the risk of issues if one system requires a change or the password is disclosed.

Recently someone sent me a question about service accounts. They weren’t sure how they should go about setting accounts up for various instances and services in their environments. Specifically they asked me about having domain accounts, or accounts separate for services.

Note that I’ve managed SQL Server for years this way in environments up to hundreds of instances. I haven’t managed thousands, so there might be issues with this philosophy at scale.

Here’s how I view service accounts. In a short list, I try to manage things like this:

• Domain accounts for the SQL database engine and SQL Agent
• Separate accounts for all instances and all Agent services
• Long, complex, one-time passwords that aren’t stored.

This has worked well for me, providing separation of services so that password changes or security issues on one instance don’t affect other instances.

It’s also been scalable in that I rarely setup SQL Server instances. In most organizations I’ve worked in, we are adding a few instances a week at the most. The overhead to create two new accounts per instance (db engine and Agent) is minimal.

Note that I would also have a separate domain account for SSAS or other items I install.

With today’s rapid provisioning of machines through virtualized environments, I realize this isn’t necessarily a good hard and fast rule. If I expect an instance to be a production level instance and live for some period of time in the organization, I’d follow this philosophy.

However if I am bringing online development and test instances that may not be kept around permanently, I think the local service accounts are fine. These will probably handle your needs and are worth scripting into your VM/instance creation process.

I’ll add a few more thoughts on this across other posts, but there’s my idea in a nutshell.