Security Best Practices Guide for Cisco Unified ICM/Contact Center Enterprise & Hosted Release 9.0
SQL Server Hardening
Download this chapter
SQL Server HardeningSQL Server Hardening
Download the complete book
Security Best Practices Guide for Cisco Unified ICM/Contact Center Enterprise & Hosted Release 9.0Security Best Practices Guide for Cisco Unified ICM/Contact Center Enterprise & Hosted Release 9.0 (PDF - 4 MB)
 Feedback

SQL Server Hardening

SQL Server Hardening suggestions

Top Hardening suggestions

Top Hardening Suggestions:

  1. Do not install SQL Server on an Active Directory Domain Controller.
  2. In a multitier environment, run Web logic and business logic on separate computers.
  3. Install the latest applicable SQL Server service pack and security updates. Refer to the Hardware & System Software Specification (Bill of Materials) for Cisco Unified ICM/Contact Center Enterprise & Hosted, Release 9.0(1) for the compatible service pack for your product.
  4. Set a strong password for the sa account before installing ICM. For information, see SQL Server Users and Authentication.
  5. Always install SQL Server service to run using a least privilege account. Never install SQL Server to run using the built-in Local System account. Follow the steps below to modify the SQL Server service account.

    Note

    The following assumes the SQL Server has been installed previously with the service configured to run as the "LocalSystem" Account. It is possible that these steps can be shortened if the SQL Server is installed initially to run using a least privileged account. Refer to the Staging Guide for Cisco Unified ICM/Contact Center Enterprise & Hosted, Release 9.x(y) for more information about how to properly install SQL Server using a Domain User Account to run the MSSQL Server service.

    1. Create a Windows domain user account (for example, <domain>\SQLServiceAcct>). (Refer to the Staging Guide for Cisco Unified ICM/Contact Center Enterprise & Hosted, Release 9.x(y) for details.) Appropriate file system permissions (Modify) must be given to this user account for the \mssql\data directory to be able to create, expand or delete databases as needed by the "icmdba" application.
    2. Configure Security Account Delegation in Active Directory (Users folder) for this account:
      1. From the Account property page, select Account is trusted for delegation.
      2. Make sure Account is sensitive and cannot be delegated is not selected.
    3. Configure Security Account Delegation in Active Directory (Computers folder) for each machine that has SQL (or MSDE) installed: Select Trust computer for delegation on the General property page.
    4. Have a Domain Administrator configure Security Account Delegation using the SetSPN utility from the Windows Server 2008 R2 resource kit to set a Service Principal Name as follows: List the existing SPN for the machine by typing the following at a command prompt: setspn -L <machine> Delete any existing SPN for the MSSQLSvc entry by typing the following at a command prompt: setspn -D "MSSQLSvc/<machine:port> <serviceaccountname>" <machine>1 Create a new SPN entry for the MSSQLSvc entry by typing the following at a command prompt: setspn -A "MSSQLSvc/<machine:port> <serviceaccountname>" <machine>
    5. Add the domain user account created in Step a. to the NTFS permissions for the Operating System and data partitions at the root level (For example, C:\). Allow all permissions, except Full Control.

      Note

      The SQL Server 2008 R2 automated hardening utility, and the ICMDBA tool, will automatically ensure this permission is appropriately granted.

    6. Finally, add this domain user account created in Step a. to the Registry permissions for the HKEY_LOCAL_MACHINE\Software, HKEY_LOCAL_MACHINE\System and HKEY_USERS hives, giving it Full Control.
    7. From the SQL Server Configuration Manager (for SQL Server 2008 R2), configure the SQL Server service to run as the domain user account created in Step a. (for example, <domain>\SQLServiceAcct>).
  6. SQL Server Agent Service must be enabled and set to Automatic for database maintenance functioning in Unified ICM.

    Note

    Applying SQL Server security updates or hotfixes may require that the SQL Server Agent service be disabled. It is recommended that this service be reset to "disabled" before performing the update. When the update has completed, stop the service and set it back to "enabled".

  7. In all releases before 7.5, the Distributed Transaction Coordinator, MSDTC, is disabled (this is done by default by the automated server hardening in the Cisco Unified ICM Security Template Settings shipped with 7.0, 7.1, and 7.2). However, from ICM 7.5 onwards, MSDTC services must be set to manual (done by default by the automated server hardening in the Cisco Unified ICM Security Template shipped with ICM 7.5 and 8.0).

    Note

    The SQLServerAgent and MSDTC services may be used for Third-Party Backup solutions; therefore we recommend checking the Backup Agents system requirements before disabling these services.

  8. Use NTFS directory security with EFS for SQL Server data directories. EFS must be set while logged in under the account credentials that the SQL service will run under (for example, <domain>\SQLServiceAcct>). From the Local Policy editor, temporarily grant "logon locally" privileges to this account to enable EFS then remove this right after logging off.

    Warning

    Only enable EFS if there is a concern with data theft; there will be a performance impact.


    Note

    In order to copy and send the data to other parties, it will be necessary to back up the database to a different directory that is not encrypted to ensure that the receiving party is able to read the data in the backup. This can be accomplished by backing up the database from the SQL Server Enterprise Manager.

  9. Disable the SQL guest account.
  10. Restrict sysadmin membership to your Unified ICM administrators.
  11. Block TCP port 1433 and UDP port 1434 at the firewall except for when the Administration & Data Server is not in the same security zone as the Logger.
  12. Provide protection with good housekeeping:
    1. Run the KillPwd utility to remove password data from setup files. Detailed instructions on how to run this utility can be found in the Microsoft article KB 263968.
    2. Delete or secure old setup files: Delete or archive the following files after installation: sqlstp.log, sqlsp.log, and setup.iss in the <systemdrive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder for a default installation, and the <systemdrive>:\Program Files\Microsoft SQL Server\ MSSQL$<Instance Name>\Install folder for named instances. If the current system is an upgrade from SQL Server 7.0, delete the following files: setup.iss in the %Windir% folder, and sqlsp.log in the Windows Temp folder.
  13. Change the recovery actions of the Microsoft SQL Server service to restart after a failure.
  14. Remove all sample databases, for example, Pubs and Northwind.
  15. Enable auditing for failed logins.

