There is no consensus as to the original identity of the Sabians, and, barring the discovery of some new smoking gun evidence, it's likely to remain that way. The Qur'an refers to them simply as among "those who believe," alongside the Christians and Jews (2:62, 5:69, 22:17). But it gives no real hint as to their identity, nor to what their beliefs may have been. Scholars have hastened to identify nearly every religious sect in the late antique Near East as the Sabians (and there were a lot of them). The lack of a clear identity reflects the sources' own confusion, with later Islamic sources quickly using the term quite loosely. Arguably, the afterlife of the term is just as interesting as its original deployment, and I'll discuss the two most important groups to whom the term was later applied below. Either (or both) could be what the Qur'an refers to, though I don't see any conclusive proof, and my discussion isn't an endorsement of the respective theories (though I do lean slightly toward Mandaeans as initial referents).

The term "Sabian" was (and indeed still is) used in reference to the Mandaeans, an ethnoreligious group in lower Mesopotamia, though they are increasingly diasporic after the Iraq War. Their origins are hotly debated within the academy, but they were certainly in place by the 5th century, and thus would have been present during the rise of Islam and long familiar to the nearby Arabs. They consider themselves followers of John the Baptist, and their beliefs are highly Gnostic in flavor (intricate cosmic dualism, secret sacraments, elevation of the soul over the body, etc.). That makes them a pretty good fit for the label of "people of the book," since they have scripture (the Ginza Rabba), clearly share a great deal with Abrahamic traditions and worship the God of Adam, even if in a more abstract form.

The other group were the "star-worshippers" at the city of Harran in modern Turkey. Their beliefs appear to have been highly syncretic, and there are clear strands of Mesopotamian, Hellenic, and above all Esoteric/Hermetic traditions. Of course, we rely on scattered evidence here, mostly from heresiographers and a later agricultural text which describes some folk rituals and beliefs. Unlike the Mandaeans, most of the Harran Sabians who appear in the sources are a highly educated, elite class, and rose to prominent positions within the intellectual world of the Golden Age - most importantly the astronomer/mathematician Al-Battani. Harran itself housed a prominent university. It's likely that the philosophical brand of religion followed by these elites was as far from the majority religion as Neoplatonism was from Greco-Roman paganism.

These are far from the only groups to receive the designation, and Sabian essentially becomes a catch-all for any "pagans" that were allowed dhimmi status, including Buddhists and Hindus out east. Obviously, there's the strong motivation of self-preservation for the groups to claim the Sabian identity, and it provided the opportunity to prosper, as dhimmi (including the scholars from Harran) so often did. But it is also a great category for the state to apply to pagans who are useful, like the Harran scholars, or in cases where tolerance was a practical necessity, as with Buddhism and Hinduism. In a strange way, the fact that the original term was so ambiguous ended up being its greatest attribute, and ensured its continued relevance.

Selected Sources/Further Readings:

Kevin van Bladel, From Sasanian Mandaeans to Ṣābians of the Marshes (Leiden: Brill, 2017)

Kevin van Bladel, The Arabic Hermes: From Pagan Sage to Prophet of Science (OUP 2009)

Jaakko Hämeen-Anttila, The Last Pagans of Iraq: Ibn Wahshiyya and His NAbatean Agriculture (Brill: 2006)

Tamara M. Green, The City of the Moon God: Religious Traditions of Harran. (Leiden: Brill 1992).

David Pingree, “The Ṣābians of Ḥarrān and the Classical Tradition.” International Journal of the Classical Tradition 9 (2002): 8–35.