I was able to make it work in 2025.

The process:

1. No need to sign out of your main iCloud Account

2. First, download the app normally on Test Flight (you need your main account for this, the sandbox one won't work and attempting to use it will display the error message you saw)

3. Then, go to Settings > your iCloud account on top with the picture > Media & Purchases > Sign Out. This is like a small, temporary signing out

4. If you haven't already, sign into the Sandbox account in Settings > Developer (at the bottom), you need to have developer mode active on your device. Then scroll down near the bottom to "Sandbox Apple Account".

5. Now you can open the app you previously downloaded from Test Flight and it will use your sandbox account instead of your main one. But you won't be able to open Test Flight until you log back in with your main account, and then you need to repeat the entire process.

It's all described here: https://developer.apple.com/documentation/storekit/testing-in-app-purchases-with-sandbox