مشاركة عبر

  • مقالة

Database Engine Configuration - Account Provisioning

Beginning in SQL Server 2005, significant changes were implemented to help ensure that SQL Server was more secure than previous versions. Changes included a "secure by design, secure by default, and secure in deployment" strategy designed to protect the server instance and its databases from security attacks.

SQL Server 2008 continues the security hardening process by introducing more changes to the server and database components. The changes introduced in SQL Server 2008 further decrease the surface and attack areas for the server and its databases by instituting a policy of least privileges and increases separation of Windows administration and SQL Server administration. This means that internal accounts are protected and separated into operating system functions and SQL Server functions. These measures include:

  • New SQL Server 2008 installations no longer add the local Windows Group BUILTIN\Administrators to the SQL Server sysadmin fixed server role.

  • The ability to provision one or more Windows principals into the sysadmin server role inside SQL Server. This option is available during SQL Server Setup for new installations of SQL Server 2008.

  • The Surface Area Configuration (SAC) tool has been removed, and replaced by policy-based management and changes in the SQL Server Configuration Manager tool.

These changes will affect your security planning for SQL Server, and help you create a more complete security profile for your system.

Considerations for Running SQL Server 2008 on Windows Vista and Windows Server 2008

Windows Vista and Windows Server 2008 include a new feature, User Account Control (UAC), that helps administrators manage their use of elevated permissions. By default, on Windows Vista and Windows Server 2008, administrators do not use their administrative rights. Instead, they perform most actions as standard users, temporarily assuming their administrative rights only when it is necessary. However, instead of elevating privileges, we recommend one of the following options:

  • If you are using Windows authentication mode, you should create a Windows user account that has sufficient permissions to perform all necessary administrative tasks.

  • If you are using Mixed Mode (SQL Server authentication and Windows authentication), you might consider creating a SQL Server login account that is used for administrative purposes only.

UAC causes some known issues. For more information, see the following Web pages:

Options

Security Mode - Select Windows authentication or Mixed Mode authentication for your installation.

Windows Principal Provisioning - In previous versions of SQL Server, the Windows BUILTIN\Administrators local group was placed into the SQL Server sysadmin server role, effectively granting Windows administrators access to the instance of SQL Server. In SQL Server 2008, the BUILTIN\Administrators group is not provisioned in the sysadmin server role. Instead, you should explicitly provision SQL Server administrators for new installations during setup.

Important

If your organization's processes or code depend on Windows BUILTIN\Administrators local group access, you must explicitly provision SQL Server administrators for new installations during setup. Setup will not allow you to continue until you complete this step.

Specify SQL Server Administrators - You must specify at least one Windows principal for the instance of SQL Server. To add the account under which SQL Server Setup is running, click the Current User button. To add or remove accounts from the list of system administrators, click Add or Remove, and then edit the list of users, groups, or computers that will have administrator privileges for the instance of SQL Server.

When you are finished editing the list, click OK, and then verify the list of administrators in the configuration dialog. When the list is complete, click Next.

If you select Mixed Mode authentication, you must provide logon credentials for the built-in SQL Server system administrator (sa) account.

Security noteSecurity Note

Do not use a blank password. Use a strong password.

  • Windows authentication Mode
    When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This is the default authentication mode, and is much more secure than Mixed Mode. Windows authentication utilizes Kerberos security protocol, provides password policy enforcement in terms of complexity validation for strong passwords, provides support for account lockout, and supports password expiration.

    Security noteSecurity Note

    When possible, use Windows Authentication.

    Important

    Do not use a blank password. Use a strong password. Never set a blank or weak sa password.

  • Mixed Mode (Windows authentication or SQL Server authentication)
    Allows users to connect by using Windows authentication or SQL Server authentication. Users who connect through a Windows user account can use trusted connections that are validated by Windows.

    If you must choose Mixed Mode authentication and you have a requirement for using SQL logins to accommodate legacy applications, you must set strong passwords for all SQL Server accounts.

    Note

    SQL Server authentication is provided for backward compatibility only. When possible, use Windows Authentication.

  • Enter Password
    Enter and confirm the system administrator (sa) login. Passwords are the first line of defense against intruders, so setting strong passwords is essential to the security of your system. Never set a blank or weak sa password.

    Note

    SQL Server passwords can contain from 1 to 128 characters, including any combination of letters, symbols, and numbers. If you choose Mixed Mode authentication, you must enter a strong sa password before you can continue to the next page of the Installation Wizard.

  • Strong Password Guidelines
    Strong passwords are not readily guessed by a person, and are not easily hacked using a computer program. Strong passwords cannot use prohibited conditions or terms, including:

    • A blank or NULL condition

    • "Password"

    • "Admin"

    • "Administrator"

    • "sa"

    • "sysadmin"

    A strong password cannot be the following terms associated with the installation computer:

    • The name of the user currently logged onto the machine.

    • The computer name.

    A strong password must be more than 8 characters in length and satisfy at least three of the following four criteria:

    • It must contain uppercase letters.

    • It must contain lowercase letters.

    • It must contain numbers.

    • It must contain non-alphanumeric characters; for example, #, %, or ^.

    Passwords entered on this page must meet strong password policy requirements. If you have any automation that uses SQL Server authentication, ensure that the password meets strong password policy requirements.

Change History

Updated content

Added information about the exclusion of the BUILTIN\Administrators Windows group in the sysadmin fixed server role.

Clarified information about User Account Control (UAC).