Online Documentation for SQL Administrator for SQL Server

Security


The Security section of the Server Properties dialog allows you to configure the instance of Microsoft® SQL Server by setting options pertaining to server security in groups: Server Authentication, Login audit level, Other security options, xp_cmdshell Proxy Account.

 

Server properties - Security

 

 

Server authentication

!RadioButton Windows Authentication

When a user connects through a Microsoft® Windows user account, SQL Server validates the account name and password using information in the Windows operating system. This is the default authentication mode, and is much more secure than the 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.

 

!RadioButton Windows and SQL Server Authentication

This option enables the mixed mode and allows users to connect using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows user account can make use of trusted connections that are validated by Windows.

 

Login audit level

!RadioButton None (does not log authentication attempts)

!RadioButton Failed logins only (logs failed authentication)

!RadioButton Successful logins only (logs successful authentication)

!RadioButton Both failed and successful logins (logs all authentication attempts regardless of success or failure)

 

Other security options

!CheckBox Cross database ownership chaining

This server option allows you to control cross-database ownership chaining at the database level or to allow cross-database ownership chaining for all databases. If this option is checked then cross-database ownership chaining is on for all databases. Otherwise, cross-database ownership chaining is disabled for all databases.

 

!CheckBox C2 audit mode

Selecting this option will configure the server to record both failed and successful attempts to access statements and objects. This information can help you profile system activity and track possible security policy violations.

 

!CheckBox Common Criteria compliance

The common criteria compliance option enables the following elements that are required for the Common Criteria:

  • Residual Information Protection (RIP)

RIP requires a memory allocation to be overwritten with a known pattern of bits before memory is reallocated to a new resource. Meeting the RIP standard can contribute to improved security; however, overwriting the memory allocation can slow performance. After the common criteria compliance enabled option is enabled, the overwriting occurs.

 

  • The ability to view login statistics

After the common criteria compliance option is enabled, login auditing is enabled. Each time a user successfully logs in to SQL Server, information about the last successful login time, the last unsuccessful login time, and the number of attempts between the last successful and current login times is made available.

 

  • Column GRANT should not override table DENY

After the common criteria compliance option is enabled, a table-level DENY takes precedence over a column-level GRANT. When the option is not enabled, a column-level GRANT takes precedence over a table-level DENY.

 

Note: The common criteria compliance enabled option is an advanced option and is not available in every edition of Microsoft SQL Server.

 

xp_cmdshell Proxy Account

This option allows SQL Server users that do not belong to the sysadmin fixed server role to execute xp_cmdshell commands and to own SQL Server jobs. To get access to this feature, you must enter an Account name and an Account Password.

 

Note that this option is only available when you use SQL Server 2005 (and higher). The login used by the server proxy account should have the least privileges required to perform the intended work. Excessive privileges for the proxy account could be used by a malicious user to compromise your system security.