In Part 1 we built an immutable container. In Part 2 we gave the SQL Server 2025 VM a Managed Identity and Microsoft Entra authentication. This is where it comes together. We’ll keep the traffic between SQL Server and storage on the private network, lock down who can reach the storage account, give the VM permission to write, and then take a backup and try to delete it.
What’s in this environment
This post touches most of the components, so here’s the full list again.
| 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 |
| Blob endpoint | 2025immutablebackups.blob.core.windows.net |
| Backup container | sqlbackups |
| Private endpoint | 2025-blob-pe |
| Private DNS zone | privatelink.blob.core.windows.net |
| Role on the container | Storage Blob Data Contributor |
| SQL Credential | https://2025immutablebackups.blob.core.windows.net/sqlbackups |
| Management tool | SSMS 22 |
The private endpoint
We want the storage account to resolve to a private IP address inside our VNet rather than going out over the public endpoint. A private endpoint does that. Check whether you already have one:
- Open the storage account from Part 1
- Expand Security + networking and select Networking
- Click Private endpoints
If there isn’t one, create it:
- Click Create private endpoint
- Give it a sensible name
- Set Target sub-resource to blob
- On the Virtual Network tab, make sure it’s the same VNet and subnet the SQL VM is on, and keep the other defaults
- Keep the DNS defaults so it integrates with a private DNS zone
Check what it created
Back on the storage account, under Security + networking > Networking > Private endpoints, you should now see it.
Open it, go to Settings > DNS configuration, and note the IP address, FQDN and private DNS zone. You’ll use them shortly.
Now open the private DNS zone and confirm there’s an A record for the storage account pointing at that same IP address. Then check Virtual network links and make sure the zone is linked to the VNet the VM is on.
That link is easy to miss. Without it, everything looks correct in the portal and the VM carries on resolving the public address.
Lock down the storage firewall
- On the storage account, expand Security + networking and select Networking
- On the Public access tab, Public network access is probably set to Enabled from all networks. Change it to selected networks.
- You don’t need to add the VM’s VNet here, because it’s coming in through the private endpoint
- Leave Allow trusted Microsoft services to access this storage account ticked
- Add your own client IP address, so you can still browse the container from the portal later
- Click Save
Test it from the VM
Again, looking right isn’t the same as working. Log on to the VM and open PowerShell as administrator.
You need the primary endpoint for the blob service, from Storage account > Settings > Endpoints. For the first two tests, drop the `https://` at the front and the trailing `/`.
Flush DNS and resolve the name:
ipconfig /flushdns
Resolve-DnsName 2025immutablebackups.blob.core.windows.net
It should resolve through `privatelink.blob.core.windows.net` to the private endpoint IP you noted earlier. If you get a public IP address back, go back and check the DNS zone and the VNet link.
Test the port:
Test-NetConnection 2025immutablebackups.blob.core.windows.net -Port 443
Then an HTTPS request against the container:
curl.exe -s -o NUL -w "%{http_code}`n" "https://2025immutablebackups.blob.core.windows.net/sqlbackups?restype=container"
This request isn’t authenticated, so a 4xx response such as 403 or 404 is fine. It means you reached the storage service and it answered. What you don’t want is a timeout or `000`. If that’s what you get, stop and work out why before going any further, because nothing after this point will work either.
The NSG on the VM’s NIC
On the VM:
- Expand Networking and select Network settings
- Expand Outbound port rules
Make sure nothing is blocking outbound 443 to the private endpoint’s IP address, or to Entra ID and Microsoft Graph from Part 2.
Give the VM permission to write
The identity exists. Now it needs rights on the container, and only on the container.
- On the storage account, expand Data storage and select Containers
- Open sqlbackups
- Select Access Control (IAM)
- Click + Add > Add role assignment
- Under Job function roles, search for Storage Blob Data Contributor, select it and click Next
- On Members, set Assign access to as Managed identity
- Click Select members, find the VM and select it
- Click Review + assign, then again, and wait for it to finish
While you’re there, give yourself **Storage Blob Data Reader** or Contributor. If you later turn off storage account key access, which is a sensible next step once this is working, that role is what lets you keep browsing the blobs in the portal.
Create the credential
Connect to the instance in SSMS 22 on the VM. The credential name has to be the URL of the container, and the identity is just the words `Managed Identity`. There’s no secret in it at all, which is the point.
USE master;
GO
-- Remove any old SAS credential with the same name first
IF EXISTS (SELECT 1
FROM sys.credentials
WHERE name = 'https://2025immutablebackups.blob.core.windows.net/sqlbackups')
DROP CREDENTIAL [https://2025immutablebackups.blob.core.windows.net/sqlbackups];
GO
CREATE CREDENTIAL [https://2025immutablebackups.blob.core.windows.net/sqlbackups]
WITH IDENTITY = 'Managed Identity';
GO
SELECT name, credential_identity, create_date
FROM sys.credentials;
Take a backup
DECLARE @url nvarchar(400) =
N'https://2025immutablebackups.blob.core.windows.net/sqlbackups/'
+ N'MyDatabase_FULL_' + FORMAT(SYSDATETIME(), 'yyyyMMdd_HHmmss') + N'.bak';
BACKUP DATABASE [MyDatabase]
TO URL = @url
WITH COMPRESSION, CHECKSUM, STATS = 10;
One thing that will catch people out: in an immutable container you can’t overwrite a file. Every backup needs a unique name, which is what the timestamp is for. If you have jobs that write to the same file every night with `FORMAT` or `INIT`, they’ll fail. That isn’t a fault. It’s the immutability doing its job.
Now try to delete it
Browse to the container in the portal and delete the backup file you just took. Azure refuses. The blob is protected by the immutability policy and there’s nothing you can do about it until the retention period passes. That’s the result we’ve been working towards for three posts. Ransomware can’t delete it, and neither can anyone else.
Locking the policy
At this point you can lock the immutability policy. Once it’s locked, the retention period can be extended but never reduced, and the container stays until every backup in it has aged out. To lock it you adjust the container’s access policy and lock it. I’m deliberately not walking through the clicks, because it would be far too easy to follow along and lock it without thinking. Make sure the retention period is the one you actually want, your backups are working, and you’ve restored from them. Then make that decision on purpose.
The bit that still matters
An immutable backup protects you from somebody deleting your backups. It doesn’t tell you whether the backup is any good, and it doesn’t tell you whether you can restore it in the time the business needs. So restore it somewhere. Time it. Run integrity checks on the databases you’re backing up, because an immutable copy of a corrupt database is still a corrupt database. The day you need this is not the day to find out.
—
Warwick Rudd is a Microsoft Certified Master and Data Platform MVP, and the founder of SQL Masters Consulting. If you’d like help putting immutable backups in place, or checking your backups would actually get you through a bad day: Backup, Recovery & Data Integrity
Leave a Reply