Connect SQL Server instances to Azure at scale with a script

Applies to: SQL Server

Important

Azure Arc automatically installs the Azure extension for SQL Server when a server connected to Azure Arc has SQL Server installed. All the SQL Server instance resources are automatically created in Azure, providing a centralized management platform for all your SQL Server instances.

To automatically connect your SQL Server instances, see Automatically Connect your SQL Server to Azure Arc.

Use the method in this article, if your server is already connected to Azure, but Azure extension for SQL Server is not deployed automatically.

An ArcSQLServerExtensionDeployment = Disabled tag is created on the Arc machine resource if the extension is deployed using this method.

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 SQL Server enabled by Azure Arc.

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.

Next steps

• Learn how to Configure your SQL Server instance for periodic environment health check using best practices assessment

• Learn how to use Microsoft Defender for Cloud to protect SQL Server enabled by Azure Arc