Flux de travail d'analyse de sécurité avec SQL Server

For AI agents: a documentation index is available at /llms.txt; a markdown version of this page is available at /v1.2.0.0/fr/docs/examples/security-analysis-sql/index.md.

Cet exemple montre un flux de travail pratique pour l’analyse de sécurité : analyser un journal de débogage DNS avec Convert-DNSDebugLogFile, importer le CSV généré dans SQL Server, et exécuter une requête qui met en évidence des volumes anormalement élevés de requêtes sur les enregistrements TXT.

Pour une meilleure interopérabilité, cet exemple écrit les horodatages dans un format proche de l’ISO en utilisant -OutputCulture 'sv-SE'.

Modules requis

Cet exemple utilise les modules PowerShell suivants :

• DNSServer.DebugLogParser
• SqlServer

Installe-les si besoin :

Install-Module -Name DNSServer.DebugLogParser -Scope CurrentUser
Install-Module -Name SqlServer -Scope CurrentUser

Scénario

Utilise ce flux de travail lorsque tu souhaites transférer les données analysées d’un journal de débogage DNS dans SQL Server afin de pouvoir :

• rechercher efficacement dans de grands ensembles de données
• créer des requêtes de détection répétables
• corréler l’activité entre plusieurs serveurs DNS
• conserver des données normalisées pour des investigations ultérieures

Résultat de l’étape de conversion

Convert-DNSDebugLogFile n’écrit pas directement dans SQL Server. Il crée d’abord un fichier CSV. Ce fichier CSV est le jeu de données importé dans la base.

Dans cet exemple :

• journal d’entrée : C:\Administration\Logs\DNS\dns.log
• CSV généré : C:\Administration\Logs\DNS\dns.csv
• table cible : dbo.DNSQueries

Créer la table cible

Exécute la commande suivante une fois dans SQL Server pour créer la table cible.

