Automate management with the Windows SQL Server IaaS Agent extension

Applies to: SQL Server on Azure VM

The SQL Server IaaS Agent extension (SqlIaasExtension) runs on SQL Server on Azure Windows Virtual Machines (VMs) to automate management and administration tasks.

This article provides an overview of the extension. To install the SQL Server IaaS Agent extension to SQL Server on Azure VMs, see the articles for Automatic registration, Register single VMs, or Register VMs in bulk.

To learn more about the Azure VM deployment and management experience, including recent improvements, see:

Overview

The SQL Server IaaS Agent extension allows for integration with the Azure portal, and unlocks a number of benefits for SQL Server on Azure VMs:

  • Feature benefits: The extension unlocks a number of automation feature benefits, such as portal management, license flexibility, automated backup, automated patching and more. See Feature benefits later in this article for details.

  • Compliance: The extension offers a simplified method to fulfill the requirement of notifying Microsoft that the Azure Hybrid Benefit has been enabled as is specified in the product terms. This process negates needing to manage licensing registration forms for each resource.

  • Free: The extension is completely free. There's no additional cost associated with the extension.

  • Integration with centrally managed Azure Hybrid Benefit: SQL Server VMs registered with the extension can integrate with Centrally managed Azure Hybrid Benefit, making it easy manage the Azure Hybrid Benefit for your SQL Server VMs at scale.

  • Simplified license management: The extension simplifies SQL Server license management, and allows you to quickly identify SQL Server VMs with the Azure Hybrid Benefit enabled using:

    You can use the SQL virtual machines resource in the Azure portal to quickly identify SQL Server VMs that are using the Azure Hybrid Benefit.

Enable auto upgrade to ensure you're getting the latest updates to the extension each month.

Management modes

Prior to March 2023, the SQL IaaS Agent extension relied on management modes to define the security model, and unlock feature benefits. In March 2023, the extension architecture was updated to remove management modes entirely, instead relying on the principle of least privilege to give customers control over how they want to use the extension on a feature-by-feature basis.

Starting in March 2023, when you first register with the extension, binaries are saved to your virtual machine to provide you with basic functionality such as license management. Once you enable any feature that relies on the agent, the binaries are used to install the SQL IaaS Agent to your virtual machine, and permissions are assigned to the SQL IaaS Agent service as needed by each feature that you enable.

Feature benefits

The SQL Server IaaS Agent extension unlocks a number of feature benefits for managing your SQL Server VM, letting you pick and choose which benefit suits your business needs. When you first register with the extension, the functionality is limited to a few features that don't rely on the SQL IaaS Agent. Once you enable a feature that requires it, the agent is installed to the SQL Server VM.

The following table details the benefits available through the SQL IaaS Agent extension, and whether or not the agent is required:

Feature Description
Portal management Unlocks management in the portal, so that you can view all of your SQL Server VMs in one place, and enable or disable SQL specific features directly from the portal.

Included with basic registration.
Automated backup Automates the scheduling of backups for all databases for either the default instance or a properly installed named instance of SQL Server on the VM. For more information, see Automated backup for SQL Server in Azure virtual machines (Resource Manager).

Requires SQL IaaS Agent extension.
Automated patching Configures a maintenance window during which important Windows and SQL Server security updates to your VM can take place, so you can avoid updates during peak times for your workload. For more information, see Automated patching for SQL Server in Azure virtual machines (Resource Manager).

Requires SQL IaaS Agent extension.
Azure Key Vault integration Enables you to automatically install and configure Azure Key Vault on your SQL Server VM. For more information, see Configure Azure Key Vault integration for SQL Server on Azure Virtual Machines (Resource Manager).

Requires SQL IaaS Agent extension.
Azure Update Manager integration Since Automated Patching doesn't install Cumulative Updates for SQL Server, you can replace it with by integrating Azure Update Manager into your SQL virtual machines resource, which adds improved patching functionality for your SQL Server VMs. This feature is currently in preview.
Flexible licensing Save on cost by seamlessly transitioning from the bring-your-own-license (also known as the Azure Hybrid Benefit) to the pay-as-you-go licensing model and back again.

Included with basic registration.
Flexible version / edition If you decide to change the version or edition of SQL Server, you can update the metadata within the Azure portal without having to redeploy the entire SQL Server VM.

Included with basic registration.
Configure tempdb You can configure your tempdb directly from the Azure portal, such as specifying the number of files, their initial size, their location, and the autogrowth ratio. Restart your SQL Server service for the changes to take effect.

Requires SQL IaaS Agent extension.
Defender for Cloud portal integration If you've enabled Microsoft Defender for SQL, then you can view Defender for Cloud recommendations directly in the SQL virtual machines resource of the Azure portal. See Security best practices to learn more.

Requires SQL IaaS Agent extension.
SQL best practices assessment Enables you to assess the health of your SQL Server VMs using configuration best practices. For more information, see SQL best practices assessment.

Requires SQL IaaS Agent extension.
View disk utilization in portal Allows you to view a graphical representation of the disk utilization of your SQL data files in the Azure portal.

Requires SQL IaaS Agent extension.

Permissions models

By default, the SQL IaaS Agent extension uses the least privilege mode permission model. The least privilege permission model grants the minimum permissions required for each feature that you enable. Each feature that you use is assigned a custom role in SQL Server, and the custom role is only granted permissions that are required to perform actions related to the feature.

SQL Server VMs deployed prior to October 2022 use the older sysadmin model where the SQL IaaS Agent extension takes sysadmin rights by default. For SQL Server VMs provisioned before October 2022, you can enable the least privilege permissions model by going to your SQL virtual machines resource, choosing Security Configuration under Security and then checking the box next to Enable least privilege mode.

To enable the least privilege permissions model, go to your SQL virtual machines resource, choose Security Configuration under Security and then check the box next to Enable least privilege mode:

Screenshot of the Azure portal SQL virtual machines resource, Security Configuration page, enable least privilege highlighted.

The following table defines the permissions and custom roles used by each feature of the extension:

Feature Permissions Custom role (Server / DB)
SQL best practices assessment Server permission - CONTROL SERVER SqlIaaSExtension_Assessment
Automated backups Server permission - CONTROL SERVER
Database permission - db_ddladmin on master, db_backupoperator on msdb
SqlIaaSExtension_AutoBackup
Azure Backup Service sysadmin
Credential management Server permission - CONTROL SERVER SqlIaaSExtension_CredentialMgmt
Availability group portal management sysadmin
R Service Server permission - ALTER SETTINGS SqlIaaSExtension_RService
SQL authentication sysadmin
SQL Server instance settings Server permission - ALTER ANY LOGIN, ALTER SETTINGS SqlIaaSExtension_SqlInstanceSetting
Storage configuration Server permission - ALTER ANY DATABASE SqlIaaSExtension_StorageConfig
Status reporting Server permission - VIEW ANY DEFINITION, VIEW SERVER STATE, ALTER ANY LOGIN, CONNECT SQL SqlIaaSExtension_StatusReporting

Installation

When you register your SQL Server VM with the SQL IaaS Agent extension, binaries are copied to the VM. Once you enable a feature that relies on it, the SQL IaaS Agent extension is installed to the VM and has access to SQL Server. By default, the agent follows the model of least privilege, and only has permissions within SQL Server that are associated with the features that you enable - unless you manually installed SQL Server to the VM yourself, or deployed a SQL Server image from the marketplace prior to October 2022, in which case the agent has sysadmin rights within SQL Server.

Deploying a SQL Server VM Azure Marketplace image through the Azure portal automatically registers the SQL Server VM with the extension. However, if you choose to self-install SQL Server on an Azure virtual machine, or provision an Azure virtual machine from a custom VHD, then you must register your SQL Server VM with the SQL IaaS Agent extension to unlock feature benefits. By default, self-installed Azure VMs with SQL Server 2016 or later are automatically registered with the SQL IaaS Agent extension when detected by the CEIP service. SQL Server VMs not detected by the CEIP should be manually registered.

When you register with the SQL IaaS Agent extension, binaries are copied to the virtual machine, but the agent is not installed by default. The agent will only be installed when you enable one of the SQL IaaS Agent extension features that require it, and the following two services will then run on the virtual machine:

  • Microsoft SQL Server IaaS agent is the main service for the SQL IaaS Agent extension and should run under the Local System account.
  • Microsoft SQL Server IaaS Query Service is a helper service that helps the extension run queries within SQL Server and should run under the NT Service account NT Service\SqlIaaSExtensionQuery.

