Share via

SQL Database discovery is failing for SQL Instances.

Manish Kumar 1 Reputation point
2021-12-13T10:15:59.867+00:00

Hi,
With SQL Server Mompack (version 7.3.32.0) I am getting following error during database discovery for an instance of “MSSQL on Windows: DB Engine”:

Log Name: Operations Manager
Source: SQL Server Discovery MP Windows
Date: 12/13/2021 1:23:42 AM
Event ID: 4221
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: dl380g9-48v01.vcswin.com
Description:
Management Group: "SCOMMgMT2"
Module: Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery
Version: 7.0.32.0

Error(s) was(were) occurred:
Message: An error occurred during discovery.

---------- Exception: ----------
Exception Type: Microsoft.SQLServer.Module.Helper.Sql.SmartConnectException
Message: Cannot connect to the target Sql Server instance.
Connection log:
Failed to connect to data source 'VCSSQL4.VCSWIN.COM': Connection target check failed: connected to VCSWIN08-V18\MSSQLSERVER, but got VCSSQL4\MSSQLSERVER.

Failed to connect to data source 'VCSSQL4.VCSWIN.COM,1433': Connection target check failed: connected to VCSWIN08-V18\MSSQLSERVER, but got VCSSQL4\MSSQLSERVER.

Source: Microsoft.SQLServer.Module4.Helper
Stack Trace:
at Microsoft.SQLServer.Module.Helper.Sql.SqlConnectivityHelper.SmartConnect(String connectionDataSource, String databaseName, String computerName, String instanceName, SqlCredential sqlCredential, ApplicationIntent intent, String applicationName, String inputWmiPath, Int32 timeout, Boolean useSqlErrorStopList, IEnumerable1 stopSqlErrorCodes, Boolean stopOnNetworkRelatedExceptions, Nullable1 instanceVersionMajor, Boolean instanceIsLocal, String debugUser, String debugPassword)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<GetNewOpenedConnectionInternalAsync>d__41.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<InitConnectionAsync>d__42.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandDataReaderInternalAsync>d__40.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandDataReaderAsync>d__35.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery.<FillListsOfClassesAndRelationsAsync>d__19.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetDiscoveryDataAsyncStatic>d__6.MoveNext()

State:
The configuration properties are:
ManagementGroupName = SCOMMgMT2
Publisher = SQLDiscoveryWindows
ConnectionString = VCSSQL4.VCSWIN.COM
InstanceEdition = Enterprise Edition
InstanceName = MSSQLSERVER
InstanceVersion = 13.0.1601.5
MachineName = VCSSQL4.VCSWIN.COM
MonitoringType = Local
NetbiosComputerName = VCSSQL4
Login =
ConnectionString = VCSSQL4.VCSWIN.COM
DatabaseClassId = 722594ba-bc11-45d5-81a2-a6059ada4682
DatabaseReferencesUserResourcePoolClassId = 505d3154-32c8-4242-48e1-c0531ce8dcae
DefaultPoolClassId = 0fca59ad-48dc-6118-5665-3fdddc183bbc
DiscoverySourceManagedEntityId = 2f26461b-1d82-6741-cd67-a3dc2f9a42c9
DiscoverySourceObjectId = f8651819-6311-a818-3e20-86dc7fe0817f
ExcludeList =
InstanceName = MSSQLSERVER
LocalDatabaseClassId = 0122e31e-8872-ec01-8993-63a4b2eb3d01
MachineName = VCSSQL4.VCSWIN.COM
SqlTimeoutSeconds = 15
TimeoutSeconds = 300
UserDefinedResourcePoolClassId = c5d88380-0c94-93ce-5379-885862735019
Password = ********

Error(s):
An error occurred during discovery.

The error says that “Connection target check failed: connected to VCSWIN08-V18\MSSQLSERVER, but got VCSSQL4\MSSQLSERVER.”
The DB engine object on which the discovery is running is having Connection String / NetBIOSComputer name as VCSSQL4. However still the discovery is somehow finding VCSWIN08-V18.

The detailed list of properties of the instance can be shown as follows:

