15907401a9
Some checks are pending
CI / Build og unit tests (push) Waiting to run
CI / Integration (mcr.microsoft.com/mssql/server:2017-latest) (push) Blocked by required conditions
CI / Integration (mcr.microsoft.com/mssql/server:2019-latest) (push) Blocked by required conditions
CI / Integration (mcr.microsoft.com/mssql/server:2022-latest) (push) Blocked by required conditions
CI / Integration (mcr.microsoft.com/mssql/server:2025-latest) (push) Blocked by required conditions
- Availability group collector (replica and database replica states, queues, commit times) and rules: SQLI-AG-001 unhealthy/disconnected replica or suspended data movement (scripts HADR RESUME), SQLI-AG-002 secondary behind (estimated data loss and redo time) - SQL Agent collector using only granted msdb tables (no EXECUTE on msdb functions): SQLI-JOB-001 failed job with the failing step's message (backup/CHECKDB jobs are High), SQLI-JOB-002 SQL Agent not running - Integration tests create a real availability group (CLUSTER_TYPE NONE) and a failing job in the test container (HADR and Agent enabled) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01E3EExwndy8Z22EK2Qpt6BK
81 lines
3.2 KiB
C#
81 lines
3.2 KiB
C#
using Microsoft.Data.SqlClient;
|
||
using Testcontainers.MsSql;
|
||
|
||
namespace SQLInsight.IntegrationTests;
|
||
|
||
/// <summary>
|
||
/// One SQL Server container for the test run, with the agent login set up by the real least-privilege script.
|
||
/// The image is chosen with <c>SQLINSIGHT_MSSQL_IMAGE</c> so CI can run the same tests against 2017–2025.
|
||
/// </summary>
|
||
public sealed class SqlServerFixture : IAsyncLifetime
|
||
{
|
||
public const string DefaultImage = "mcr.microsoft.com/mssql/server:2022-latest";
|
||
public const string AgentLogin = "sqlinsight_agent";
|
||
public const string AgentPassword = "Agent-Pa55word!";
|
||
public const string TestDatabase = "SQLInsightQsTest";
|
||
|
||
public static string Image => Environment.GetEnvironmentVariable("SQLINSIGHT_MSSQL_IMAGE") ?? DefaultImage;
|
||
|
||
// HADR and SQL Agent on, so availability group and job collectors run against the real DMVs.
|
||
private readonly MsSqlContainer _container = new MsSqlBuilder(Image)
|
||
.WithEnvironment("MSSQL_ENABLE_HADR", "1")
|
||
.WithEnvironment("MSSQL_AGENT_ENABLED", "true")
|
||
.Build();
|
||
|
||
public string SaConnectionString => _container.GetConnectionString();
|
||
|
||
public string Server => new SqlConnectionStringBuilder(SaConnectionString).DataSource;
|
||
|
||
public string SaPassword => new SqlConnectionStringBuilder(SaConnectionString).Password;
|
||
|
||
public string AgentConnectionString => new SqlConnectionStringBuilder(SaConnectionString)
|
||
{
|
||
UserID = AgentLogin,
|
||
Password = AgentPassword,
|
||
InitialCatalog = "master",
|
||
}.ConnectionString;
|
||
|
||
public string DatabaseConnectionString(string database) =>
|
||
new SqlConnectionStringBuilder(SaConnectionString) { InitialCatalog = database }.ConnectionString;
|
||
|
||
public async Task InitializeAsync()
|
||
{
|
||
await _container.StartAsync();
|
||
|
||
await ExecuteAsSaAsync($"""
|
||
CREATE LOGIN [{AgentLogin}] WITH PASSWORD = N'{AgentPassword}', CHECK_POLICY = OFF;
|
||
CREATE DATABASE [{TestDatabase}];
|
||
""");
|
||
await ExecuteAsSaAsync($"""
|
||
USE [{TestDatabase}];
|
||
ALTER DATABASE [{TestDatabase}] SET QUERY_STORE = ON;
|
||
CREATE TABLE dbo.CustomerData (Id int NOT NULL PRIMARY KEY, Secret nvarchar(50) NOT NULL);
|
||
INSERT dbo.CustomerData VALUES (1, N'fortroligt'), (2, N'hemmeligt');
|
||
""");
|
||
await ExecuteAsSaAsync(await GrantScriptAsync());
|
||
}
|
||
|
||
public static async Task<string> GrantScriptAsync()
|
||
{
|
||
var script = await File.ReadAllTextAsync(Path.Combine(AppContext.BaseDirectory, "Scripts", "grant-least-privilege.sql"));
|
||
return script.Replace(@"N'DOMÆNE\svc-sqlinsight$'", $"N'{AgentLogin}'", StringComparison.Ordinal);
|
||
}
|
||
|
||
public Task DisposeAsync() => _container.DisposeAsync().AsTask();
|
||
|
||
public async Task ExecuteAsSaAsync(string sql)
|
||
{
|
||
await using var connection = new SqlConnection(SaConnectionString);
|
||
await connection.OpenAsync();
|
||
await using var command = connection.CreateCommand();
|
||
command.CommandText = sql;
|
||
command.CommandTimeout = 120;
|
||
await command.ExecuteNonQueryAsync();
|
||
}
|
||
}
|
||
|
||
[CollectionDefinition(Name)]
|
||
public sealed class SqlServerTestGroup : ICollectionFixture<SqlServerFixture>
|
||
{
|
||
public const string Name = "SQL Server";
|
||
}
|