The sentence is grammatically correct, but few native speakers would ever use it, because it looks confusing.
It is correct with for consecutive "hads" because "had" is a very versatile word. The sentence means "All the faith he hadpossessed in the past hadproduced no effect on the outcome of his life." And that's probably more or less how we would write it.
The number of repeated words in English by no means correlates to the grammatical correctness of the sentence. Here we have "All the faith [that he had had] had had no effect."
We sometimes make sentences like this for no reason, besides playing with the flexibility of our language and because repeated words are amusing or interesting. This sentence is hard to understand, because the person who wrote the sentence was more interested in repeating the word "had" than in expressing a thought in a natural or easily comprehensible manner.
Haven't you come across this? It's quite sweet, really, but of course extremely contrived. More so than the five 'and's example (a chip shop owner complaining to the sign writer that the space between "fish" and "and" and "and" and "chips" isn't the same). I could make it thirteen if I mis-spell a name.
Two pupils, Abdul and Haddad, wrote equivalent sentences, but one of them wrote the simple past "had" where the other use past perfect "had had". Their teacher thought the past perfect more appropriate. Moreover, the whole story is told in a way that makes past perfect appropriate for narrating this part.
Abdul, where Haddad had had "had", had had "had had". "Had had" had had the teacher's approval.
No, it isn't "past perfect of past perfect", the two "had"s are simply one past perfect. The other two make a second past perfect.
Two of the four "had"s are what you call helping verbs, the other two are participles of the main verbs.
What makes things confusing is that the main verb is the same as the helper.
See Florentia's explanation in #11. The first main verb is "have" in the sense of "possess", the second is part of "have no effect" meaning "make no difference", so in Florentia's version the two remaining "had"s are helpers which make the two past perfects "had possessed" and "had made no difference".
Remember that the original example quoted in #1 is not entirely serious. It's only an attempt to see how many "had"s you can string together with it still making some kind of sense.