PS C:\> New-SCOMManagementGroupConnection -ComputerName "localhost"
PS C:\> $mg = Get-SCOMManagementGroup
PS C:\> $Class = Get-SCOMClass -DisplayName "MSSQL on Windows: DB Engine"
PS C:\> $ClassInstance = Get-SCOMClassInstance -Class $Class | where { $_.DisplayName -eq "VCSSQL4\MSSQLSERVER"}
PS C:\> $ClassInstance.Values

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : MonitoringType
Value : Local
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : PrincipalName
Value : VCSSQL4.VCSWIN.COM
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : AgentClusterName
Value :
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : AuthenticationMode
Value : Windows Authentication Mode
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : FullTextSearchServiceName
Value : MSSQLFDLauncher
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : FullTextSearchServiceClusterName
Value :
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : MasterDatabaseLocation
Value : C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : MasterDatabaseLogLocation
Value : C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : ErrorLogLocation
Value : C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : ServicePackVersion
Value : 0
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : AuditLevel
Value : Failure
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : InstallPath
Value : C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : ToolsPath
Value : C:\Program Files\Microsoft SQL Server\130\Tools
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : EnableErrorReporting
Value : True
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : Account
Value : VCSWIN\Administrator
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : InstanceID
Value : MSSQL13.MSSQLSERVER
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : TcpPorts
Value : 1433
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : MachineName
Value : VCSSQL4.VCSWIN.COM
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : InstanceName
Value : MSSQLSERVER
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : ConnectionString
Value : VCSSQL4.VCSWIN.COM
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : NetbiosComputerName
Value : VCSSQL4
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : NetbiosDomainName
Value : VCSWIN
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : Edition
Value : Enterprise Edition
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : Language
Value : 1033
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : Version
Value : 13.0.1601.5
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : ServiceName
Value : MSSQLSERVER
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : ServiceClusterName
Value :
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : Cluster
Value : False
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : PerformanceCounterObject
Value : SQLSERVER
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : AgentName
Value : SQLSERVERAGENT
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : Type
Value : DB Engine
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

PropertyAccessRights : Unknown
Parent : VCSSQL4\MSSQLSERVER
Type : DisplayName
Value : VCSSQL4\MSSQLSERVER
Id : 00000000-0000-0000-0000-000000000000
ManagementGroup : SCOMMgMT2
ManagementGroupId : e91564ff-34c2-9192-d25e-276ebdcf0b08

If the target computer name for this object is mentioned as VCSSQL4 then why is it connecting to VCSWIN08-V18? Due to this the database discovery is failing for SQL Instances under VCS cluster.

System Center Operations Manager
System Center Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,625 questions
SQL Server | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andrew Blumhardt 10,051 Reputation points Microsoft Employee
    2021-12-14T04:04:33.183+00:00

    This is a common issue when monitoring SQL with SCOM; most commonly due to insufficient database access. The following articles may help. You may find that some of these errors are hard to eliminate completely. After the database engine and primary database are properly discovered you may find some lingering errors for more obscure components. Tuning away some of these errors may be necessary.

    https://learn.microsoft.com/en-us/troubleshoot/system-center/scom/troubleshoot-sql-database-discovery
    https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/
    https://kevinholman.com/2010/03/09/basic-troubleshooting-of-scom-discovery-scripts/

    1 person found this answer helpful.
  2. Fursel 341 Reputation points
    2021-12-21T19:22:14.96+00:00

    Check if in the registry all is correct regarding name of the SQL DB engine and ServerName

    Also I would just search in registry in general for this wrong server name VCSWIN08-V18

    MSSQL on Windows: Discover Installation Source (seed)

    <Discovery ID="Microsoft.SQLServer.Windows.Discovery.LocalDiscoverySeed" Enabled="true" Target="Windows!Microsoft.Windows.Server.Computer" ConfirmDelivery="false" Remotable="true" Priority="Normal">
    <Category>Discovery</Category>
    <DiscoveryTypes>
    <DiscoveryClass TypeID="Microsoft.SQLServer.Windows.LocalDiscoverySeed"/>
    </DiscoveryTypes>
    <DataSource ID="DS" TypeID="Windows!Microsoft.Windows.FilteredRegistryDiscoveryProvider">
    <ComputerName>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</ComputerName>
    <RegistryAttributeDefinitions>
    <RegistryAttributeDefinition>
    <AttributeName>AppExists</AttributeName>
    <Path>SOFTWARE\Microsoft\Microsoft SQL Server\140\Machines</Path>
    <PathType>0</PathType>
    <AttributeType>0</AttributeType>
    </RegistryAttributeDefinition>
    </RegistryAttributeDefinitions>
    <Frequency>14400</Frequency>
    <ClassId>$MPElement[Name="Microsoft.SQLServer.Windows.LocalDiscoverySeed"]$</ClassId>
    <InstanceSettings>
    <Settings>
    <Setting>
    <Name>$MPElement[Name="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Name>
    <Value>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Value>
    </Setting>
    <Setting>
    <Name>$MPElement[Name="System!System.Entity"]/DisplayName$</Name>
    <Value>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Value>
    </Setting>
    </Settings>
    </InstanceSettings>
    <Expression>
    <SimpleExpression>
    <ValueExpression>
    <XPathQuery Type="String">Values/AppExists</XPathQuery>
    </ValueExpression>
    <Operator>Equal</Operator>
    <ValueExpression>
    <Value Type="String">true</Value>
    </ValueExpression>
    </SimpleExpression>
    </Expression>
    </DataSource>
    </Discovery>

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.