Title: The DBMS must fail to a known safe state for defined types of failures.
Vulnerability ID: V-32528
IA Controls: None
Description: Failure in a known state can address safety or security in accordance with the mission/business needs of the organization.
Failure in a known secure state helps prevent a loss of confidentiality, integrity, or availability in the event of a failure of the information system or a component of the system.
Failure in a known safe state helps prevent systems from failing to a state that may cause loss of data or unauthorized access to system resources. Applications or systems that fail suddenly and with no incorporated failure state planning may leave the hosting system available but with a reduced security protection capability. Preserving information system state information also facilitates system restart and return to the operational mode of the organization with less disruption of mission/business processes.
An example is a firewall that blocks all traffic rather than allowing all traffic when a firewall component fails. This prevents an attacker from forcing a failure of the system in order to obtain access.
Databases must fail to a known consistent state. Transactions must be successfully completed or rolled back.
Check Text: Check DBMS settings and vendor documentation to verify the DBMS properly handles transactions in the event of a system failure. If open transactions are not rolled back to a consistent state during system failure, this is a finding.
Fix Text: Configure DBMS settings to properly handle transactions in the event of a system failure. DBMS failures must not leave transactions in an inconsistent state.
SQL Server is a transaction based system. In fact if the transaction log cannot be written to (missing, full, etc) the service stops since it cannot record the transactions and fail to a known state.
When a transaction is sent to SQL Server it first logs the transaction in the transaction log, it then makes the change to the data in memory (everything in SQL is done in memory for performance). At that point the transaction is considered committed, even though it has not made it to the HDD yet. There is a process that comes along later (lazywriter) that will then sync the committed memory transaction to the disk. Depending on when a failure of the system might happen, the first thing the SQL Server does as it comes back online (or failover in a cluster, etc) is check the transaction log status and either roll back (transactions that did not make it fully to memory) or roll forward (those that made it to memory, but not to disk) to get the database back to a stable known state. Only once this process is completed will users be able to reconnect.