There are three ways to register with the extension:

Registering your SQL Server VM with the SQL Server IaaS Agent extension creates the SQL virtual machine resource within your subscription, which is a separate resource from the virtual machine resource. Unregistering your SQL Server VM from the extension removes the SQL virtual machine resource from your subscription but won't drop the underlying virtual machine.

Multiple instance support

The SQL IaaS Agent extension only works on virtual machines with multiple instances if there is a default instance. When you register your virtual machine with the SQL IaaS Agent extension, it registers the default instance, and that's the instance you'll be able to manage from the Azure portal.

The SQL IaaS Agent extension does not support virtual machines with multiple named instances if there is no default instance.

Named instance support

The SQL IaaS Agent extension works with a named instance of SQL Server if it's the only SQL Server instance available on the virtual machine. The SQL IaaS Agent extension does not support VMs with multiple named instances.

To use a named instance of SQL Server, deploy an Azure virtual machine, install a single named SQL Server instance to it, and then register it with the SQL IaaS Agent extension.

Alternatively, to use a named instance with an Azure Marketplace SQL Server image, follow these steps:

  1. Deploy a SQL Server VM from Azure Marketplace.
  2. Unregister the SQL Server VM from the SQL IaaS Agent extension.
  3. Uninstall SQL Server completely within the SQL Server VM.
  4. Restart the virtual machine.
  5. Install SQL Server with a named instance within the SQL Server VM.
  6. Restart the virtual machine.
  7. Register the VM with the SQL IaaS Agent Extension.

Failover Clustered Instance support

Registering your SQL Server Failover Clustered Instance (FCI) is supported with limited functionality. Due to the limited functionality, SQL Server FCIs registered with the extension do not support features that require the agent, such as automated backup, patching, and advanced portal management.

If your SQL Server VM has already been registered with the SQL IaaS Agent extension and you've enabled any features that require the agent, you'll need to unregister the SQL Server VM from the extension and register it again after your FCI is installed.

Verify status of extension

Use the Azure portal, Azure PowerShell or the Azure CLI to check the status of the extension.

Verify the extension is installed in the Azure portal.

Go to your Virtual machine resource in the Azure portal (not the SQL virtual machines resource, but the resource for your VM). Select Extensions under Settings. You should see the SqlIaasExtension extension listed, as in the following example:

Screenshot from the Azure portal of the status of the SQL Server IaaS Agent extension.

Limitations

The SQL IaaS Agent extension only supports:

  • SQL Server VMs deployed through the Azure Resource Manager. SQL Server VMs deployed through the classic model aren't supported.
  • SQL Server VMs deployed to the public or Azure Government cloud. Deployments to other private or government clouds aren't supported.
  • SQL Server FCIs with limited functionality. SQL Server FCIs registered with the extension do not support features that require the agent, such as automated backup, patching, and advanced portal management.
  • VMs with a default instance, or a single named instance.
  • If the VM has multiple named instances, then one of the instances must be the default instance to work with the SQL IaaS Agent extension.
  • SQL Server instance images only. The SQL IaaS Agent extension does not support Reporting Services or Analysis services, such as the following images: SQL Server Reporting Services, Power BI Report Server, SQL Server Analysis Services.

Privacy statements

When using SQL Server on Azure VMs and the SQL IaaS Agent extension, consider the following privacy statements:

  • Automatic registration: By default, Azure VMs with SQL Server 2016 or later are automatically registered with the SQL IaaS Agent extension when detected by the CEIP service. Review the SQL Server privacy supplement for more information.

  • Data collection: The SQL IaaS Agent extension collects data for the express purpose of giving customers optional benefits when using SQL Server on Azure Virtual Machines. Microsoft will not use this data for licensing audits without the customer's advance consent. See the SQL Server privacy supplement for more information.

  • In-region data residency: SQL Server on Azure VMs and the SQL IaaS Agent extension don't move or store customer data out of the region in which the VMs are deployed.

To install the SQL Server IaaS extension to SQL Server on Azure VMs, see the articles for Automatic installation, Single VMs, or VMs in bulk. For problem resolution, read Troubleshoot known issues with the extension.

To learn more, review the following articles: