Skip to content

Latest commit

 

History

History
116 lines (76 loc) · 6.71 KB

connect-at-scale-script.md

File metadata and controls

116 lines (76 loc) · 6.71 KB
title description author ms.author ms.reviewer ms.date ms.topic
Connect SQL Server instances on Azure Arc-enabled servers at scale with a script
In this article, you learn different ways of connecting SQL Server instances to Azure Arc at scale with a script.
anosov1960
sashan
mikeray, randolphwest
03/08/2024
how-to

Connect SQL Server instances to Azure at scale with a script

[!INCLUDE sqlserver]

[!INCLUDE automatic]

This article describes how to connect SQL Server instances installed on multiple Windows or Linux machines to Azure Arc as a single task using a script. After the machines are connected to Azure, Azure Arc automatically installs the Azure extension for SQL Server. For information, see Automatically connect your SQL Server to Azure Arc.

For the best experience, use Microsoft Entra ID. With Microsoft Entra ID, you define a service principal. The service principal is a special limited management identity that is granted only the minimum permissions necessary to connect machines to Azure and to create the Azure resources for Azure Arc-enabled server and [!INCLUDE ssazurearc].

Before you get started, be sure to review the prerequisites and make sure that you've created a custom role that meets the required permissions.

Connect multiple instances

Windows

Each machine must have Azure PowerShell installed.

  1. Create the service principal. Use the New-AzADServicePrincipal cmdlet. Make sure to store the output in a variable. Otherwise, you won't be able to retrieve the password needed later.

    $sp = New-AzADServicePrincipal -DisplayName "Arc-for-servers" -Role <your custom role>
$sp

  2. Give the service principal permissions to access Microsoft Graph.

    [!NOTE]

    • When you create a service principal, your account must be an Owner or User Access Administrator in the subscription that you want to use for onboarding. If you don't have sufficient permissions to create role assignments, the service principal might be created, but it won't be able to onboard machines. The instructions on how to create a custom role are provided in prerequisites.

  3. Retrieve the password stored in the $sp variable:

    $credential = New-Object pscredential -ArgumentList "temp", $sp.PasswordCredentials.SecretText 
$credential.GetNetworkCredential().password

  4. Retrieve the value of the service principal's tenant ID:

    $tenantId= (Get-AzContext).Tenant.Id

  5. Copy and save the password, application ID, and tenant ID values using the appropriate security practices. If you forget or lose your service principal password, you can reset it using the New-AzADSpCredential cmdlet.

    [!NOTE] Note that Azure Arc for servers doesn't currently support signing in with a certificate, so the service principal must have a secret to authenticate with.

  6. Download the PowerShell script from the Portal following the instructions in Connect your SQL Server to Azure Arc.

  7. Open the script in an administrator instance of PowerShell ISE and replace the following environment variables using the values generated during the service principal provisioning described earlier. These variables are initially empty.

    $servicePrincipalAppId="{serviceprincipalAppID}"
$servicePrincipalSecret="{serviceprincipalPassword}"
$servicePrincipalTenantId="{serviceprincipalTenantId}"

  8. Execute the script on each target machine.

Linux

Each target machine must have the Azure CLI installed. The registration script will automatically sign in to Azure with the service principal credentials if they're provided and no other user is already signed in. Use the following steps to connect SQL Server instances on multiple Linux machines.

  1. Create the service principal using the 'az ad sp create-for-rbac' command.

    az ad sp create-for-rbac --name <your service principal name> --role <your custom role name> --scopes /subscriptions/<subscription id>

  2. Download the Linux shell script from the Portal following the instructions in Connect your SQL Server to Azure Arc.

    [!NOTE] When you create a service principal, your account must be an Owner or User Access Administrator in the subscription that you want to use for onboarding. If you don't have sufficient permissions to create role assignments, the service principal might be created, but it won't be able to onboard machines. The instructions on how to create a custom role are provided in prerequisites.

  3. Replace the following variables in the script using the values returned by the 'az ad sp create-for-rbac' command. These variables are initially empty.

    servicePrincipalAppId="{serviceprincipalAppID}"
servicePrincipalSecret="{serviceprincipalPassword}"
servicePrincipalTenant="{serviceprincipalTenant}"

  4. Execute the script on each target machine

    sudo chmod +x ./RegisterSqlServerArc.sh
./RegisterSqlServerArc.sh

Validate successful onboarding

After you connected the SQL Server instances to Azure, go to the Azure portal and view the newly created Azure Arc resources. You'll see a new Server - Azure Arc resource for each connected machine and a new SQL Server - Azure Arc resource for each connected SQL Server instance within approximately 1 minute. If these resources aren't created, it means something went wrong during the extension installation and activation process. See Troubleshoot Azure extension for SQL Server for the troubleshooting options.

:::image type="content" source="./media/join-at-scale/successful-onboard.png" alt-text="Screenshot showing a successful onboard.":::

Next steps