Microsoft KB Archive/932881

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 09:26, 21 July 2020 by X010 (talk | contribs) (Text replacement - ">" to ">")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Article ID: 932881

Article Last Modified on 11/20/2007



APPLIES TO

  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition



INTRODUCTION

The Microsoft SQL Server 2005 Setup program creates a local Windows group for each service that you install. The SQL Server 2005 Setup program adds the service account for each service to its respective group. For a SQL Server failover cluster installation, Windows domain groups are used in the same manner. These domain groups must be created by a domain administrator before you run the SQL Server 2005 Setup program. All the Windows NT rights and permissions that are required by a specific service are added by the system access control list (SACL) to each Windows group. The domain administrator does not grant permissions directly to the service account.

In addition, the Windows groups that you created for SQL Server 2005, for SQL Server Agent, and for the BUILTIN\Administrators group are granted SQL Server 2005 logins that are provisioned in the SQL Server 2005 SYSADMIN fixed server role. This configuration makes it possible for any account that is a member of these groups to log on to SQL Server 2005 by using a Windows NT authenticated connection. Because the user has a group membership in the SQL Server SYSADMIN fixed server role, the user is logged into SQL Server 2005 as a SQL Server 2005 systems administrator. (The user is logged in by using the sa account). Then, the user has unrestricted access to the SQL Server 2005 installation and to its data. Also, any user who knows the password for the instance of SQL Server 2005 or for the SQL Server Agent service account can use the service account to log on to the computer. Then, the user can make a Windows NT authenticated connection to SQL Server 2005 as a SQL Server administrator.

The Windows groups that you created for SQL Server 2005 Reporting Services (SSRS) and for the full-text search service are also granted SQL Server logins. However, Reporting Services and the full-text search service are not provisioned in the SYSADMIN fixed server role.

Some SQL Server 2005 administrators want the functional roles of the database administrator and of the operating system administrator to be strictly separated. These administrators want to protect SQL Server 2005 against unwanted access by the operating system administrator.

MORE INFORMATION

How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult

