Troubleshooting extended or database discovery failures

When you observe extended or database discovery failures in BMC Discovery, use the troubleshooting steps described in this section to either resolve the problem or create a BMC Support case.

The first step to troubleshoot deep database discovery error is to check the Additional Discovery methods under the Discovery Access page for the host. The method sqlQuery shows the queries BMC Discovery tried to execute on the host. These queries come from the database detail pattern. For more information, see Extended discovery.  

If there are no additional discovery methods for SQL, it is likely the database server software instance was not discovered. As a result, the database detail pattern was not triggered. Or, there is no deep database discovery for the database type yet. If the database SI is not discovered, it is likely there was no database server process running at the time of discovery.

Clicking on the script failures link for sqlQuery displays the error message, such as, unable to connect to the database.

There are several ways to troubleshoot a connection issue.

Case 1

Be sure that the login is the current credential for the database server. To do this, go to the Manage > Credentials page in the UI and click Action > Test to test the access.

The most common connection issue is a bad login credential. When login access is denied, the test results may show any of the following error messages:.  

• connection to oracle failed with error java.sql.SQLException: ORA-01017: invalid username/password; logon denied
• connection to sqlserver failed with error com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'sa'. ClientConnectionId:414958ed-7f05-40f4-a2a2-cb4a2cb6db6d
• connection to mysql failed with error java.sql.SQLException: Access denied for user 'mysqladmin'@mysql.bmc.com' (using password: YES) 

Solution: Get updated credentials from your DBA to resolve this issue.

Case 2

Remote access may fail if the database server port is blocked or remote access is disabled. When testing the database credentials, you may get an error, as listed below. Failure messages differ between database types.

• Connection to oracle failed with error java.sqlSQLRecoverableException: IO Error: The Network Adapter could not establish the connection
• Connection to sqlserver failed with error com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 10.10.5.30, port 1433 has failed .  Error: “Connection refused (Connection refused).
  Verify the connection properties. Make sure that an instance of SQL server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall. 

Solution: Confirm that the database port is open from the appliance/instance or Outpost. You can test this using the following Nmap command:

 i.   Nmap -p <PORT_NUMBER>  < IP_ADDRESS>     
ii.   “C:\Program Files\BMC Software\Discovery Outpost\nmap\nmap” -p <PORT_NUMBER>  < IP_ADDRESS>

This example verifies that MS SQL port 1433 is open from the appliance to the server. An open state should be returned:

Case 3

The database credentials and remote database access can also be tested using various methods. This helps eliminate BMC Discovery as the source of the issue. 

Solution: This can be verified by accessing the database locally from the server but should also be verified using a remote connection as remote access can be restricted by the database administrator. 

1. MSSQL:

   1. To test the access, use  Microsoft Management Studio  or the sqlcmd utility.

      sqlcmd -S <SERVER_NAME>\<INSTANCE_NAME> -U <USERNAME> -P <PASSWORD>

   2. Remote access can also be tested using a .udl file. Create a file on your workstation called sql_connection.udl. This creates a Microsoft Data Link file. Double click the file and enter the fields under the Connection tab to test the connection. 
2. MySQL:

   1. Test the local access using  the mysql command:

      mysql -h <HOSTNAME> -u username -p <DATABASE_NAME>

   2. MySQL Workbench  can also be used to connect to the database.
3. Oracle:

   1. Local access can be achieved using sqlplus:

      sqlplus <USERNAME>/<PASSWORD>@<SERVER_NAME> : <PORT>/ <SID>

   2. Oracle SQL Developer  can be used for remote or local access. Other methods are outlined in this topic .
4. Sybase: 

   • The Interactive SQL tool can be used to access the Sybase or using isql:

     isql -SServiceName -Uuserid -Ppassword

5. PostgreSQL:

   1. Psql can be used locally on the server to test credentials:

      psql -d <DATABASE_NAME>  -U  <USERNAME>  -W 

   2. The  pgAdmin  tool can also be used locally or from a remote device. 

Case 4

If remote access is confirmed to be working, the database drivers may also be a failure point for deep database discovery. The following are examples of error messages for incorrect SQL driver settings:

• connection to sqlserver failed with error cannot find the jdbc driver class ‘com.microsoft.sqlserver.jdbc.SQLServerDriver’
• connection to oracle failed with error cannot find the jdbc driver class ‘oracle.jdbc.driver.OracleDriver’
• connection to mysql failed with error java.sql.SQLNonTransientConnectionException: CLIENT_PLUGIN_AUTH is required 

Solution: For information on download links to the database JDBC drivers, see Managing database drivers. The JDBC driver must be compatible with OpenJDK 8 and with the version of the database server it is connecting to.

Case 5

When viewing the discovery access for the sqlQuery method, there may be situations where you have successful results along with NoAccessMethod failures as shown in the following image:

Solution: Clicking on the 13 SQL Query results in the above example, you see that three of them have the failure reason for NoAccessMethod. 

This means that the credential used did not have permission to run the requested query. You can manually execute these queries to verify access using the local or remote verification methods listed earlier. A database administrator must correct the privileges assigned to the database credential being used.  

You can view the exact queries that get executed in the Database Detail Pattern for the database type. You can find this in the Discovery UI by searching for the database type or searching under Manage > Knowledge. You can also find the queries in the Discovery content reference (formerly called Configipedia). You can find a list of these patterns in  Extended discovery .

You can update these queries along with other commands that get executed using patterns through the monthly TKU release. With each TKU release, an updated list of commands is released. You can download this Command/File Matrix from the Monthly TKU release notes page as shown in this topic . Share this spreadsheet file with your database administrator to assure that they provide a database account with the proper privileges.

Case 6

What should successful deep database discovery look like?

Solution: You should see a list of databases for the database server software instance. 

Additional common issues

1. MSSQL discovery fails due to a bad login. The credential configured is a working Windows AD account that has access to Microsoft SQL Server.

   • This is because the MSSQL driver is not configured for Windows authentication. It uses SQL authentication by default. You must add the following code to the JDBC connection: ;authenticationScheme=NTLM;integratedSecurity=true. The entire code is as shown here:

     jdbc:sqlserver://;serverName={address};port={port};databaseName={name};authenticationScheme=NTLM;integratedSecurity=true

     The MSSQL credential will not need the domain name in the username.

2. Sybase connection requires an encrypted connection.
   • This can be enabled in the Sybase driver. In the data parameter add ENCRYPT_PASSWORD=true. 
3. An Oracle scan does not find PDB databases.
   • Pluggable database listings (PDB) are pulled using one of these methods.
     Pattern extracts a list of pluggable databases using methods:
     sql query: SELECT NAME, PDB FROM V$SERVICES ORDER BY PDB, NAME
<ora_base>/oradata/<db_unique_name> directory listing. If the oradata directory is located in ASM storage, then this method fails.
4. What database queries are used for DB2 deep database discovery?

   • No database queries are executed. Instead, the following commands for Windows and UNIX are executed using the credential used to discover the host. Otherwise, the DB2 database credential is used:
     
     

     'set DB2INSTANCE=<DB2_INSTANCE> && "<DB2_INSTALL_ROOT>\\BIN\\db2cmd" -i -w -c db2 list database directory'
     
     '(. <DB2_INSTALL_ROOT>/sqllib/db2profile && db2 list database directory)'