What I don't get is why this is grounds for a resignation. Is there some sort of special security issue that supersedes his private life?
Being in the military he has no private life; he's in violation of the UCMJ. Also, because of his rank and position there are security issues.
But I'm guessing the real reason for the investigation is that he pissed the wrong person off, or it probably would have been swept under the rug, so to speak.
CIA is civillian not military, so he is under civillian law. At least currently.
The affair, however, may have pre-dated his current apointment.
He retired from the military last year.
Being head of the CIA qualifies as a special security issue.
Yes. For his security clearance he was required to self-report any adverse information, which includes an affair. The FBI uncovered the affair, so he probably did not self-report. That the FBI discovered it probably made the situation even worse.