概要
<script type="text/javascript">loadTOCNode(1, 'summary');</script>
在您初次安装 Microsoft SQL Server 以在 Microsoft Windows NT 帐户下运行时,SQL Server 会为该 Windows NT 帐户设置对特定文件、文件夹和注册表项的各种 Windows 用户权限。如果您以后使用 SQL Server 企业管理器 (SEM) 或 SQL Server 配置管理器 (SSCM) 更改 SQL Server(MSSQLServer 服务)和 SQL Server 代理服务的启动帐户,SEM 将自动为新的启动帐户分配所有必需的权限和 Windows 用户权限,这样您就不必执行任何其他操作了。我们建议您使用这种方法来更改服务帐户。
注意:您必须拥有远程服务器上的管理员权限,此功能在 SQL Server 企业管理器中才可用。
但是,如果您使用“控制面板”或“管理工具”中的“服务”加载项来更改 MSSQLServer 服务或 SQL Server 代理服务的启动帐户信息,则必须设置一些其他权限和用户权限。
本文讨论当您使用“服务”加载项更改启动帐户信息时必须采取的步骤。
在您继续本文的其余部分之前,请访问以下 Microsoft 网站并查看 Microsoft 知识库中的文章:
以下段落中分别讨论了每一方面。
“完全控制”权限适用于以下项和所有子项:
下面是一个用于命名实例的示例:
Drive:/Program Files/Microsoft SQL Server/<MSSQL.1>/MSSQL
如果您不希望 SQL Server 或 SQL Server 代理启动帐户成为“本地管理员组”的成员,那么,MSSQLServer 服务和 SQLServerAgent 服务的启动帐户(本地 Windows NT 帐户或域 Windows NT 帐户)必须具有以下用户权限:
注意:为了便于编程,在用户权限全名的旁边放置了 Microsoft Windows NT 用户权限名。
有关更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
如果启动 MSSQLServer 服务要使用的帐户是下列两个帐户之一,您必须将 MSSQLServer 或 SQLServerAgent 服务的启动帐户(或这两者)添加到 SQL Server ( sysadmin) 角色中,并对 [Domain/NTaccount] 用户授予登录到 SQL Server 的权限。
例如:
如果您在使用 SQL Server 时执行全文搜索或群集操作,则使用 SEM 以外的任何工具更改 SQL Server 启动帐户可能会导致多种问题。
如果您遇到有关全文搜索或群集操作的问题,请参见本文的“参考”一节以获取更多信息。
如 果您在 SQL Server 2000 和 Microsoft Windows 2000 环境中使用 Kerberos“安全性支持提供程序接口”(SSPI) 身份验证,您必须删除旧的服务主体名称 (SPN),然后使用新的帐户信息创建一个新的服务主体名称。有关如何使用 SETSPN 完成此任务的更多信息,请参见 SQL Server 2000 联机丛书中的“安全帐户委派”主题。
注意:您必须拥有远程服务器上的管理员权限,此功能在 SQL Server 企业管理器中才可用。
但是,如果您使用“控制面板”或“管理工具”中的“服务”加载项来更改 MSSQLServer 服务或 SQL Server 代理服务的启动帐户信息,则必须设置一些其他权限和用户权限。
本文讨论当您使用“服务”加载项更改启动帐户信息时必须采取的步骤。
在您继续本文的其余部分之前,请访问以下 Microsoft 网站并查看 Microsoft 知识库中的文章:
Microsoft 安全公告 MS02-038
http://www.microsoft.com/china/security/Bulletins/MS02-038.asp (http://www.microsoft.com/china/security/bulletin/MS02-038.mspx)
Microsoft 安全公告 MS02-034
http://www.microsoft.com/technet/security/bulletin/MS02-034.mspx (http://www.microsoft.com/technet/security/bulletin/MS02-034.mspx)
注意:遵守 MS02-034 和 MS02-038 会消除现有的管理凭据提升漏洞并有助于防止将来发生这种漏洞。
http://www.microsoft.com/china/security/Bulletins/MS02-038.asp (http://www.microsoft.com/china/security/bulletin/MS02-038.mspx)
Microsoft 安全公告 MS02-034
http://www.microsoft.com/technet/security/bulletin/MS02-034.mspx (http://www.microsoft.com/technet/security/bulletin/MS02-034.mspx)
322853
(http://support.microsoft.com/kb/322853/) FIX:SQL Server 授予了不必要的权限或者加密函数包含未检查的缓冲区
316333
(http://support.microsoft.com/kb/316333/) SQL Server 2000 Service Pack 2 的安全更新程序
使用“服务”加载项而不是使用 SQL 企业管理器或 SQL Server Management Studio 更改 SQL Server 或 SQL Server 代理服务帐户
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 如果您使用“服务”加载项而不是使用 SEM 或 SS,CM来更改 SQL Server 服务帐户或 SQL Server 代理服务帐户,则还必须设置某些注册表和 NTFS 文件系统权限以及 Microsoft Windows 用户权限。对于 SQL Server Desktop Engine(也称为 MSDE 2000)或 SQL Server 2005 Express Edition 安装尤为如此,原因是您并不具有 SEM 或 SSCM 来执行权限更改。您必须集中处理以下三个特定方面:• | 注册表项。 |
• | 磁盘上的 NTFS 文件系统权限。 |
• | Windows 用户权限。 |
注册表项
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 在下面的列表中的注册表项上为 MSSQLServer 服务和 SQLServerAgent 服务的启动帐户(本地 Microsoft Windows NT 帐户或域 Windows NT 帐户)设置“完全控制”。在下面的注册表配置单元下,此列表中的这些注册表项是在其中设置访问控制列表 (ACL) 的项。对于群集,请在群集的每个节点上执行此步骤。“完全控制”权限适用于以下项和所有子项:
• | 对于命名实例:
HKEY_LOCAL_MACHINE/Software/Clients/Mail HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/80 HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/<Instancename> |
• | 对于默认实例:
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 |
如果您使用的是 SQL Server 2005
<script type="text/javascript">loadTOCNode(2, 'summary');</script>对于命名实例或默认实例,将“完全控制”权限应用于下列项和所有子项:
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/90
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/<MSSQL.x>
注意:在此注册表子项中,<MSSQL.x> 是系统相应值的占位符。在下面的注册表子项中,您可以根据已命名为实例名的注册表项的值来确定系统的相应值。对于默认实例,实例名是 MSSQLSERVER:
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/<MSSQL.x>
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/Instance Names/SQL/
磁盘上的 NTFS 文件系统权限
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 在这些 NTFS 文件夹上为 MSSQLServer 服务和 SQLServerAgent 服务的启动帐户(本地 Microsoft Windows NT 帐户或域 Windows NT 帐户)设置“完全控制”。对于群集,您必须还要修改每个计算机节点上的相应路径。下面是一个用于命名实例的示例:
D:/Program Files/Microsoft SQL Server/MSSQL$_instancename_/
下面是一个用于默认实例的示例:
D:/Program Files/Microsoft SQL Server/MSSQL/
子文件夹和文件也必须具有相同的权限。
如果您使用的是 SQL Server 2005
<script type="text/javascript">loadTOCNode(2, 'summary');</script>对应的文件夹如下:Drive:/Program Files/Microsoft SQL Server/<MSSQL.1>/MSSQL
Windows 用户权限
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 通常,操作系统的默认安装会赋予“本地管理员组”SQL Server 正常工作所需的所有用户权限。因此,要成为 SQL Server 服务的启动帐户且已添加到“本地管理员组”中的本地 Windows NT 帐户或域帐户具有它们所需的所有用户权限。但是,建议您不要在这么高的用户权限下运行 SQL Server。如果您不希望 SQL Server 或 SQL Server 代理启动帐户成为“本地管理员组”的成员,那么,MSSQLServer 服务和 SQLServerAgent 服务的启动帐户(本地 Windows NT 帐户或域 Windows NT 帐户)必须具有以下用户权限:
• | 作为操作系统的一部分操作 = SeTcbPrivilege |
• | 跳过遍历检查 = SeChangeNotify |
• | 锁定内存页 = SeLockMemory |
• | 作为批处理作业登录 = SeBatchLogonRight |
• | 作为服务登录 = SeServiceLogonRight |
• | 替换进程级令牌 = SeAssignPrimaryTokenPrivilege |
其他步骤
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 注意:如果您的计算机上的默认 NTFS 文件系统权限已被更改,请确保 SQL Server 启动帐户在 SQL Server 数据库数据和日志文件所在的根驱动器上启用了“列出文件夹”权限。有关更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
239759
(http://support.microsoft.com/kb/239759/) PRB:创建数据库时可能引发错误 5177
如果启动 MSSQLServer 服务要使用的帐户是下列两个帐户之一,您必须将 MSSQLServer 或 SQLServerAgent 服务的启动帐户(或这两者)添加到 SQL Server ( sysadmin) 角色中,并对 [Domain/NTaccount] 用户授予登录到 SQL Server 的权限。
• | 不是计算机“本地管理员组”的成员。 |
• | BUILTIN/Administrators SQL Server 登录已被删除。 |
EXEC sp_grantlogin [Example/test]然后,将该帐户添加到“sysadmin”角色中:
EXEC sp_addsrvrolemember @loginame = [Example/test]
, @rolename = 'sysadmin'
如果您在使用 SQL Server 时执行全文搜索或群集操作,则使用 SEM 以外的任何工具更改 SQL Server 启动帐户可能会导致多种问题。
如果您遇到有关全文搜索或群集操作的问题,请参见本文的“参考”一节以获取更多信息。
如 果您在 SQL Server 2000 和 Microsoft Windows 2000 环境中使用 Kerberos“安全性支持提供程序接口”(SSPI) 身份验证,您必须删除旧的服务主体名称 (SPN),然后使用新的帐户信息创建一个新的服务主体名称。有关如何使用 SETSPN 完成此任务的更多信息,请参见 SQL Server 2000 联机丛书中的“安全帐户委派”主题。
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
SUMMARY
<script type="text/javascript">loadTOCNode(1, 'summary');</script>
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:
Each one is discussed separately in the following paragraphs.
Full Control permission applies to the following keys and all child keys:
Here is an example for a named instance:
Drive:/Program Files/Microsoft SQL Server/<MSSQL.1>/MSSQL
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:
Note For programming convenience, Microsoft Windows NT user rights names are located next to the full name of the user right.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
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.
For example:
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.
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 (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 (http://www.microsoft.com/technet/security/bulletin/MS02-034.mspx)
Note Complying with MS02-034 and MS02-038 removes existing administrative credential elevation vulnerabilities and helps prevent future ones.
http://www.microsoft.com/technet/security/bulletin/MS02-038.mspx (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 (http://www.microsoft.com/technet/security/bulletin/MS02-034.mspx)
322853
(http://support.microsoft.com/kb/322853/) FIX: SQL Server grants unnecessary permissions or an encryption function contains unchecked buffers
316333
(http://support.microsoft.com/kb/316333/) SQL Server 2000 security update for Service Pack 2
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
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 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. |
Registry keys
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 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
<script type="text/javascript">loadTOCNode(2, 'summary');</script>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/<MSSQL.x>
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/Instance Names/SQL/
NTFS file system permissions on the disk
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 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
<script type="text/javascript">loadTOCNode(2, 'summary');</script>the corresponding folder is the following:Drive:/Program Files/Microsoft SQL Server/<MSSQL.1>/MSSQL
Windows user rights
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 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 |
Miscellaneous steps
<script type="text/javascript">loadTOCNode(2, 'summary');</script> 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
(http://support.microsoft.com/kb/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. |
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
<script type="text/javascript">loadTOCNode(1, 'references');</script>
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
317746
(http://support.microsoft.com/kb/317746/) SQL Server full-text search does not populate catalogs
317232
(http://support.microsoft.com/kb/317232/) Event ID 1107 and 1079 messages occur after you change the Cluster service account password
295051
(http://support.microsoft.com/kb/295051/) FIX: Changing SQL Server account to non-admin for full-text search makes existing catalogs unusable
254321
(http://support.microsoft.com/kb/254321/) Clustered SQL Server do's, don'ts, and basic warnings
239885
(http://support.microsoft.com/kb/239885/) How to change service accounts on a SQL virtual server
219264
(http://support.microsoft.com/kb/219264/) Order of installation for SQL Server 7.0 clustering setup
198168
(http://support.microsoft.com/kb/198168/) BUG: Problems might occur when you change account information for SQL Server cluster
For more information, visit the following Microsoft Web site:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx
(http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx)
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: | kbinfo kbsql2005cluster kbhowtomaster KB283811 |