8/18/2020 10:11 PM

12/10/2021 1:22 AM

In DPA 2020.2 and earlier versions, registering a SQL Server database instance for monitoring required that you provide the credentials of a privileged user. 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 an Amazon RDS SQL Server instance without a privileged user. (For earlier DPA versions, see SQL Server permissions for DPA monitoring to learn how to remove the privileged user 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 an Amazon RDS for SQL Server 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 an Amazon RDS SQL Server database instance without a privileged user

Step 1: Create the monitoring user with the necessary permissions

To avoid entering the credentials of a privileged user, you must create the monitoring user yourself. Complete the following steps to run a script that creates the monitoring user and assigns all required the permissions. The majority of the permissions are optional. DPA can monitor without them, but the monitoring will be limited. Limitations are described in the following sections.

To create a monitoring user that does not have a privileged role:

1. Copy the following script to a file: CreateMonUserAmazonSqlServer.sql

   Note: This script is valid only if the default SQL Server permissions for system roles such as [Public] have not been altered with items revoked. If default system roles have been altered, DPA Support cannot help you find all items that are assumed to be allowed.

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

3. Connect to the Amazon RDS SQL Server instance and run the script. To ensure that the connected user has all the privileges needed to create the monitoring user. SolarWinds recommends connecting as master user name (a member of the processadmin, public, and setupadmin role) to run the script.

    

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

If you use DPA 2020.2.1 through 2021.1.x, use mass registration or the DPA management API as described below.

If you use DPA 2021.3 and later, you can use mass registration or the API as described below. Or you can use a wizard. To use a wizard, see Register an Amazon RDS for SQL Server database instance in the DPA Administrator Guide.

When you use mass registration or the DPA API to register the database instance, 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 an Amazon RDS SQL Server database instance without specifying a privileged user. Change the values to reflect your monitored database and the credentials of the monitoring user created in the previous step.

  {
    "databaseType": "SQLSERVER",
    "serverName": "127.0.0.1",
    "port": 1433,
    "monitoringUser": "dpa_mon_user",
    "monitoringUserPassword": "Pa$$word1",
    "amazonRds": true,
    "displayName": "DatabaseName"
  }

• If you register the instance through mass registration, use the spreadsheet for multiple database types as shown in the following shows an example.

  • 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.
  • In the Amazon RDS (Y/N) column, enter Y.

  

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.

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. Unless otherwise stated, the permissions are optional. (If optional permissions are missing, basic monitoring is possible but specific data or functionality is not available.)

Permissions granted to login

Permission	Effect on DPA monitoring
VIEW SERVER STATE (mandatory)	Required to access the Dynamic Management Views used for polling. Without it, DPA cannot monitor the instance.
VIEW ANY DATABASE	Required to see any database. Without it, DPA can monitor only master, tempdb, and any databases that the DPA monitoring user owns.
VIEW ANY DEFINITION	Required to access the definitions of tables, indexes, and other database objects. Without it, Storage I/O data and Table Tuning Advisor current definitions are not available.

Role membership

Role	Description
processadmin	Required to kill sessions. Without it, the Kill Session option in the Real Time Sessions view does not work.
db_datareader	Required for the monitoring user on each database. Without it: Table Tuning Advisors cannot include current data, table size, and table churn. ‘Database Freespace’ and ‘Transaction Log Freespace’ alerts will not function for databases, where user does not have the role Note: The user and db_datareader role must be created/added manually every time new database is created as the role is specifically granted to the user for specific DB.