How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005

Article translations Article translations
Article ID: 283811 - View products that this article applies to.
Expand all | Collapse all

On This Page

Summary

When you first install Microsoft SQL Server to run under a Microsoft Windows NT account, SQL Server sets for that Windows NT account various Windows user rights and permissions on certain files, folders, and registry keys. If you later change the startup account for SQL Server (the MSSQLServer service) and the SQL Server Agent service by using SQL Server Enterprise Manager (SEM) or SQL Server Configuration Manager (SSCM), SEM automatically assigns all the required permissions and Windows user rights to the new startup account for you so that you do not have to do anything else. We recommend that you use this is the approach to change the service account.

Note You must have administrator rights on the remote server for this functionality to be available within SQL Server Enterprise Manager.

However, if you use the Services add-in that is in Control Panel or in Administrative Tools to change the startup account information for the MSSQLServer service or the SQL Server Agent service, there are additional permissions and user rights that you must set.

This article discusses the steps that you must take when you change the startup account information by using the Services add-in.

Before you continue, visit the following Microsoft Web sites and view the articles in the Microsoft Knowledge Base:Microsoft Security Bulletin MS02-038
http://www.microsoft.com/technet/security/bulletin/MS02-038.mspx

Microsoft Security Bulletin MS02-034
http://www.microsoft.com/technet/security/bulletin/MS02-034.mspx
322853 FIX: SQL Server grants unnecessary permissions or an encryption function contains unchecked buffers
Note Complying with MS02-034 and MS02-038 removes existing administrative credential elevation vulnerabilities and helps prevent future ones.

Changing the SQL Server or the SQL Server Agent Service Account by using the services add-in instead of using SQL Enterprise Manager or SQL Server Management Studio

If you change the SQL Server Service Account or SQL Server Agent Service Account by using the Services add-in instead of using SEM or SSCM, there are certain registry and NTFS file system permissions and Microsoft Windows user rights that must also be set. This is especially true for SQL Server Desktop Engine (also known as MSDE 2000) or SQL Server 2005 Express Edition installations because you do not have SEM or SSCM to use to perform the permissions changes. There are three specific areas that you must focus on:
  • Registry keys.
  • NTFS file system permissions on the disk.
  • Windows User rights.
Each one is discussed separately in the following paragraphs.

Registry keys

Set Full Control for the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Microsoft Windows NT account, or a domain Windows NT account) on the registry keys that are in the following list. Under the following hives, thes keys in this list are the keys where Access Control Lists (ACLs) are set. For clusters, follow this step on every node in the cluster.

Full Control permission applies to the following keys and all child keys:
  • For a named instance:
    HKEY_LOCAL_MACHINE\Software\Clients\Mail

    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\80

    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instancename>
  • For a default instance:
    HKEY_LOCAL_MACHINE\Software\Clients\Mail

    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\80

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Cluster

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Providers

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Replication

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Setup

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\SQLServerAgent

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Tracking

If you are using SQL Server 2005

For a named instance or a default instance, applies the Full Control permission to the following keys and all child keys:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>
Note In this registry subkey, <MSSQL.x> is a placeholder for the corresponding value for the system. You can determine the corresponding value for the system from the value of the registry entry that is named as the instance name in the following registry subkey. For a default instance, the instance name is MSSQLSERVER:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\

NTFS file system permissions on the disk

Set Full Control for the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) on these NTFS folders. For clusters, you must also modify the corresponding paths on each computer node.

Here is an example for a named instance:
D:\Program Files\Microsoft SQL Server\MSSQL$_instancename_\
Here is an example for a default instance:
D:\Program Files\Microsoft SQL Server\MSSQL\
Subfolders and files must also have the same permissions.

If you are using SQL Server 2005

the corresponding folder is the following:
Drive:\Program Files\Microsoft SQL Server\<MSSQL.1>\MSSQL

Windows user rights

Typically, the default installation of the operating system gives the Local Administrators Group all the user rights that SQL Server requires to function correctly. Therefore, local Windows NT accounts or domain accounts that have been added to the Local Administrators Group, with the intent of being the startup account for the SQL Server service, have all the user rights that they require. However, we do not recommend that you run SQL Server under such high user rights.

For SQL Server 2005, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, see the "Reviewing Windows NT Rights and Privileges Granted for SQL Server Service Accounts" section in the "Setting Up Windows Service Accounts" topic in SQL Server 2005 Books Online.

For SQL Server 2000, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, then the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) must have these user rights:
  • Act as Part of the Operating System = SeTcbPrivilege
  • Bypass Traverse Checking = SeChangeNotify
  • Lock Pages In Memory = SeLockMemory
  • Log on as a Batch Job = SeBatchLogonRight
  • Log on as a Service = SeServiceLogonRight
  • Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
Note For programming convenience, Microsoft Windows NT user rights names are located next to the full name of the user right.

Note Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted the SE_MANAGE_VOLUME_NAME right. Members of the Windows Administrator group have this right. These members can grant this right to other users by adding the users to the Perform Volume Maintenance Tasks security policy.

Miscellaneous steps

Note If the default NTFS file system permissions on your computer have been changed, make sure that the SQL Server startup account has List Folder permission enabled on the root drive where the SQL Server database data and the log files are located.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
239759 Error 5177 may be raised when creating databases

If the account that the MSSQLServer service is going to start with is one of the following two accounts, you must add the startup account for the MSSQLServer and the SQLServerAgent services, or both, to the SQL Server sysadmin)role, and grant the [Domain\NTaccount] user a logon to SQL Server.
  • Not a member of the computer's Local Administrators Group.
  • The BUILTIN\Administrators SQL Server login has been removed.
For example:
EXEC sp_grantlogin [Example\test]
Then, add that account to the sysadmin role:
EXEC sp_addsrvrolemember @loginame = [Example\test] 
   , @rolename =  'sysadmin'

If you are using SQL Server together with either full-text search or with clustering, changing the SQL Server startup accounts by using anything other than SEM may cause several problems.

If you experience problems with either full-text search or clustering, see the "References" section of this article for more information.

If you are using Kerberos Security Support Provider Interface (SSPI) authentication in a SQL Server 2000 and Microsoft Windows 2000 environment, you must drop your old service principal name (SPN), and then create a new one with the new account information. See the "Security Account Delegation" topic in SQL Server 2000 Books Online for more information about how to use SETSPN to do this.

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
317746 SQL Server full-text search does not populate catalogs
317232 Event ID 1107 and 1079 messages occur after you change the Cluster service account password
295051 FIX: Changing SQL Server account to non-admin for full-text search makes existing catalogs unusable
254321 Clustered SQL Server do's, don'ts, and basic warnings
239885 How to change service accounts on a SQL virtual server
219264 Order of installation for SQL Server 7.0 clustering setup
198168 BUG: Problems might occur when you change account information for SQL Server cluster
For more information, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/library/cc966496.aspx

Properties

Article ID: 283811 - Last Review: June 22, 2014 - Revision: 12.0
Applies to
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbsqlsetup kbinfo kbsql2005cluster kbhowtomaster KB283811

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com