Home
  •  

AKN652417

Monitor_AKN652417 (UnitMonitor)

SQL Server Database is unsupported if the key security principals are altered

Knowledge Base article:

External

http://go.microsoft.com/fwlink/?linkid=393721

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.DBEngine
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityHigh
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server Database is unsupported if the key security principals are altered
<Details>
<Content>In this instance of SQL Server, System Center Advisor detected that a key security principal is altered. You can run into various authentication or authorization issues due to this. This is similar to direct modification to the system catalog and is not supported.</Content>
<CollectedInformation>
<Info>
<Name>Database Name:</Name>
<Value>{0}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AKN652417" 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="MonitorMessagead95a62e8378448786864e5c5408aaec">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN652417.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 "DatabaseNameOutput" ""

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

# MAIN CODE SECTION

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



# Environment



$scriptenv = New-Object psobject

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

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

$scriptenv | add-member NoteProperty "AffectedDatabaeList" $null



# Helper functions

# Function to get the connection string to the target Database

Function GetDBConnectionString($everyDatabase){

    $connStrBulider = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder

            $ConnStrBulider["Data Source"] = $scriptargs.ConnectionString

            $ConnStrBulider["Database"] = $everyDatabase

            $ConnStrBulider["Trusted_Connection"] = "SSPI"

    $connStrBulider.ConnectionString

}



# Private function to format XML data for UI display

Function Format-XMLData($Items){

	$formatString = ""

	$xmlTemplate = "&lt;row&gt;&lt;DatabaseName&gt;{0}&lt;/DatabaseName&gt;&lt;/row&gt;"

	$i = 0

	If($Items -ne $null -and ([Array]$Items).Count -ne 0)

	{

		([Array]$Items) | ForEach-Object{

			$formatString += [String]::Format($xmlTemplate,$_)

		}

	}

	$formatString

}



Function Check_Database(){

	$DBs = @()

	$AffectedDatabaes = @()

	# Create SqlConnection object

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

	$oConnection.ConnectionString = GetDBConnectionString("master")

	$oConnection.open()



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

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

	$oSQLCommand.Connection = $oConnection

	

    # Use the query below to get the all database on the server

	$oSQLCommand.CommandText = "SELECT Name FROM sys.databases" 

	$oSQLDataReader = $oSQLCommand.ExecuteReader()

	If ($oSQLDataReader.HasRows){

 		While ($oSQLDataReader.Read()){

			$DBs += $oSQLDataReader["Name"]

		}

	}

	$oSQLDataReader.Close()

	

	Foreach($DB in $DBs){

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

		$oConnection.ConnectionString = GetDBConnectionString($DB)

		$oConnection.open()

		

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

		$oSQLCommand.Connection = $oConnection

		$oSQLCommand.CommandText =  "select name, principal_id from sys.database_principals where name = 'public'"

				

		$oSQLDataReader = $oSQLCommand.ExecuteReader()

		If ($oSQLDataReader.HasRows){

	 		While ($oSQLDataReader.Read()){

				if($oSQLDataReader["principal_id"] -ne 0)

				{

					$AffectedDatabaes += $DB

					$scriptenv.HasIssue = $true

				}

			}

		}

		else

		{

			$AffectedDatabaes += $DB

			$scriptenv.HasIssue = $true

		}

		$oSQLDataReader.Close()		

	

		# Close the connection

		$oConnection.Close()

		

		# return  the brokerenabled status of msdb

		$scriptenv.AffectedDatabaeList = $AffectedDatabaes

	}

}



# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{ 

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

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



	trap [Exception] {

		$scriptenv.RuntimeError = $true

      	continue; 

   	}

	

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.DatabaseNameOutput = ""

	

	# Set parameter values

	Check_Database	

	$scriptoutput.DatabaseNameOutput = Format-XMLData ($scriptenv.AffectedDatabaeList)



	if($scriptenv.RuntimeError -eq $false)

	{

		$scriptoutput.HasIssue = $scriptenv.HasIssue

	}

}

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.DatabaseNameOutput -ne $null)

{

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

}



$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>