V-40932 – SQL Server 2012 Database Instance DISA STIGs

If you have any additional questions that aren’t addressed in the blog, feel free to contact me. I’d be more than happy to help you out!

V-40932

Title: “SQL Server must recover to a known state that is verifiable.”

Severity = High

Details = Obtain the SQL Server recovery procedures and technical system features to determine if mechanisms exist and are in place to specify use of trusted files during SQL Server recovery.

If recovery procedures do not exist or are not sufficient to ensure recovery is done in a secure and verifiable manner, this is a finding.

Check the configurations of all transaction log files that are enabled by running the following SQL Server query:

EXEC sp_MSforeachdb
'
SELECT ''?'' AS ''database name''

, name AS ''log file name''
, physical_name AS ''log file location and name''
, state_desc
, size
, max_size
, growth
, is_percent_growth
FROM [?].sys.database_files
WHERE type_desc = ''LOG''
AND state = 0;';

If any transaction log files are not configured correctly for size, max_size, and growth to log sufficient transaction information, this is a finding.

My modification to the SQL script above will use a temp table so that everything is displayed nice and neat. Feel free to modify any way you deem fit:

USE master;
GO

--create the procedure
CREATE PROCEDURE dbLogFiles
AS
EXEC sp_MSforeachdb
'
SELECT ''?'' AS ''database name''
, name AS ''log file name''
, physical_name AS ''log file location and name''
, state_desc
, size
, max_size
, growth
, is_percent_growth
FROM [?].sys.database_files
WHERE type_desc = ''LOG''
AND state = 0;'
GO

--create the temptable
CREATE TABLE #testTable(
database_name nvarchar(128),
name nvarchar(128),
physical_name nvarchar(260),
state_desc nvarchar(60),
size int,
max_size int,
growth int,
is_percent_growth bit);
GO

--Insert data into temptable
INSERT INTO #testTable
exec dbLogFiles;
GO

SELECT 
    database_name AS [Database Name],
    name AS [Logical File Name],
    physical_name AS [Pysical Location of LOG File],
    state_desc,
    size AS [Current Size of File in 8KB pages],
    CASE max_size
    	WHEN -1 THEN 'Unlimited'
    	WHEN 268435456 THEN '2 TB'
    END as [Max File Size],
    	CASE is_percent_growth
    	WHEN 0 THEN 'No'
    	WHEN 1 THEN 'Yes'
    END AS [Is Percent Growth?],
    growth AS [Growth Rate in %]
FROM #testtable


--Drop the table when you're done.
DROP TABLE #testTable;
GO

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

THEN

1. Add database transaction log file if one does not exist.
Hopefully you won’t see a missing Log File. SQL Server database won’t even start without a log file.

2. To modify Initial Size (MB), click on “Initial Size (MB)” then edit value.

3. To modify Autogrowth, click on the “Autogrowth/Maxsize” button that is in Log record, choose “In Percent” or “In Megabytes”, enter value, and then click OK.4. To modify Maximum File Size, click on the “Autogrowth/Maxsize” button that is in Log record, choose “Limited to (MB)” or “Unlimited”, enter value, and then click OK.