SQL Control Server permission

• Use the following Transact-SQL script to assign the “Control Server” permission to the service account:

USE master
GRANT CONTROL SERVER TO [domain\user]
GO

• The “Control Server” permission does not appear on the Login Properties window in SQL Server Management Studio. The “SysAdmin” server role is basically the equivalent of the “Control Server” permission, and this could be assigned instead of “Control Server”:
  • In SQL Server Management Studio, navigate to Security\Logons
  • Select the login for the service account and open its properties
  • Select the Server Roles page
  • Check “sysadmin” and close
• NOTE: Whereas the “SysAdmin” server role supersedes all other permissions, having the “Control Server” privilege is affected by other statements—‘DENY’ statements can reduce the amount of privileges. While this is beyond the scope of this document to outline specific scenarios, “Control Server” could be used in situations where it is necessary to reduce the privileges of the service account.

Local Security Policy Changes

The following chart summarizes the changes to be made in the Local Security Policy. More detailed explanations are found after the chart.

Log On as a Service

• Open the "Local Security Policy" (secpol.msc) Microsoft Management Console (MMC) snap-in.
• Select Security Settings\Local Policies\User Rights Assignment
• Open "Log on as a service" and add user
• NOTE: You can also configure this via a group policy object in Active Directory. If you try to modify this setting in Local Security Policy and the dialog is read-only, it means it is already being configured via Group Policy and you'll need to configure it from there.

Generate Security Audits (SeAuditPrivilege)

• Open the "Local Security Policy" (secpol.msc) Microsoft Management Console (MMC) snap-in.
• Select Security Settings\Local Policies\User Rights Assignment
• Open "Generate security audits" and add user
• NOTE: You can also configure this via a group policy object in Active Directory. If you try to modify this setting in Local Security Policy and the dialog is read-only, it means it is already being configured via Group Policy and you'll need to configure it from there.

Audit Policy

Windows Server 2003

• Open the "Local Security Policy" (secpol.msc) Microsoft Management Console (MMC) snap-in.
• Select Security Settings\Local Policies\Audit Policy
• Edit "Audit object access," ensuring that "Success" is enabled. (LOGbinder for SQL Server does not require that the "Failure" option be enabled.)
• NOTE: You can also configure this via a group policy object in Active Directory. If you try to modify this setting in Local Security Policy and the dialog is read-only, it means it is already being configured via Group Policy and you'll need to configure it from there.

Windows Server 2008 and 2012

Audit policy can be configured with the original top level categories as described above for Windows Server 2003 but most environments have migrated to the new more granular audit sub-categories available in Windows Server 2008 aka (Advanced Audit Policy).

Using Advanced Audit Policy Configuration allows for more granular control of the number and types of events that are audited on the server. (NOTE: The steps described here are for Windows Server 2008 R2; see TechNet for information on earlier releases.)

• First, you must ensure that ‘basic’ and ‘advanced’ audit policy settings are not used at the same time.
  • Microsoft gives this warning: “Using both the basic audit policy settings under Local Policies\Audit Policy and the advanced settings under Advanced Audit Policy Configuration can cause unexpected results. Therefore, the two sets of audit policy settings should not be combined. If you use Advanced Audit Policy Configuration settings, you should enable the Audit: Force audit policy subcategory settings (Windows Vista or later) to override audit policy category settings policy setting under Local Policies\Security Options. This will prevent conflicts between similar settings by forcing basic security auditing to be ignored.” (http://technet.microsoft.com/en-us/library/dd692792(WS.10).aspx)
  • Select Security Settings\Local Policies\Security Options
  • Open and enable “Audit: Force audit policy subcategory settings (Windows Vista or later) to override audit policy category settings”
• To enable LOGbinder for SQL Server events to be sent to the security log:
  • Select Security Settings\Advanced Audit Policy Configuration\Object Access
  • Edit “Audit Application Generated,” ensuring that “Success” is enabled. (LOGbinder for SQL Server does not require that the “Failure” option be enabled.)
  • NOTE: You can also configure this via a group policy object in Active Directory.