Frequently Asked Questions

This contains some questions users have asked while trying to setup or use PATROL for Microsoft SQL Server to monitor Microsoft SQL Server.
If you have a question that does not appear in this, please refer to PATROL for Microsoft SQL Server Documentation for migration, installation, and configuration information, and general information about the features in PATROL for Microsoft SQL Server.
BMC Software, Inc. also offers a customer support website that contains a complete list of fixes, workarounds, and frequently asked questions. However, the latest version of PATROL for Microsoft SQL Server may resolve many if not all of the problems discussed in the customer support website, so we recommend that you upgrade to the latest version, if you have not already done so. To access the customer support website, go to http://www.bmc.com/support and under the Solve a Technical Problem topic, select Search for Fixes, Workarounds & FAQs in our Knowledge Database.

Note

You need a BMC customer user name and password to enter the BMC customer support website. If you do not have one, please contact your BMC sales representative.

List of questions

How do I check whether the Windows user used in the configuration, be used for monitoring?

You can use the following two step procedure to verify whether the Windows user used in the configuration, has the right permissions for monitoring.

Note

If the Windows user is not an integrated security user (there is a MS SQL Login user configured which is not integrated), skip step 1, since the MS SQL KM external process is launched with PATROL default account when there is no use of integrated security.

Step 1:

The Windows integrated security user is used to launch the MS SQL KM external process (SQLCollector.exe). This user should have the right permissions to launch the executable located under the <PATROL_HOME>\mssql\x64 directory.

To verify the permissions, use the ‘Runas’ command line utility with the following format: runas /user:<localmachinename>\administrator cmd

  1. Open the command prompt from the <PATROL_HOME>\mssql\x64 directory.
  2. Type the following:
    Runas /user:<localmachinename>\administrator “SQLCollector.exe –env TestUser”
    For example:

    And click enter.
  3. Enter the password.
     
  4. In case of failure, the following message is displayed:
     
  5. When successful, the KM name with version is displayed in a new window. This indicates that the Windows integrated security user had the right permissions to launch the external process.
     
  6. Close this window, 

Step 2:

After successful completion of Step 1, proceed with Step 2.

Run the MS SQL KM external process in the diagnostic mode to verify the connection with the remote host. If the Windows user is an integrated security user, launch the SQLCollector.exe process with the ‘Runas’ utility.

  1. Open the command prompt from the <PATROL_HOME>\mssql\x64 directory.
  2. Type the following command:
    SQLCollector.exe -diag check_host_conn
    or
    Runas /user:clm-aus-003666\administrator “SQLCollector.exe – diag check_host_conn”
    (if the Windows user is an integrated security user)

     Output window for BMC PATROL for Microsoft SQL server 9.6.11 and before. Click here to expand.

     Output window for BMC PATROL for Microsoft SQL server 9.6.12 and later. Click here to expand.

  3. The connection and the discovery status are displayed:
    For example: Success connection to the remote host using the Windows user, but there are no MS SQL Server instances on this host:

     Output window for BMC PATROL for Microsoft SQL server 9.6.11 and before. Click here to expand.

     

     Output window for BMC PATROL for Microsoft SQL server 9.6.12 and later. Click here to expand.

     

How do I check the MS SQL Server login credentials used in the configuration?

You must log in the monitored MS SQL Server instance using the SQL Server Management studio.

How do I check the host name or IP address used in the configuration?

You can use the following procedure to check the host name or IP address: 

  1. Ping the Host name/IP address from the PATROL Agent machine.
  2. Run the MS SQL KM external process in the diagnostic mode to verify a connection to the remote host. For more information, see How do I check whether the Windows user used in the configuration, be used for monitoring? 

How do I filter the MS SQL Server instances?

You can use any regex validator to verify the regular expressions.


For example: The following MS SQL Server instances are installed on one clm-test-1234 host:
clm-test-1234
clm-test-1234\M1
clm-test-1234\Mirror

For monitoring only:
clm-test-1234
clm-test-1234\M1

enter the following regex in MS SQL server instance name (regex) field:
clm-test-1234|clm-test-1234\\M1

How do I filter the MS SQL Server databases?

You can use the following procedure to filter the MS SQL Server databases:

  1. In the Databases Filtering Options section, set Include in the Filtering mode field.
  2. Set the relevant regex in the Include/Exclude from monitoring (regex) field.
    For example: To monitor only ‘master’ DB, set ‘^master$’ regex.

How do I enable PSL logging?

You can enable PSL logging using any of the following options:

Configuring in BMC PATROL Consoles:
Right-click the MSSQL node > Knowledge Module Commands > Logging PSL > Enable

Configuring in BMC ProactiveNet Central Monitoring Administration console:
In the ‘Global administration settings’ section, select the Logging checkbox.

How do I enable MS SQL KM external process (SQLCollector.exe) logging?

Step 1:

Configuring in BMC PATROL Consoles:

  1. To enable the MS SQL KM external process (SQLCollector.exe) logging, right click the MSSQL node > Knowledge Module Commands > Environment Configuration
  2. Select the relevant Environment to modify or choose to add new Environment.
  3. From the Environment Configuration dialog box check the ‘Logging’ checkbox.
  4. Save the changes

Configuring in BMC ProactiveNet Central Monitoring Administration console:

  1. In ‘KM administration’ section, enable MS SQL KM external process (SQLCollector.exe) by checking the ‘Logging’ checkbox.
  2. Save the changes. 

    Note

    Step 2 is not required for versions 9.6.12 and later.

Step 2:

  1. Open the file ‘NLog.config’ located under <PATROL_HOME>\mssql\ configuration directory.
  2. Change the line:
    <logger name="*" minlevel="Error" writeTo="All"/>
    to:
    <logger name="*" minlevel="Debug" writeTo="All"/>
  3. Save the file

How do I check which .NET framework is installed on the PATROL machine?

To know which version of .NET framework supports the KM, please click System requirements.

To determine which .NET Framework versions are Installed, see the Microsoft documentation

How do I change default collection interval for the environment?

Configuring in BMC PATROL Consoles:

  1. To change default collection interval for the environment, right click the MSSQL node > Knowledge Module Commands > Environment Configuration
  2. Select the relevant Environment to modify or choose to add new Environment.
  3. From the Environment Configuration dialog box change the ‘Data collection interval’ field.
  4. Save the changes.

Configuring in BMC ProactiveNet Central Monitoring Administration console:

  1. In ‘KM administration’ section, change the ‘Data collection interval’ field.
  2. Save the changes

Note

The minimum collection interval is 10 minutes.

What should I check if the KM does not discover the SQL cluster?

Check the following if the KM is unable to discover your SQL cluster:

  1. The host is unreachable from the server running PATROL Agent (ping command)
  2. User credentials might be wrong -  use SQLCollector diagnostic tool  
  3. Make sure use are able to connect to the SQL cluster using MS SQL Management Studio

How should I troubleshoot WMI errors if the connection to a remote machine fails?

DCOM permission

Follow the given steps to verify the DCOM permission:

  1. Click Start > Run > enter Dcomcnfg
  2. In the Component Services window, in the left navigation tree, expand Component Services > Computers > My Computer
  3. Right click My Computer and select Properties
  4. Select the COM Security Tab
  5. In the Access Permissions dialog box, click Edit Limits, ensure that the Everyone user group has Local Access and Remote Access permission. Click Ok after verifying the permissions.
  6. In the Launch and Activation Permissions dialog box, click Edit Limits, ensure that the Everyone user group has Local Activation and Local Launch permission. Click Ok after verifying the permissions.

Permission for the user to the WMI namespace

  1. Click Start > Run > enter WMImgmt.msc
  2. In the WMImgmt window, in the left navigation tree, right-click WMI Control (Local) and select Properties
  3. In the WMI Control (Local) Properties dialog box, select the Security tab.
  4. Select Root and click Security button.
  5. Ensure that authenticated users have Execute MethodsProvider Right, and Enable Account rights.
  6. Ensure that the Administrators have all the permissions.

Verify WMI Impersonation Rights

  1. Click Start > Run > enter gpedit.msc, click OK.
  2. In the Local Group Policy Editor window, in the left navigation tree, under Local Computer Policy, expand Computer Configuration and then expand Windows Settings.
  3. Expand Security Settings, expand Local Policies, click User Rights Assignment.
  4. Verify that the SERVICE account is specifically granted Impersonate a client after authentication rights. 

Which diagnostic methods are supported by Microsoft SQL Server 9.6?

Discovery

This diagnostic simulate creates/updates/removes of SQL instance under host. To start discovery diagnostic run the next command: SQL_Collector.exe diag discovery

Host connection

This diagnostic simulate connection to local/remote host and if credentials are ok to gather required info from host. To start host connection diagnostic run the next command: SQL_Collector.exe diag host_connection

Machine information

This diagnostic simulate connection to local/remote host and asking for info from connected host. To start Machine info diagnostic run the next command: SQL_Collector.exe diag machine_info

How to troubleshoot the following error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication?

This is an environmental issue. It can be resolved at domain/Network/Active Directory level. Use the following workarounds to resolve it:

  • Specify Hostname field as 127.0.0.1 or localhost instead of regular IP address.
  • Disable Loop Back Check feature in the registry:
    • Edit the registry using regedit. (Start > run > Regedit)
    • Browse: HKLM\System\CurrentControlSet\Control\LSA and add a DWORD value called DisableLoopbackCheck to it.
    • Set the value to 1
    • Restart the PATROL Agent

   By doing the above mentioned registry changes, you can able to set Hostname field with regular IP Address. 

Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Kishan Kondhekar

    How do I filter the MS SQL Server databases?

    Kindly provide us regex pattern to include multiple Databases.

    Aug 09, 2019 04:33
  2. Liron Asos

    E.g. Regex pattern to include only 'master' and 'msdb': (^master$|^msdb$)

    Aug 12, 2019 03:36
  3. Anthony Valuikas

    How to troubleshoot OpenSubKey Success to: SOFTWARE\Microsoft then OpenSubKey Failed for path [SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL] permissions on the key are correct and the data is in there..

    Oct 29, 2019 12:30
    1. Liron Asos

      "OpenSubKey Success" is success scenario. Nothing to troubleshoot.

      On failed scenario you will see "OpenSubKey Failed".

      Oct 31, 2019 06:31