SQL Databases Properties are not Discovered by SQL Server Management Pack

I had this issue recently in two separate environments where properties for some SQL databases were not discovered by SQL Server Management Pack as you can see below:

image

Also collecting performance data for free space of the databases was returning zero (0):

image

Meanwhile in the Operations Manager log on the SQL servers I was only seeing this error:

image

Management Group: <MGMTGROUPNAME>.Script: DiscoverSQL2012FileGroups.js : Script ‘DiscoverSQL2012FileGroups.js’ failed.
Inner exception:
Error Number      :
Error Code        : 0
Win32 Facility    : 0
Error Description :
Call stack:Exception.constructor(Script ‘DiscoverSQL2012FileGroups.js’ failed.,Can’t close connection.
Inner exception:
Error Number      : -2146824584
Error Code        : 3704
Win32 Facility    : 10
Error Description : Operation is not allowed when the object is closed.
Call stack:Exception.constructor(Can’t close connection.,Operation is not allowed when the object is closed.
Error Number      : -2146824584
Error Code        : 3704
Win32 Facility    : 10
Error Description : Operation is not allowed when the object is closed.
), ADODB.Close),
Main({D6B26EFE-E183-24E9-DD23-F165CB716A28},{ADFFD930-BDE9-66A1-20F3-CB6FBA80CBC9},<SERVER FQDN>,<SERVER NETBIOS NAME>,MSSQLSERVER),
),
Main({D6B26EFE-E183-24E9-DD23-F165CB716A28},{ADFFD930-BDE9-66A1-20F3-CB6FBA80CBC9},<SERVER FQDN>,<SERVER NETBIOS NAME>,MSSQLSERVER),

So this was my issue but I couldn’t find where exactly was the problem. Some of the properties were discovered and there was even databases that all properties were discovered but performance data for free space was not working on all databases. This lead me think it was some kind of permissions issue but I’ve made the SCOM Action Account “SA” on all SQL servers.

How I fixed it:

1. I’ve created Run As account in SCOM.

2. Named it SQL with type Windows.

3. For credentials I’ve entered the SCOM Action Account.

4. Secured the account only to the SQL servers in my environment.

5. Added this account to these three profiles: SQL Server Default Action Account, SQL Server Discovery Account and SQL Server Monitoring Account.

After these steps all was working as it should be:

image

image

So it seems you really need to create Run As account and add it to the SQL Profiles even though the SQL Server Management Pack guide says otherwise:

By default, all discoveries, monitors, and tasks defined in the SQL Server management packs default to using the accounts defined in the “Default Action Account” Run As profile. If the default action account for a given system does not have the necessary permissions to discover or monitor the instance of SQL Server, then those systems can be bound to more specific credentials in the SQL Server Run As profiles, which do have access.

 

Keep in mind that I haven’t changed any permissions to my SCOM Action Account. This account had all SQL permissions all the time.

My Configuration was:

  • Windows Server 2012 with latest updates;
  • SCOM 2012 SP1 UR2
  • SQL Server Management Pack 6.3.173.1
  • SQL Server 2012 SP1 CU3

Move the SCSM Data Warehouse (DW) Databases

If you are facing the task to move SCSM Data Warehouse Databases from one server to other server now you can find documentation on this here. Soon this document will be formatted and published in Technet Library.

SQL Server Collation Requirements for System Center 2012 Components

This is very important article for anyone who is planning installation of all System Center 2012 components and especially for SCOM and SCSM. Read the full article here.

System Center 2012–using a common SQL backend database

Another great article from Cameron Fuller about putting all System Center DB’s on one SQL Backend. Such scenario is possible but a lot of considerations have to be taken before implementing it. Read here.

KB: How to troubleshoot Event ID 2115 in Operations Manager

There are several causes behind this error. Read how to identify and fix them here.