Absolutely!
I've made some incredibly close connections (lifers, as they say).
That said, this isn't something that consulting has a monopoly on...every single thing you're a part of in life should lead to this. I have made close friends in High School, College, at my first job (not strategy consulting), during my MBA, through soccer, through Alliance Francaise, in my neighborhood, at bars, on planes, etc. etc.
"Stressful projects" if anything makes it harder to build/create strong relationships. I'll also add, I made 0 friends from any clients I worked with.
I would also add that I made super close friends at BCG despite the stress. I'd have a project with someone for 5 weeks, get along with them excellently, then only see them 6 months later because we were too busy to meet up. I made close friends because BCGers were completely up my alley in terms of cultural fit/mindset. But, if I could have all those people in a different setting I probably would have made more friends!
(edited)