AK574681

Monitor_AK574681 (UnitMonitor)

SQL Server database master key for master database is not encrypted by service master key

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Monitor_AK574681" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="High" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessageb82ad7cc5cb04658b4de880599174c02">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='IsMasterDatabaseDMKEncrypted']$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='CountOfTDEDatabasesEnabled']$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AK574681.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

        <Value>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "ConnectionString" $ConnectionString



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

$scriptoutput | add-member NoteProperty "IsMasterDatabaseDMKEncrypted" $false

$scriptoutput | add-member NoteProperty "CountOfTDEDatabasesEnabled" 0

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment



$scriptenv = New-Object psobject

$scriptenv | Add-Member NoteProperty "RuntimeError" $false

$scriptenv | Add-Member NoteProperty "IsMasterKeyEncryptedByServer" $true

$scriptenv | Add-Member NoteProperty "CountOfDatabaseEnabled" 0





function GetAndCountOfMasterKeyEncryptedByServer()

{

	#IsMasterKeyEncryptedByServer = "select is_master_key_encrypted_by_server from sys.databases where name = 'master'"

	#return IsMasterKeyEncryptedByServer 

	#Select of encrypted is used in Information collected section

	#Create SqlConnection object and define connection string 

	$oConnection = New-Object System.Data.SqlClient.SqlConnection 

	$oConnection.ConnectionString = "Server=" + $scriptargs.ConnectionString + ";" + "Database=master;Integrated Security=true" 

	$oConnection.Open()



	# Create SqlCommand object, define command text, and set the connection

	$oSQLCommand = New-Object System.Data.SqlClient.SqlCommand

	$oSQLCommand.CommandText = "select is_master_key_encrypted_by_server from sys.databases where name = 'master' union all select COUNT(*) from sys.databases where is_encrypted = 1" 

	$oSQLCommand.Connection = $oConnection 

	# Execute Command 

	$sqlDataReader = $oSQLCommand.ExecuteReader()

	

	[array] $aryOutputDataList = $null

	While($sqlDataReader.Read())

	{

		$aryOutputDataList += $sqlDataReader[0]

	}

	

	if($aryOutputDataList[0] -eq 0)

	{

		$scriptenv.IsMasterKeyEncryptedByServer = $false

	}

	else

	{

		$scriptenv.IsMasterKeyEncryptedByServer = $true

	}

	

	$scriptenv.CountOfDatabaseEnabled = $aryOutputDataList[1]

	

	$sqlDataReader.Close()

	$oConnection.Close()

}





# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{ 

	# All parameters should be populated outside of the main function. 

	# The mian function should only include the detection logic so that it can be easyly reused by the Atlanta authoring tool.



	trap [Exception] {

		$scriptenv.RuntimeError = $true

      	continue; 

   	}

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.IsMasterDatabaseDMKEncrypted = $true

	$scriptoutput.CountOfTDEDatabasesEnabled = 1



	# Set parameter values

	GetAndCountOfMasterKeyEncryptedByServer

	

	$scriptoutput.IsMasterDatabaseDMKEncrypted = $scriptenv.IsMasterKeyEncryptedByServer

	$scriptoutput.CountOfTDEDatabasesEnabled = $scriptenv.CountOfDatabaseEnabled



	if($scriptoutput.IsMasterDatabaseDMKEncrypted -eq $false)

	{

		if($scriptoutput.CountOfTDEDatabasesEnabled -ne 0)

		{

			if($scriptenv.RuntimeError -eq $false)

			{

				#Raise alert

				$scriptoutput.HasIssue = $true

			}

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



if ($scriptoutput.IsMasterDatabaseDMKEncrypted -ne $null)

{

	$bag.AddValue("IsMasterDatabaseDMKEncrypted", $scriptoutput.IsMasterDatabaseDMKEncrypted)

}



if ($scriptoutput.CountOfTDEDatabasesEnabled -ne $null)

{

	$bag.AddValue("CountOfTDEDatabasesEnabled", $scriptoutput.CountOfTDEDatabasesEnabled)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>