DoD DISA SQL Server 2012 STIGs

The american (USA) Department of Defence (DoD) has released some Security Technical Implementation Guides (STIGs) for Microsoft SQL Server 2012.
There are two principal documents:

• "Microsoft SQL Server Database Instance Security Technical Implementation Guide"
• "Microsoft SQL Server 2012 Database Security Technical Implementation Guide"

Also DISA has composed a general "Database Security Requirements Guide", that can be used across database managers and versions.
In general the documents are a collection of discussions about issues like:

1. Least Privilege.
2. Separation of Duties (SoD).
3. Least Service.
4. Role Based Access Control (RBAC).
5. Security Classification.

Least Privilege

In the STIG Least Privilege is refered to as a concept, where other places it is refered to as a principle. I will not discuss the difference here as it might take this text off course.
This principle sounds simple and easy to implement, but if you really asks the same question to every acces or right it is really complicated to construct a solution that implements this principle to the core.
If you are expected to implement a system build with a framework like SharePoint or Entity Framework, it might be rather difficult - like "impossible" -  to figure out the really needed privileges.
A good sub-principle is to avoid the builtin server roles and database roles in SQL Server. This forces everybody to define the actual privilege.

Seperation of Duties

As a DBA in a financial organisation this is a very important. Not only to comply with formal rules but also to ensure a robust installation. This concept (principle?) is very often tightly integrated with Role Based Access Control in the construction and implementation.

Least Service

To contribute to at stable and secure installation it is a good idea only to install the needed services. Other nice-to-have services increases the risk of security holes and also increases the need for upgrades. Such upgrades might reduce the service availability while restarting og likewise stopping the installation.

Role Based Access Control

To ensure Seperation of Duties and meet audit requirements it is usually expected that the Security Plan is based on Role Based Access Control. Mostly the roles are business functional roles, but they can also be based on duties in a system.
There is a lot of litteratur on this complex subject. If you are expected to enforce this principle I think you should spend some time on the theories behind.
Also there is a huge administrative benefit of using Role Based Access Control, in common opposition to individual user access and rights.
If you are asked to give a user "the same rights as <existing user>" this is a worring and sure sign of no security plan and no us of Role Based Access Control. Such a request could make the day very long rather suddenly.
The subject of Role Based Access Control easily leads to the subject of Discretionary Access Control (DAC), that also is refered to several times in the documents. This is important when setting a policy of ownership.
Enforcing Role Based Access Control is not only implementing functional roles, but also to handle the faces of SQL Server that might break this principle if handled without proper awareness. A classical situation is the database owner, where you might have a database owned by a subject that should have limited rights on some object in the database.

Security Classification

This issue is mentioned in several of the documents, sometimes as security labels on the data.
Such a rule is very usefull to implement especially when dealing with sensitive data. But it requires the organisation to define both the sensitive data and the security levels shown by the labels. These defnintions must be precise and unique enough to be implemented.

History

2014-01-19 Blog post created.
2017-05-29 Link for DISA database STIGs updated.

Audit Log for Analysis Services

The SQL Server Database Engine has the feature Login Auditing, that can create a simple audit log. This log contains among other things service start and stop and logins.
I was approached with a similar requirement on SQL Server Analysis Services (SSAS), but this does not have something like Login Auditing.
SSAS does have tracing features, and can be monitored with Extended Events. The documentation is mostly written for the Database Engine, but with some trial-and-error it can be implemented on SSAS with a XMLA statement.

