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)
OPTIONAL (see below) - the user account must be granted sysadm rights in the SQL.

You can install the agent with both windows authentication and a SQL account.
If you do not wish to grant sysadm rights to the user account, please run the 
corresponding sql script at the bottom of this page before running the installer. 
The script will grant detailed rights to the user account.

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. 
Please run the script "Script for SQL authentication" at the bottom of this 
page before you run the installer. 
The script will grant the user account with necessary rights. 

To avoid local admin rights for the service account, please grant full 
access to the following folders for the service account after the install:

--C:\Program Files (x86)\AIMS Innovation\MSSQL Agent
--C:\ProgramData\AIMS Innovation\MSSQL Agent

After the rights have been granted, restart the agent service.

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 sysadm / script rights. Depending on Windows / SQL authentication, please select corresponding script below and run before you click "next" if you chose a non-sysadm rights install.
6. Provide credentials for the agent service account
7. Review the summary page.
8. Check if there are any errors in C:\Program Files (x86)\AIMS Innovation\MSSQL Agent\logs\error.txt
9. 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

Script for SQL authentication

USE master;

CREATE LOGIN aims_agent WITH PASSWORD = 'your password';
GO

--grant server permissions
GRANT VIEW SERVER STATE to "aims_agent"
GRANT CONNECT ANY DATABASE to "aims_agent";
GRANT VIEW ANY DEFINITION TO "aims_agent";
GRANT VIEW ANY DATABASE TO "aims_agent";

CREATE USER "aims_agent"
FOR LOGIN "aims_agent";
GO
--grant database permissions
GRANT SELECT ON sys.dm_exec_query_stats TO "aims_agent";
GRANT EXECUTE ON xp_readerrorlog TO "aims_agent";
GO

USE msdb
CREATE USER "aims_agent"
FOR LOGIN "aims_agent";
GO
GRANT VIEW DATABASE STATE to "aims_agent"
GRANT SELECT ON msdb.dbo.sysmaintplan_log to "aims_agent";
GRANT SELECT ON msdb.dbo.sysmaintplan_subplans to "aims_agent";
GRANT SELECT ON msdb.dbo.sysmaintplan_plans to "aims_agent";
GRANT SELECT ON msdb.dbo.sysmaintplan_logdetail to "aims_agent";
GRANT SELECT ON msdb.dbo.sysjobhistory to "aims_agent";
GRANT SELECT ON msdb.dbo.sysjobschedules to "aims_agent";
GRANT SELECT ON msdb.dbo.sysschedules to "aims_agent";
GRANT SELECT ON msdb.dbo.syscategories to "aims_agent";
GRANT SELECT ON msdb.dbo.sysjobs to "aims_agent";
GO

USE model
CREATE USER "aims_agent"
FOR LOGIN "aims_agent";
GO
GRANT VIEW DATABASE STATE to "aims_agent"

Script for Windows Service Account authentication

USE master;

CREATE LOGIN [DOMAIN\account] from WINDOWS;
GO

--grant server permissions
GRANT VIEW SERVER STATE to [DOMAIN\account]
GRANT CONNECT ANY DATABASE to [DOMAIN\account];
GRANT VIEW ANY DEFINITION TO [DOMAIN\account];
GRANT VIEW ANY DATABASE TO [DOMAIN\account];

CREATE USER "aims_agent"
FOR LOGIN [DOMAIN\account];
GO
--grant database permissions
GRANT SELECT ON sys.dm_exec_query_stats TO "aims_agent";
GRANT EXECUTE ON xp_readerrorlog TO "aims_agent";
GO

USE msdb
CREATE USER "aims_agent"
FOR LOGIN [DOMAIN\account];
GO
GRANT VIEW DATABASE STATE to "aims_agent"
GRANT SELECT ON msdb.dbo.sysmaintplan_log to "aims_agent";
GRANT SELECT ON msdb.dbo.sysmaintplan_subplans to "aims_agent";
GRANT SELECT ON msdb.dbo.sysmaintplan_plans to "aims_agent";
GRANT SELECT ON msdb.dbo.sysmaintplan_logdetail to "aims_agent";
GRANT SELECT ON msdb.dbo.sysjobhistory to "aims_agent";
GRANT SELECT ON msdb.dbo.sysjobschedules to "aims_agent";
GRANT SELECT ON msdb.dbo.sysschedules to "aims_agent";
GRANT SELECT ON msdb.dbo.syscategories to "aims_agent";
GRANT SELECT ON msdb.dbo.sysjobs to "aims_agent";
GRANT SELECT ON msdb.dbo.sysjobhistory to "aims_agent";
GO

USE model
CREATE USER "aims_agent"
FOR LOGIN [DOMAIN\account];
GO
GRANT VIEW DATABASE STATE to "aims_agent"