1. Help Center
  2. Install Guides
  3. Install Guide - Microsoft SQL Server Agent

AIMS Platform (SaaS) - Install Guide - Microsoft SQL Server Agent


 

The agent can be installed directly on the Microsoft SQL Servers you want to monitor, or a remote server with access to the SQL server. The SQL agent will monitor the selected instance on a server. 

Prerequisites local install

.NET framework 4.5 or higher must be installed.
The agent service account must be a member of the local administrators group
The agent service account must be granted "logon as a service"
The agent uses ports 80 / 443 (HTTP / HTTPS)

During install the service account must have sysadmin rights. If you want to remove the sysdba rights after install please run the script shown at the bottom of this guide then remove sysadmin rights

Prerequisites remote install


.NET framework 4.5 or higher must be installed.
The agent service account must be a member of the local administrators group
The agent service account must be granted "logon as a service"
The agent uses ports 80 / 443 (HTTP / HTTPS)

We recommend that you connect to the SQL server with a SQL account. During install the SQL account must have sysadmin rights. If you want to remove the sysadmin rights after install please run the script shown at the bottom of this guide then remove sysadmin rights

 

Install Agent

  1. If you have not already created an environment, please see this article before proceeding
  2. Right click the installer and select "run as administrator".
  3. Select the environment the agent should connect to
  4. Have the installer create a new system "create a new system"
  5. Provide SQL instance name, authentication method (Windows / SQL authentication) and the credentials
  6. Provide credentials for the agent service account
  7. Review the summary page.
  8. If you have installed on a remote server, you can change the name of the agent in the Topology section in the UI to reflect the correct SQL instance


Install video - local account example



Script for detailed rights to service account


--GRANT VIEW DATABASE STATE to aims
USE master;

--grant server permissions
GRANT VIEW SERVER STATE to aims

---- create master database access certificate
--CREATE CERTIFICATE MasterDatabaseAimsCertificate
-- ENCRYPTION BY PASSWORD = 'your password'
-- WITH SUBJECT = 'Master database AIMS access certificate',
-- START_DATE = '20160901', EXPIRY_DATE = '20990101';
--GO

---- create the certificate login to assign server permissions
--CREATE LOGIN aims
-- FROM CERTIFICATE MasterDatabaseAimsCertificate;

CREATE USER aims
FOR LOGIN aims;

--GO

--grant database permissions
GRANT SELECT ON sys.dm_exec_query_stats TO aims;
GO

GRANT EXECUTE ON xp_readerrorlog TO aims;

GRANT VIEW ANY DEFINITION TO aims;

GRANT VIEW ANY DATABASE TO aims;

GO

USE msdb

CREATE USER aims
FOR LOGIN aims;

GRANT VIEW DATABASE STATE to aims
GRANT SELECT ON msdb.dbo.sysmaintplan_log to aims;
GRANT SELECT ON msdb.dbo.sysmaintplan_subplans to aims;
GRANT SELECT ON msdb.dbo.sysmaintplan_plans to aims;
GRANT SELECT ON msdb.dbo.sysmaintplan_logdetail to aims;
GRANT SELECT ON msdb.dbo.sysjobhistory to aims;
GRANT SELECT ON msdb.dbo.sysjobschedules to aims;
GRANT SELECT ON msdb.dbo.sysschedules to aims;
GRANT SELECT ON msdb.dbo.syscategories to aims;
GRANT SELECT ON msdb.dbo.sysjobs to aims;

USE model
--ALTER ROLE [db_datareader] ADD MEMBER aims

CREATE USER aims
FOR LOGIN aims;

GRANT VIEW DATABASE STATE to aims