Utiliser l’API SQL Assessment pour SQL Server sur Linux

S’applique à : SQL Server - Linux

L’API SQL Assessment fournit un mécanisme permettant déterminer si la configuration de SQL Server est conforme aux bonnes pratiques. L’API est fournie avec un ensemble de règles contenant les bonnes pratiques recommandées par l’équipe SQL Server. Cet ensemble de règles est amélioré à chaque publication d’une nouvelle version. Il est utile pour vérifier si votre configuration SQL Server est conforme aux bonnes pratiques recommandées.

L’ensemble de règles fourni par Microsoft est disponible sur GitHub. Vous pouvez voir l’ensemble de règles complet dans le dépôt d‘exemples.

Dans cet article, nous allons étudier deux façons d’exécuter l’API SQL Assessment pour SQL Server sur Linux et les conteneurs :

• Extension SQL Assessment pour Azure Data Studio (préversion)
• API SQL Assessment avec PowerShell

Extension SQL Assessment pour Azure Data Studio (préversion)

L’extension SQL Assessment pour Azure Data Studio (préversion) fournit un mécanisme permettant déterminer si la configuration de SQL Server est conforme aux bonnes pratiques.

Avec cette préversion, vous pouvez :

• Évaluer une instance SQL Server, Azure SQL Database ou Azure SQL Managed Instance et ses bases de données, avec des règles intégrées
• Obtenir la liste de toutes les règles intégrées applicables à une instance et à ses bases de données
• Exporter les résultats de l’évaluation et la liste des règles applicables sous forme de script pour la stocker dans une table SQL
• Créer des rapports HTML sur les résultats des évaluations

Démarrer une évaluation SQL

• Après avoir installé l’extension SQL Assessment, développez votre liste de serveurs, cliquez avec le bouton droit sur un serveur ou une base de données que vous souhaitez évaluer, puis sélectionnez Gérer.
• Ensuite, dans la section Général, sélectionnez SQL Assessment. Sous l’onglet Évaluation, sélectionnez Appeler l’évaluation pour effectuer l’évaluation de la base de données SQL Server ou Azure SQL sélectionnée. Une fois les résultats disponibles, vous pouvez utiliser les fonctionnalités de filtrage et de tri.
• Sélectionnez Exporter sous forme de script pour obtenir les résultats dans un format d’insertion dans une table. Vous pouvez également sélectionner Créer un rapport HTML pour enregistrer les résultats de l’évaluation sous forme de fichier HTML. Certaines règles d’évaluation sont destinées à des configurations SQL Server particulières et d’autres règles sont destinées à d’autres configurations. Il en va de même pour les règles de base de données. Par exemple, il existe des règles qui s’appliquent uniquement à SQL Server 2016 (13.x) ou à la base de données tempdb.
• Le bouton Voir les règles applicables affiche les règles d’évaluation utilisées pour effectuer l’évaluation de vos serveurs et bases de données après avoir sélectionné Appeler l’évaluation. Pour afficher des informations sur SQL Server et l’API SQL Assessment, sélectionnez Informations. Les résultats de la session d’évaluation peuvent être examinés sous l’onglet Historique.

API SQL Assessment avec PowerShell

Une deuxième option consiste à utiliser PowerShell pour exécuter le script de l’API SQL Assessment.

Prérequis

1. Vérifiez que vous avez installé PowerShell sur Linux.

2. Installez le module PowerShell SqlServer à partir du PowerShell Gallery, via une exécution en tant qu’utilisateur mssql.

   su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"
   

Configurer l’évaluation

La sortie de l’API SQL Assessment est disponible au format JSON. Vous devez effectuer les étapes suivantes pour configurer l’API SQL Assessment :

