5 February 2021

Azure SQL Server IaaS Extension re-deployment

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:

Login failed for user ‘[DOMAIN\servername]$’. Reason: Could not find a login matching the name provided.

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:

Install-Module Az.Accounts
Connect-AzAccount

Install-Module Az.Compute
$vm = Get-AzVM -Name [VM Name] -ResourceGroupName [Resource Group]

Install-Module Az.SqlVirtualMachine
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!

Share:

Let’s Talk

Have a SQL challenge? We can help.

"*" indicates required fields

This field is for validation purposes and should be left unchanged.