Home → LOGbinder for SQL KB → Getting Started Guide → Appendix
2.4. Appendix
Appendix A: Assigning Permissions
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.
Local Security Policy (secpol.msc) settings summary |
Windows Server 2003 |
Windows Server 2008/2012 |
|
|||
Security Settings |
Local Policies |
User Rights Assignment |
Log on as a service |
add service account |
add service account |
This always needs to be set |
Generate security audits |
add service account |
add service account |
These need to be set if outputting to Windows Security log |
|||
Audit Policy |
Audit object access |
set Success and Failure |
N/A |
|||
Security Options |
Audit: Force audit policy subcategory settings (Windows Vista or later) to override audit policy category settings |
N/A |
set Enabled |
|||
Advanced Audit Policy Configuration |
Object Access |
Audit Application Generated |
N/A |
set Success and Failure |
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.
Appendix B: LOGbinder Event List
LOGbinder for SQL Server Events
See a list of all of the LOGbinder for SQL events at this link - https://www.logbinder.com/Products/LOGbinderSQL/EventsGenerated
Diagnostic Events
551 – LOGbinder agent successful
552 – LOGbinder warning
553 – LOGbinder settings changed
554 – LOGbinder agent produced unexpected results
555 – LOGbinder error
556 – LOGbinder insufficient authority
557 – License for LOGbinder invalid
Appendix C: Diagnostic Events
551 – LOGbinder agent successful
Occurs when LOGbinder for SQL Server successfully translates log entries. Usually appearing in pairs, as one indicates that log entries have been 'exported' from their source (for example, SQL Server), and the other that entries have been 'imported' to their destination (for example, the Windows event log). This event is informational in nature.
This event is written to the Windows Application log.
Example A
LOGbinder agent successful
LOGbinder SQL exported 3 entries from SQL logs from c:\sqlaudit\
Example B
LOGbinder agent successful
LOGbinder SQL imported 3 entries to Security event log
Example C
LOGbinder agent successful
LOGbinder SQL imported 3 entries to LOGbinder SQL event log
552 – LOGbinder warning
Occurs when LOGbinder for SQL Server does not find information as expected. In most cases, it does not indicate a serious problem, but is provided so as to complete the audit trail. This event is written to Windows application log.
For example, as LOGbinder for SQL Server translates entries, it performs various lookups to provide complete information. If the related item was deleted, a "LOGbinder warning" is generated.
Example A
LOGbinder warning
Lookup failed. Could not find Scope Item with ID of 89de71fe-1442-48ff-9a6e-052bddda3440.
Example B
LOGbinder warning
Lookup failed. Could not find User with ID of 19.
553 – LOGbinder settings changed
Occurs when the LOGbinder settings are changed. This event is written to Windows Application log.
For LOGbinder for SQL Server, this includes changes to the Audit File Location.
Example A
LOGbinder settings changed
Output to Security log enabled. Noise events included.
Example B
LOGbinder settings changed
Settings for c:\sqlaudit\ adjusted: Last export value is c:\sqlaudit\Audit-LocalFile_3B48C4ED-9DA8-462E-BFD9-4935A28148B8_0_129590759441100000.sqlaudit; offset 0
Example C
LOGbinder settings changed
Settings for C:\SQLAudit2 adjusted: folder changed from C:\SQLAudit2 to C:\SQLAudit
554 – LOGbinder agent produced unexpected results
Occurs when LOGbinder for SQL Server encounters something unexpected when translating a log entry. At times it may be from a custom log entry.
This event is written to Windows Application log.
You can help us improve LOGbinder by reporting these events to the LOGbinder support team so that the LOGbinder product may be improved. Private data will not be shared.
Example A
In this example, the developer created an audit entry with the type "MakeItSo."
LOGbinder agent produced unexpected results
As the LOGbinder agent translated this entry, it encountered data is could not handle properly. It could have been caused by a custom or undocumented feature. So that LOGbinder can handle these entries in the future, it is suggested that you submit the entry to the LOGbinder support team.
<LogEntry siteName="http://shpnt" itemType="Site" userName="Robert Solomon" locationType="Url" occurred="2009-06-26T14:13:02" eventType="MakeItSo"><RawData siteId="3b7fb82c-f30d-4604-99c0-df8325e9cff4" itemId="3b7fb82c-f30d-4604-99c0-df8325e9cff4" itemType="Site" userId="1" locationType="Url" occurred="633816223820000000" event="Custom" eventName="MakeItSo" eventSource="ObjectModel"><EventData><Version><Major>1</Major><Minor>2</Minor></Version></EventData></RawData><Details /></LogEntry>
Example B
In this example, the developer used an existing event type, "Workflow," but included non-standard event data.
LOGbinder agent produced unexpected results
As the LOGbinder agent translated this entry, it encountered data is could not handle properly. It could have been caused by a custom or undocumented feature. So that LOGbinder can handle these entries in the future, it is suggested that you submit the entry to the LOGbinder support team.
<LogEntry siteName="http://shpnt" itemType="List Item" userName="Robert Solomon" locationType="Url" occurred="2009-06-29T21:49:11" eventType="Workflow"><RawData siteId="3b7fb82c-f30d-4604-99c0-df8325e9cff4" itemId="c04f5388-bf24-4007-b463-1dd1b3c19a02" itemType="ListItem" userId="1" documentLocation="Cache Profiles/1_.000" locationType="Url" occurred="633819089510000000" event="Workflow" eventSource="ObjectModel"><EventData>http://shpnt/docLib/CopiedFile.ext</EventData></RawData><Details /></LogEntry>
555 – LOGbinder error
Occurs when LOGbinder encounters a problem that needs attention. This event is written to Windows Application log. In most cases this gives enough information for you to address the problem successfully. Otherwise, please contact LOGbinder support for assistance.
Example A
In this example, the error indicates that LOGbinder for SQL Server has not been configured properly: in that no SQL audit location was set to be monitored by LOGbinder.
LOGbinder error
Cannot start LOGbinder SQL service, SQL Audit Locations not configured.
556 – LOGbinder insufficient authority
Occurs when LOGbinder for SQL Server (LOGbinder SQL) service cannot run because of invalid or inadequate permissions. The event will include the module lacking the permission, the name or description of the permission, as well as relevant details. Each example below also includes the action needed in order to correct it.
Example A: No permission to write to security log
LOGbinder insufficient authority
The LOGbinder agent cannot operate normally because it lacks sufficient authority.
Source: Security Log
Privilege: SeAuditPrivilege
Details: The LOGbinder agent does not have the permissions to configure the security log
Action: The service account needs the "Generate security audits" privilege (https://www.ultimatewindowssecurity.com/wiki/WindowsSecuritySettings/Generate-security-audits), or do not enable LOGbinder to output to the Windows Security log.
Example B: Attempt to write to security log from invalid location
One measure to protect the security log is to write security events only from authorized locations. When LOGbinder is configured, it registers its program location with the security log. If this error occurs, then LOGbinder had been reinstalled to a different location, and the previous location was not removed properly.
LOGbinder insufficient authority
The LOGbinder agent cannot operate normally because it lacks sufficient authority.
Source: Security Log
Privilege: Invalid Location
Details: Cannot write to because the program location does not match what has been previously configured
Action: Recommended to delete the registry key manually. First ensure that LOGbinder is not open. Then delete the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Security\LOGbndSC. Be careful not to delete other parts of the registry, as it can cause the server to be unstable. When you reopen LOGbinder, it will reconfigure its ability to write to the security log.
Example C: Internal error
LOGbinder insufficient authority
The LOGbinder agent cannot operate normally because it lacks sufficient authority.
Source: Security Log
Privilege: Internal Error
Details: The security account database contains an internal inconsistency
Action: One factor that can cause an internal error is if the LOGbinder program path is too long. By default, LOGbinder is installed to C:\Program Files\LOGbndSQ. It is recommended that the default be used. If the software has been installed to a different location with a longer program path, to correct this error it will be necessary to reinstall LOGbinder.
Example D: Log on as service
LOGbinder insufficient authority
The LOGbinder agent cannot operate normally because it lacks sufficient authority.
Source: LOGbinder service
Privilege: Log on as service
Details: Account running LOGbinder agent does not have user right "Logon as a service"
Action: The service account needs to be assigned the "Logon as a service" user right. (https://www.ultimatewindowssecurity.com/wiki/WindowsSecuritySettings/Log-on-as-a-service)
Example E: Cannot start LOGbinder control panel
LOGbinder insufficient authority
The LOGbinder agent cannot operate normally because it lacks sufficient authority.
Source: LOGbinder Manager
Privilege: File Permissions
Details: Account running LOGbinder Control Panel needs to be a member of the local Administrators group
Action: Ensure that the user account used to run the LOGbinder for SQL Server control panel has local administrator access.
557 – License for LOGbinder invalid
Occurs when the license for LOGbinder is not valid and an attempt is made to start the service. This event is written to the Application log.
If the license is not valid, the LOGbinder for SQL Server control panel continues to operate as normal. However, the LOGbinder service will not start if the license is invalid. Follow the instructions in the control panel, in the menu File\License, in order to obtain a license to the software.
Example
License for LOGbinder invalid
Details: License is invalid. Open LOGbinder SQL Control Panel to remedy.