Database Server Configuration

The goal for this phase of the review is to identify vulnerabilities in the configuration of your SQL Server database server. For further background information about the issues raised by the review questions in this section, see Chapter 18, "Securing Your Database Server."

To help focus and structure the review process, the review questions have been divided into the following configuration categories:

Patches and updates
Services
Protocols
Accounts
Files and directories
Shares
Ports
Registry
Auditing and logging
SQL Server security
SQL Server logins, users, and roles

Patches and Updates

Check that your server is updated with the latest service packs and software patches. This includes service packs and patches for the operating system and SQL Server.

Make sure you have run the Microsoft Baseline Security Analyzer (MBSA) tool to identify common Windows and SQL Server vulnerabilities, and to identify missing service packs and patches.

Respond to the MBSA output by fixing identified vulnerabilities and by installing the latest patches and updates. For more information, see "Step 1. Patches and Updates" in Chapter 18, "Securing Your Database Server."

Services

Make sure that only those services that you require are enabled. Check that all others are disabled to reduce the attack surface of your server.

Which SQL Server services do you run?

SQL Server installs four services. If you require just the base functionality, then disable Microsoft Search Service, MSSQLServerADHelper, and SQLServerAgent to reduce the attack surface of your server.
Do you use distributed transactions?

If your applications use the transactional services of COM+ to manage transactions with SQL Server, then the Microsoft Distributed Transaction Coordinator (DTC) service is required on the database server.

If you do not use distributed transactions, ensure that the DTC service is disabled.

Protocols

By preventing the use of unnecessary protocols, you reduce the attack surface area. Review the following questions:

For which protocols is SQL Server configured?

SQL Server supports multiple protocols. Use the Server Network Utility to check that only TCP/IP protocol support is enabled.
Have you hardened the TCP/IP stack?

To check whether the stack is hardened on your server, use Regedt32.exe and examine the following registry key:
```
 HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters 
```

The presence of the following child keys indicates a hardened TCP/IP stack: SynAttackProtect, EnableICMPRedirect, and EnableReadGWDetect .

For a full list of the required keys and appropriate key values for a fully hardened stack, see "How To: Harden the TCP/IP Stack" in the How To section of this guide.

Accounts

Review the accounts used on your database server by answering the following questions:

Do you use a least privileged account to run SQL Server?

Review which account you use to run SQL Server and make sure it is a least privileged account. It should not be an administrative account or the powerful local system account. Also make sure that the account is not a member of the Users group on the local computer.
Have you deleted or disabled unused accounts?

Audit local accounts on the server and check that all unused accounts are disabled.
Have you disabled the Guest account?

Check that the Windows Guest account is disabled to restrict anonymous connections to your database server.
Have you created a new administrator account?

The default local administrator account is a prime target for attack. To improve security, check that you have created a new custom account for administration and that the default Administrator account has been disabled.
Do you use strong password policies?

Use the Local Security Policy tool to review password policy. For information about the recommended password policy, see "Step 4. Accounts" in Chapter 18, "Securing Your Database Server."
Do you restrict remote logons ?

Check the user rights assignments within the Local Security Policy tool to ensure that the Everyone group is not granted the "Access this computer from the network" user right.
Have you disabled null sessions?

Check that null sessions are disabled to prevent anonymous (unauthenticated) sessions from being created with your server. To check this, run Regedt32.exe and confirm that the RestrictAnonymous key is set to 1 , as shown below.
```
 HKLM\System\CurrentControlSet\Control\LSA\RestrictAnonymous=1 
```
Do clients connect by using Windows authentication?

If so, check that the strongest version of NTLM authentication (NTLMv2) is enabled and enforced. To check that NTLMv2 authentication is enforced, use the Local Security Policy Tool. Expand Local Policies and select Security Options and then double-click LAN Manager Authentication Level . Verify that Send NTLMv2 response only\ refuse LM & NTLM is selected.

Files and Directories

The following review questions enable you to verify that you have used NTFS permissions appropriately on your database server.

Have you configured permissions on the SQL Server install directories?

Review the permissions on the SQL Server installation directories and make sure that the permissions grant limited access. For detailed permissions, see "Step 5. Files and Directories" in Chapter 18, "Securing Your Database Server."
Have you removed Everyone permissions for SQL Server files?

Review the permissions on the SQL Server file location (by default, \Program Files\Microsoft SQL Server\MSSQL) and check that the Everyone group has been removed from the directory ACL. At the same time, make sure that full control has been granted to only the SQL Service account, the Administrators group, and the local system account.
Have you secured setup log files?

If you have installed SQL Server 2000 Service Pack 1 or 2, the system administrator or service account password may be left in the SQL installation directory. Make sure that you have used the Killpwd.exe utility to remove instances of passwords from the log files.

For information about obtaining and using this utility, see Microsoft Knowledge Base article 263968, "FIX: Service Pack Installation May Save Standard Security Password in File."

Review the following questions to ensure that your server is not unnecessarily exposed by the presence of file shares:

What shares are available on your server?

To review shares and associated permissions, run the Computer Management MMC snap-in and select Shares beneath Shared Folders . Check that all the shares are required. Remove any unnecessary shares.
Can the Everyone group access shares?

Check that the Everyone group is not granted access to your shares unless intended, and that specific permissions are configured instead.
Have you removed the administration shares?

