Configuration: Database configurations

Contents:

Summary

ID: KB20200804-1

Content: 

Scenario: Connecting to a database on the first run

1. Login to the Cayosoft Guardian web portal.
2. Select Configure database.
   
   
   
   
3. Select one of the options, for example, select Connect to an on-premises SQL database. 
4. Provide connection options and click Next.
5. Check the settings and confirm.

Scenario: Changing local database to external server

By default, the Cayosoft 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. You can also create SQL account.
There are two 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, the database and SQL account with required permissions will be created.

Note: Choose either manual or script creation as you do not need to execute both.

Preparing SQL account and database manually

1. Open Microsoft SQL Server Management Studio, navigate to Security > Logins, right-click, and select the New Login... command.
2. Create a new SQL login account with the domainName\machineName$ name.
   
   
   
   
3. Navigate to Databases, right-click, and select the New Database... command.
4. On General page, specify Cayo.Guardian as Name and the created SQL login account as Owner.
   
   
   
5. On the Options page, change the value of Is Read Committed Snapshot On option to True.
   
   
   
6. Navigate to Databases > System databases.
7. Open the main database properties and on Permissions page, click View server permissions.
8. Select the created Guardian SQL login account in the logins list.
9. Enable checkboxes for View any definition and View server state permissions.
   
   

Prepare SQL account and database by script

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. Create a login for 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$]

Changing current Guardian database

1. Login to Cayosoft Guardian web portal and navigate to Settings > Service Settings > Storage Settings and open Properties.
2. Click Configure Database on the top.
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. If you created machine SQL account, leave the checkbox not selected.
4. The service needs to restart after changing the database settings.
5. Leave Restart service when the configuration is applied selected if you want service to perform restart automatically after the settings applied.
6. 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.
7. Select 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 the ownership over this copy of the database even if it contains information about previous owner.
8. Click Yes to complete the operation.
9. 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 Cayosoft Guardian web portal and navigate to Settings > Service Settings > Storage Settings and open 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 preferred database name.
   - Set Database path to preferred path, for example E:\Guardian\.
4. Leave SQL Server authentication empty.
   
   The service needs to restart after changing the database settings.
5. Leave Restart service when the configuration is applied selected if you want service to perform restart automatically after the settings applied.
6. 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.

Note: You can move the whole data including configuration and collected history. To do this:

1. Copy your current database files from C:\ProgramData\Cayo Software\Guardian\database to the new path.
2. Set Database name to Cayo.SvcMgr and uncheck Copy Guardian configuration data to the new database.
3. Click Yes to complete operation.
4. Wait until service restarts.

Scenario: Change Cayosoft Guardian database through appsettings.json 

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

1. Stop Cayosoft Guardian service.
2. Open appsettings.json in the Cayosoft Guardian installation folder. By default, it is located here: C:\Program Files\Cayo Software\Guardian\appsettings.json.
3. Modify the ConnectionString parameter in DataBaseSettings section:
   
   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"

   Customized connection string examples after editing:
   

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

4. Start Cayosoft Guardian service.

Scenario: Moving data from a local database to an external SQL server

To move a database to an external SQL server:

1. Login to the Cayosoft Guardian web portal.
2. Navigate to Settings > System Databases.
3. Select a database to be moved.
4. Click Create SQL Server.
5. Specify the following information:
   • SQL Server name
   • Database name
   • Database path
6. Specify Additional parameters.
7. Select Do not copy history data from the drop-down list.
8. Click Yes.

Scenario: Moving data from a local database to Azure AD SQL Server

To move a database to an Azure SQL server:

1. Open the Cayosoft Guardian web portal.
2. Navigate to Settings > System Databases.
3. Select a database to be moved.
4. Click Create Azure SQL Database.
5. Provide SQL Server name, login name, and password.
6. Specify Additional parameters.
7. Select Do not copy history data from the drop-down list.
8. Click Yes.