The documentation from Microsoft is not good, but I found a great blogpost with a working example (http://byobi.com/blog/2013/06/extended-events-for-analysis-services/). To this example I have added some features to meet requirements.

Auto Restart

Auto Restart is required to ensure that the trace is running even after a restart of the SSAS service or the server. This is implemented by adding a AutoRestart element to the XMLA statement.

Limited number of tracefiles

To ensure that the tracing does not run wild, we decided to limit the size of the logfiles and the number of logfiles. This is by the configuration of the Event File Target.
The size of each logfile is defined with the option "max_file_size" with a value i MBs (MiBs).
The number of logfiles is defined by the option "max_rollover_files".

Implementation

The XMLA statement to implement the Audit Log:
<Create
  xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
  xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
  xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
  xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
  xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
  <ObjectDefinition>
    <Trace>
      <ID>SQLAdmin_Audit</ID>
      <Name>SQLAdmin_Audit_2014-W02</Name>
      <AutoRestart>true</AutoRestart>

      <ddl300_300:XEvent>
        <event_session name="xeas"
          dispatchLatency="1"
          maxEventSize="4"
          maxMemory="4"
          memoryPartitionMode="none"
          eventRetentionMode="allowSingleEventLoss"
          trackCausality="true">

        <event package="AS" name="AuditLogin" />
        <event package="AS" name="AuditLogout" />

        <event package="AS" name="ExistingConnection" />
        <event package="AS" name="ExistingSession" />
        <event package="AS" name="SessionInitialize" />

        <target package="Package0" name="event_file">
          <parameter name="filename" value="L:\MSSQL_AS_Log\SQLAdmin_Audit.xel" />
          <parameter name="max_file_size" value="1024" />
          <parameter name="max_rollover_files" value="3" />
        </target>
      </event_session>
    </ddl300_300:XEvent>
    </Trace>
  </ObjectDefinition>
</Create>


Operation

This DMX statement shows what traces that are running:
SELECT *
FROM [$System].[DISCOVER_TRACES];

This XMLA statement deletes the trace. The logfiles (.xel) will not be deleted.
<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <TraceID>SQLAdmin_Audit</TraceID>
  </Object>
</Delete>


A logfile can be browsed with SQL Server Management Studio. The file can be accessed on a UNC path.
The original blogentry mentions that the logfile can be read with the T-SQL function sys.fn_xe_file_target_read_file, but this is a Database Engine function.
To use this function to get the contents of the logfile – in XML – the file must be moved or copied to a location where a Database Engine can acces the file.

Temporary Windows file

Sometimes it can be usefull to create a temporary Windows file, that is more general available than a personal temporary user file.
If you use the environmental variables „TEMP“ or „TMP“ you get your personal folder for temporary files, e.g. „C:\Users\Niels\AppData\Local\Temp“. Both variables gives the same folder name.

To get the path of the more general folder of temporary files „C:\Windows\Temp“ you can use the environmental variables „SystemRoot“ or „windir“ and add the name of the Temp-folder in PowerShell like
"$Env:SystemRoot\Temp"

To create a unique and identifiable temporary file I usually add a timestamp to the filename like
.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).
The Z is to indicate that the timestamp is UTC. That is timezone Z also called Zulu time.
The Replace method of the formatted String-object is used to get rid of the colons in the standard format. A colon is not acceptep by Windows in a file name.

The name of a temporary file can be created in a single PowerShell statement
$TempFile = "$Env:SystemRoot\Temp\_temp.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).ps1"

Writing to the temporary file can be done in PowerShell with the cmdlet Out-File or PowerShell redirection operators.
The first line can be written without any terms, but the following lines must be added to preserve the existing contents of the temporary file.
The first line, that also creates the temporary file, can be written in PowerShell like
'1st line' | Out-File -FilePath $TempFile
A second line can be added using the cmdlet Out-File like
'2nd line' | Out-File -FilePath $TempFile -Append
A third line can be added with the PowerShell appending redirection operator like
'3rd line' >> $TempFile

With the three examples above a temporary file is created
C:\Windows\Temp\_temp.2013-12-11T15_33_50Z.ps1
The contents of the temporary file is
1st line
2nd line
3rd line

This simple technique can be used to create other temporary Windows files like text files or data (csv) files.

xp_instance_regwrite syntax

The stored procedure "master.sys.xp_instance_regwrite" is not documented in SQL Server Books Online (BOL), but is usefull. In general the syntax is
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'<name of root key>'
 ,@key = N'<key name>'
 ,@value_name = N'<key name value>'
 ,@type = N'<key type>'
 ,@value = N'<key value>';

For example the number of SQL Server Errorlog files can be updated by the statement
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'Software\Microsoft\MSSQLServer\MSSQLServer'
 ,@value_name = N'NumErrorLogs'
 ,@type = N'REG_DWORD'
 ,@value = 42;

Use with caution!

The parameter @type takes a value without N'' around (@type = <key type>), but I like the Unicode framing. This way I try to avoid difficulties when building the values outside the call.

The type of the value for the parameter @value depends on the registry value type.
If the registry value type is a integer like REG_DWORD, the parameter value must be a integer.
 ,@value = 42;
If the registry value type is a string like REG_SZ, the parameter value must be a string.
 ,@value = N'John';