To make unwanted access to SQL Server 2005 by an operating system administrator more difficult, you must remove the logon permissions that were granted to the BUILTIN\Administrators group. Then, you must grant logins directly to the service accounts for SQL Server 2005 and for SQL Server Agent. Next, you must provision the logins in the SYSADMIN fixed server role. Finally, you must delete the logins that were granted to their respective Windows groups. To do this, follow these steps:

  1. Make sure that you have an account that is a member of the SYSADMIN fixed server role. This account is not granted the SQL Server 2005 logon permission by being a member of the BUILTIN\Administrators group.
  2. Remove the logon permissions that were granted to the BUILTIN\Administrators group. To do this, follow these steps:
    1. Log on to SQL Server 2005 by using a user account that has the ALTER ANY LOGIN permission.
    2. Expand Security, expand Logins, right-click BUILTIN\Administrators, and then click Delete.
    3. In the Delete Object dialog box, click OK.

    Note After you delete the login that was granted to the BUILTIN\Administrators group, any account that relies only on membership in this group to log on to SQL Server 2005 will no longer be able to access SQL Server 2005.

    For information about the Microsoft Cluster Service (MSCS) service account, see the "Microsoft Cluster Service (MSCS) service account" section.
  3. Use an account that has the ALTER ANY LOGIN permission to explicitly grant SQL Server 2005 logins directly to the service accounts that are used by SQL Server 2005 and by SQL Server Agent. To do this, execute the following SQL statement.

    CREATE LOGIN [<Domain Name>\<SQL Server Service Account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE LOGIN [<Domain Name >\<SQL Server Agent Service Account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
  4. Use an account that is a member of the SYSADMIN fixed server role to provision the logins that you added in step 2 in the SYSADMIN fixed server role.

    EXEC master..sp_addsrvrolemember @loginame = N'<Domain Name>\<SQL Server Service Account> ', @rolename = N'sysadmin'
    EXEC master..sp_addsrvrolemember @loginame = N'<Domain Name>\<SQL Server Agent Service Account> ', @rolename = N'sysadmin'
  5. Use an account that has the ALTER ANY LOGIN permission to delete the logins that were granted to the SQL Server 2005 group and to the SQL Server Agent Windows group.

    DROP LOGIN [<Computer Name>\<SQLServer2005SQLServerUser>$<Computer Name>$MSSQLSERVER]
    DROP LOGIN [<Computer Name>\<SQLServer2005AgentUser>$<Computer Name>$MSSQLSERVER]

Even after you follow these steps, the password for the SQL Server 2005 service account and for the SQL Server Agent service account must be kept secret from the operating system administrator. If the MSCS service account has been provisioned in the SYSADMIN fixed server role, the MSCS service account password must also be kept secret from the operating system administrator. If the operating system administrator knows the password for the SQL Server 2005 service account or for the SQL Server Agent service account, the operating system administrator can use the service account to log on to a computer. After the operating system administrator logs on to the computer, the operating system administrator can connect to the instance of SQL Server 2005 as a SQL Server administrator.

To keep the operating system administrator from learning the password of the service accounts that are used by SQL Server 2005 and by SQL Server Agent, the SQL Server system administrator must be able to set a new password for the service account. In most cases, the SQL Server 2005 system administrator is not an operating system administrator. Therefore, a special-purpose utility must be written to provide this functionality. For example, you could create a trusted service that the SQL Server 2005 system administrator can use to change the passwords for the service accounts that are used by SQL Server 2005. Microsoft currently does not offer this service.

Microsoft Cluster Service (MSCS) service account

In a SQL Server 2005 failover cluster installation, the MSCS service account relies on membership in the BUILTIN\Administrators group to log on to SQL Server 2005 to run the IsAlive check. If you remove the BUILTIN\Administrators group from a failover cluster, you must explicitly grant the MSCS service account permissions to log on to the SQL Server 2005 failover cluster. To do this, execute the following SQL statement in the instance of SQL Server 2005.

CREATE LOGIN [<Domain Name>\<MSCS Service Account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

SQL Server 2005 Service Pack 2 added new diagnostic capabilities for SQL Server 2005 failover clusters. The diagnostics automatically capture the state of the SQL Server 2005 cluster resource before the cluster fails over. The SQL Server 2005 resource dynamic link library (DLL) makes collecting this diagnostic data easier, as follows:

  • The SQL Server 2005 resource starts an instance of the Sqlcmd.exe utility under the security context of the MSCS service account. Then, the SQL Server 2005 resource runs an SQL script over a dedicated administrator connection (DAC) that samples various dynamic management views (DMV).
  • The SQL Server 2005 resource captures a user dump file of the SQL Server 2005 process before the cluster fails over.

Because a dedicated administrator connection is used to collect some diagnostic data, the MSCS service account must be provisioned in the SYSADMIN fixed server role. If the security practices of your organization mean that the MSCS service account cannot be provisioned in the SYSADMIN fixed server role, the MSCS service account can be granted a SQL Server login that is not provisioned in the SYSADMIN fixed server role. In this scenario, the diagnostics that are usually captured by the Sqlcmd.exe utility will fail because the Sqlcmd.exe utility cannot log on to SQL Server 2005. The SQL Server 2005 resource DLL should be able to collect a user dump file regardless of whether the service account of the SQL Server 2005 resource DLL is provisioned in the SYSADMIN fixed server role.

If you want, log on to SQL Server 2005 by using an account that is a member of the SYSADMIN fixed server role. Then, execute the following SQL statement to add the MSCS service account to the SYSADMIN fixed server role.

EXEC master.sp_addsrvrolemember @loginame = N'<Domain Name>\<MSCS Service Account> ', @rolename = N'sysadmin'

How to change the service accounts

Although the previous steps may make it more difficult for an operating system administrator to connect to SQL Server 2005, the previous steps make it more cumbersome to change the service accounts for SQL Server 2005 and for SQL Server Agent. To change the service accounts for SQL Server 2005 and for SQL Server Agent, follow these steps:

  1. Add the new service account or service accounts to the Windows group or groups that you created for SQL Server and for SQL Server Agent.
  2. Use an account that has the ALTER ANY LOGIN permission to create a SQL Server 2005 login for the new service accounts. To do this, execute the following SQL statement from the account that has the ALTER ANY LOGIN permission.

    CREATE LOGIN [<Domain Name>\<New SQL Server Service Account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE LOGIN [<Domain Name>\<New SQL Server Agent Service Account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
  3. Use an account that is provisioned in the SYSADMIN fixed server role to execute the following SQL statement.

    EXEC master..sp_addsrvrolemember @loginame = N’ <Domain Name>\<New SQL Server Service Account> ', @rolename = N'sysadmin'
    EXEC master..sp_addsrvrolemember @loginame = N’ <Domain Name>\<New SQL Server Agent Service Account> ', @rolename = N'sysadmin'

    Note This statement adds the SQL Server 2005 service account and the SQL Server Agent service account to the SYSADMIN fixed server role.

  4. Change the service account for the appropriate service by using SQL Server Configuration Manager. To do this, follow these steps:
    1. In SQL Server Configuration Manager, click SQL Server 2005 Services.
    2. Right-click the service that you want to modify, and then click Properties.
    3. Click the Log On tab, and then enter the user account information that you want the service to use.
    4. Click OK when you finish entering the account information.
    Note When you change the service account, SQL Server Configuration Manager prompts you to restart the service.
  5. Use an account that has the ALTER ANY LOGIN permission to delete the logins that were used by the SQL Server 2005 service account and by the SQL Server Agent service account. To do this, execute the following SQL statement.

    DROP LOGIN [<Domain Name>\<Old SQL Server Service Account>]
    DROP LOGIN [<Domain Name>\<Old SQL Server Agent Service Account>]

Note You do not have to grant any new Windows NT rights or permissions to the new service accounts because you added the new service accounts to their respective Windows groups in step 1.

Recommendation to audit processes

If you want to protect SQL Server against unwanted access by operating system administrators, you should also audit the following processes:

  • Audit starts and stops of the Windows-based server.
  • Audit starts and stops of the SQL Server 2005 services and of the SQL Server Agent services.
  • Audit access to the directories in which SQL Server database files, data files, log files, and database backup files are stored.
  • Audit changes to the SQL Server 2005 service account and to the SQL Server Agent service account.
  • Audit network logins and computer logins by the SQL Server 2005 service account, by the SQL Server Agent service account, or by the MSCS service account.

The NT AUTHORITY\SYSTEM account

The NT AUTHORITY\SYSTEM account is also granted a SQL Server login. The NT AUTHORITY\SYSTEM account is provisioned in the SYSADMIN fixed server role. Do not delete this account or remove it from the SYSADMIN fixed server role. The NTAUTHORITY\SYSTEM account is used by Microsoft Update and by Microsoft Operations Manager (MOM) to apply service packs and hotfixes to a SQL Server 2005 installation. The NTAUTHORITY\SYSTEM account is also used by the SQL Writer Service.

Also, if SQL Server 2005 is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server 2005 as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behavior is by design. This behavior is intended to be used for data recovery scenarios.

For more information about security best practices for SQL Server 2005, see the "Security Considerations for a SQL Server Installation" topic in SQL Server 2005 Books Online.

REFERENCES

For more information about security considerations for a SQL Server installation, visit the following Microsoft TechNet Web site:

Keywords: kbhowto kbinfo kbcode kbpubtypekc KB932881