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:
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 !
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!
