You might need to pull the rear quarter trim and partially drop the headlining to check the drain pipe hasn't detached itself from the sunroof subframe, or split.
They drain out below the tail lights, behind the bumper, so it seems likely the suspect area is where the pipe attaches to the subframe.
Anything involving trim removal will require pre-arming yourself with replacement clips, otherwise the removed trims will rattle forever more.