SQL Server users and authentication

When creating a user for the SQL Server account, create Windows accounts with the lowest possible privileges for running SQL Server services. It is preferable that this be done during the installation of SQL Server.

During installation, SQL Server Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To enable the sa login, use the ALTER LOGIN statement. For additional details see: http://msdn.microsoft.com/en-us/library/ms188670.aspx.

The local user or the domain user account that is created to function as the SQL Server service account follows the Windows or domain password policy respectively. It is imperative that a strict password policy is applied on this account. However, do not set the password to expire, because SQL Server service will cease to function and that in turn will cause the Administration & Data Server to fail.

The password and account settings may be governed by the site requirements. At the least, the following settings are recommended:

Table 1 Password and account settings

Setting

Value

Enforce Password History

24 passwords remembered

Minimum Password Length

12 characters

Password Complexity

Enabled

Minimum Password Age

1 day

Account Lockout Duration

15 minutes

Account Lockout Threshold

3 invalid logon attempts

Reset Account Lockout Counter After

15 minutes


Note
The service account password must explicitly be set to Not expire.

Mixed mode authentication is enforced through SQL Server 2008 R2 automated hardening.

During web setup, if the sa password is found to be blank, a randomly generated strong password is generated and used to secure the sa account.

Important:

This randomly generated sa password is displayed only once during the install. Make note of the password because it is not presented again.

Resetting of the sa account password may be done after installation by logging on to the SQL Server using a Windows Local Administrator account.

SQL Server 2008 R2 security considerations

Microsoft SQL Server 2008 R2 is far more secure by design, default, and deployment than prior versions. Microsoft SQL Server 2008 R2 provides a much more granular access control and a new utility to manage attack surface, and runs with lower privileges. To make the best of the security features provided by Microsoft SQL Server 2008 R2, the database administrator must follow the best practices as described below in the "Automated SQL 2008 R2 Hardening" section.

Automated SQL 2008 R2 Hardening

The SQL Server Security Automated Hardening utility performs the following:
  • Enforces Mixed Mode Authentication.
  • Verifies that the Named Pipe (np) is listed before TCP/IP (tcp) in the SQL Server Client Network Protocol Order.
  • Disables SQLWriter, SQLBrowser, MSSQLServerADHelper100 Services.
  • Forces SQL server user 'sa' password if found blank.

SQL Server Security Hardening utility

The SQL Server Security Hardening utility allows you to harden or roll back the SQL Server security on Logger and Administration & Data Server/HDS components. The Harden option disables unwanted services and features, as explained in the "Automated SQL 2008 R2 Hardening" section above. If the latest version of the security settings are already applied, then the Harden option does not change anything. The Rollback option allows you to return to the state of SQL services and features that existed prior to your applying the last hardening.

The SQL Server Security Hardening utility is launched via Setup, by default, to harden the SQL Server security. However, you can run it manually, as described below.

Utility Location

The utility is located at:

%SYSTEMDRIVE%\CiscoUtils\SQLSecurity

Harden SQL Server

From the command line type in:

Perl ICMSQLSecurity.pl HARDEN


Note

The current SQL Server configuration will be backed up to <ICMInstallDrive>:\CiscoUtils\SQLSecurity\ICMSQLSEcurity.bkp before the utility applies the SQL Server hardening.

Roll Back SQL Server security hardening

The ROLLBACK command rolls back to the previous SQL Server configuration, if hardening was applied before.

To roll back to the previous SQL Server configuration, from the command line type in:

Perl ICMSQLSecurity.pl ROLLBACK


Note

The following security hardening settings are not removed when:

  1. SQL Server security mode is currently set to Windows Only Authentication.
  2. SQL Server user "sa" is set to random password.
  3. SQLVSSWriter, SQLBrowser and MSSQLServerADHelper100 services are disabled.

You can roll back these settings manually using SQL Server Management Studio tool.

No argument

If no argument is used with the command line, usage help is displayed.

Output log

All output logs are saved in the file:

%SYSTEMDRIVE%\CiscoUtils\SQLSecurity\Logs\ICMSQLSecurity.log

Manual SQL 2008 R2 Server hardening

By default, SQL Server 2008 R2 disables VIA endpoint and limits the Dedicated Administrator Connection (DAC) to local access. Also, by default, all logins have GRANT permission for CONNECT using Shared Memory, Named Pipes, TCP/IP and VIA endpoints. Unified ICM requires only Named Pipes and TCP/IP endpoints.

  • Enable both Named Pipes and TCP/IP endpoints during SQL Server 2008 R2 setup. Make sure Named Pipes has a higher order of priority than TCP/IP.


    Note

    The SQL Server Security Hardening utility will check for the availability and order of these endpoints.

  • Disable access to all endpoints that are not required. For instance: Deny connect permission to VIA endpoint for all users/groups who have access to the database.

1 The string inside quotes must match exactly what is seen in the List command:: setspn -L <machine>