Register a SQL Server database instance

The following sections provide instructions for using a wizard to register a self-managed SQL Server database instance for monitoring with DPA.

For DPA to collect metrics from a monitored SQL Server instance, Azure SQL instance, or ASMI, the SQL option NUMERIC_ROUNDABOUT must be set to OFF.

If you register a database instance within the 14-day trial period, DPA begins monitoring the instance immediately. After the trial period, you must activate a license to monitor the database instance.

Options for creating the monitoring user

DPA requires an account with certain privileges to gather information from the database instance. This account is called the monitoring user. If DPA is notintegrated with CyberArk, the registration wizard displays options to determine how the monitoring user and utility package are created or configured.

If DPA is integrated with CyberArk, these options are not displayed. You must create the monitoring user.

You have the following options for creating and authenticating the monitoring user:

• Allow DPA to create the monitoring user or configure an existing user to give it the required privileges. With this option:

  • You must provide the credentials of a user with the sysadmin role so that DPA can create or configure the monitoring user.
  • The monitoring user is assigned the sysadmin role.

  DPA does not store the user credentials. To use this option, see Register a SQL Server database instance and let DPA create the monitoring user below.

• Create the monitoring user manually. With this option, you can create the monitoring user manually or by running a script. Then you use the wizard to complete the registration. You are not prompted to enter privileged user credentials into DPA.

  If you create the monitoring with one of the scripts provided, the DPA monitoring user is not assigned the sysadmin role. Because the monitoring user is not granted the sysadmin role, DPA has the following limitations:

  • DPA cannot collect near-zero or zero cost plans.
  • The 'SQL Server Log has Many Virtual Logs' alert does not work.
  • The 'Windows Service Not Running - SQL Server' alert does not work.

  For detailed information about the privileges granted to the monitoring user, see this article.

  To use this option, see Register a SQL Server database instance and create the monitoring user yourself below.

• If you don't want to create, manage, and update user credentials, you can use a group managed service account (gMSA). See the prerequisites in the following section, and then follow the instructions under Register a SQL Server database instance and create the monitoring user yourself below.

Prerequisites for authenticating with a gMSA

The DPA monitoring user can log in to DPA with a group managed service account (gMSA) if the following conditions are met:

• DPA must be installed on a server with a Windows operating system.

• All SQL Server instances that use a gMSA to authenticate the monitoring user must use the same gMSA login and they must be monitored by the same DPA server.

• DPA and SQL Server must run under the same AD domain controller (workgroup) and must be configured to access this gMSA.

• You must download the mssql-jdbc_auth-12.2.0.x64.dll file and install it in the Java library path on the DPA server. This file is not shipped with DPA.

• The DPA service must have access to access the DPA folder for the gMSA.

Register a SQL Server database instance and create the monitoring user yourself

Task 1: Create the monitoring user

If the monitoring user will use a gMSA for authentication, skip this task and continue with Task 2.

To create the monitoring user manually, do one of the following:

• To avoid the limitations listed above, create the monitoring user manually and grant the sysadmin role to that user. For more information, see Create the DPA monitoring user for SQL Server and Azure SQL Managed Instance.

• To create the monitoring user without the sysadmin role, run a script:

  1. Copy one of the following scripts to a file:

     • For a SQL Server 2014 or later instance: CreateMonUserSqlServer2014orLater.sql

     • For a SQL Server 2012 instance: CreateMonUserSqlServer2012.sql

     These scripts are 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 SQL Server database 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 sysadmin to run the script.

Task 2: Complete the registration wizard

1. In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.
2. Under Self-managed, select Microsoft SQL Server.
3. If the monitoring user prompt is displayed, select I will create the monitoring user manually. Then click Next.

4. Complete the Connection information panel:

   1. Enter connection information for the SQL Server instance:

      • If the SQL Server Browser service is running, enter the server name or IP address and the instance name in this format: Server\Instance.

      • If the SQL Server instance contains one or more Availability Groups, click the Availability Groups link for instructions on how to register primary and secondary replicas.

      • Otherwise, enter the server name or IP address and the port number.

      DPA monitors all databases within the instance. If more than one instance exists on the server, you must register each instance separately in DPA.

   2. Under SSL mode, specify the type of secure socket layer (SSL) connections established between the instance and the DPA server.

      If the ForceEncryption flag is set to Yes on the SQL Server database instance you are registering, all communication with the instance must be encrypted. Do not choose Disable as the SSL mode.

      SSL mode	Description
      Disable	SSL encryption is not used.
      No certificate validation	SSL is enabled, but no server certificate checks are performed. This SSL configuration does not protect against man-in-the-middle attack because no certificate is required.
      Validate server certificate	SSL is enabled. The client verifies that the server is trustworthy by checking the certificate chain up to a trusted certificate authority (CA).
      Validate server certificate and match hostname	SSL is enabled. The client verifies the certificate chain and also verifies that the server hostname matches its certificate's Subject Alternative Name or Common Name (CN).

   3. Select the type of authentication you want to use.

      To authenticate the monitoring user with a gMSA:

      • Choose Windows Authentication.

      • Select Is gMSA?

      If a gMSA is not being used and Mixed Mode was selected during the SQL Server installation, you can choose either option.

   4. Complete the Monitoring user and Password fields:

      • If you manually created the monitoring user, enter the user name and password of that user.

      • If DPA is configured to use CyberArk, enter the CyberArk credentials query for the monitoring user.

      • If the monitoring user is authenticated with a gMSA, the Monitoring user field is prepopulated and both fields are disabled.

   5. Click Next.

      DPA validates the connection information and the privileges of the monitoring user.

      SSL is requested by default. If the server does not support SSL, a plain connection is used.

      If you receive errors, see DPA for SQL Server installation troubleshooting.

