10/30/2018 10:35 PM

12/22/2022 5:16 PM

In SolarWinds DPA, you can register and monitor a SQL Server database using a Windows Computer Account (such as a Network Service Account).

To use this feature, you must make the following configuration changes:

• On the DPA Server, modify the IgnitePI service and grant permissions to the account.
• On each monitored SQL Server database instance, add the user/login.
• Enable the functionality in DPA.
• Register new instances for monitoring or update instances that are already registered.

Note: You can only use Computer Account authentication if the DPA server has a Windows operating system.

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.

On the DPA Server, modify the IgnitePI service and grant permissions to the account

Modify the DPA IgnitePI service to run under the Computer Account

1. From a command prompt, enter services.msc.
2. Right-click the Ignite PI Server service, and select Properties.
3. On the Log On tab, click This Account and enter the credentials for the Windows Computer Account.

Grant the Computer Account full control to the DPA installation directory and subdirectories

1. Open a File Explorer window, and browse to the DPA installation directory. The default is:

   C:\Program Files\SolarWinds\DPA

2. Right-click the folder, and click Properties.
3. Click the Security tab, and click Edit.
4. Click Add, enter the account, and then click OK.
5. Under Permissions, select Full control, and then click OK.

On each monitored SQL Server database instance, add the user/login

On each SQL Server database instance that you want to monitor with the Windows Computer Account, complete the following steps to add the login or user.

1. Add the following user or login to the SQL Server:

   DOMAIN\<DPA_MACHINE_NAME>$

   Where <DPA_MACHINE_NAME> is the hostname of the SolarWinds DPA server. This must be done on all monitored SQL Servers.

   For example, run the following SQL statement to create the login:

   CREATE LOGIN [DOMAIN\<DPA_MACHINE_NAME>$] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

2. Do one of the following to grant the required permissions:
   • Grant the SYSADMIN role:

     ALTER SERVER ROLE sysadmin ADD MEMBER DOMAIN\<DPA_MACHINE_NAME>$

   • If you do not want to grant the SYSADMIN role, you can grant specific privileges. For more information, see After registering a SQL Server instance, remove the SYSADMIN role from the DPA monitoring user.

Enable the functionality in DPA

This functionality is disabled by default. Complete the following steps to enable it.

1. On the DPA main menu, click Options.
2. Under Administration > Configuration, click Advanced Options.
3. Select the Support Options check box.
4. Click the SQL_SERVER_COMP_ACC_AUTH_ENABLED system option.
   
5. Select True from the New Value list, and click Update.

Register new instances for monitoring or update instances that are already registered

To register instances for monitoring with a Windows Computer Account, see the following sections:

• Register new instances using mass registration
• Use the Update wizard to update a registered instance to use a Windows Computer Account
• Advanced: Use SQL statements to mass update registered instances to use a Windows Computer Account

Register new instances using mass registration

To register new SQL Server instances using a Windows Computer Account, you must use the mass registration feature.

1. On the DPA main menu, click Options.
2. Under Monitor Setup > Database Instances, click Mass Registration.
3. In DPA 2021.3 and earlier, complete the following steps:
   1. Select SQL Server from the Database Type list.
   2. From the Authentication Type list, select Computer Account.
      Notes:
      • In DPA 2021.3 and earlier, the Authentication Type option is available only when this functionality is enabled.
      • In later versions, the same spreadsheet is used for all versions, and the Authentication Type option is not required.
4. Follow the on-screen How to instructions. Note that registering SQL Server instances using a Computer Account requires less information than standard password authentication. DPA does not require a username and password. The only identifier is the domain name. Click the required information link to display a list of the required fields.
   

Use the Update wizard to update each registered instance to use a Windows Computer Account 

1. On the DPA main menu, click Options.
2. Under Monitor Setup > Database Instances, click Update Connection Info.
3. Select a SQL Server instance, and click Next.
4. Next to Authentication type, select Computer Account.
5. Select the Domain check box, and enter the information.
   
6. Click Next to finish the wizard.

Advanced: Use SQL statements to mass update registered instances to use a Windows Computer Account 

1. Connect to the repository database with a SQL tool.
2. To find the IDs of registered SQL Server instances, run the following command:

   SELECT ID, NAME, USERNAME, CONN_HOST FROM COND WHERE DB_TYPE='SQL Server'

3. To update one or more instances, run the following command:

   UPDATE COND SET AUTH_SCHEMA='SSO', PASSWORD=NULL, USERNAME='<USERNAME>' WHERE DB_TYPE='SQL Server' AND ID IN (...)

   • <USERNAME> must be in the following format: DOMAIN\
   • In the parenthesis (...), insert IDs from the COND table that belong to the instances you want to update.

Troubleshooting

If you receive the error Could not load library required for Computer Authentication, it indicates one of the following issues:

• Your DPA installation runs on a Linux server.

  This feature is only available for DPA running on a Windows operating system.

• The jTDS driver is used to connect to the SQL Server instance, and the file ntlmauth.dll is not available in the expected directory.

  The jTDS driver is used by default to connect to self-managed SQL server instances in DPA 2021.1 and earlier. The ntlmauth.dll file is distributed as part of the embedded Java, but might need to be added manually for custom JRE installations. This DLL library allows the jTDS driver to impersonate the user that was previously defined to run the DPA service. To fix this issue:

  1. Get the location of the Java version used by DPA from the following file:

     <DPA_INSTALLATION_DIR>\iwc\tomcat\ignite_config\java_loc.txt

  2. Copy the following file to the bin folder of the Java version used to run DPA:

     <DPA_INSTALLATION_DIR>\iwc\jre\bin\ntlmauth.dll

• The Microsoft JDBC driver is used to connect to the SQL Server instance, and the file mssql-jdbc_auth-8.4.1.x64.dll is not available in the expected directory.

  The MS JDBC driver is used by default to connect to self-managed SQL server instances for new registrations in DPA 2021.3 and later. The mssql-jdbc_auth-8.4.1.x64.dll file is not distributed as part of the embedded Java and is not shipped with DPA. It must be added manually. This DLL library allows the MS JDBC driver to impersonate the user that was previously defined to run the DPA service. To fix this issue:

  1. Get the location of the Java version used by DPA from the following file:

     <DPA_INSTALLATION_DIR>\iwc\tomcat\ignite_config\java_loc.txt

  2. Copy the following file to the bin folder of the Java version used to run DPA:

     <DPA_INSTALLATION_DIR>\iwc\jre\bin\mssql-jdbc_auth-8.4.1.x64.dll

For other issues:

• Determine if the service runs using an NSA user. This information can be found in the services menu in Windows on the monitored server. The SQL Server service for registration should be running under the account in use. 
• If DPA is not correctly displaying options for Network Account AUTH_SCEMA in the COND table, there should be a PASSWORD for standard authentication and SSO for Network Account authentication.