Validation of MS SQL Server login


This topic provides instructions about how to validate a Standard and an Integrated user on Microsoft SQL KM version 9.6.12 and 9.6.11. 

Validation of MS SQL server login for BMC PATROL for Microsoft SQL Server version 9.6.12 and later. Click here to expand.

The MS SQL Server login can be either standard or integrated user.

Important

If the MS SQL Server login field is left blank, then the Windows user will serve to connect the MS SQL Server. In this case Windows user must pass all validation steps as described in Validation for integrated user section.

Validation for standard user

  1. Open the command prompt from the <PATROL_HOME>\mssql\x64 directory.
  2. Type the following command:
    SQLCollector.exe -diag check_sql_conn
  3. Enter the Microsoft SQL server name and port, press enter for default port (1433).

    L_B5BE.tmp.PNG

  4. Enter 'i' for integrated login, enter 's' for standard login:

    image2016-9-7 20:12:16.png
    1. Standard Login
      Enter user name and password:

      image2016-9-7 20:16:22.png
      1. Success scenario output

        L_34CB.tmp.PNG

      2. Failed scenario output
        L_7824.tmp.PNG 
    2. Integrated Login
      When using integrated mode the current logged in user will be used for the test.
      1. Success scenario output
        L_85BA.tmp.PNG
      2. Failed scenario output
        L_9363.tmp.PNG
  1. To execute another query enter ‘y’, enter ‘n’ to continue.
  2. To run another test type ‘y’, type ‘n’ to quit.

 

Note

To use another integrated login, run SQLCollector.exe using the RUNAS command

Runas /user:<domain_name>\<user_name> "SQLCollector.exe -diag check_sql_conn"

For example, to test the integrated user BMC-6YS58W1\patrol do the following:

  1. Runas /user: BMC-6YS58W1\patrol "SQLCollector.exe -diag check_sql_conn"
    L_9A0C.tmp.PNG

  2. Another CMD window will pop-up as BMC-6YS58W1\patrol user. Select integrated mode.
    L_E50.tmp.PNG 
Validation of MS SQL server login for BMC PATROL for Microsoft SQL Server version 9.6.11 and before. Click here to expand.

The MS SQL Server login can be either standard or integrated user.

Important

If the MS SQL Server login field is left blank, then the Windows user will serve to connect the MS SQL Server. In this case Windows user must pass all validation steps as described in Validation for integrated user section.

Validation for standard user

  1. Open the command prompt from the <PATROL_HOME>\mssql\x64 directory.
  2. Type the following command:
    SQLCollector.exe -diag check_sql_conn
    SQL_3.png
  3. Enter the properties of the SQL Server instance that you want to check.
  4. The SQL Server connection status and query execution status displayed.
    1. Success scenario output
      SQL_4.png

      In the above example, 

      Connection to SQL instance vw-tlv-ngp-dv13\A2 is open indicates that the connection is established successfully.Query execution success indicates that the entered query is executed successfully.
    2. Failed scenario output
      SQL_5.png

Validation for integrated user 

  1. Open the command prompt from the <PATROL_HOME>\mssql\x64 directory
  2. Type the following command
    Runas /user:<domain_name>\<user_name> "SQLCollector.exe -diag check_sql_conn"

    SQL_6.png 
  3. Enter the password
    SQL_7.png
  4. The following message is displayed in case of a failure:
    SQL_8.png
    This message indicates that PATROL will fail to start external collector to collect data from SQL Server instance with this integrated user. Reslove this issue, before using integrated user for monitoring.
  5. A window with the RUNAS user opens in case of a successful login
    SQL_9_new.png

    In the above example, a window with the BMC-6YS58W1 (RUNAS user) can be seen.
  6. Enter the properties of the SQL Server instance that you want to check.

    Important

    Use the same username and password combination that was used for the RUNAS command.

  7. The SQL Server connection status and query execution status displayed.
    1. Success scenario output
      SQL_11.png
      In the above example, 
      Connection to SQL instance BMC-6YS58W1 is open
       indicates that the connection is established successfully.
      Query execution success indicates that the entered query is executed successfully.
    2. Failed scenario output
      SQL_12.png

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*