A lot of games considered "classics" today were not initially successes, but became that way over time.
That said, if you want to look at why SotN was a success regardless of the fact it was not initially so- it was just a good game. But also a good game in that pre-reflectionist kind of way, if that makes sense. The franchise as a whole post SotN, sort of became self reflective once Konami started trying to replicate it's success. Everything up until it was a slow march of progress (for better or for worse) but also represented the old school way of making games. The sort of wild-west-ness of classic game design. Where everything was a lot more flexible and fast and loose, and you were never guaranteed to have the same people working between one game and the next, and thus tones and directions would be wildly different, and the approach was just to try things and see what sticks. (and the simpler development of sprite based games vs nowadays meant you had way more freedom to experiment- as a Mega Man fan, despite when interviews point out weird development and troubles for classic MM games, we still had a new mainline megaman game every single year between MM1 and 6 Can you imagine making what would be considered a "proper" game, from a big studio, 1 every year, with like 90% new assets every year?)
SotN was yet another differently toned castlevania. It was yet another new idea.
post SotN, or I guess post CotM when they saw that people actually liked that formula, it became *the* Castlevania formula. Basically every castlevania game from there up until LoS was looking *back* at SotN specifically. Whereas pre SotN, what castlevania looked to was the greater classic horror media. Universal Horror obviously always being the big core influence. But Simon's popular media design was clearly Conan the barbarian.
back to SotN itself though, it was elegant, which was somewhat new. Before this it was all mostly universal horror themed, and it had a certain roughness to it, considering you were the rough n tumble belmont on his way to brute force through Dracula's castle. (and other environments, of which the castle was merely one of) SotN though put way greater emphasis on the castle, and Alucard was not the typical Castlevania manly man protagonist. Nor was he the monstrous thin veneer of humanity over a monster archetype of classic horror vampires. He was less Christopher Lee, and more interview with the vampire.
the environment was also way more detailed than previous games. And had some real unique effects. SotN was unlike anything, even the cavalcade of IGAvanias that followed it- SotN is decidedly 90's in some of it's quirks and oddities. future games really dialed in to the gothic elements and anime elements more heavily, while discarding some of the fluff.
Some of that "decidedly 90's" stuff is for instance, the soundtrack. Future games, again, went heavy on the gothic and such, even for music- But for every Requiem for the Gods, or Dracula's own boss theme, you have a track like Wandering Ghosts. or Crystal Teardrops. Which are more jazzy.
I guess the TLDR is it was fresh in a way that you can argue the games haven't really been since, while at the same time, having a lot of content and replay-ability.
not sure if any of my rambling made sense or im just talking out of my ass, but that's my 2 cents as to why SotN was a success in general since it's release. Why it's stuck around so long. (the general gaming public remembers SotN way more than something like, say, Mega Man X4- it had broad appeal while MM was already starting to suffer from stagnation, where really only MM fans paid attention to MM games)