AWS CLI AWS RDS DBA Tasks PowerSHell SQL Server SSIS SSRS

SQL Performance Monitor – Part 2: Active Directory User

By David Speight / August 25, 2022

This is Part 2 of my plan to set up SQL Monitoring between AWS accounts for a multi-company, multi-account enterprise.

To move the performance counter data around, we’ll need a User Service Account set up in Active Directory for this. Since I’m working with multiple companies I will need to duplicate this process in each domain.

I have set up a SERVICE ACCOUNT USER called SqlSsisUser. This AD USER is created in each and every company I will be gathering stats from, with only the domain name (and password) changing.

  • 1stDomain\SqlSsisUser
  • 2ndDomain\SqlSsisUser
  • 3rdDomain\SqlSsisUser

Permissions granted to this user, include these groups:

  • AWS Delegated Remote Access Service Administrator
  • Domain Users (default group)
  • Other GROUPS as needed, depending on your system.
  • AppSvc_LogonBatch

NOTE 1: AppSvc_LogonBatch is a custom group that I will attach to the Local Policy on the main server, to allow the user to run PowerShell scripts.

On each EC2 Server that will be running PowerShell, I will need to configure the AD User to have LOGON AS A BATCH JOB permissions. Under WINDOWS ADMINISTRATIVE TOOLS, launch the LOCAL SECURITY POLICY. Under SECURITY SETTINGS / LOCAL POLICIES / USER RIGHTS AND ASSIGNMENTS, add the custom group as shown:

SQL Server Permissions

Add this new service account user (SqlSsisUser) to the SQL Server instance and configure at least these permissions on the databases: msdb, SSISDB, and ServerAnalysis (created in a later step.)

Create SQL Server Credential and Proxy

On each SQL Server instance, create a credential and proxy to SSIS for the service account user SqlSsisUser:

USE master;
CREATE CREDENTIAL [CRSqlSsisUser] WITH IDENTITY = N'MYDOMAIN\SqlSsisUser', SECRET=N'MyPASSWORD'


USE msdb;
EXEC dbo.sp_add_proxy  
    @proxy_name = 'SqlSsisUser proxy',  
    @enabled = 1,  
    @description = 'SSIS tasks on catalog.',  
    @credential_name = 'CRSqlSsisUser' ;  

EXEC msdb.dbo.sp_grant_login_to_proxy 
     @proxy_name = N'SqlSsisUser proxy', 
     @login_name = N'MYDOMAIN\SqlSsisUser';

EXEC msdb.dbo.rds_sqlagent_proxy 
     @task_type = 'GRANT_SUBSYSTEM_ACCESS', 
     @proxy_name = 'SqlSsisUser proxy', 
     @proxy_subsystem = 'SSIS';

In the next step we’ll create the ServerAnalysis database(s) and objects.

You may also like