Security and Credentials

The Credential Pattern (Mandatory)

BizFirstGO enforces a strict credential pattern for all database connections. Connection strings are never stored in workflow definitions, SQL node configurations, or application settings files. They are always stored in the Credential Store and accessed via ICredentialResolver at runtime.

// ICredentialResolver contract
public interface ICredentialResolver
{
    Task<string> GetPasswordAsync(int credentialId, CancellationToken ct = default);
    Task<string> GetConnectionStringAsync(int credentialId, CancellationToken ct = default);
}

// Usage in IDatasourceConnectionFactory — internal implementation
public async Task<SqlConnection> GetConnectionAsync(string datasourceId, int tenantId, CancellationToken ct)
{
    var datasource = await _datasourceRegistry.GetAsync(datasourceId, tenantId, ct);
    var connectionString = await _credentialResolver.GetConnectionStringAsync(datasource.CredentialId, ct);
    var connection = new SqlConnection(connectionString);
    await connection.OpenAsync(ct);
    return connection;
}

Credential Store Integration

The Credential Store supports multiple backends depending on your deployment:

Tenant Row Isolation

Multi-tenancy in Data Ocean SQL Server is enforced at the application layer by the SQL node execution engine. Every query executed by a SQL node automatically receives a tenantId parameter sourced from the workflow execution context (workflow.tenantId).

The framework validates that every SQL query passed to a SqlQueryNode or SqlUpdateNode includes @tenantId as a parameter. Queries missing this parameter are rejected at design-time validation:

// This query will FAIL validation — missing @tenantId filter
SELECT * FROM Customer WHERE CustomerId = @customerId

// This query will PASS validation
SELECT * FROM Customer WHERE TenantId = @tenantId AND CustomerId = @customerId AND IsDeleted = 0

Column-Level PII Masking

For tables containing Personally Identifiable Information (PII), SQL Server's Dynamic Data Masking provides column-level masking for database users without data access rights:

-- Apply masking to PII columns
ALTER TABLE Customer
    ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE Customer
    ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');

-- The BizFirstApp login must be GRANTED UNMASK to see real values
-- Remove this grant for reporting/analytics accounts that should see masked data
GRANT UNMASK TO BizFirstApp;

-- For a reporting-only login, do NOT grant UNMASK
-- CREATE LOGIN BizFirstReports WITH PASSWORD = '...';
-- (No UNMASK grant — this login sees masked values)

Connection Security Checklist