I've recently had this problem with my Audi A4 B8 Avant with Panoramic Sunroof. The water was finding its way into both footwells inside passenger side and driver side, dripping down behind the fuse boxes.
I used some strimmer wire and loaded it down into the drain holes to make sure they weren't blocked, flushed with hot soapy water.
Whilst the water was then running clear and through the drainage ports on the ourside of the door frame, I still had water coming into the passenger cabin.
I dropped the pillar and roof lining off at the front (pillar airbags) on each side of the front and managed to gain access to the blue hose-type drain pipe with white connector that connects to the black cassette drainage channels at the top. These were leaking and letting water past, probably because the fit has failed over time. This was the case for both passenger and driver sides front.
The water then runs on the inside of the head lining down through the airbag side pillar liner and past the fuse boxes into the footwells.
For now, I've used some silicon sealant to seal off the connection between white/blue pipe grommet to the black cassette drain port. It's worked on one side and it remains dry for now. Just waiting for the other side silicon to dry.
I also had water in the trunk where the battery sits underneath the spare wheel, so the rear drainage hoses will probably have the same issue.
The fit of the white end grommets from the blue drainage hose to the drainage ports isn't at all tight and I can quite easily see how this would let water through/out, especially if they are clogged.
First port of call for anyone with leakage problems is to use strimmer wire and unblock those hoses, then check out the fit of those white grommets.