다음을 통해 공유

Automated backups configuration fails when configured from Azure portal

  • 아티클

In this post, we would like to explain one of the interesting issues that we encountered while using the automated backup feature for a VM from the azure portal (We can find the option once we click on VM > SQL Server Configuration> Automated Backup)

Symptoms

Cannot Configure Automated backups in azure VM from the portal which was created on ARM (Azure Resource Manager Model). It fails with the following error

• TYPE Microsoft.Compute/virtualMachines/extensions • RESOURCE ID /subscriptions/6c28b945-6d98-403d-8936-5e658f228a0f/resourceGroups/Group/providers/Microsoft.Compute/virtualMachines/LTO-CT-SQL/extensions/SqlIaasExtension • STATUSMESSAGE { "status": "Failed", "error": { "code": "ResourceDeploymentFailure", "message": "The resource operation completed with terminal provisioning state 'Failed'.", "details": [ { "code": "VMExtensionHandlerNonTransientError", "message": "Handler 'Microsoft.SqlServer.Management.SqlIaaSAgent' has reported failure for VM Extension 'SqlIaasExtension' with terminal error code '1009' and error message: 'Enable failed for plugin (name: Microsoft.SqlServer.Management.SqlIaaSAgent, version 1.2.10.0) with exception Command C:\\Packages\\Plugins\\Microsoft.SqlServer.Management.SqlIaaSAgent\\1.2.10.0\\enable.cmd of Microsoft.SqlServer.Management.SqlIaaSAgent has exited with Exit code: 255'" } ] } } • RESOURCE LTO-CT-SQL/SqlIaasExtension • OPERATION ID B3B967D4EF42741A

Cause

SQL IAAS Agent Service was disabled and dint starts due to insufficient permissions.

Resolution

We can reproduce the issue by the following method.

We deployed a VM on our end and navigated to VM > SQL Server Configuration> Automated Backup and this failed with a similar error:

automatedbackup

Error", "message": "Handler 'Microsoft.SqlServer.Management.SqlIaaSAgent' has reported failure for VM Extension 'SqlIaasExtension' with terminal error code '1009' and error message: 'Enable failed for plugin (name: Microsoft.SqlServer.Management.SqlIaaSAgent, version 1.2.10.0) with exception statusMessage:{"status":"Failed","error":{"code":"ResourceDeploymentFailure","message":"The resource operation completed with terminal provisioning state 'Failed'.","details":[{"code":"VMExtensionHandlerNonTransientError","message":"Handler 'Microsoft.SqlServer.Management.SqlIaaSAgent' has reported failure for VM Extension 'SqlIaasExtension' with terminal error code '1009' and error message: 'Enable failed for plugin (name: Microsoft.SqlServer.Management.SqlIaaSAgent, version 1.2.10.0) with exception Command C:\\Packages\\Plugins\\Microsoft.SqlServer.Management.SqlIaaSAgent\\1.2.10.0\\enable.cmd of Microsoft.SqlServer.Management.SqlIaaSAgent has exited with Exit code: -532462766'"}]}}

We then went to the VM and checked the event viewer application and system logs and found the below errors:

The Microsoft SQL Server IaaS Agent service failed to start due to the following error: The service did not start due to a logon failure.

The SQLIaaSExtension service was unable to log on as NT Service\SQLIaaSExtension with the currently configured password due to the following error:
Logon failure: the user has not been granted the requested logon type at this computer.

Service: SQLIaaSExtension
Domain and account: NT Service\SQLIaaSExtension

This service account does not have the required user right "Log on as a service."

The above clearly indicates that SQLIAASEXTENSION account needs to have the permissions in security policy.

We went to Run> Secpol.msc> Under Security settings looked for Local Policies > User Rights Assignment > Log on as a service (In right pane) >Right click and go to its properties and this account with Admin permissions.

secpol-msc

We then again tried to create automated backup and dint see the error in event viewer.
Now to figure out where is this account used, we looked at services.msc and found the account is used by Microsoft SQL IAAS Agent service.
We saw the service is in a stopped state.

services-msc

Now researching on this, we found SQL Server IAAS Agent service can help us to automate some administrative tasks, for example run jobs, monitors SQL Server, and processes alerts. When we enable Automated Backup on virtual machine, the extension will be automatically installed but somehow it didn’t start in our scenario due to the account permission issues.

We started the service and then tried to configure the automated backups from the azure portal and saw it completed successfully without any errors. In case if it still fails even after that then the next step is to Look at the locations C:\WindowsAzure\Logs and C:\Packages\Plugins for any errors in the IAAS VM.

More Information:

We require the Microsoft SQL IAAS Agent service in running state for enabling automated backups and its functioning. When we enable Automated Backup on our virtual machine, the extension will be automatically installed.
Automated Backup automatically configures Managed Backup to Microsoft Azure for all existing and new databases on an Azure VM running SQL Server 2014 Standard or Enterprise. This enables us to configure regular database backups that utilize durable Azure blob storage. Automated Backup depends on the SQL Server IaaS Agent Extension.

More information on Automated Backups: https://azure.microsoft.com/en-in/documentation/articles/virtual-machines-windows-sql-automated-backup/

More information on IAAS Agent Service: https://azure.microsoft.com/en-in/documentation/articles/virtual-machines-windows-sql-server-agent-extension/

 

Written by:
Ujjwal Patel, Support Engineer, SQL Server Support

Reviewed by:
Raghavendra Srinivasan, Sr. Support Engineer, SQL Server Support