DB24 Data Collection

DB24 Data Collection – Authentication

Introduction

DB24 collects health check pings and monitoring data from your SQL Server instances through the DB24 Datastore. By default, DB24 collects using the SQL Agent service account running on the DB24 Datastore. You can instead authenticate collection with a dedicated SQL login, either applied to all instances or as an override for a single instance.

Collection authentication is configured under Settings → Organization → Instances and requires the Admin role.

Default: SQL Agent Service Account

When no SQL login is configured, DB24 collects using the SQL Agent service account of the DB24 Datastore.

Permissions: The SQL Agent account running on the DB24 Datastore must have appropriate permissions on the targeted instance to successfully perform health check pings and collect monitoring data.

When installation is done using our Installation Wizard, these SQL Agent permissions will be configured automatically during installation.

Configuration Scopes

Collection authentication can be configured on two levels:

• Global (all instances)
  On the Instances page, the toolbar button Collection auth: SQL Agent / Collection auth: SQL Login opens the configuration for all instances. The button configures how DB24 authenticates collection for all instances (SQL Agent or a dedicated SQL login).

• Per instance (override)
  Select an instance in the list to open its configuration panel. The button Collection auth: SQL Agent / Collection auth: SQL Login in the panel configures how DB24 authenticates collection for this instance (SQL Agent or a dedicated SQL login override).

The button label reflects the effective mode: it reads SQL Login when a SQL login applies to the scope, otherwise SQL Agent.

Resolution Order

DB24 resolves the credentials used for an instance in this order:

1. Instance override – a SQL login configured for that specific instance.
2. Global SQL login – a SQL login configured for all instances.
3. SQL Agent (default) – no SQL login configured.

The Authentication column on the Instances page shows the effective mode for every instance:

The configuration dialog shows the same information as Current: at the top:

• SQL Login (override): <user> – an override is set for the instance.
• SQL Login: <user> – the global SQL login, when viewing the global scope.
• Inheriting global SQL Login: <user> – the instance inherits the global SQL login.
• SQL Agent (default) – no SQL login is configured.

Prerequisites for SQL Login Authentication

Mixed Mode

Each target instance must be in Mixed Mode for SQL login authentication to work.

When configuring an instance override, DB24 checks the collected authentication mode of the instance and shows one of the following messages:

• The instance is in Mixed Mode – SQL login authentication is supported.
• The instance is in Windows (or another non-Mixed) authentication mode. SQL login authentication requires Mixed Mode. Enable Mixed Mode on the instance before configuring a SQL login.
• Authentication mode is not collected yet for this instance. Ensure the instance is in Mixed Mode before configuring a SQL login.

Encryption Hierarchy on the Datastore

The DB24 Datastore stores the SQL login password encrypted. This requires the SQL encryption hierarchy (certificate + symmetric key DB24_Credential_Key) to be set up on the Datastore. If it is missing, saving fails with:

Failed to set SQL login: the encryption hierarchy is not set up on the Datastore. See the script instructions to the right.

DB24 Installation Wizard automatically sets up this encryption during intallation.

Required Login on the Target Instance

The login must exist on the target SQL Server with the permissions described in the next section.

Required Login and Permissions

The configuration dialog provides a Permission script that creates the SQL login DB24 uses for collection on the runtime. The script follows the value entered in SQL login user (default db24_collector). Set the password to match the DB24 portal, then run the script on the target instance as sysadmin. The script is idempotent and safe to re-run.

The script grants the login:

• VIEW SERVER STATE on the server
• A database user in DB24_Data with default schema dbo
• Membership in the db_datareader role in DB24_Data
• SELECT, INSERT, UPDATE, DELETE on schema cfg in DB24_Data

The login is created with DEFAULT_DATABASE = [DB24_Data], CHECK_EXPIRATION = OFF and CHECK_POLICY = ON.

/* Creates the SQL login DB24 uses for collection on this runtime.
   Set the password to match the DB24 portal, then run on the target
   instance as sysadmin. Idempotent - safe to re-run. */

DECLARE @Login    NVARCHAR(128) = N'db24_collector';
DECLARE @Password NVARCHAR(128) = N'your_password_here'; /* UPDATE */
DECLARE @SQLCmd   NVARCHAR(MAX);

SELECT @SQLCmd = N'
USE [master];

IF NOT EXISTS (SELECT [name] FROM master.sys.server_principals WHERE [name] = ''' + @Login + ''')
BEGIN
    CREATE LOGIN [' + @Login + '] WITH PASSWORD = N''' + @Password + ''',
        DEFAULT_DATABASE = [DB24_Data],
        CHECK_EXPIRATION = OFF,
        CHECK_POLICY     = ON;
END;

GRANT VIEW SERVER STATE TO [' + @Login + '];

USE [DB24_Data];

IF NOT EXISTS (SELECT 1 FROM DB24_Data.sys.database_principals WHERE [name] = ''' + @Login + ''')
BEGIN
    CREATE USER [' + @Login + '] FOR LOGIN [' + @Login + '] WITH DEFAULT_SCHEMA = [dbo];
END;

ALTER ROLE [db_datareader] ADD MEMBER [' + @Login + '];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[cfg] TO [' + @Login + '];
';

EXEC sp_executesql @SQLCmd;

Setting or Updating Credentials

Open the configuration dialog for the desired scope (all instances or a single instance) and fill in Set Runtime Credentials (or Update Runtime Credentials when a login is already configured for the scope):

• SQL login user
  The SQL login DB24 uses for collection. Defaults to db24_collector.

• Password
  The password of the SQL login. The stored password is never displayed. Enter it again to update.

Both fields are required. Click Save (or Update) to store the credentials. On success the portal confirms with SQL login configured for collection.

Removing a SQL Login

Click Remove SQL login in the configuration dialog. The portal asks for confirmation:

This will clear the SQL login for all instances (or the selected instance) and revert collection to the default SQL Agent account.

After removal the scope falls back one level: an instance without an override inherits the global SQL login if one exists, otherwise collection reverts to the SQL Agent account. On success the portal confirms with Reverted to the default SQL Agent account.

Good to Know

• The password is write-only. It is never returned to the portal and never displayed; only the login user name is shown.
• Every change to collection credentials is recorded in the audit trail with the login user and scope. The password is never audited.
• Viewing the configuration requires the Member role. Changing it requires the Admin role.
• The global SQL login applies to every instance that has no override of its own. There is no separate "inherit" switch; an instance inherits automatically when it has no override.
• Data collection of newly added instances is by default set to Disabled. Collection authentication only matters for instances where collection is enabled.

Related Documentation

• DB24 Overview - Platform introduction
• LDAP Authentication - Directory sign-in for DB24 Portal users