How to check female header connection?
The connections to the 8-pin SPI header don't look very nice, but I don't think we can diagnose it from that photo. There are two possibilities- one or more of the pins are not soldered, in which case remelting the solder and adding a bit with flux could fix it. They should be shiny and tapered up from the board (less shiny if you're using lead-free solder). The solder should not look like a ball sitting on top of the board if you look at it from the side, it should look like a volcano or a mesa coming up from the board surface. 
Some diagrams from this site: 

Bad: 



Good: 



If you've damaged the board by scraping it with the iron or lifting a pad, then it will be harder to see, and fixing it would involve jumpering the header to some other place on the board. It's not impossible, but the fact it works when you wiggle the connector would tend to point to the first possibility. 

After you've remelted it with some fresh solder, try cleaning the board with pure IPA (pure isopropanol, not low purity rubbing alcohol) and a (dedicated) toothbrush and it will be easier to see what is going on.