Tutorial: Configure Microsoft Fabric mirrored databases from SQL Server

Connect server to Azure Arc and enable manage identity

To configure Fabric Mirroring, you need to configure Azure Arc for your SQL Server 2025 instance.

1. Connect the server to Azure Arc. Follow the steps in Quickstart - Connect hybrid machine with Azure Arc-enabled servers.

   For SQL Server instances running in an Always On availability group or failover cluster instance configuration, all nodes must be connected to Azure Arc.

2. Three registry keys are needed on the Windows Server hosting the source SQL Server instance for Fabric Mirroring. The registry keys include information about the system-assigned managed identity (SAMI) for the Windows Server. The following PowerShell script adds three registry keys, the necessary file system permissions, and managed identities.

   Note

   This section contains a script to modify the Windows registry. Make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, see How to back up and restore the registry in Windows.

   The three registry keys are added in the following ___location:

   • For a default instance: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\FederatedAuthentication
   • For a named instance: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.Instancename\MSSQLServer\FederatedAuthentication

   The script adds the following keys:

   • ArcServerManagedIdentityClientId
   • ArcServerSystemAssignedManagedIdentityClientId
   • ArcServerSystemAssignedManagedIdentityTenantID

   Run the following PowerShell script to configure the system-assigned managed identity (SAMI) and necessary registry keys on the Windows Server that hosts the source SQL Server instance.

    $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
    } 
   

   Important

   For SQL Server instances running in an Always On availability group or failover cluster instance configuration, run the PowerShell script locally on each node.

3. Connect to your local SQL Server 2025 instance. When you connect, select Trust server certificate.

4. View the managed identities:

   SELECT *
   FROM sys.dm_server_managed_identities;
   

   This should return 1 row with the correct client_id and tenant_id. Identity_type should be "System-assigned".

Add managed identities permissions in Microsoft Fabric

The managed identity of the SQL Server is created and granted permissions by Microsoft Fabric automatically.

However, for SQL Server instances running in an Always On availability group or failover cluster instance configuration, the system-assigned managed identity (SAMI) of every secondary node needs to be granted Contributor permissions to the Fabric workspace. A managed identity is created by the PowerShell script provided for each secondary node, and that must be granted Fabric permissions manually.

1. In the Fabric portal, grant Fabric permissions to each secondary node's managed identity.

   1. In the Fabric workspace, select Manage access.

      

   2. Select Add people or groups.

   3. In the Add people dialogue, find the server names for each node in the availability group or failover cluster.

   4. Grant each membership to the Contributor role.

      

Configure the on-premises data gateway

Check your networking requirements for Fabric to access your SQL Server. You need to install an on-premises data gateway to mirror the data. Make sure the on-premises gateway machine's network can connect to the SQL Server instance. For more information, see How to: Secure data Microsoft Fabric mirrored databases From SQL Server.

1. Download On-premises data gateway from the Official Microsoft Download Center.
2. Start installation. Follow instructions in Install an on-premises data gateway.
   • Provide your Microsoft account email address.
   • Name: MyOPDG or any name you desire.
   • Recovery key: provide a strong recovery key.

Create a mirrored SQL Server

1. During the current preview, open the Fabric portal using this link: https://<your_org_name>.powerbi.com/home?experience=power-bi&feature.dpe_madrid.mirrorSql2025=1.
2. Use an existing workspace, or create a new workspace.
3. Navigate to the Create pane. Select the Create icon.
4. Scroll to select Mirrored SQL Server database.
5. Enter the name of your SQL Server database to be mirrored, then select Create.

Connect Fabric to your SQL Server instance

To enable Mirroring, you will need to connect to the SQL Server instance from Fabric to initiate the connection from Fabric. The following steps guide you through the process of creating the connection to your SQL Server:

1. Under New sources, select SQL Server database. Or, select an existing SQL Server connection from the OneLake hub.
2. If you selected New connection, enter the connection details to the SQL Server instance.
   • Server: The fully qualified server name path that Fabric will use to reach your SQL Server instance, the same that you would use for SSMS.
   • Database: Enter the name of your SQL Server.
   • Connection: Create new connection.
   • Connection name: An automatic name is provided. You can change it.
   • Data gateway: Select the on-premises data gateway you set up according to your scenario.
   • Authentication kind: Choose the authentication method and provide the principal you set up in Use a login and mapped database user.
   • Select the Use encrypted connection checkbox.
3. Select Connect.

Prepare your SQL Server instance

1. The SQL Server Agent service must be running. It is highly recommended to configure Automatic Startup.
2. Changed Data Capture (CDC) will be automatically enabled and configured by Fabric Mirroring for each desired table in your database. Review the Known issues and errors with CDC. CDC requires that each table has a primary key.

Configure the on-premises data gateway

Check your networking requirements for Fabric to access your SQL Server. You need to install an on-premises data gateway to mirror the data. Make sure the on-premises gateway machine's network can connect to the SQL Server instance. For more information, see How to: Secure data Microsoft Fabric mirrored databases From SQL Server.

1. Download the on-premises data gateway, see Download the on-premises data gateway from the Official Microsoft Download Center.
2. Start installation. Follow instructions in Install an on-premises data gateway.
   • Provide your Microsoft account email address.
   • Name: MyOPDG or any name you desire.
   • Recovery key: provide a strong recovery key.

Create a mirrored SQL Server

1. During the current preview, open the Fabric portal using this link: https://<your_org_name>.powerbi.com/home?experience=power-bi&feature.dpe_madrid.mirrorSql2025=1.
2. Use an existing workspace, or create a new workspace.
3. Navigate to the Create pane. Select the Create icon.
4. Scroll to select Mirrored SQL Server database.
5. Enter the name of your SQL Server database to be mirrored, then select Create.

Connect Fabric to your SQL Server instance

To enable Mirroring, you will need to connect to the SQL Server instance from Fabric to initiate the connection from Fabric. The following steps guide you through the process of creating the connection to your SQL Server:

1. Under New sources, select SQL Server database. Or, select an existing SQL Server connection from the OneLake hub.
2. If you selected New connection, enter the connection details to the SQL Server instance.
   • Server: The fully qualified server name path that Fabric will use to reach your SQL Server instance, the same that you would use for SSMS.
   • Database: Enter the name of your SQL Server.
   • Connection: Create new connection.
   • Connection name: An automatic name is provided. You can change it.
   • Data gateway: Select the name of the on-premises data gateway you set up according to your scenario.
   • Authentication kind: Choose the authentication method and provide the principal you set up in Use a login and mapped database user.
3. Select Connect.