IF OBJECT_ID('dbo.DNSQueries', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.DNSQueries (
        DateTime      datetime2(0)   NOT NULL,
        ThreadId      int            NULL,
        Context       nvarchar(20)   NULL,
        PacketId      int            NULL,
        Protocol      nvarchar(10)   NULL,
        Direction     nvarchar(10)   NULL,
        ClientIP      nvarchar(64)   NULL,
        Xid           nvarchar(16)   NULL,
        Type          nvarchar(16)   NULL,
        Opcode        nvarchar(16)   NULL,
        FlagsHex      nvarchar(16)   NULL,
        FlagsChar     nvarchar(16)   NULL,
        ResponseCode  nvarchar(32)   NULL,
        QuestionType  nvarchar(32)   NULL,
        QuestionName  nvarchar(512)  NULL,
        Information   nvarchar(max)  NULL,
        Details       nvarchar(max)  NULL,
        ComputerName  nvarchar(256)  NULL
    );
END;

Convertir le journal et importer le CSV

L’exemple PowerShell suivant réalise le flux complet :

1. importe les modules requis
2. convertit le journal de débogage DNS en CSV
3. charge le CSV généré
4. importe en masse les lignes dans SQL Server

# Requires -Modules DNSServer.DebugLogParser, SqlServer

Import-Module -Name DNSServer.DebugLogParser -ErrorAction Stop
Import-Module -Name SqlServer -ErrorAction Stop

$logPath = 'C:\Administration\Logs\DNS\dns.log'
$csvPath = 'C:\Administration\Logs\DNS\dns.csv'
$serverInstance = 'SQLServer'
$databaseName = 'DNSLogs'
$delimiter = ';'

$createTableSql = @'
IF OBJECT_ID('dbo.DNSQueries', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.DNSQueries (
        DateTime      datetime2(0)   NOT NULL,
        ThreadId      int            NULL,
        Context       nvarchar(20)   NULL,
        PacketId      int            NULL,
        Protocol      nvarchar(10)   NULL,
        Direction     nvarchar(10)   NULL,
        ClientIP      nvarchar(64)   NULL,
        Xid           nvarchar(16)   NULL,
        Type          nvarchar(16)   NULL,
        Opcode        nvarchar(16)   NULL,
        FlagsHex      nvarchar(16)   NULL,
        FlagsChar     nvarchar(16)   NULL,
        ResponseCode  nvarchar(32)   NULL,
        QuestionType  nvarchar(32)   NULL,
        QuestionName  nvarchar(512)  NULL,
        Information   nvarchar(max)  NULL,
        Details       nvarchar(max)  NULL,
        ComputerName  nvarchar(256)  NULL
    );
END
'@

Convert-DNSDebugLogFile `
    -InputFile $logPath `
    -ComputerName 'DNS01' `
    -OutputType CSV `
    -OutputFile $csvPath `
    -Delimiter $delimiter `
    -OutputCulture 'sv-SE'

$rows = Import-Csv -Path $csvPath -Delimiter $delimiter

if (-not $rows) {
    throw "Le fichier CSV généré '$csvPath' ne contient aucune ligne."
}

$dataTable = [System.Data.DataTable]::new()
foreach ($columnName in $rows[0].PSObject.Properties.Name) {
    $null = $dataTable.Columns.Add($columnName, [string])
}

foreach ($row in $rows) {
    $dataRow = $dataTable.NewRow()
    foreach ($column in $dataTable.Columns) {
        $columnName = $column.ColumnName
        $dataRow[$columnName] = $row.$columnName
    }

    $null = $dataTable.Rows.Add($dataRow)
}

$connectionString = "Server=$serverInstance;Database=$databaseName;Integrated Security=True"
$connection = [System.Data.SqlClient.SqlConnection]::new($connectionString)

try {
    $connection.Open()

    $command = $connection.CreateCommand()
    $command.CommandText = $createTableSql
    $null = $command.ExecuteNonQuery()

    $bulkCopy = [System.Data.SqlClient.SqlBulkCopy]::new($connection)
    $bulkCopy.DestinationTableName = 'dbo.DNSQueries'

    foreach ($column in $dataTable.Columns) {
        $null = $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName)
    }

    $bulkCopy.WriteToServer($dataTable)
}
finally {
    $connection.Dispose()
}

Requête pour détecter une activité suspecte sur les enregistrements TXT

Une fois les données dans SQL Server, tu peux rechercher les clients qui émettent un nombre anormalement élevé de requêtes sur les enregistrements TXT.

SELECT
    ComputerName,
    ClientIP,
    QuestionName,
    COUNT(*) AS QueryCount
FROM dbo.DNSQueries
WHERE QuestionType = 'TXT'
GROUP BY
    ComputerName,
    ClientIP,
    QuestionName
HAVING COUNT(*) > 100
ORDER BY QueryCount DESC;

Si tu préfères exécuter la requête depuis PowerShell, utilise Invoke-Sqlcmd du module SqlServer :

$query = @'
SELECT
    ComputerName,
    ClientIP,
    QuestionName,
    COUNT(*) AS QueryCount
FROM dbo.DNSQueries
WHERE QuestionType = 'TXT'
GROUP BY
    ComputerName,
    ClientIP,
    QuestionName
HAVING COUNT(*) > 100
ORDER BY QueryCount DESC;
'@

Invoke-Sqlcmd `
    -ServerInstance 'SQLServer' `
    -Database 'DNSLogs' `
    -Query $query

Pourquoi les requêtes TXT sont intéressantes

Un volume élevé de requêtes sur les enregistrements TXT peut valoir la peine d’être examiné car cela peut indiquer :

• un tunnel DNS
• une exfiltration de données via DNS
• un abus des enregistrements TXT par des malwares ou des outils
• des clients anormalement bruyants ou mal configurés

Cette requête n’est qu’un point de départ. En production, tu devrais ajuster le seuil et ajouter des filtres adaptés à ton environnement.

Notes opérationnelles

• Import-Csv charge le fichier complet en mémoire. Pour des exports de journaux très volumineux, envisage une approche en streaming plutôt que de construire un DataTable complet.
• Garde -ComputerName dans l’étape de conversion pour que les enregistrements restent attribuables après ingestion centralisée.
• Utilise un délimiteur et une culture cohérents lors de l’export et de l’import.
• Vérifie la rétention, l’indexation et le contrôle d’accès dans SQL Server avant d’utiliser ce flux pour un stockage à long terme.