I finally got this fixed and I just want to post a quick update to help anyone who has the same problem.
So, in my 2006 Impala, the front sunroof drain tubes run down the windshield pillars to the bottom of the A-pillars and are not connected in any way to the door jamb. They just drain into the bottoms of the A-pillars. This is contrary to the factory service manual, which says that they should exit the A-pillar into the door jamb somewhere between the hinges. On my car, there is no hole for this to happen, so I assume the factory service manual is wrong. You can see the ends through two access holes:
Passenger side: Pull up the front carpet retainer trim (the plastic moulding at the bottom of the door frame that includes the cabin fuse box door). Then, release and pull out the fuse panel assembly to expose a big hole in the sheet metal at the bottom of the A-pillar. This is your window to see the black drain tube coming into the bottom of the A-pillar.
Driver's side: Pull up the front carpet retainer trim (the plastic moulding at the bottom of the door frame that includes an opening for the hood release lever). If you pull back a bit of carpet and insulation right behind the hood release lever, there is a small opening that lets you see into the bottom of the A-pillar, where the black drain tube terminates.
In my situation, the drain tube on the driver's side was not clogged. However, water would accumulate in the bottom of the A-pillar and not drain, preventing any additional water from draining through the front driver's side drain tube, leading to water leaking into the car through the sunroof opening. On the passenger side, I could see water pour out of the A-pillar through openings in the pinch weld/rocker panel.
Here's what I did to fix this:
For a better view of what I was doing, I removed the front fender liner, but this may not be necessary. I took a long Allen wrench (any sufficiently long, sturdy poking device will do) and stuck it in the drain opening where the pinch weld meets the front fender liner (the plastic that lines the wheel well). I tried poking it in at different angles until I found an angle (nearly straight upwards) where it went in fairly deep and I felt it pushing on some foam. This foam is body sealer. The A-pillars rely on gaps in the body sealer to drain, but if they become clogged or if there's too much body sealer from the factory, they won't be able to drain. I continued to push into the foam until I pierced it/removed the clogging debris and water starting running out. The sunroof now drains properly!