Home
  •  

AKN631838

Monitor_AKN631838 (UnitMonitor)

SQL Server process has 3rd Party modules that might cause SQL Server to crash.

Knowledge Base article:

External

http://support.microsoft.com/kb/2033238

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 process has 3rd Party modules that might cause SQL Server to crash.
<Details>
<Content>In this SQL Server instance advisor detected the presence of one or more 3rd party modules within SQL Server process space. These can cause issues within SQL Server process. Refer to the link for more details.</Content>
<CollectedInformation>
<Info>
<Name>Loaded Modules</Name>
<Value>{0}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AKN631838" 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="MonitorMessageffc0cb7a13264d4285c997d2015cd7e9">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN631838.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 "StrModuleListXMLOutput" ""

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

# MAIN CODE SECTION

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



$scriptenv = New-Object System.Management.Automation.PSObject

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

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

$scriptenv | Add-Member NoteProperty "ModuleCountEnv" 0

$scriptenv | Add-Member NoteProperty "moduleNamePathArrays" @()

$scriptenv | Add-Member NoteProperty "ModuleListtXMLEnv" ""



function GetFileVersionInfo($filepath) 

{ 

	[System.Diagnostics.FileVersionInfo]::GetVersionInfo($filepath) 

}



#Private method to create affected databases entity object

function NewAffectedDBColumn($moduleName)

{

	$scriptCollectedInfo = New-Object System.Management.Automation.PSObject

	$scriptCollectedInfo | Add-Member NoteProperty "ModuleNameDC" $moduleName



	$scriptCollectedInfo

}



#Private method to format XML data for UI display

function FormatXMLData([array]$arrayList)

{

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



	if($arrayList)

	{

		$arrayList | ForEach-Object{

			$scriptenv.ModuleListtXMLEnv += ($xmlTemplate -f $_.ModuleNameDC)

		}

	}

}



function GetDBConnectionString()

{

	if($scriptargs.ConnectionString)

	{

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

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

        $ConnStrBulider["Database"] = "master"

        $ConnStrBulider["Trusted_Connection"] = "SSPI"

    

		$connStrBulider.ConnectionString

	}

}



function Check-ModuleFile()

{	

	$DBConnectionString = GetDBConnectionString

	if($DBConnectionString)

	{

		# Create SqlConnection object and define connection string 

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

		$oConnection.ConnectionString = $DBConnectionString

		$oConnection.Open()



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

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

		$oSQLCommand.CommandText = "select name from sys.dm_os_loaded_modules where (company is null or company not like 'Microsoft%Corporation') and upper(name) not like '%ODBC32.DLL'"

		$oSQLCommand.Connection = $oConnection

		

		# Execute Command 

		$reader = $oSQLCommand.ExecuteReader()

		$rowID = 0

		

		$moduleNameArray = @()

		

		while($reader.Read())

		{

			$moduleNameArray += (NewAffectedDBColumn $reader["name"])

			$scriptenv.moduleNamePathArrays += $reader["name"]

			$rowID++

		}

		

		if($rowID -ne 0)

		{

			FormatXMLData $moduleNameArray

			$scriptenv.ModuleCountEnv = $rowID

			$scriptenv.IsDBAffectedEnv = $true

		}



		# Close reader object

		$reader.Close()



		# Close SqlConnection object

		$oConnection.Close()

	}

}





# 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.StrModuleListXMLOutput = ""

	

	Check-ModuleFile

	if($scriptenv.IsDBAffectedEnv)

	{

		if($scriptenv.RuntimeError -eq $false)

		{

			if($scriptenv.ModuleCountEnv -gt 0)

			{

				foreach($moduleNamePathArray in $scriptenv.moduleNamePathArrays)

				{

					if($moduleNamePathArray.ToLower().EndsWith("oraoledbutl11.dll"))

					{

						$file_info = GetFileVersionInfo($moduleNamePathArray)

						if ($file_info -ne $null) 

						{

							if(($file_info.ProductMajorPart -eq 11 -and $file_info.ProductMinorPart -eq 1 -and $file_info.ProductBuildPart -eq 0 -and $file_info.ProductPrivatePart -eq 6) -or`

								($file_info.ProductMajorPart -eq 11 -and $file_info.ProductMinorPart -eq 2 -and $file_info.ProductBuildPart -eq 0 -and $file_info.ProductPrivatePart -eq 1))

							{

								$scriptoutput.HasIssue = $true

							}

						}					

					}

					elseif($moduleNamePathArray.ToLower().EndsWith("oraoledbrst11.dll"))

					{

						$file_info = GetFileVersionInfo($moduleNamePathArray)

						if ($file_info -ne $null) 

						{

							if(($file_info.ProductMajorPart -eq 11 -and $file_info.ProductMinorPart -eq 1 -and $file_info.ProductBuildPart -eq 0 -and $file_info.ProductPrivatePart -eq 6) -or`

								($file_info.ProductMajorPart -eq 11 -and $file_info.ProductMinorPart -eq 2 -and $file_info.ProductBuildPart -eq 0 -and $file_info.ProductPrivatePart -eq 1))

							{

								$scriptoutput.HasIssue = $true

							}

						}							

					}

					elseif($moduleNamePathArray.ToLower().EndsWith("oraoledbrst10.dll"))

					{

						$file_info = GetFileVersionInfo($moduleNamePathArray)

						if ($file_info -ne $null) 

						{

							if(($file_info.ProductMajorPart -eq 11 -and $file_info.ProductMinorPart -eq 1 -and $file_info.ProductBuildPart -eq 0 -and $file_info.ProductPrivatePart -eq 6) -or`

								($file_info.ProductMajorPart -eq 11 -and $file_info.ProductMinorPart -eq 2 -and $file_info.ProductBuildPart -eq 0 -and $file_info.ProductPrivatePart -eq 1))

							{

								$scriptoutput.HasIssue = $true

							}

						}							

					}					

				}

				

			}

			$scriptoutput.StrModuleListXMLOutput = $scriptenv.ModuleListtXMLEnv

		}

	}

}

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

{

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

}



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