05 February 2021

Azure SQL Server IaaS Extension re-deployment

By Christian Bolton

Posted in

Azure SQL Server IaaS Extension re-deployment

Local machine account failing to login to SQL Server

We encountered a problem recently with a local machine account failing to login to SQL Server 4 times every minute and filling up the SQL Server errorlog. You’ll recognise the local machine account as [domainname\servername$].

We found the cause to be a failed deployment of the SQL Server IaaS Extension and I wanted to document the process of fixing it for our own benefit as well as for anyone else it might help.

Azure SQL Server IaaS Extension

The failed deployment was confirmed in the Azure portal under Extensions within the Settings of the Virtual Machine:

SQL Server IaaS Extension

The Azure SQL Server IaaS Extension is a free tool to integrate SQL Server deployments running on VMs with the Azure Portal and provides numerous automation benefits you can read about here: https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management?tabs=azure-powershell

Re-deploy the Azure SQL Server IaaS Extension Extension

To re-deploy the extension you need to delete the SQL virtual machine which is poorly named because it’s not the actual Virtual Machine, which is different, it just removes it from the SQL virtual machine view in your portal by removing the extension. Nevertheless, you’ll still be holding your breath when you do it 😊

SQL virtual machine

delete virtual SQL server extension

Re-deploy with PowerShell

Once removed, you can then re-deploy it. Check your license type which will either be Pay As You Go (PAYG) or Azure Hybrid Use Benefit (AHUB) and re-deploy with PowerShell:

Connect-AzAccount

Import-Module Az.SQlVirtualMachine

$vm = Get-AzVM -Name [VM Name] -ResourceGroupName [Resource Group]

New-AzSqlVM -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName
-Location $vm.Location -LicenseType PAYG  -SqlManagementType LightWeight  

This deploys the LightWeight version of the extension which is an online operation and doesn’t interrupt service. You can then enable Full Mode if required, which needs a service restart, during a maintenance window.

Check the Azure SQL Server IaaS Extension of the Virtual Machine

You can confirm it by checking the Extensions of the Virtual Machine again and of course by the fact that there are no more failed logins in SQL Server!

Leave a Comment

Your email address will not be published. Required fields are marked *