For anyone who has run Microsoft SQL Server under a domain account, the first thing that comes to mind upon thinking about changing the account or the account password is planning for downtime. However, starting Microsoft SQL Server 2008, this does not have to be the case.

Microsoft SQL Server 2005 and below

In Microsoft SQL Server 2005 and below, changing the account password for the Microsoft SQL Server service normally involved:

1. Launching Microsoft Management Console (MMC)
2. Adding the Services snap-in (services.mmc)
3. Navigate out to the SQL Server service
4. Change the domain account and/or the account password
5. Repeat the same for all dependent services (like the Full Text Search service, etc)
6. Restart the SQL Server & dependent services

Restarting the SQL Server means downtime for your applications – a message which no administrator wants to take to the rest of the organization.

Microsoft SQL Server 2008 and above

Starting Microsoft SQL Server 2008, this is no longer the case. If the above steps are followed, then one might end up with at least one of the following issues:

1. Windows Registry permissions are not properly – this prevents the service running under the new account from reading SQL server settings
2. A service restart is required for the changes to take effect

If the following steps are followed, then the Microsoft SQL Server service does not need to be restarted and the appropriate Windows Registry permissions also set automatically.

The right way…

1. Go to Start –> All programs –> Microsoft SQL Server <version>
2. Go to Configuration Tools
3. Click on “SQL Server Configuration Manager”
4.  
   • Alternatively, you can run the following on the “Run” prompt:
     • SQLServerManager11.msc – For Denali CTP03
     • SQLServerManager10.msc – For SQL Server 2008
5. Within the SQL Server Configuration Manager, go to the “SQL Server Services” node
6. In the right-hand side pane, you will see all the SQL Server services listed
   1. Simply right-click on the required SQL Server Service and go to “Properties”
   2. 
   3. Next, change the domain service password and click OK
   4. 
7. Once the password has been changed – there is no need for the SQL Server service to be restarted

If ever you need a service restart

Let’s say you need a service restart because of a configuration change. Instead of heading over to the Services.msc console, one should be using one of the following options:

Using SQL Server Configuration Manager

1. Right click on the SQL Server Service
2. Click on “Restart”
3. 

Using SQL Server Management Studio

1. In the Object Explorer pane, connect to the server in question
2. Right-click the server name and click “Restart”
3. 
4. Click on “Yes” on the following Message Box that comes up (beware, this message box might appear in the background)
5. 

My observation

Old habits die hard. I have seen a lot of professionals (developers, especially) who still use the Services snap-in to manipulate SQL Server permissions and to restart these services. It is my recommendation to them to make a conscious effort to use the SQL Server Configuration Manager for these tasks.

For more reference on how to setup Windows Service accounts for Microsoft SQL Server, please refer: http://msdn.microsoft.com/en-us/library/ms143504.aspx

Until we meet next time,

Be courteous. Drive responsibly.