SQL Configuration Database Unavailable

Microsoft.ActiveDirectoryFederationServices.2016.FederationServerRemoteSQLServerPingMonitor (UnitMonitor)

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Microsoft.ActiveDirectoryFederationServices.2016.FederationServerRemoteSQLServerPingMonitor" Accessibility="Public" Enabled="true" Target="Microsoft.ActiveDirectoryFederationServices.2016.FederationServer" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.ActiveDirectoryFederationServices.2016.TwoStateScriptMonitorType" ConfirmDelivery="false">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.ActiveDirectoryFederationServices.2016.FederationServerRemoteSQLServerPingMonitor_AlertMessageResourceID">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property["Server"]$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <PowerShellPath>%windir%\system32\windowspowershell\v1.0\powershell.exe</PowerShellPath>

    <ScriptName>FederationServerRemoteSQLServerPing.ps1</ScriptName>

    <ScriptBody><Script>

            $scomapi = new-object -comObject "MOM.ScriptAPI"



            $script:serverReachable = $true

            $script:exceptionMessage = ""

            $script:server = ""



            $stsWMIObject = (Get-WmiObject -Namespace root\ADFS -Class SecurityTokenService)

            $sqlConnectionString = $stsWMIObject.ConfigurationDatabaseConnectionString



            [System.Data.SqlClient.SqlConnectionStringBuilder] $sqlConnectionBuilder = new-object System.Data.SqlClient.SqlConnectionStringBuilder $sqlConnectionString



            $isWID = [StringComparer]::OrdinalIgnoreCase.Equals( $sqlConnectionBuilder.DataSource, 'np:\\.\pipe\Microsoft##WID\tsql\query');



            if ($isWID -eq $false)

            {

            # if sql configuration store, check if remote sql server is pingable

            $ping = new-object System.Net.NetworkInformation.Ping

            &amp;{

            $script:server = $sqlConnectionBuilder.DataSource

            if ( $script:server.StartsWith(".") -or $script:server.StartsWith("(local)") )

            {

            $script:server = "localhost"

            }

            else

            {

            $script:server = $script:server.Split('\')[0].Split(',')[0]

            }

            $scomapi.LogScriptEvent("ActiveDirectoryFederationServices", 1000, 4, "SQL server ping:" + $script:server)

            $reply = $ping.Send( $script:server )

            if ($reply -ne $null)

            {

            if ($reply.Status -ne [System.Net.NetworkInformation.IPStatus]::Success)

            {

            $script:serverReachable = $false

            $script:exceptionMessage = $reply.Status.ToString()

            }

            }

            }

            }

            else

            {

            # if wid configuration store, check if the sql servcie is running

            # TODO: Think of taking the connection string and do a dummy query, instead of just checking status

            $widService = get-service MSSQL'$MICROSOFT##WID'

            $scomapi.LogScriptEvent("ActiveDirectoryFederationServices", 1001, 4, "WID sql service status:" + $widService.Status)

            if ($widService.Status -ne "Running")

            {

            $script:serverReachable = $false

            }

            }

            Trap [System.Exception]

            {

            $script:serverReachable = $false

            $script:exceptionMessage = $_.Exception.Message

            continue

            }



            $scompb = $scomapi.CreatePropertyBag()

            $scompb.AddValue("ServerReachable",  $script:serverReachable )

            $scompb.AddValue("Server",  $script:server )

            $scompb.AddValue("ErrorMessage",  $script:exceptionMessage )

            $scomapi.AddItem($scompb)

            $scomapi.ReturnItems()

          </Script></ScriptBody>

    <IntervalSeconds>300</IntervalSeconds>

    <TimeoutSeconds>180</TimeoutSeconds>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="String">Property[@Name='ServerReachable']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="String">false</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="String">Property[@Name='ServerReachable']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

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

        </ValueExpression>

      </SimpleExpression>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>