Configuration: Connect to external SQL database

Summary: 

By default, Cayosoft Guardian uses Microsoft SQL Server Express LocalDB as a database to store all configuration and collected data. This database has some limitations, including a maximum database size of 10 GB. In a production environment, it is strongly recommended to use an external MS SQL database (such as Microsoft SQL Server Standard 2019 Standard or Enterprise) for storing history data. If you configured Cayosoft Guardian with a local database, later, you can copy your configuration data to the new database, so you don't need to configure your Guardian installation once again.

If you have limitations for C: disk space, it is possible to change the default local database location to another drive.

Applies to: Cayosoft Guardian 1.3.2 and later, Microsoft SQL 2016 and later.

ID: KB20200804-1

Content: 

Scenario: Moving from a local database to an external server

Here is a walkthrough on how to move the Guardian database 2.1.2 version to the SQL server:

By default, the Guardian service uses a Local System Account, so if you're planning to use Windows Authentication, a machine account should be created in Microsoft SQL. Also, you can create a SQL account as well.

There are two simple ways to create a SQL account and database that can be used by Cayosoft Guardian. First, an account can be created manually using the Microsoft SQL Management Studio. Second, those familiar with running SQL Scripts can use the script provided below to create the account. In both cases, a database and SQL accounts with required permissions will be created.

1. Prepare SQL account and database manually

1.3. Navigate to Databases, right-click, and select New Database... command.

1.4. On General page specify Cayo.Guardian as Name and just created SQL login account as Owner.

1.5. On Options page change the value of Is Read Committed Snapshot On option to True.

2. Prepare SQL account and database by script

2.1. Create a database Cayo.Guardian. Specify correct database paths for Cayo.Guardian.mdf and Cayo.Guardian.ldf files.

USE [master]
CREATE DATABASE [Cayo.Guardian]
ON (NAME = N'CayoServiceMgr', FILENAME = N'E:\Guardian\Cayo.Guardian.mdf')
LOG ON (NAME = N'CayoServiceMgr_log', FILENAME = N'E:\Guardian\Cayo.Guardian.ldf')
IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE[Cayo.Guardian]
SET READ_COMMITTED_SNAPSHOT ON; END

2.2. Create a login for the Guardian machine account and grant permissions. Replace domainName\machineName$ with your domain and machine names.

-- Create a login for Guardian machine account
CREATE LOGIN [domainName\machineName$]
FROM WINDOWS WITH DEFAULT_DATABASE=[Cayo.Guardian], DEFAULT_LANGUAGE=[us_english]

-- Grant database owner permissions
USE [Cayo.Guardian]
CREATE USER [domainName\machineName$] FOR LOGIN [domainName\machineName$]
EXEC sp_addrolemember N'db_owner', N'domainName\machineName$'

-- Grant permissions to read system objects
USE [master]
GRANT VIEW ANY DEFINITION TO [domainName\machineName$]
GRANT VIEW SERVER STATE TO [domainName\machineName$]

3. Change current Guardian database

3.1. Login to Guardian Web UI and navigate to Settings > Service Settings > Storage Settings and open its Properties

3.2. Click Configure Database on the top

3.3. In the opened dialog specify parameters:

- SQL Server name like Host\Instance

- Database name (default is Cayo.Guardian)

- Select SQL Server authentication if you need it. If you created a machine SQL account, leave the checkbox not selected.

- The service needs to restart after changing the database settings. Leave 'Restart service when the configuration is applied' selected if you want service to perform restart automatically after the settings are applied.

- Leave 'Copy Guardian configuration data to the new database' selected to copy managed system registrations, job configurations, saved queries, alerting rules, and other configuration data. Change History, Jobs History and collected events would not be copied. This Guardian Service would connect to all managed systems and start a new collection. You cannot copy the current configuration to another database that has been used by Guardian before.

- Select the 'I confirm this database is not used by another Guardian Service' option if the target database was copied or moved from another installation of the Cayosoft Guardian. This Guardian Service would take ownership over this copy of the database even if it contains information about the previous owner. 

3.4. Click Yes to complete the operation.

3.5. Wait until service restarts.

Scenario: Move local database to another drive

By default Guardian stores databases in C:\ProgramData\Cayo Software\Guardian\database. If you have disk space limitations on your C: drive and want to move the database to another location, you can do it in Web UI Storage Settings. 

1. Login to Guardian Web UI and navigate to Settings > Service Settings > Storage Settings and open its Properties

2. Click Configure Database on the top

3. In the opened dialog specify parameters:

- Set SQL Server name to (LocalDb)\CayoInstance.

- Set Database name to the preferred database name.

- Set Database path to preferred path, for example, E:\Guardian\.

- Leave SQL Server authentication empty.

- The service needs to restart after changing the database settings. Leave 'Restart service when the configuration is applied' selected if you want service to perform restart automatically after the settings are applied.

- Leave 'Copy Guardian configuration data to the new database' selected to copy managed system registrations, job configurations, saved queries, alerting rules, and other configuration data. Change History, Jobs History and collected events would not be copied. This Guardian Service would connect to all managed systems and start a new collection.

3.4. Click Yes to complete the operation.

3.5. Wait until service restarts.

Scenario: Change Cayosoft Guardian database through appsettings.json

You can specify external database settings or revert service to use a local database in application settings.

1. Stop Cayosoft Guardian service.

2. Open appsettings.json in the Cayosoft Guardian installation folder. By default it should be located here: C:\Program Files\Cayo Software\Guardian\appsettings.json

3. Modify the ConnectionString parameter in DataBaseSettings section:

3.1. Default connection string example (before editing):

"Data Source=(LocalDb)\\CayoInstance;Initial Catalog=Cayo.SvcMgr;AttachDbFileName=%programdata%\\Cayo Software\\Guardian\\database\\CayoServiceMgr.mdf;Integrated Security=SSPI;Connection Timeout=240"

"Data Source=dch1.hercules.milkyway.cayotest.net\\SQLEXPRESS;Initial Catalog=Cayo.SvcMgr;Integrated Security=SSPI;Connection Timeout=240"

4. Start CayoGuardian service.

Scenario: Change the password of the account that Cayosoft Guardian uses to connect to a SQL Server database

1. Login to Guardian Web UI and navigate to Settings > Service Settings > Storage Settings and open its Properties

2. Click Change password on the top

3. Specify a new password and confirm

Scenario: Change the account that Cayosoft Guardian uses to connect to a SQL Server database

1. Login to Guardian Web UI and navigate to Settings > Service Settings > Storage Settings and open its Properties

2. Click Configure credentials on the top

3. Enter new credentials and confirm

Scenario: Using Guardian with Azure AD SQL Server

Here is a walkthrough on using Guardian with the Azure AD SQL server.

Scenario: Move Guardian Database to Azure SQL with Change History

Change History