In Part 1 we built somewhere safe to put the backups: a storage account with a delete lock and a container with a time-based retention policy.
The next problem is getting SQL Server to write to it without giving it a password. That’s what the Managed Identity is for. The VM gets an identity in Microsoft Entra ID, SQL Server uses it to authenticate to storage, and there’s no SAS token or storage key anywhere to steal, rotate or forget about.
There are a few moving parts to get there, and most of the time spent on this is in Entra ID and networking rather than SQL Server.
What’s in this environment
These are the names I use throughout the series. Swap in your own as you go.
| Component | Name used in this series |
|---|---|
| SQL Server | SQL Server 2025 on an Azure VM |
| Resource group | 2025-Immutable-Backups |
| Virtual machine | sql2025 |
| Virtual network | vnet-australiaeast-1 |
| subnet | snet-australiaeast-1 |
| Storage account | 2025immutablebackups |
| Backup container | sqlbackups |
| Managed Identity | System-assigned on our virtual machine |
| Entra ID role | Directory Readers |
Check the SQL IaaS Agent extension
On the virtual machine, expand Settings and select Extensions + applications. You’re looking for SqlIaasExtension. Without it, the SQL-specific settings later in this post won’t be there.
Turn on the system-assigned Managed Identity
It has to be a system-assigned identity.
- In the portal, open the VM and go to Security > Identity
- On the System assigned tab, set Status to On if it isn’t already
- Click Save
Note down the Object (principal) ID. You’ll need it later. You can confirm it exists in Entra ID if you want to see it for yourself:
- Go to Microsoft Entra ID
- Expand Manage and select Enterprise applications
- Filter the application type to Managed Identities
- Search for your VM
Grant it Directory Readers
For Microsoft Entra authentication to work on the VM, the identity needs to be able to read from Microsoft Graph. The Directory Readers role gives it that.
- In Microsoft Entra ID, under Manage, select Roles and administrators
- Find and open Directory Readers
- Click Add assignments and find your VM
- Click Add
This is the step that needs Privileged Role Administrator or Global Administrator. If that isn’t you, this is where you go and find the person who has it.
Enable Microsoft Entra authentication on the SQL VM
This setting lives on the SQL virtual machine resource, not the plain VM.
- Open the SQL virtual machine in the portal
- Expand Security and select Security configuration
- Enable Microsoft Entra authentication
- In the Managed identity type drop-down, select System-assigned managed identity
- Click Apply
Wait for the confirmation that it’s finished before moving on. It takes a little while, and moving on too early just gives you a confusing failure later.
Make sure the VM can get out
The VM needs outbound access on TCP 443 and 80 to Microsoft Entra ID and Microsoft Graph. How much work this is depends entirely on how tightly you’ve locked down outbound traffic.
The subnet
Open the virtual network the VM sits on. In this series it’s in the same resource group.
- Expand Settings and select Subnets
- Open the relevant subnet
If Enable private subnet (no default outbound access) isn’t ticked, the VM is using Azure’s default outbound access and can already get out. In production I’d be looking to replace that with a NAT gateway so outbound traffic is something you’ve designed rather than something you’ve inherited. For this series we’re leaving the default in place.
The NSG rules
Back on the VM:
- Expand Networking and select Network settings
- Open Effective security rules
- Check AllowInternetOutBound is there at priority 65001, and that nothing at a higher priority is blocking outbound 443
Prove it works
Configuration that looks right isn’t the same as configuration that works, so test it. Start in the portal:
- On the VM, expand Help and select Connection troubleshoot
- Change the destination to Specify manually
- Enter `login.microsoftonline.com` as the URI
- Set the destination port to 443
- Click Run diagnostic tests
You want Reachable before you go any further.
Then log on to the VM and check from the inside, because that’s where SQL Server will actually be making the connection:
'login.microsoftonline.com','graph.microsoft.com' | ForEach-Object {
Test-NetConnection $_ -Port 443 |
Select-Object ComputerName, RemoteAddress, TcpTestSucceeded
}
Both should come back with `TcpTestSucceeded` as `True`.
Where this leaves us
The VM now has the SQL IaaS Agent extension, a system-assigned Managed Identity with Directory Readers, Microsoft Entra authentication enabled, and confirmed outbound access to Entra ID and Microsoft Graph.
It has an identity. It doesn’t yet have permission to do anything with the storage account, and it’s still reaching that storage account the long way round. Part 3 sorts out both, and ends with a backup nobody can delete.
—
Warwick Rudd is a Microsoft Certified Master and Data Platform MVP, and the founder of SQL Masters Consulting. If you’d like a hand with this in your environment: Contact Us.
Leave a Reply