I remember having to kill the marine that goes crazy on 343 Guilty Spark during H1 being really upsetting when I was a kid. I would walk by him and just leave him, but then think, "But what if the Flood gets to him? Better put him out of his misery." Other than that, Sgt Johnson's death pissed me off because I loved the character so much. Cortana's "death" in H4 made me tear up a bit.
Who's death was upsetting for you?