| title | What is the SQL Server IaaS Agent extension? (Windows) |
|---|---|
| description | This article describes how the SQL Server IaaS Agent extension helps automate management specific administration tasks of SQL Server on Azure Windows VMs. These include features such as automated backup, automated patching, Azure Key Vault integration, licensing management, storage configuration, and central management of all SQL Server VM instances. |
| author | dplessMSFT |
| ms.author | dpless |
| ms.reviewer | mathoma |
| ms.date | 12/19/2024 |
| ms.service | azure-vm-sql-server |
| ms.subservice | management |
| ms.topic | concept-article |
| tags | azure-resource-manager |
[!INCLUDE appliesto-sqlvm]
[!div class="op_single_selector"]
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 SQL Server on Azure VM deployment and management experience, watch the following Data Exposed videos:
- Automate Management with the SQL Server IaaS Agent extension
- New and Improved SQL on Azure VM deployment and management experience.
[!INCLUDE sql-vm-deployment-failure]
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.
Get-AzSqlVM | Where-Object {$_.LicenseType -eq 'AHUB'}$ az sql vm list --query "[?sqlServerLicenseType=='AHUB']"
Enable auto upgrade to ensure you're getting the latest updates to the extension each month.
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:
[!INCLUDE SQL VM feature benefits]
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.
The following table defines the SQL Server permissions and custom roles used by each feature of the extension:
| Feature | Permissions | Custom role (Server / DB) |
|---|---|---|
| Automated backups | Server permission - CONTROL SERVER Database permission - db_ddladmin on master, db_backupoperator on msdb |
SqlIaaSExtension_AutoBackup |
| Availability group portal management | sysadmin |
|
| Azure Backup Service | sysadmin to account NT SERVICE\AzureWLBackupPluginSvc |
|
| Credential management | Server permission - CONTROL SERVER | SqlIaaSExtension_CredentialMgmt |
| I/O related best practices | Server permission - CONTROL SERVER | SqlIaaSExtension_ThrottlingAssessment |
| SQL best practices assessment | Server permission - CONTROL SERVER | SqlIaaSExtension_Assessment |
| 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 |
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 manually.
Note
The option to enable least privilege mode is only available for SQL Server VMs provisioned before October 2022. If this option is not visible in your environment, it's because your SQL Server VM already has least privilege mode enabled by default.
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:
:::image type="content" source="media/sql-server-iaas-agent-extension-automate-management/least-privilege.png" alt-text="Screenshot of the Azure portal SQL virtual machines resource, Security Configuration page, enable least privilege highlighted.":::
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 isn't 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:
- Automatically for all current and future VMs in a subscription
- Manually for a single VM
- Manually for multiple VMs in bulk
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. Deleting the extension from your SQL Server VM removes the SQL virtual machine resource from your subscription but doesn't drop the underlying virtual machine.
The SQL IaaS Agent extension supports the following environments:
- One default instance.
- If there are multiple instances, only the default instance is supported and managed by the extension in the Azure portal. Environments with multiple named instances without a default instance aren't supported.
- One named instance, if it's the only installed instance.
If you want to manage a single named instance in the Azure portal, install SQL Server with a nondefault name to an Azure virtual machine and then register it with the SQL IaaS Agent extension.
If you want to manage a single named instance in a SQL Server image from Azure Marketplace, you must first uninstall the existing SQL Server instance, install SQL Server with a named instance, and then register it with the SQL IaaS Agent extension.
To use a single named instance with SQL Server on Azure VMs, follow these steps:
- Deploy a SQL Server VM from Azure Marketplace.
- Delete the SQL IaaS Agent extension from the SQL Server VM.
- Connect to the virtual machine and uninstall SQL Server completely.
- Restart the virtual machine.
- Connect to the virtual machine and then use the setup media (typically located in
C:\SQLServerFull) to install a named SQL Server instance. - Restart the virtual machine.
- Register the VM with the SQL IaaS Agent Extension.
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 don't support features that require the agent, such as automated backup, patching, Microsoft Entra authentication 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 delete the extension from the SQL Server VM and register it again after your FCI is installed.
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:
:::image type="content" source="./media/sql-server-iaas-agent-extension-automate-management/azure-rm-sql-server-iaas-agent-portal.png" alt-text="Screenshot from the Azure portal of the status of the SQL Server IaaS Agent extension.":::
You can also use the Get-AzVMSqlServerExtension Azure PowerShell cmdlet:
Get-AzVMSqlServerExtension -VMName "vmname" -ResourceGroupName "resourcegroupname"
The previous command confirms that the agent is installed and provides general status information. You can get specific status information about automated backup and patching by using the following commands:
$sqlext = Get-AzVMSqlServerExtension -VMName "vmname" -ResourceGroupName "resourcegroupname"
$sqlext.AutoPatchingSettings
$sqlext.AutoBackupSettings
It's not currently possible to check the status of the extension by using the Azure CLI.
Before 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.
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 cloud, Azure Government cloud, and 21Vianet (Azure in China). Deployments to other private or government clouds aren't supported.
- TCP/IP must be enabled in SQL Server Configuration Manager and for the VM for the extension to work with your SQL Server on Azure VMs.
- SQL Server FCIs with limited functionality. SQL Server FCIs registered with the extension don't 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 when no default instance is present.
- 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 doesn't support Reporting Services or Analysis services, such as the following images: SQL Server Reporting Services, Power BI Report Server, SQL Server Analysis Services.
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: