After you perform SQL Server security hardening procedures, you cannot open the SMS Administrator Console

Symptoms

After you perform Microsoft SQL Server security hardening procedures, you cannot open the Microsoft Systems Management Server (SMS) 2003 Administrator Console. Additionally, you experience the following symptoms:
  • The AdminUI.log file contains an entry that resembles the following:
    Failed to set the connection. error code: -2147217389
  • The Smsprov.log file contains an entry that resembles the following:
    SQL Error: [42000][4060][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'SMS_SMS'.
    Login fails. SQL Connection attempt timed out [42000][4060][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'SMS_SMS'.
    Login fails.


    CANT CONNECT TO SQL, RETURNING ERROR <*><*>
This problem occurs when all the following conditions are true:
  • The SMS database is on a remote computer that is running SQL Server.
  • You have locked down the SQL Server security settings as recommended in the "SMS 2003 SQL Server Hardening list" section of the "Scenarios and Procedures for Microsoft Systems Management Server 2003: Security" white paper.

Cause

This problem occurs because the “SQL Server Settings” section in the SMS 2003 SQL Server Hardening list states that the BUILTIN\Administrators group must be removed from the SQL Server logins. When you harden SQL Server by removing the BUILTIN\Administrators group, the SMS site server no longer has access to the remote site database. SMS sites use Database accounts to connect from the site server, management point, and server locator point, and to manipulate the databases. In this case, the SQL Server (site database) account must be added to the System Administrator server role on the database server. This account can be one of the following:
  • The SMS 2003 service accounts if you are using SMS Standard security
  • The SMS site server computer account if you are using SMS Advanced security

Resolution

To resolve this problem, use one of the following methods as appropriate for your situation.

You are using SMS Standard security

  1. On the remote computer that is running SQL Server, create SQL Server logins for the following service accounts:
    • The SMS service account
    • The Remote Service account (SMSSvc_sitecode_xxxx)
  2. Give SQL Server System Administrators the rights to use both logins. To do this, see the "the "How to add service account to the SQL Server System Administrators role" section.

You are using SMS Advanced security

  1. On the remote computer that is running SQL Server, create a SQL Server login for the SMS site server computer account.
  2. Give SQL Server System Administrator rights to the site server computer account login. To do this, see the "the "How to add service account to the SQL Server System Administrators role" section.

How to add service accounts to the SQL Server System Administrators role

  1. Open SQL Server Enterprise Manager, and then expand SQL Server Group.
  2. Expand the server that is hosting the remote SMS database, and then expand Security.
  3. Right-click Logins, and then click New Login.
  4. On the General tab, add the account name, the authentication, and the database information that applies to the new login.
  5. Click the Server Roles tab, click to select the System Administrators check box, and then click OK.
  6. In Security, click Server Roles.
  7. In the right pane, double-click System Administrators.
  8. In the Server Role Properties - sysadmin dialog box, verify that the new account name is listed in the

    Specify which logins are members of the security role list, and then click OK.
  9. Exit SQL Server Enterprise Manager.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

For more information about logins and about the System Administrator role, see the "Managing Users, Roles, and Logins" section in the most recent version of the SQL Server Books Online.

For more information about the SMS service account, the Remote Service account (SMSSvc_sitecode_xxxx), and the site server computer account, see the following sections in the "Scenarios and Procedures for SMS 2003: Security" white paper:
  • Mapping the SMS Site Server Computer Account to the DBO User for the SMS Site Database
  • Appendix C: SMS Accounts, Groups, and Passwords
To download this white paper, visit the following Microsoft Web site:
Propriedades

ID do Artigo: 918911 - Última Revisão: 27 de out de 2006 - Revisão: 1

Comentários