How To STIG SQL Server 2016

I was recently asked about STIG’ing a database server running SQL Server 2016. I checked DISA’s website and, to my surprise, they have not yet released an official STIG checklist for SQL Server 2016. The latest edition they have a STIG for is SQL Server 2014.

In fact, if you go to their website’s “master list“, and scroll down to “Microsoft SQL Server 2016 FAQ“, the link will direct you to the following FAQ page (image below):

How To STIG SQL Server 2016

So there you have it. Until DISA releases their official SQL Server 2016 STIGs, you can use the current SQL Server 2014 STIGs to harden SQL Server 2016.

Fair enough.

V-40951 – 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-40951

Title: “SQL Server must support the organizational requirement to employ automated mechanisms for enforcing access restrictions.”

Severity = Medium

Details = Obtain the SQL Server software library installation directory location.

From a command prompt, type regedit.exe, and press [ENTER].

Navigate to HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> Instance Names. Each instance installed on the server possesses a key inside a folder under this registry entry.

Analysis Services Instances are registered in the OLAP subfolder.
Reporting Services Instances are registered in the RS subfolder.
Standard SQL Server Instances are registered in the SQL subfolder.

Inside each one of these folders, a single key is used to reference an instance’s specific Windows Registry tree. Each key will have its own registry tree at the following registry location: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME].

An [INSTANCE NAME] is listed as the data component of a key found in one of the above OLAP, RS, or SQL folders.

To find the installation location of a particular instance, navigate to the following location in the Windows Registry:
HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME] >> Setup. Examine the value of the ‘SqlProgramDir’ key. The value of the ‘SqlProgramDir’ key is the SQL Server installation directory for that SQL Server Instance.

Navigate to that folder location using a command prompt or Windows Explorer. Note any custom subdirectories within the SQL Server software library directory. Only applications that are required for the functioning and administration of SQL Server should be located in the same disk directory as the SQL Server software libraries.

If any directories or files not installed with the SQL Server software exist within the SQL Server software library directory, this is a finding.

FIX: Install SQL Server software using directories separate from the OS and other application software library directories.

Relocate any directories or reinstall other application software that currently shares the DBMS software library directory to separate directories.

Recommend dedicating a separate partition for the SQL software libraries.

This is pretty self explanatory. You should never install your SQL Server on the default “C” drive. Let your SAN team, or Windows Admin team know that you will need separate disk partitions. For example, the installation files can reside on the “E” drive, data file on the “F” drive and the log file on the “G” drive.

MSDN has a great article that addresses security considerations for a SQL Server installation. Read the article here. I also highly recommend buying a great book by Denny Cherry called, “Securing SQL Server.” I did a review of the book that you can read here.

V-40950 – 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-40950

Title: “SQL Server must support the employment of automated mechanisms supporting the auditing of the enforcement actions.”

Severity = Medium

Details = Verify that Files and Folders that are part of the SQL Server 2012 Installation have auditing enabled.

Right click the root folder of the SQL Server installation. Typically, this is at :\Program Files\Microsoft SQL Server\. Select Properties.

Click on the Security tab

Click on the Advanced button

Click on the Auditing tab

If “Everyone” is not listed in the “Name” column, this is a finding.

If “This folder, subfolders and files” is not listed in the “Apply To” column, this is a finding.

When “Everyone” … ” is listed, select the “Everyone” row and click on the Edit button.

If either the Successful or Failed checkbox is not selected for any of the following access types, this is a finding:
Traverse folder/execute file
List folder/read data
Read attributes
Read extended attributes
Create files/write data
Create folders/append data
Write attributes
Write extended attributes
Delete
Read permissions

FIX: Navigate to Advanced Security Settings by selecting Properties > Security > Advanced > Auditing > Continue.

Where “Everyone” is missing from the “Name” column, click the Add button; type “Everyone” in the object names box; click the OK button. The Auditing Entry dialog opens.

Where “Everyone” is in the “Name” column, select that row and click on the Edit button. The Auditing Entry dialog opens.

In the Auditing Entry dialog, set “Apply onto” to “This folder, subfolders and files”.

In the Auditing Entry dialog, select both the Successful and the Failed checkbox for each of the following access types, where not already selected:
Traverse folder/execute file
List folder/read data
Read attributes
Read extended attributes
Create files/write data
Create folders/append data
Write attributes
Write extended attributes
Delete
Read permissions

