8/18/2020 10:10 PM

9/29/2021 8:21 PM

In DPA 2020.2 and earlier versions, registering a Sybase database instance for monitoring required that you provide the credentials of a privileged user with the sa_role. During registration, the privileged user either creates the DPA monitoring user or grants the required privileges to an existing user that you designate as the DPA monitoring user.

With DPA 2020.2.1 and later, you can register a self-managed Sybase instance without a privileged user. (For earlier DPA versions, see Sybase permissions for DPA monitoring to learn how to remove the sa_role from the DPA monitoring user after registration.)

In DPA 2020.2.1 through 2021.1.x, you can use mass registration or the DPA management API to register a SQL Server database instance without a privileged user. (If you register a database instance through the UI wizard, a privileged user is required.) See the following sections for instructions. 

In DPA 2021.3 and later, you can use mass registration, the API, or a UI wizard to register a SQL Server database without a privileged user:

• To use a wizard, see Register a Sybase database instance in the DPA Administrator Guide.
• To use mass registration or the API, see the following sections.

Database Performance Analyzer

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

How to register a Sybase database instance without a privileged user

Note: Do not specify the sa user as the monitoring user.

Task 1: Create the monitoring user with the necessary permissions

1. Copy one of the following scripts to a file. These scripts create the monitoring user.

   • For a Sybase 15.7 or later instance: CreateMonUserSybase15-7orLater.sql

   • For a Sybase instance earlier than 15.7: CreateMonUserSybaseBefore15-7.sql

2. Edit the script to update the user name and password values.

3. Copy the following script to a file: ConfigureDbInstanceSybase.sql.

   This script configures the database instance. The same script is used for all versions. For more information, see Database configuration below.

4. Connect to the Sybase database instance and the first script and then the second script. To ensure that the connected user has all the privileges needed to create the monitoring user, SolarWinds recommends connecting as a user with the sa_role to run the script.

Task 2: Register the database instance without specifying a privileged user

Use mass registration or the DPA API to register the database instance, and omit the privileged user in the registration details. 

• The following example is a segment of a JSON script that calls the DPA API to register a Sybase database instance without specifying a privileged user. Change the values to reflect your monitored database and the credentials of the monitoring user created previously.

  {
    "databaseType": "SYBASE",
    "serverName": "127.0.0.1",
    "port": 5000,
    "monitoringUser": "dpa_mon_user",
    "monitoringUserPassword": "Pa$$word1",
    "displayName": "DatabaseName"
  }

• To use mass registration to register a Sybase database instance, choose the spreadsheet for multiple database types, as shown in the following example. When you complete the spreadsheet:

  • Leave the Privileged User and Privileged User Password columns empty.
  • In the Create Monitoring User (Y/N) column, enter N.
  • In the Monitoring User and Monitoring User Password columns, enter the credentials of the monitoring user created in the previous step.

  

Database configuration

The database configuration script (run in Task 1 above) configures the following parameters on the database instance. These parameters are required for DPA monitoring to work properly.

Parameter	Mandatory	Expected value	Effect
enable monitoring	Yes	1	Required for monitoring to work.
enable cis	Yes	1	Required for monitoring to work.
statement statistics active	Yes	1	Required for monitoring to work
wait event timing	No	1	Required to collect wait event statistics.
SQL batch capture	No	1	Required to collect SQL texts.
max SQL text monitored	No	>=4000	Affects the length of SQL text available in DPA.
per object statistics active	No	1	Affects SQL statistics available in DPA.
statement pipe active	No	1
statement pipe max messages	No	>=10000

Monitoring user validation

During the registration, DPA validates that the monitoring user has the required permissions. By default, the registration fails if any mandatory permission is missing and displays a warning if optional permissions are missing. The same validations are also done when database connection details are updated. Additionally, the sa user is not allowed to be the monitoring user.

This behavior can be overridden by setting the advanced option MONITOR_VALIDATION_OVERRIDE to TRUE. This allows the registration to pass regardless of which permissions are missing. All permissions are treated as optional and DPA displays a warning.

Impact of specific permissions on DPA monitoring

DPA validates that the monitoring login and user has the following permissions.

User roles

The monitoring user must be member of ‘mon_role’, which grants access to mon tables. Without this role, DPA monitoring will not work. Optionally, ‘sa_role’ can be granted as well. This has the same effect as the granular permissions below, but it is also applicable on Sybase versions earlier than 15.7. 

Granular permissions

The following permissions can be granted only if the ‘enable granular permissions’ parameter is enabled on the database instance. These permissions are optional and are valid only for Sybase 15.7 or later.

Permission	Effect
kill any process	Required to kill sessions.
use any database	Required to access all databases for: Polling of procedure names Alternatively, procedure names collection can be changed by setting the advanced option SYBASE_SUPPORT_LONG_PROC_NAME value to lockedOff. With this setting, DPA does not require the use any database permission for procedure names polling. However, the collected values are limited to 30 characters. Longer names are truncated. The Database Freespace alert and the Transaction Log Freespace alert

User table permissions

The monitoring user requires SELECT permission on the following tables. Usually those are accessible to the user if that user is a member of ‘mon_role’. However, they can be explicitly revoked. Therefore, DPA checks those permissions.

Table	Mandatory	Effect
monProcess	Yes	Required for DPA monitoring.
monProcessSQLText	Yes	Required for DPA monitoring.
monProcessLookup	Yes	Required for DPA monitoring.
monProcessProcedures	No	Required for quick poll and real time sessions.
monWaitEventInfo	No	Required for real time sessions and the 'Active sessions' metric.
monEngine	No	Required for the 'CPU utilization by database' metric.
monDataCache	No	Required for the 'Buffer cache hit ratio' metric.
monProcedureCache	No	Required for the 'Procedure cache hit ratio' metric.
monDeviceIO	No	Required for physical and disk metrics.
monNetworkIO	No	Required for Network bytes sent/received metrics.
master..monSysStatement	No	Required for Stats poll.
master..sysprocesses	No	Required for real time sessions and the 'Distinct machines total' metric.
master..sysconfigures	No	Required for the 'CPU count' metric.