Changing SQL Server Service Account or Password – Avoid restarting SQL Server


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. image 
    • 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. image
    3. Next, change the domain service password and click OK
    4. image
  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. image

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. image
  4. Click on “Yes” on the following Message Box that comes up (beware, this message box might appear in the background)
  5. image

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.

Advertisements

3 thoughts on “Changing SQL Server Service Account or Password – Avoid restarting SQL Server

  1. marc_jellinek@hotmail.com

    You state:

    > Once the password has been changed –
    > there is no need for the SQL Server
    > service to be restarted

    SQL Server 2008 R2 Books Online states:

    > To change the SQL Server service
    > startup account On the Start menu,
    > point to All Programs, point to
    > Microsoft SQL Server 2008 R2, point to
    > Configuration Tools, and then click
    > SQL Server Configuration Manager.
    >
    > In SQL Server Configuration Manager,
    > click SQL Server Services.
    >
    > In the details pane, right-click the
    > name of the SQL Server instance for
    > which you want to change the service
    > startup account, and then click
    > Properties.
    >
    > In the SQL Server
    > Properties dialog box, click the Log
    > On tab, and select a Log on as account
    > type.
    >
    > After selecting the new service
    > startup account, click OK.
    >
    > A message box asks whether you want to
    > restart the SQL Server service.
    >
    > Click Yes, and then close SQL Server
    > Configuration Manager.

    To me, it sounds like a service restart is still required after changing the account under which SQL Server runs.

    Good pointer to SQL Server Configuration Manager and its ability to modify the registry permissions.

    Like

    Reply
  2. Nakul Vachhrajani

    @marc_jellinek: I think we are reading two different versions of Books On Line:

    Per [http://msdn.microsoft.com/en-us/library/ms365941.aspx][1],

    To change the password used by the SQL Server service

    1. Click the Start button, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
    2. In SQL Server Configuration Manager, click SQL Server Services.
    3. In the details pane, right-click SQL Server (), and then click Properties.
    4. In the SQL Server () Properties dialog box, on the Log On tab, for the account listed in the Account Name box, type the new password in the Password and Confirm Password boxes, and then click OK.

    ***The password takes effect immediately, without restarting SQL Server***

    I have changed the service passwords with this method a couple of times, without any issues.

    What BOL page did you refer? This might be a documentation issue that needs to be brought to the attention of Microsoft.

    [1]: http://msdn.microsoft.com/en-us/library/ms365941.aspx

    Like

    Reply
  3. Pingback: #0405 – SQL Server – Msg 5133 – Backup/Restore Errors – Directory lookup for file failed – Operating System Error 5(Access is denied.). | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s