Click OK, OK, OK, OK to save the new settings and exit the dialog boxes.

This is pretty self explanatory. MSDN has a great article that addresses security considerations for a SQL Server installation. Read the article here. I also highly recommend buying a great book by Denny Cherry called, “Securing SQL Server.” I did a review of the book that you can read here.

V-43196 – 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-43196

Title: “Domain accounts used to manage a SQL Server platform must be different from those used to manage other platforms.”

Severity = Medium

Details = Determine the accounts being used to manage the SQL Server operating system. Determine whether the same accounts are being used to manage other platforms. If the same account is used to manage more than one platform, this is a finding.

Note: If, because of the application configuration, there are multiple instances of SQL that would share a given exploit, a single account would be allowed to be used for the group and would not be considered a finding.

FIX: Set up and use separate domain accounts to manage the SQL Server platform. These accounts must be different from those used to manage other platforms.

MSDN has a great article that addresses security considerations for a SQL Server installation. Read the article here. I also highly recommend buying a great book by Denny Cherry called, “Securing SQL Server.” I did a review of the book that you can read here.

Book Review – “Securing SQL Server 3rd Edition” by Denny Cherry

 

V-40948 – 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-40948

Title: “Software, applications, and configuration files that are part of, or related to, the SQL Server 2012 installation must be monitored to discover unauthorized changes.”

Severity = High

Details = Verify that files and folders that are part of, or related to, the SQL Server 2012 installation have only the appropriate privileges.

In Windows Explorer, right-click the file/folder, click Properties. On the Security tab, modify the security permissions, so that at most the following permissions are present:

Trusted Installer (Full Control)
SYSTEM (Full Control)
Administrators (Full Control) [See Note 1]
Users (Read, List Folder Contents, Read & Execute)
Creator Owner (Special Permissions – Full control – Subfolders and files only)

All Application Packages (Read & Execute) [Only as needed – see Note 2]

If any less restrictive permissions are present (and not specifically justified and approved), this is a finding.

Verify that files and folders that are part of, or related to, the SQL Server 2012 installation have auditing enabled. Right-click on the file/folder, click Properties. On the Security tab, click Advanced. On the Auditing tab, verify that the following is set up on at least one audit:

Type: All
Principal: Everyone
Access: Modify
Applies to: This Folder, subfolder, and files [where applicable]

If the required audit settings are not configured, there is a risk that unauthorized changes to the software will go undetected, and this is a finding.

If a third-party security and data integrity tool is not used for monitoring and alerting files and folders based on cryptographic hashes, this is a finding.

If the tool does not verify files/folder locations as listed in the documentation, this is a finding.

FIX: Include locations of all files, libraries, scripts, and executables that are part of, or related to, the SQL Server 2012 installation in the documentation.

Ensure that files and folders that are part of, or related to, the SQL Server 2012 installation have only the following privileges. Right-click the file/folder, click Properties. On the Security tab, modify the security permissions, so that at most the following permissions are present:

Trusted Installer (Full Control)
SYSTEM (FULL CONTROL)
Administrators (FULL CONTROL)
Users (READ, LIST FOLDER CONTENTS, READ & EXECUTE)
Creator Owner (Special Permissions – Full control – Subfolders and files only)
All Application Packages (Read & Execute) [Only as needed – see Note 2]

Ensure that files and folders that are part of, or related to, the SQL Server 2012 installation have auditing enabled. Right-click on the file/folder, click Properties. On the Security tab, click Advanced. On the Auditing tab, use the Add or Edit buttons and the dialogs that follow from them, to set up the following on at least one audit:

Type: All
Principal: Everyone
Access: Modify
Applies to: This Folder, subfolder, and files [where applicable]

Deploy a third-party security and data integrity tool for monitoring and alerting files and folders based on cryptographic hashes, to verify files/folder locations as listed in the documentation.

Note 1: In the interest of separation of responsibilities with least privilege, consider granting Full Control only to SQL Database Administrators (or another appropriate group of administrators) and providing the local Administrators group with Read access only.

Note 2: Some files also require ‘ALL APPLICATION PACKAGES (READ, EXECUTE)’ permissions for certain functionality to work appropriately, and this is considered acceptable where those permissions are required. (All SQL Server files that require this access reside by default in the ..\Microsoft SQL Server\110\ directory.)

