Step 10. SQL Server Security

The settings discussed in this section are configured using the Security tab of the SQL Server Properties dialog box in Enterprise Manager. The settings apply to all the databases in a single instance of SQL Server. The SQL Server Properties dialog box is shown in Figure 18.5.

Figure 18.5: SQL Server security properties

In this step, you:

• Set SQL Server authentication to Windows only .

• Set SQL Server audit level to Failure or All .

• Run SQL Server using a least privileged account .

Set SQL Server Authentication to Windows Only

You should configure SQL Server to support Windows-only authentication because it provides a number of benefits. Credentials are not passed over the network, you avoid embedding usernames and passwords in database connection strings, security is easier to manage because you work with the single Windows security model instead of a separate SQL Server security model, and login security improves through password expiration periods, minimum lengths, and account lockout policies.

 Task   To configure Windows only authentication

1. Start SQL Server Enterprise Manager, expand the SQL Server Group , and then expand your SQL Server.

2. Right-click your SQL Server, and then click Properties .

3. Click the Security tab.

4. Select Windows only , and then click OK .

5. Restart SQL Server for the changes to take effect.

Set SQL Server Audit Level to Failure or All

By default, SQL Server login auditing is not enabled. Minimally, you should audit failed logins.

Note  

Log entries are written to SQL log files. By default, these are located in C:\Program Files\Microsoft SQL Server\MSSQL\LOG. You can use any text reader, such as Notepad, to view them.

 Task   To enable SQL Server auditing

1. Start SQL Server Enterprise Manager , expand the SQL Server Group , and then expand your SQL Server.

2. Right-click your SQL Server, and then click Properties .

3. Click the Security tab.

4. Set the Audit level to either All or Failure .

5. Restart SQL Server for the changes to audit policy to take effect.

For more information about SQL Server audit logs, see the TechNet article and its section "Understanding the Audit Log" in the "SQL Server 2000 Auditing" article at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/prodtech/dbsql/sql2kaud.asp?frame=true .

Run SQL Server Using a Least Privileged Account

Run the SQL Server service using a least privileged account to minimize the damage that can be done by an attacker who manages to execute operating system commands from SQL Server. The SQL Server service account should not be granted elevated privileges such as membership to the Administrators group.

 Task   To configure the SQL Server run as account

This procedure uses Enterprise Manager instead of the Services MMC snap-in because Enterprise Manager automatically grants the user rights that a SQL Server service account requires.

1. Start SQL Server Enterprise Manager , expand the SQL Server Group , and then expand your SQL Server.

2. Right-click your SQL Server, and then click Properties .

3. Click the Security tab.

4. Click This account in the Startup service account group. Enter the user name and password of your least privileged account.

5. Restart SQL Server for the changes to take effect.

   Note  

   If you use the SQLSERVERAGENT service, the run-as account must also be changed. Use the Services MMC snap-in to change this setting.

For more information about creating a least privileged account to run SQL Server, see "Step 4: Accounts."