1. Dans l’instance que vous souhaitez évaluer, créez une connexion pour les évaluations SQL Server à l’aide de l’authentification SQL. Vous pouvez utiliser le script Transact-SQL (T-SQL) suivant pour créer une connexion et un mot de passe fort. Remplacez <*PASSWORD*> par un mot de passe fort de votre choix.

   USE [master];
   GO
   
   CREATE LOGIN [assessmentLogin] WITH PASSWORD = N'<*PASSWORD*>';
   ALTER SERVER ROLE [CONTROL SERVER] ADD MEMBER [assessmentLogin];
   GO
   

   Le rôle CONTROL SERVER fonctionne pour la plupart des évaluations. Toutefois, certaines évaluations peuvent nécessiter des privilèges sysadmin. Si vous n’exécutez pas ces règles, nous vous recommandons d’utiliser les autorisations CONTROL SERVER.

2. Stockez les informations d’identification pour la connexion sur le système comme suit, en remplaçant <*PASSWORD*> par le mot de passe que vous avez utilisé à l’étape précédente.

   echo "assessmentLogin" > /var/opt/mssql/secrets/assessment
   echo "<*PASSWORD*>" >> /var/opt/mssql/secrets/assessment
   

3. Sécurisez les nouvelles informations d’identification de l’évaluation en vous assurant que seul l’utilisateur mssql peut accéder à ces informations d’identification.

   chmod 600 /var/opt/mssql/secrets/assessment
   chown mssql:mssql /var/opt/mssql/secrets/assessment
   

Télécharger le script d’évaluation

Voici un exemple de script qui appelle l’API SQL Assessment, en utilisant les informations d’identification créées lors des étapes précédentes. Le script génère un fichier de sortie au format JSON dans cet emplacement : /var/opt/mssql/log/assessments.

Notes

L’API SQL Assessment peut également générer une sortie aux formats CSV et XML.

Ce script peut être téléchargé à partir de GitHub.

Vous pouvez enregistrer ce fichier en tant que /opt/mssql/bin/runassessment.ps1.

[CmdletBinding()] param ()

$Error.Clear()

# Create output directory if not exists

$outDir = '/var/opt/mssql/log/assessments'
if (-not ( Test-Path $outDir )) { mkdir $outDir }
$outPath = Join-Path $outDir 'assessment-latest'

$errorPath = Join-Path $outDir 'assessment-latest-errors'
if ( Test-Path $errorPath ) { remove-item $errorPath }

function ConvertTo-LogOutput {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline = $true)]
        $input
    )
    process {
        switch ($input) {
            { $_ -is [System.Management.Automation.WarningRecord] } {
                $result = @{
                    'TimeStamp' = $(Get-Date).ToString("O");
                    'Warning'   = $_.Message
                }
            }
            default {
                $result = @{
                    'TimeStamp'      = $input.TimeStamp;
                    'Severity'       = $input.Severity;
                    'TargetType'     = $input.TargetType;
                    'ServerName'     = $serverName;
                    'HostName'       = $hostName;
                    'TargetName'     = $input.TargetObject.Name;
                    'TargetPath'     = $input.TargetPath;
                    'CheckId'        = $input.Check.Id;
                    'CheckName'      = $input.Check.DisplayName;
                    'Message'        = $input.Message;
                    'RulesetName'    = $input.Check.OriginName;
                    'RulesetVersion' = $input.Check.OriginVersion.ToString();
                    'HelpLink'       = $input.HelpLink
                }

                if ( $input.TargetType -eq 'Database') {
                    $result['AvailabilityGroup'] = $input.TargetObject.AvailabilityGroupName
                }
            }
        }

        $result
    }
}

function Get-TargetsRecursive {

    [CmdletBinding()]
    Param (
        [Parameter(ValueFromPipeline = $true)]
        [Microsoft.SqlServer.Management.Smo.Server] $server
    )

    $server
    $server.Databases
}

function Get-ConfSetting {
    [CmdletBinding()]
    param (
        $confFile,
        $section,
        $name,
        $defaultValue = $null
    )

    $inSection = $false

    switch -regex -file $confFile {
        "^\s*\[\s*(.+?)\s*\]" {
            $inSection = $matches[1] -eq $section
        }
        "^\s*$($name)\s*=\s*(.+?)\s*$" {
            if ($inSection) {
                return $matches[1]
            }
        }
    }

    return $defaultValue
}