5. Specify the following Instance options.

   The instance name and group membership can be changed after registration.

   1. If your repository database is Oracle, choose the tablespace in the repository database to store DPA performance data for this monitored instance.

      By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.

   2. Enter the name that DPA will display to identify this database instance.

      The Display name field defaults to the name retrieved from the database instance.

   3. (Optional) If you have manually created instance groups, you can assign this database instance to one of the groups.

      If no manual groups exist, this option is not shown.

   4. (Optional) If you have existing alert groups, you can assign this database instance to one or more groups.

      If no alert groups exist, or the existing groups do not match this instance's database type, this option is not shown.

   5. Click Next.

6. Review the information on the Summary page. Click Back if you need to make changes. When the information is correct, click Register.

Register a SQL Server database instance and let DPA create the monitoring user

Task 1: Identify the privileged user

When you register a database instance using this option, you must provide the credentials of a privileged user. During registration, the privileged user either creates the monitoring user or grants the required privileges to an existing user that you designate as the monitoring user. DPA does not store the credentials of the privileged user. 

For self-managed SQL Server database instances, the privileged user requires the SYSADMIN role. It cannot be the repository database user.

Task 2: Complete the registration wizard

1. In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.
2. Under Self-managed, select SQL Server.
3. At the monitoring user prompt, select DPA will create/configure the monitoring user. Then click Next.

4. Complete the Enter Monitored Database Instance Connection Information panel:

   1. Enter connection information for the SQL Server instance:

      • If the SQL Server Browser service is running, enter the server name or IP address and the instance name in this format: Server\Instance.

      • If the SQL Server instance contains one or more Availability Groups, click the Availability Groups link for instructions on how to register primary and secondary replicas.

      • Otherwise, enter the server name or IP address and the port number.

      DPA monitors all databases within the instance. If more than one instance exists on the server, you must register each instance separately in DPA.

   2. Select the type of authentication you want to use. If Mixed Mode was selected during the SQL Server installation, you can choose either option.

   3. In the SYSADMIN Login and Password fields, enter credentials for the privileged user that DPA can use to register the instance.

      • For Windows authentication, enter <DOMAIN>\<username> in the SYSADMIN Login field.

      • For SQL Server authentication, enter the credentials that you enter on the Connect to Server dialog in SQL Server Management Studio (with Database Engine as the Server type).

      DPA does not use or store these credentials after you complete the wizard.

   4. Click Next.

      DPA validates the connection information and the privileges of the privileged user.

      SSL is requested by default. If the server does not support SSL, a plain connection is used.

      If you receive errors, see DPA for SQL Server installation troubleshooting.

5. Create or specify the account that DPA will use to gather information (the monitoring user).

   To ensure that the account has the required permissions, SolarWinds recommends creating a new account.

   • To create a new account:

     1. Next to Create Monitoring User, select Yes.
     2. Select SQL Server as the authentication method. (DPA cannot create a new Windows account.)
     3. Enter a user name and password for the new account, or accept the default values.
     4. Click Next.

   • To specify an existing account:

     1. Next to Create Monitoring User, select No.

     2. Select either authentication method.

     3. Enter the user name and password of an existing account.

        For Windows authentication, enter <DOMAIN>\<username> in the Monitoring User field.

        You can also authenticate using a Windows Computer Account.

        For SQL Server authentication, only the user name is required. Do not specify a domain.

     DPA ignores data on the monitored database instance from the monitoring user. Make sure the monitoring user will not cause load on the monitored instance.

     4. Click Next.

        If your repository database is Oracle, the Oracle Repository Tablespace panel opens. If not, continue with step 7.

6. If your repository database is Oracle, choose the tablespace in the repository database to store DPA performance data for this monitored instance. Then click Next.

   By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.

   If your repository database is not Oracle, the wizard skips this step.

7. (Optional) Select the alert groups you want the new database instance to join. Then click Next.

   • If no alert groups exist, or the existing groups do not match the database type of this instance, the wizard skips this step.

   • Group membership can be changed after registration

8. Review the information, and click Register Database Instance.

9. When the registration is complete, click Finish to return to the DPA home page.

The scripts are not supported under any SolarWinds support program or service. The 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.