SQL Server runs under the aegis of a “service” in Windows. That means it’s running in the background all of the time. A Service in Windows requires the ability to “log on” to the system – even if you don’t see that happening. Which brings us to this post.
First, you should use a regular, low-privileged user account to start SQL Server. There are several “system” accounts that you can use, but most of time they have either too many rights an privileges in Windows to run SQL Server, or too few. The only exception is the Express Edition – but that’s another post. so create a domain user, just a regular account, with no special privileges or rights (yet).
The next important thing to remember is that you should always assign and work with SQL Server accounts using the SQL Server Configuration Manager tool – not the Services Applet in Control Panel. The reason why is that the CM tool does more than just set a password or start a service – it grants all of the rights and privileges a body needs to run the SQL Server system, even down to the files level.
Which brings us to today’s post. Most shops have a “rotation” rule about changing passwords – for people. When SQL Server is started with a user account, it often falls under this rule. So when it’s time to change the passwords for everyone else, you have to change the SQL Server password as well.
The question is, should you have to re-start the SQL Server service after you change it’s password? The answer is yes for SQL Server 2005 and lower (more here on that). The reason is that Windows doesn’t actually pass around passwords when it “knocks at the door” of a resource like a fileshare. Windows creates a separate value for the password, called a “token”. That is what is passed around for the account. So when you change the password for the user account that starts SQL Server, the domain now knows about the new token, but the Service (which hasn’t “logged off”, so to speak) is still using the “old” token. So while the database might keep running for a while, eventually backups fail and so on. So you need to restart the service – again, it’s best to use Configuration Manager for that, but in this case that can be automated with PowerShell or some other utility for a simple restart.
Now, if you have hundreds of servers (like I’ve hand in the past) then this can be a real pain. You can – and this might not be the best solution – set the user account that runs SQL Server not to require a password change. In some of my shops I set a very low priv account to run SQL Server, make a very complicated password, record that on paper, and then lock that paper in a sealed envelope in a safe. That way only one person knows the password (me), but others could get to it in an emergency. But if they did, they would need physical access (the safe) and we would know (the envelope would be torn open).
This may not be a security best practice in your shop, so make sure you discuss it with everyone to make sure you’re doing the right thing.
In SQL Server 2008 and later, this isn't so much of a problem. Check here for more: http://msdn.microsoft.com/en-us/library/ms365941.aspx