PowerShell usage

If the usage of this (undocumented) procedure should be included in a PowerShell script, it could be with ADO.NET in a function like this
function Write-MsSqlDbInstanceRegistry
{
  [CmdletBinding()]
  Param (
    # help description
    [Parameter(Mandatory=$true, Position=0)]
    [System.Data.SqlClient.SqlConnection]$MsSqlDbCnn,

    # Registry key path
    [Parameter(Mandatory=$true, Position=1)]
    [String]$RegKey,

    # Registry key name
    [Parameter(Mandatory=$true, Position=2)]
    [String]$RegName,

    # Registry key type
    [Parameter(Mandatory=$true, Position=3)]
    [String]$RegType,

    # Registry key value
    [Parameter(Mandatory=$true, Position=4)]
    [String]$RegValue
  )

  Begin {
    "{0:s}Z Write-MsSqlDbInstanceRegistry( '$RegKey', '$RegName', '$RegType', '$RegValue')" -f $([System.DateTime]::UtcNow) | Write-Verbose
  }

  Process {
    $MsSqlDbCmd = New-Object System.Data.SqlClient.SqlCommand
    $MsSqlDbCmd.Connection = $MsSqlDbCnn
    $MsSqlDbCmd.CommandText = '[master].[sys].[xp_instance_regwrite]'
    $MsSqlDbCmd.CommandType = [System.Data.CommandType]::StoredProcedure

    $MsSqlDbCmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'

    $MsSqlDbCmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@key'].Value = $RegKey

    $MsSqlDbCmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@value_name'].Value = $RegName

    $MsSqlDbCmd.Parameters.Add("@type", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@type'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@type'].Value = $RegType

    switch -CaseSensitive ($RegType) {
      'REG_DWORD' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::Int) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = [Int]$RegValue
      }
      'REG_SZ' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = $RegValue
      }
      default {
        "{0:s}Z Unknown Registry Value Type '$RegKeyType'." -f $([System.DateTime]::UtcNow) | Write-Error
        return
      }
    }
    $MsSqlDbCmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Input

    $MsSqlDbCnn.Open()
    [Int]$RowCount = $MsSqlDbCmd.ExecuteNonQuery()
    $MsSqlDbCnn.Close()
  }

  End {}
}
The PowerShell function can be used like this
$Cnn = New-Object System.Data.SqlClient.SqlConnection
$Cnn.ConnectionString = 'Data Source=(local);Integrated Security=SSPI;Application Name=MsSqlDbInstanceRegistry'

Write-MsSqlDbInstanceRegistry `
  -MsSqlDbCnn $Cnn `
  -RegKey 'Software\Microsoft\MSSQLServer\MSSQLServer' `
  -RegName 'NumErrorLogs' `
  -RegType 'REG_DWORD' `
  -RegValue '42' `
  -Verbose

History

2010-09-15  First entry.
2013-09-09  PowerShell added.
2013-11-19  Registry Value Types handled correct.

Backup failed - nonrecoverable I/O

On a SQL 2008 SP2 (10.0.4000) I take database backup with a Maintenance Plan. This morning I had a failed database backup:
Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
No nice :-(

When I looked in the log file generated by the Maintenance Plan, the error message was:
Task start: 2011-01-18T22:14:00.
Task end: 2011-01-18T22:14:01.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When I looked in the Windows System Log, there was no relevant entries.
But looking at the drive and the free space I saw that there was not space enough for the next backup file.
After a cleanup and manual execution of the Maintenance Plan everything was green.

The amount of free space can be looked up with PowerShell like this:
Get-WmiObject -Query "SELECT FreeSpace, Size FROM Win32_LogicalDisk WHERE DeviceID = 'T:'" -ComputerName SANDY.sqladmin.lan |
Format-Table @{Name='Freespace (GB)';Expression={"{0:N1}" -f($_.FreeSpace/1gb)}}, @{Name='Size (GB)';Expression={"{0:N1}" -f($_.Size/1gb)}} -AutoSize
The value of the parameter ComputerName should be changed to the actual databaseserver, also value in the WQL WHERE clause should be changed to the drive indicated in the error message.

The follow up is to order some additional storage.

I find the error message somewhat misleading. It looks like the ResultSet part is from the connectivity and not from the root error.

History

2011-01-19 : This is postede for the first time.
2013-08-22 : The PowerShell script is added.