Part 3 of 6. The series: 1. The plan and the lab · 2. Check what is there · 3. Export what the target needs (this post) · 4. Back up and upload · 5. Restore · 6. Check post-migration, and clean up.
Part 2 ended with a VM that passed all ten gates: it can take the database. This post prepares the two things a backup does not carry on its own.
- The TDE certificate. An encrypted backup restores only on a server that has the certificate it was encrypted with. If you lose the certificate… just take a wild guess of what happens when you try to restore the DB.
- The SQL login. The user inside the database comes across with the restore; the login it belongs to lives in master on the laptop and does not.
I will not go into too much detail here, but without the login you will end up with orphaned users – it can be fixed, but it is extra work.
AdventureWorks2025 uses neither out of the box. I switched TDE on and created the SQL login on purpose, because a real migration almost always has both (or they should – check Azure SQL and Azure MI, they do come with TDE enabled by default), and both are where migrations go wrong.
This is the first part that changes the laptop. Both scripts follow the rules from part 1: -WhatIf first, safe to run again, and no password is ever shown, typed or written to a file. Every password goes into Key Vault before anything is protected with it.
Part C. Export what the target needs
C1. TDE on the laptop: script 20
20_Enable-SourceTde.ps1 does six things, in this order, and each one only if it is missing:
- A locked key folder,
D:\TDE-Keys: Administrators and SYSTEM get full control, the SQL Server service gets modify (it writes the backup of the certificate), nobody else gets in.
Why a locked key folder? Well, my sad experiences are telling me that there are way too many hands with privileges in older on-prem boxes: the architect because why not, the lead dev because of that pesky folder where we need to do stuff by hand, the sys admin, the DBA, you name it. It is nobody’s fault, it is how things used to run 20 years ago. Locking down the folder reduces the chance that anyone touches sensitive data. - The database master key in master. Its password is generated in memory, stored in Key Vault as
aw2025-source-dmk-password, and only then used. - The TDE certificate,
AW2025_TDE, in master. - The certificate and its private key, backed up to the key folder (
.cerand.pvk), with a new password that goes to Key Vault first, asaw2025-pvk-password. Then the proof: the two files are loaded back as a temporary certificate and its thumbprint compared with the real one. A backup of a certificate that nobody ever opened is a hope, not a backup. - The database encryption key (AES_256, protected by the certificate), then
ENCRYPTION ON, and it waits until the database reports encrypted. - FULL recovery, so part D can take log backups. The log chain itself starts with the first FULL backup.
[laptop, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server, signed in to Azure (B1), with Key Vault Secrets Officer on the vault (01 gave it to you in A3):
.\20_Enable-SourceTde.ps1 -KeyVault aw2025-kv-1234 -WhatIf.\20_Enable-SourceTde.ps1 -KeyVault aw2025-kv-1234
On AdventureWorks2025 the encryption itself takes seconds. The script waits up to ten minutes; a real database can take hours, while it stays online.
The key files stay in the locked folder on the laptop, go to Blob storage with the backups in part E, and never into a repository, an e-mail or a ticket. I know it sounds like wishful thinking, but it can be done.
tempdb gets encrypted too. The moment one database on an instance uses TDE, SQL Server encrypts tempdb as well, for every database on that instance. On a laptop nobody notices; on a busy shared server, measure before and after.
A source that already uses TDE. The script keeps an existing master key as it is. If the database is already encrypted with a certificate in master, run it with
-CertName <that certificate>: it backs that certificate up again with a new password and proves it, and leaves the database alone. It stops, before changing anything, if you name the wrong certificate, and it does not cover TDE with a key in Azure Key Vault (EKM). It also assumes the default instance,MSSQLSERVER.
Mine ran in under a minute: key folder locked, master key and certificate created, the backup proved, AdventureWorks2025 encrypted, SIMPLE switched to FULL.
Expected: a CHECK table of six lines, all OK: the master key in master, the certificate with its thumbprint, both key files in the folder, the secret in Key Vault, the database encrypted with that certificate, the database in FULL recovery. Then All OK. AdventureWorks2025 is encrypted with AW2025_TDE (<thumbprint>); key files in D:\TDE-Keys, password in Key Vault as aw2025-pvk-password. Next: 21_Export-SourceLogins.ps1. Write the thumbprint down: part F compares it with the one in the backup.
C2. The SQL logins, with SID and password hash: script 21
A database user is tied to its login by the SID, not by the name. Create a login with the same name on the VM and the restored user still will not map to it: different SID, orphaned user, the classic ALTER USER ... WITH LOGIN clean-up afterwards. Create it with the same SID, and the user maps on its own, with no fix-up at all. Add the password hash, and the login keeps its password without anyone knowing it, typing it or resetting it. That is what sp_help_revlogin has done for years; 21_Export-SourceLogins.ps1 does the same in PowerShell.
For each SQL login that a user of AdventureWorks2025 maps to, it exports: the name, the SID, the password hash, the default database and language, the password policy and expiration flags, whether it is disabled, its server roles and its server-level permissions. All of it goes into one Key Vault secret, aw2025-logins, as JSON. Nothing is written to disk.
Word of caution here: permissions inside the database (say, EXECUTE on the stored procedures of one schema) come across with the restore; what lives outside it does not. Permissions in msdb or master (SQL Agent roles, proxies, objects in master) and anything granted to a login that is not exported need their own scripts.
[laptop, PowerShell as administrator] Same login and sign-in as C1:
.\21_Export-SourceLogins.ps1 -KeyVault aw2025-kv-1234 -WhatIf.\21_Export-SourceLogins.ps1 -KeyVault aw2025-kv-1234
Why Key Vault and not Blob. A password hash is not a password, but it is a secret: it can be attacked offline, at leisure. So it goes where the passwords go, and the VM reads it in part F with its own identity (Key Vault Secrets User). Run the script again later and the secret simply gets a new version.
Windows logins stay behind. A Windows account of the laptop (or of a domain the VM is not in) cannot exist on the VM, so
21lists Windows logins but does not export them.41reports them again in part F, and you decide what each one comes on the target. Even for machines in the same domain you can have a ton of Windows logins for people who left the company 10 – 15 years ago.
Mine found one SQL login, AW2025Migration (default database master, policy and expiration off, no server roles or server-level permissions), and no Windows logins mapped to the database.
Expected: a CHECK table of three lines, all OK: the secret is readable, it holds every SQL login with its SID and hash, and there are no Windows logins to decide on. Then All OK. Next: 30_Backup-Source.ps1.
Two words on the CHECK tables. Most scripts mark a line that needs a look with CHECK. A few, the ones that build things (
00,01and20), say MISSING instead: something that should exist does not. Either way, anything but OK means stop and read the steps above it.
Check the scripts before you run them
The scripts of this post, from the v1.0 release (immutable: nobody, me included, can change its files or its tag). On the laptop:
Get-FileHash C:\AW2025Migration\scripts\2*.ps1 -Algorithm SHA256 | Format-Table Hash, Path -AutoSize
Every hash must match its line below; SHA256SUMS.txt on the release has all of them. If one differs, don’t run it.
| File | SHA-256 |
|---|---|
aw2025-azure-migration-v1.0.zip | 0db411127783f4f2c604211d8293b2854a7776b0289d54b57344890e73c45d4f |
20_Enable-SourceTde.ps1 | e00a4966e09289a046984882a152ab3bcc93251b6f4eae85ffc168f02ecf51dd |
21_Export-SourceLogins.ps1 | 589a7edb0cf9d8453bd38c16277940e1deb05b02a155455afc10190ee8201949 |
Next
Part 4 covers parts D and E. Script 30 takes the FULL and LOG backups, checksum-ed and verified, and 31 records the facts of the database right after the last one: row counts, checksums, objects, settings, the hash of the code. Part G holds the VM against those facts. Then 32 uploads the backups, the facts, the key files and the inventory to Blob storage.
The scripts are on GitHub: calinsqlplay/aw2025-azure-migration (the version these posts use is the v1.0 release).