Watch the step by step implementation as a You tube Video.

Config Table script

USE [JB_Config_DB]
GO

CREATE TABLE [dbo].[Tbl_Instance_List](
[Tbl_Instance_List_ID] [int] IDENTITY(1,1) NOT NULL,
[SQLServerInstance] nvarchar NULL,
[SQLServerInstance_Type] nvarchar NULL,
PRIMARY KEY CLUSTERED
(
[Tbl_Instance_List_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Powershel Modules to be installed on Hybrid Worker Virtual Machine

Install-Module -Name Az -Scope AllUsers -Force

Install-Module -Name SqlServer -Scope AllUsers -Force

Powershell Script to be used within the runbook

Write-Output "SQL Hardening Script - Started"

# Connect to Azure using Managed Identity
Connect-AzAccount -Identity

# Get Access Token for Azure SQL Managed Instance
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Initialize SQL Connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Data Source=jbmi.688acec0d83c.database.windows.net;Initial Catalog=JB_Config_DB;Connect Timeout=60"
$sqlConnection.AccessToken = $token

# Open SQL Connection
$sqlConnection.Open()

# Fetch List of Managed Instances from Database
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 0
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "SELECT [SQLServerInstance] FROM [dbo].[Tbl_Instance_List] WHERE [SQLServerInstance_Type] = 'Azure'"

# Read Results
$sqlReader = $sqlCommand.ExecuteReader()
$result = @()

while ($sqlReader.Read()) {
    $result += [PSCustomObject]@{
        SQLServerInstance = $sqlReader["SQLServerInstance"]
    }
}

$sqlReader.Close()
$sqlConnection.Close()

# SQL Query to Execute on Each Instance
$sqlQuery = @"
USE master;
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
"@

# Loop Through Each Managed Instance
foreach ($miName in $result) {
    try {
        $serverName = $miName.SQLServerInstance
        Write-Output "Executing query on: $serverName"

        # Create Connection for Each Managed Instance
        $miConnection = New-Object System.Data.SqlClient.SqlConnection
        $miConnection.ConnectionString = "Data Source=$serverName;Initial Catalog=master;Connect Timeout=60"
        $miConnection.AccessToken = $token
        $miConnection.Open()

            $miCommand = New-Object System.Data.SqlClient.SqlCommand
            $miCommand.CommandTimeout = 0
            $miCommand.Connection = $miConnection
            $miCommand.CommandText = $sqlQuery

            # Execute the Query
            $miCommand.ExecuteNonQuery()
            Write-Host "Query executed successfully on $serverName"
    
        # Close Connection
        $miConnection.Close()
    } catch {
        Write-Host "Failed to execute query on $serverName. Error: $_"
    }
}

Write-Output " SQL Hardening Script - completed"

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.