SQL login authentication for DB24 collection

SQL login authentication for DB24 collection

Configure a dedicated SQL login for runtime data collection when Windows authentication is unavailable.

Early access notice. This is the first version of SQL login configuration for DB24. In future releases, you will be able to manage these credentials directly in the portal without running SQL scripts manually.

By default, DB24 connects to SQL instances using the Windows service account of the SQL Server engine. Switch to SQL login authentication when Windows auth is not available, or when you need per-instance credential isolation.

⚠️ All runtimes needs to be configured to run Mixed Authentication Mode

Setup involves three steps:

1. Datastore Set up the encryption hierarchy — Run once on the DB24_MAC database to prepare credential storage.
2. Datastore Configure DB24 to use sql logins - [DB24_MAC].[cfg].[Global] [CollectionSQLAuthUser] Can't be null or empty so assign a value. This will disable validation in installer.
3. Webserver Install Runtimes - Run installation Wizard and install Runtime's.
4. Runtime(s) Provision the SQL login — Run on each target SQL instance to create the collection login.
5. Datastore Runtime specific credentials — Optional step if you desire specific connection details for different instances.

Step 1 — Set up the encryption hierarchy

Run on: Datastore DB24_MAC

Run this script once on the DB24_MAC database. It creates the key chain used to encrypt stored credentials. Safe to re-run — each object is only created if it does not already exist.

Note: Replace REPLACE_ME with a strong master key password before running.

USE [DB24_MAC]
GO

DECLARE @MasterKeyPassword NVARCHAR(128) = 'REPLACE_ME';

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
    IF @MasterKeyPassword = 'REPLACE_ME'
        THROW 50000, 'Replace the REPLACE_ME placeholder before running.', 1;

    PRINT 'Creating Master Key'
    DECLARE @sql NVARCHAR(256) = 'CREATE MASTER KEY ENCRYPTION BY PASSWORD = '
        + QUOTENAME(@MasterKeyPassword, '''');
    EXEC sp_executesql @sql;
END

IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'DB24_Credential_Cert')
BEGIN
    PRINT 'Creating Certificate'
    CREATE CERTIFICATE DB24_Credential_Cert
        WITH SUBJECT = 'DB24 Runtime SQL Auth Credentials';
END

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'DB24_Credential_Key')
BEGIN
    PRINT 'Creating Symmetric Key'
    CREATE SYMMETRIC KEY DB24_Credential_Key
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE DB24_Credential_Cert;
END

-- Grant access to db24 portal user
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[DB24_Credential_Key] TO [portal];
GRANT CONTROL ON CERTIFICATE::[DB24_Credential_Cert] TO [portal];

Step 2 — Configure DB24 to use sql logins

Run on: Datastore DB24_MAC

Update snippet with a strong password that meets password policies.

Note: This will disable the Installer validation for Datastore service account.

Pass NULL for @InstanceName to set a default credential applied to all instances.

EXEC [DB24_MAC].[cfg].[sp_SetRuntimeCredential]
    @InstanceName = NULL,
    @SQLAuthUser  = N'db24_collector',
    @SQLAuthPass  = N'your_password_here';

Step 3 — Install Runtimes

Run on: Webserver

Run installation Wizard and install DB24 on target Runtime's

Step 4 — Provision the SQL login

Run on: Runtime(s)

Run this script on each target SQL instance. Set @Login and @Password before executing.

⚠️ If the login already exists it will be dropped and recreated, so don't reuse an existing account used for other purposes.

The login is granted least-privilege permissions only:

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 EXISTS (SELECT [name] FROM master.sys.server_principals WHERE [name] = ''' + @Login + ''')
BEGIN
    IF EXISTS (SELECT 1 FROM DB24_Data.sys.database_principals WHERE [name] = ''' + @Login + ''')
    BEGIN
        USE [DB24_Data];
        DROP USER [' + @Login + '];
        USE [master];
    END;
    DROP LOGIN [' + @Login + '];
END;

CREATE LOGIN [' + @Login + '] WITH PASSWORD = N''' + @Password + ''',
    DEFAULT_DATABASE = [DB24_Data],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY     = ON;

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

USE [DB24_Data];

CREATE USER [' + @Login + '] FOR LOGIN [' + @Login + '] WITH DEFAULT_SCHEMA = [dbo];

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

EXEC sp_executesql @SQLCmd;

Step 5 — Runtime specific connection details

Run on: Datastore DB24_MAC

Per-instance credentials take precedence over the global default.

EXEC [DB24_MAC].[cfg].[sp_SetRuntimeCredential]
    @InstanceName = N'SERVERNAME\INSTANCENAME',
    @SQLAuthUser  = N'db24_collector',
    @SQLAuthPass  = N'your_password_here';

How it works

DB24 reads connection credentials from two places: cfg.Global for the default applied to all instances, and facts.Instances for per-instance overrides (which take precedence). Setting CollectionSQLAuthUser to a non-null value switches DB24 into SQL login mode and disables the Windows service account setup on target instances.