SQL Server must have transaction logging enabled.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-40931	SQL2-00-017600	SV-53285r1_rule		Medium

Description
Application recovery and reconstitution constitutes executing an information system contingency plan that is comprised of activities that restore essential missions and business functions. SQL Server is an example of an information system that is transaction-based. Transaction rollback and transaction journaling are examples of mechanisms supporting transaction recovery. This allows the database to recover data content to a point where the data content and its relationships are known to be intact. This data integrity is maintained when the data is undergoing a change or update event. SQL Server enables transaction rollback or recovery by default and as an automatic feature of database recovery.

Description

Application recovery and reconstitution constitutes executing an information system contingency plan that is comprised of activities that restore essential missions and business functions. SQL Server is an example of an information system that is transaction-based. Transaction rollback and transaction journaling are examples of mechanisms supporting transaction recovery. This allows the database to recover data content to a point where the data content and its relationships are known to be intact. This data integrity is maintained when the data is undergoing a change or update event. SQL Server enables transaction rollback or recovery by default and as an automatic feature of database recovery.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2014-01-17

Details

Check Text ( C-47586r2_chk )
Check the transaction log files of databases to ensure it is "ONLINE" by running the following SQL Server query: Select * FROM sys.database_files WHERE type_desc = 'LOG' AND state_desc = 'online' If the system transaction log file are not "ONLINE" (enabled), this is a finding.

Fix Text (F-46213r2_fix)
Enable SQL Server's transaction journaling. Modify system log file: Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> right click on <'system database name'> >> Properties >> Files. Modify user-defined log file: Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> right click on <'user-defined database name'> >> Properties >> Files. Add database transaction log file if one does not exist.

Fix Text (F-46213r2_fix)

Enable SQL Server's transaction journaling.

Modify system log file:
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> right click on <'system database name'> >> Properties >> Files.

Modify user-defined log file:
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> right click on <'user-defined database name'> >> Properties >> Files.

Add database transaction log file if one does not exist.