Case_study_two_veeam.ps1

    # Ensure script runs with elevated privileges on the Veeam server.
    # Step 1: Detect Veeam SQL instance and database from registry or prompt if needed.
    $SQLServer = $null
    $SQLInstance = $null
    $SQLDBName = $null
    Add-Type -AssemblyName System.Security

    # Check new registry path (for VBR v12+ configurations)
    $baseRegPath = "HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication"
    if (Test-Path "$baseRegPath\DatabaseConfigurations") {
        $dbConfig = Get-ItemProperty -Path "$baseRegPath\DatabaseConfigurations"
        $activeDB = $dbConfig.SqlActiveConfiguration  # "Mssql" or "PostgreSql"
        if ($activeDB -eq "Mssql") {
            $sqlConfig = Get-ItemProperty -Path "$baseRegPath\DatabaseConfigurations\Mssql"
            $SQLServer   = $sqlConfig.SqlServerName
            $SQLInstance = $sqlConfig.SqlInstanceName
            $SQLDBName   = $sqlConfig.SqlDatabaseName
        } elseif ($activeDB -eq "PostgreSql") {
            Write-Warning "Detected PostgreSQL as the Veeam database. This script currently supports only MSSQL."
            # (For PostgreSQL, a different approach using Npgsql or psql would be needed; not implemented here.)
        }
    }

    # If not found, check legacy registry keys (for older Veeam versions)
    if (-not $SQLServer) {
        if (Test-Path $baseRegPath) {
            try {
                $legacyConf = Get-ItemProperty -Path $baseRegPath
                $SQLServer   = $legacyConf.SQLServerName
                $SQLInstance = $legacyConf.SQLInstanceName
                $SQLDBName   = $legacyConf.SQLDatabaseName
            } catch {
                # Keys might not exist or accessible
            }
        }
    }

    # If still not resolved, prompt the user for details
    if (-not $SQLServer -or -not $SQLInstance -or -not $SQLDBName) {
        Write-Host "Could not auto-detect the Veeam SQL instance. Please provide details:" -ForegroundColor Yellow
        if (-not $SQLServer)   { $SQLServer   = Read-Host "Enter SQL Server name (e.g., . for local)"} 
        if (-not $SQLInstance) { $SQLInstance = Read-Host "Enter SQL Instance name (e.g., VEEAMSQL2017)"} 
        if (-not $SQLDBName)   { $SQLDBName   = Read-Host "Enter Veeam Database name (e.g., VeeamBackup)"}
        if (-not $SQLServer -or -not $SQLInstance -or -not $SQLDBName) {
            Write-Error "SQL instance information is incomplete. Exiting."
            return
        }
    }

    # Build the connection string for SQL
    # If instance is the default (MSSQLSERVER), no instance name is needed in the connection string
    if ($SQLInstance -eq "MSSQLSERVER") {
        $connString = "Server=$SQLServer; Database=$SQLDBName; Trusted_Connection=True;"
    } else {
        $connString = "Server=$SQLServer\$SQLInstance; Database=$SQLDBName; Trusted_Connection=True;"
    }

    # Step 2: Connect to the SQL database
    Try {
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $connString
        $connection.Open()
    } catch {
        Write-Error "Failed to connect to SQL Server `$SQLServer\$SQLInstance`: $_"
        return
    }

    # Step 3: Query the Credentials table for description, username, and password
    $query = "SELECT [description], [user_name], [password] FROM [dbo].[Credentials]"
    try {
        $command = $connection.CreateCommand()
        $command.CommandText = $query
        $reader = $command.ExecuteReader()
    } catch {
        $connection.Close()
        Write-Error "SQL query failed: $($_.Exception.Message)"
        return
    }
    # Load results into a DataTable for easy handling
    $DataTable = New-Object System.Data.DataTable
    $DataTable.Load($reader)
    $connection.Close()

    # If no records, inform and exit
    if ($DataTable.Rows.Count -eq 0) {
        Write-Host "No credentials found in the Veeam Credentials table."
        return
    }

    # Step 4: Get the EncryptionSalt from registry (if available)
    $saltBytes = $null
    $regDataPath = "$baseRegPath\Data"
    if (Test-Path $regDataPath) {
        try {
            $regValues = Get-ItemProperty -Path $regDataPath
            $saltBase64 = $regValues.EncryptionSalt
            if ($saltBase64) {
                $saltBytes = [Convert]::FromBase64String($saltBase64)
            }
        } catch {
            Write-Warning "Unable to read EncryptionSalt from registry: $($_.Exception.Message)"
        }
    }

    # Step 5: Decrypt each password
    $resultList = @()
    foreach ($row in $DataTable) {
        $desc      = $row.description
        $user      = $row.user_name
        $encPass   = $row.password
        $plainPass = "<Decryption failed>"  # default message, will be overwritten if successful

        try {
            if ($encPass -match '^(?i)AQAA') {
                # Legacy DPAPI encryption (no extra salt)
                $data = [Convert]::FromBase64String($encPass)
                $rawBytes = [System.Security.Cryptography.ProtectedData]::Unprotect($data, $null, 
                             [System.Security.Cryptography.DataProtectionScope]::LocalMachine)
                $plainPass = [System.Text.Encoding]::UTF8.GetString($rawBytes)
            }
            else {
                # Newer encryption (with salt) – requires removing header and using the salt
                if (-not $saltBytes) {
                    throw "EncryptionSalt not found (required for new format)."
                }
                # Convert Base64 to bytes and strip the first 74 hex characters (37 bytes) from the blob
                $data = [Convert]::FromBase64String($encPass)
                $hexBuilder = New-Object System.Text.StringBuilder ($data.Length * 2)
                foreach ($b in $data) { [void]$hexBuilder.AppendFormat("{0:x2}", $b) }
                $hexString = $hexBuilder.ToString()
                if ($hexString.Length -lt 74) { throw "Encrypted data blob is shorter than expected." }
                # Remove the first 74 hex chars (header/metadata)
                $hexString = $hexString.Substring(74)
                # Convert the remaining hex string back to byte array
                $dataBytes = New-Object byte[] ($hexString.Length / 2)
                for ($i = 0; $i -lt $hexString.Length; $i += 2) {
                    $dataBytes[$i/2] = [Convert]::ToByte($hexString.Substring($i, 2), 16)
                }
                # Now decrypt using DPAPI with the salt
                $rawBytes = [System.Security.Cryptography.ProtectedData]::Unprotect($dataBytes, $saltBytes, 
                             [System.Security.Cryptography.DataProtectionScope]::LocalMachine)
                $plainPass = [System.Text.Encoding]::UTF8.GetString($rawBytes)
            }
        }
        catch {
            # If any error occurs during decryption, capture the message
            $plainPass = "Decryption failed: $($_.Exception.Message)"
        }

        # Collect the result
        $resultList += [PSCustomObject]@{
            Description = $desc
            Username    = $user
            Password    = $plainPass
        }
    }

    # Step 6: Output the results (Description, Username, Decrypted Password)
    $resultList | Format-Table -Property Description, Username, Password