try {
    Write-Verbose "Acquiring credentials"

    $login, $pwd = Get-Content '/var/opt/mssql/secrets/assessment' -Encoding UTF8NoBOM -TotalCount 2
    $securePassword = ConvertTo-SecureString $pwd -AsPlainText -Force
    $credential = New-Object System.Management.Automation.PSCredential ($login, $securePassword)
    $securePassword.MakeReadOnly()
    
    Write-Verbose "Acquired credentials"

    $serverInstance = '.'

    if (Test-Path /var/opt/mssql/mssql.conf) {
        $port = Get-ConfSetting /var/opt/mssql/mssql.conf network tcpport


        if (-not [string]::IsNullOrWhiteSpace($port)) {
            Write-Verbose "Using port $($port)"
            $serverInstance = "$($serverInstance),$($port)"
        }
    }

    # IMPORTANT: If the script is run in trusted environments and there is a prelogin handshake error,
    # add -TrustServerCertificate flag in the commands for $serverName, $hostName and Get-SqlInstance lines below.
    $serverName = (Invoke-SqlCmd -ServerInstance $serverInstance -Credential $credential -Query "SELECT @@SERVERNAME")[0]
    $hostName = (Invoke-SqlCmd -ServerInstance $serverInstance -Credential $credential -Query "SELECT HOST_NAME()")[0]

    # Invoke assessment and store results.
    # Replace 'ConvertTo-Json' with 'ConvertTo-Csv' to change output format.
    # Available output formats: JSON, CSV, XML.
    # Encoding parameter is optional.

    Get-SqlInstance -ServerInstance $serverInstance -Credential $credential -ErrorAction Stop
    | Get-TargetsRecursive
    | ForEach-Object { Write-Verbose "Invoke assessment on $($_.Urn)"; $_ }
    | Invoke-SqlAssessment 3>&1
    | ConvertTo-LogOutput
    | ConvertTo-Json -AsArray
    | Set-Content $outPath -Encoding UTF8NoBOM
}
finally {
    Write-Verbose "Error count: $($Error.Count)"

    if ($Error) {
        $Error
        | ForEach-Object { @{ 'TimeStamp' = $(Get-Date).ToString("O"); 'Message' = $_.ToString() } }
        | ConvertTo-Json -AsArray
        | Set-Content $errorPath -Encoding UTF8NoBOM
    }
}

Remarque

Quand vous exécutez le script précédent dans des environnements approuvés et que vous obtenez une erreur de poignée de main de prélogin, ajoutez l’indicateur -TrustServerCertificate dans les commandes pour les lignes $serverName, $hostName et Get-SqlInstance dans le code.

Exécuter l’évaluation

1. Vérifiez que le script est détenu et exécutable par mssql.

   chown mssql:mssql /opt/mssql/bin/runassessment.ps1
   chmod 700 /opt/mssql/bin/runassessment.ps1
   

2. Créez un dossier de journal et attribuez les autorisations appropriées à l’utilisateur mssql sur le dossier :

   mkdir /var/opt/mssql/log/assessments/
   chown mssql:mssql /var/opt/mssql/log/assessments/
   chmod 0700 /var/opt/mssql/log/assessments/
   

3. Vous pouvez maintenant créer votre première évaluation, mais assurez-vous de le faire en tant qu’utilisateur mssql, afin que les évaluations suivantes puissent être exécutées automatiquement via cron ou systemd de manière plus sécurisée.

   su mssql -c "pwsh -File /opt/mssql/bin/runassessment.ps1"
   

4. Une fois la commande terminée, la sortie est générée au format JSON. Cette sortie peut être intégrée à n’importe quel outil tiers qui prend en charge l’analyse des fichiers JSON. Red Hat Insights est un exemple de ces outils.

Contenu connexe

• API d’évaluation SQL
• Évaluation des meilleures pratiques SQL pour SQL Server sur les machines virtuelles Azure
• Évaluation des vulnérabilités pour SQL Server