If you do not allow remote administration of your server, then check that the administration shares, for example, C$ and IPC$, have been removed.

Note	Some applications may require administrative shares. Examples include Microsoft Systems Management Server (SMS) and Microsoft Operations Manager (MOM). For more information, see Microsoft Knowledge Base article 318751, "How To: Remove Administrative Shares in Windows 2000 or Windows NT4."

Ports

Review the ports that are active on your server to make sure that no unnecessary ports are available. For more information about using the netstat command to do this, see the "Ports" subsection in "Web Server Configuration," earlier in this chapter. Then review the following questions:

Have you restricted access to the SQL Server port?

Review how you restrict access to the SQL Server port. Check that your perimeter firewall prevents direct access from the Internet. To protect against internal attacks, review your IPSec policies to ensure they limit access to the SQL Server ports.
Have you configured named instances to listen on the same port?

If you use named instances, check with the Network Server Utility to verify that you have configured the instance to listen on a specific port. This avoids UDP negotiation between the client and server, and means you do not need to open additional ports.

Registry

Review the security of your registry configuration with the following questions:

Have you secured the SQL Server registry keys?

Use Regedt32.exe to check that the Everyone group has been removed from the ACL attached to the following registry key.
```
 Administrators: Full Control SQL Server service account: Full Control 
```
Have you secured the SAM?

Check that you have restricted LMHash storage in the Security Account Manager (SAM) by creating the key (not value) NoLMHash in the registry as shown below.
```
 HKLM\System\CurrentControlSet\Control\LSA\NoLMHash 
```

For more information, see Microsoft Knowledge Base article 299656, "New Registry Key to Remove LM Hashes from Active Directory and Security Account Manager".

Auditing and Logging

Review the following questions to check whether or not you have used appropriate auditing and logging on your database server.

Have you enabled SQL Server auditing?

Check that SQL Server auditing is enabled. Make sure that the Audit level setting on the Security page of the SQL Server Properties dialog box in Enterprise Manager is set to either All or Failure .
Do you log all failed logon attempts?

Use the Local Security Policy tool to check that you have enabled the auditing of failed logon attempts.
Do you log all failed actions across the file system?

Use the Local Security Policy tool to check that you have enabled object access auditing. Then check that auditing has been enabled across the file system.

SQL Server Security

Review which authentication mode your SQL Server is configured to use. You can see this by viewing the Security page of your server's Properties dialog box in Enterprise Manager. If your server is configured to support SQL Server and Windows authentication, check that your applications do require SQL authentication. If possible, use Windows only authentication.

If your applications do require SQL authentication, review how they manage database connection strings. This is important if they use SQL authentication because they contain user name and passwords. Also ensure that a server certificate is installed on the database server to ensure that credentials are encrypted when they are passed over the network to the database server, or that transport level encryption is used.

SQL Server Logins, Users, and Roles

Authorization in SQL Server is managed through SQL Server logins, database users, and a variety of different types of roles. Review the following questions to ensure these roles are configured appropriately:

Do you have a strong sa (system administrator) password?

Make sure the sa account has a strong password.

Important
The sa account is still active even when you change from SQL authentication to Windows authentication.

Also make sure you have applied strong passwords to all database accounts, particularly privileged accounts, for example, members of sysadmin and db_owner . If you use replication, check that the distributer_admin account has a strong password.
Have you removed the SQL Server guest account?

If when you installed SQL Server the Windows Guest account was enabled, a SQL Server guest account is created. Check each database and ensure that the SQL Server guest account is not present. If it is, remove it.

Note
You cannot remove guest from the master, tempdb, and replication and distribution databases.
Have you removed the BUILTIN\Administrators server login?

If your company differentiates the role of domain administrator and database administrator, remove the BUILTIN\Administrators SQL Server login. It is a good idea to create a specific Windows group containing specific database administrations in its place.
Have you removed permissions for the public role?

Review the permissions granted to the public role in each database. Make sure it has no permissions to access any database objects.
How many members are there that belong to the sysadmin role?

Check how many logins belong to the sysadmin role. Ideally, no more than two users should be system administrators.
Do you grant restricted database permissions to logins?

Review the permissions granted to each database user account and make sure that each account (including application accounts) only has the minimum required permissions.

SQL Server Database Objects

Review the following questions to ensure that you have removed unnecessary database objects, including the sample databases, and that stored procedures are appropriately secured.

Have you removed sample databases?

Use SQL Server Enterprise Manager to check that all sample databases, including Pubs and Northwind, have been removed.
Have you secured stored procedures?

Check to make sure that neither the public role nor the guest user has access to any of your stored procedures. To authorize access to stored procedures, you should map the SQL Server login of your server to a database user, place the database user in a user-defined database role, and then apply permissions to this role to provide execute access to the stored procedures of your application.
Have you restricted access to cmdExec?

The cmdExec function is used by the SQL Server Agent to execute Windows command-line applications and scripts that are scheduled by the SQL Server Agent. Check that access to cmdExec is restricted to members of the sysadmin role.

To check this, use SQL Server Enterprise Manager to expand the Management node. Right-click SQL Server Agent and display the SQL Server Agent Properties dialog box. Click the Job System tab and check that Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps is selected.