Register a SQL Server instance running in the Google Cloud Platform

Task 1: Create the monitoring user with the necessary permissions

On the Google Cloud Platform (GCP) website, navigate to the SQL Server instance that you want to register.
Under Users, create a new user for DPA monitoring. Then run the following script using the default sqlserver or any other privileged account to assign the permissions needed for monitoring.

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.

The following 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.

--grant privileges to login
USE [master]
GRANT VIEW SERVER STATE TO [dpa_mon_user] AS CustomerDbRootRole
GRANT VIEW ANY DATABASE TO [dpa_mon_user] AS CustomerDbRootRole
GRANT VIEW ANY DEFINITION TO [dpa_mon_user] AS CustomerDbRootRole
--assign processaadmin role to allow kill sessions
ALTER SERVER ROLE [processadmin] ADD MEMBER [dpa_mon_user]
--create user on each database and grant db_datareader role
--in case there are databases not accessible by sqlserver user, you need to grant the permissions by the database owner
DECLARE @DPA_User VARCHAR (50) = 'dpa_mon_user';
DECLARE @dbname VARCHAR(50);
DECLARE @SQL NVARCHAR(max);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM master.dbo.sysdatabases where name NOT IN ('master', 'msdb', 'tempdb', 'model');
OPEN dbs;
FETCH NEXT FROM dbs INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'use '+@dbname +';
    CREATE USER '+@DPA_User+' FOR LOGIN '+@DPA_User+';
    EXECUTE sp_addrolemember N''db_datareader'', '+@DPA_User+';'
    EXECUTE sp_executesql @SQL;
    FETCH NEXT FROM dbs INTO @dbname;
END;
CLOSE dbs;
DEALLOCATE dbs;

Task 2: Register the database instance

You can register the instance using the Register Database wizard, mass registration wizard, or the DPA API.

Register Database wizard

Complete the following steps to use the wizard to Register Database register one instance at a time.

In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.
Under Google Cloud SQL, select Cloud SQL for SQL Server.

Complete the Connection information panel:

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.
- Otherwise, enter the server name or IP address and the port number.

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

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).

Enter the user name and password of the monitoring user created previously. Or, if DPA is configured to use CyberArk, enter the CyberArk credentials query for the monitoring user.
Click Next.

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

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.
Review the information on the Summary page. Click Back if you need to make changes. When the information is correct, click Register.

Mass registration

Use the mass registration wizard to register multiple instances. The following shows an example of a spreadsheet for mass registration used to register a GCP SQL Server database instance.

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 task.
In the Deployment column, enter Google.

DPA REST API

Run scripts that call the DPA REST API to register instances. The following example is a segment of a JSON script that calls the DPA API to register a GCP SQL Server database instance. Change the values to reflect your monitored database and the credentials of the monitoring user created in the previous task.

{
  "databaseType": "SQLSERVER",
  "serverName": "34.122.1.2",
  "port": 1433,
  "monitoringUser": "dpa_mon_user",
  "monitoringUserPassword": "Pa$$word1",
  "deployment": "GOOGLE"
}

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 Support 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.

Permissions granted to login

Unless otherwise stated, the permissions are optional. (If optional permissions are missing, basic monitoring is possible but specific data or functionality is not available.)

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

Those roles are optional.

Role Description

processadmin Required to kill sessions. Without it, the Kill Session option in the Real Time Sessions view does not work.

Role	Description
`processadmin`	Required to kill sessions. Without it, the Kill Session option in the Real Time Sessions view does not work.
`db_datareader`	Monitoring user needs to be created on each database before granting the role. Without it: SQL texts will not be available. Table Tuning Advisors cannot include current data, table size, and table churn. ‘Database Freespace’ and ‘Transaction Log Freespace’ alerts will not function for all databases. This has to be granted to any database created on the monitored instance after registration, because newly created databases will not have the user with this role created automatically. This is a limitation of GCP SQL Server, which is not able to grant the `CONNECT ANY DATABASE` privilege.

db_datareader

Monitoring user needs to be created on each database before granting the role. Without it:

SQL texts will not be available.
Table Tuning Advisors cannot include current data, table size, and table churn.
‘Database Freespace’ and ‘Transaction Log Freespace’ alerts will not function for all databases.

This has to be granted to any database created on the monitored instance after registration, because newly created databases will not have the user with this role created automatically. This is a limitation of GCP SQL Server, which is not able to grant the CONNECT ANY DATABASE privilege.

Limitations due to not having the sysadmin role in Cloud SQL for SQL Server

Some of the permissions that DPA uses can only be granted by granting the sysadmin role to the DPA monitoring user. Without the sysadmin role, the following alerts will not work:

SQL Server Error Log Alert
SQL Server Log has Many Virtual Logs
SQL Server Long Running Jobs
Windows Service Not Running - SQL Server

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.

Search SolarWinds Support