It's not his military status that makes the affair dangerous, but his status as the nation's top intelligence official. Moreover, Mr. Petraeus was not fired per se. He determined that it was difficult for him to continue on in that role given the findings.

I assume adultery is still an offense in the military, regardless of orientation.

As noted above, for purposes of access to sensitive material, a straight person having an affair, and a closeted gay person, are both subject to blackmail or other undue influence.

You can be openly gay, married, and commit adultery - which remains an offense. It's called "equal treatment".