I am trying to set up my local server for Mirroring with Fabric.
It's a SQL Server 2025, and I am following these instructions: Tutorial: Configure Microsoft Fabric Mirrored Databases From SQL Server - Microsoft Fabric | Micros...
I managed to connect my server with Azure Arc. It's visible on the Azure Arc page.
Now I need to run a PowerShell script "to configure the system-assigned managed identity (SAMI) and necessary registry keys " (this is step 2 for SQL Server 2025)
I get an error when running the ps script from step 2 for sql server 2025, the one that creates registry keys and other tings.
The error is: "Can not establish communication with IMDS service. You need either to have Azure Arc service installed or run this script on Azure VM."
The point of all this is adding an on prem server, not a VM...
Is there an error in the script, or am I doing something wrong?
This is the ps script:
$apiVersion = "2020-06-01"
$resource = "https://storage.azure.com/"
$ep = $env:IDENTITY_ENDPOINT
$msi = "arc"
if (!$ep) {
$msi = "vm"
$ep = 'http://169.254.169.254/metadata/identity/oauth2/token'
}
$endpoint = "{0}?resource={1}&api-version={2}" -f $ep,$resource,$apiVersion
$secretFile = ""
try {
Invoke-WebRequest -Method GET -Uri $endpoint -Headers @{Metadata='True'} -UseBasicParsing > $null
$msi = "vm"
} catch {
if ($_.Exception.Response.Headers) {
$wwwAuthHeader = $_.Exception.Response.Headers["WWW-Authenticate"]
if ($wwwAuthHeader -match "Basic realm=.+") {
$secretFile = ($wwwAuthHeader -split "Basic realm=")[1]
}
}
}
$secret = ""
if ($secretFile) {
$msi = "arc"
$secret = cat -Raw $secretFile
}
try {
$response = Invoke-WebRequest -Method GET -Uri $endpoint -Headers @{Metadata='True'; Authorization="Basic $secret"} -UseBasicParsing
} catch {
Write-Output "Can not establish communication with IMDS service. You need either to have Azure Arc service installed or run this script on Azure VM."
}
if ($response) {
$parts = (ConvertFrom-Json -InputObject $response.Content).access_token -split "\."
$padLength = 4 - ($parts[1].Length % 4)
if ($padLength -ne 4) { $parts[1] += "=" * $padLength }
$payload = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($parts[1])) | ConvertFrom-Json
$regPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
$instance = ""
$regKey = Get-Item -Path $regPath
$regKey.GetValueNames() | Where-Object { $regKey.GetValue($_) -match 'MSSQL17' } | ForEach-Object {
$instance = $_
$service = if ($instance -eq "MSSQLSERVER") { "MSSQLSERVER" } else { "MSSQL$" + $instance }
$reginst = $regKey.GetValue($_)
$regFed = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($reginst)\MSSQLServer\FederatedAuthentication"
if (-not (Test-Path -Path $regFed)) {
New-Item -Path $regFed -Force > $null
}
if ($msi -eq "arc") {
Write-Host "Registering Azure Arc MSI service for SQL Server instance: " $instance `n
Set-ItemProperty -Path $regFed -Name "ArcServerManagedIdentityClientId" -Value ""
Set-ItemProperty -Path $regFed -Name "ArcServerSystemAssignedManagedIdentityClientId" -Value $($payload.appid)
Set-ItemProperty -Path $regFed -Name "ArcServerSystemAssignedManagedIdentityTenantId" -Value $($payload.tid)
$svcPath = "HKLM:\SYSTEM\CurrentControlSet\Services\$($service)"
if (Test-Path -Path $svcPath) {
$keyPath = Split-Path $secretFile
$svcKey = Get-Item -Path $svcPath
$sqlAccount = $svcKey.GetValue("ObjectName")
if ($sqlAccount -ne "LocalSystem") {
Write-Host "Permissioning folder" $keyPath "for SQL Server account" $sqlAccount `n
icacls $keyPath /grant "$($sqlAccount):(OI)(CI)R"
$group = "Hybrid agent extension applications"
$isMember = Get-LocalGroupMember -Group $group | Where-Object { $_.Name -eq $sqlAccount }
if (-not $isMember) {
Write-Host "Also adding SQL running account to local group: $group" `n
Add-LocalGroupMember -Group $group -Member $sqlAccount
} else {
Write-Host ""
}
}
}
} else {
Write-Host "Registering Azure VM MSI service for SQL Server instance: " $instance `n
Set-ItemProperty -Path $regFed -Name "PrimaryAADTenant" -Value ""
Set-ItemProperty -Path $regFed -Name "OnBehalfOfAuthority" -Value "https://login.windows.net/"
Set-ItemProperty -Path $regFed -Name "FederationMetadataEndpoint" -Value "login.windows.net"
Set-ItemProperty -Path $regFed -Name "AzureVmManagedIdentityClientId" -Value ""
Set-ItemProperty -Path $regFed -Name "AzureVmSystemAssignedManagedIdentityClientId" -Value $($payload.appid)
Set-ItemProperty -Path $regFed -Name "AzureVmSystemAssignedManagedIdentityTenantId" -Value $($payload.tid)
}
}
Write-Host "Registeration complete for:" `n "Client ID: " $($payload.appid) `n "Tenant ID: " $($payload.tid) `n
}
Many Thanks,
Edgar Walther
Cheers, Edgar Walther
ITsmart BI and Analytics consultant
