I only played the first game so I can't really judge the second one, but from what I played I didn't think it was very frightening at all. It was suspenseful and atmospheric, but not scary. Lurking behind a Big Daddy did creep me out though.
Part of this may be because I'm spoiled by better incarnations of the horror genre, though most of my experience is in literature. Personally I prefer psychological horror to jump-out-of-closet terror.