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]
GOPowershel Modules to be installed on Hybrid Worker Virtual Machine
Install-Module -Name Az -Scope AllUsers -Force
Install-Module -Name SqlServer -Scope AllUsers -ForcePowershell 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.