Is it possible to configure the S3 backend so that it prompts for the secret key each time I run terraform apply?
No, this is not supported, but maybe some of the alternatives mentioned at https://www.terraform.io/language/settings/backends/s3#secret_key will be useful?
As far as I can see there are two options, environment variables and writing them to disk?
I set up a custom column in Keepass with the set AWS_ACCESS_KEY_ID= [...] commands, that works quite well.