It’s extremely crucial to configure appropriate folder access. The last thing you want is some random user deleting files!

V-40945 – 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-40945

Title: “Vendor-supported software and patches must be evaluated and patched against newly found vulnerabilities.”

Severity = High

Details = Check Microsoft’s list of supported SQL Server versions http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx

To be considered supported, Microsoft must report that the version is supported by security patches to known vulnerabilities.

Check SQL Server version by running the following script:

print @@version

If the security patch support for SQL Server cannot be determined or SQL Server version is not shown as supported, this is a finding.

If SQL Server does not contain the latest security patches, this is a finding.

FIX: Upgrade SQL Server to the Microsoft-supported version.

Apply the latest SQL Server patches after evaluation of impact.

This fix is self-explanatory. Run the command above and see what version you are running. Go to SQL Server Updates (by Brent Ozar) and check if you have the latest Service Pack or Cumulative Update. If not, update. If so, close this “Not a Finding.”

*NOTE: Test any service packs or cumulative updates on a test / staging server first!

V-40941 – 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-40941

Title: “SQL Server must have the SQL Server Data Tools (SSDT) software component removed from SQL Server if SSDT is unused.”

Severity = High

Details = Review the list of components and features installed with the database. Using an account with System Administrator privileges, from Command Prompt, open control.exe.

Navigate to Programs and Features. Check for the following entries in the ‘Uninstall or change a program’ window.

Microsoft SQL Server Data Tools – Database Projects – Web installer entry point – Prerequisites for SSDT

If SQL Server Data Tools is not documented as a server requirement, and these entries exist, this is a finding.

FIX: Document the requirement for SQL Server Data Tools to reside on this server.

If your organization requires SSDT to be installed then just document that and close the finding.

If it is not required, using an account with System Administrator privileges, from Command Prompt, open control.exe.

Navigate to Programs and Features. Remove the following entries in the ‘Uninstall or change a program’ window.

Removing SSDT

Microsoft SQL Server Data Tools – Database Projects – Web installer entry point – Prerequisites for SSDT

Removing SSDT 2

Once you click NEXT, you can choose the SSDT checkbox and click NEXT to remove it.

Removing SSDT 3

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.

Database-Properties-Files

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.

SQL Server Autogrowth Settings

V-40907 – 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-40907

Title: “SQL Server must employ cryptographic mechanisms preventing the unauthorized disclosure of information during transmission, unless the transmitted data is otherwise protected by alternative physical measures.”

Severity = High

Details = From Command Prompt, open SQL Server Configuration Manager by typing sqlservermanager11.msc, and pressing [ENTER].

Navigate to SQL Server Configuration Manager >> SQL Server Network Configuration. Right click on Protocols for [NAME OF INSTANCE], where [NAME OF INSTANCE] is a placeholder for the SQL Server instance name, and click on Properties.

On the Flags tab, if Force Encryption is set to YES, examine the certificate used on the Certificate tab.

If Force Encryption is set, a DoD Certificate is not utilized, and some type of physical encryption measure is utilized, examine the physical encryption devices to determine the following:

1. The plain text connection to the database server is afforded the highest protections, allowing no access to unauthorized or non-cleared personnel.
2. The encryption device is configured to pass traffic to only the specific IP addresses as identified by the database documentation.
3. The encryption keys utilized are current and valid keys.
4. The keys utilized meet approved organizationally defined compliant algorithms.

If any of the preceding requirements is not met, this is a finding.

If Force Encryption is set to No, a DoD Certificate is not utilized, and some type of physical encryption measure is not utilized, this is a finding.

FIX: Deploy organization approved encryption to the SQL Server Network Connections.

From Command Prompt, open SQL Server Configuration Manager by typing sqlservermanager11.msc, and pressing [ENTER].

Navigate to SQL Server Configuration Manager >> SQL Server Network Configuration. Right click on Protocols for [NAME OF INSTANCE], where [NAME OF INSTANCE] is a placeholder for the SQL Server instance name, and click on Properties.

V-40907 SQL Config Manager
Provide a DoD certificate on the Certificate tab via the drop down.

V-40907 SQL Config Manager Certificate

Now you can go back to the Flags tab and set it to True.

 V-40907 SQL Config Manager Setting

If you need help with obtaining and installing a DoD SSL Certificate, check out the following blogs:

How to Request a DoD Server Certificate

How to Create SSL Certificate for SQL Server