Almost all IDE and SCSI CD-ROM drives were indeed capable of playing audio CDs fully autonomously, with the host PC basically only sending them the command to start playing; many drives took it one step further and provided a play button in addition to the usual eject button, which worked even if the drive wasn't plugged at all into a machine. The audio was typically output both to the front panel headphone jack and to a 4-pin connector on the back of the drive, which you were supposed to connect to one of your sound card's aux inputs so that it would get mixed into the system audio output.

Unfortunately, a decent number of machines were not fitted with the relevant cable. Combined with the low-quality DACs that most drives used, the compatibility issues that plagued ATAPI implementations and the dramatic increase in CPU power and sound card quality throughout the mid-to-late 90s, this led media player software to quickly move on from drive based playback to so-called "digital audio extraction", where the CD is basically ripped in real time and streamed to your sound card's own DAC. Thus, unless you played older games that relied on hardware CD-DA playback [1], it's somewhat unlikely you ever experienced it under, say, Windows 98 or XP.

[1] As offloading playback to the drive had no CPU overhead, games often stored their music as additional tracks on the game disc and played it that way. Incidentally, basically all CD-ROM-based game consoles and arcade systems relied heavily on hardware accelerated playback as well, with some going even further and allowing for compressed (ADPCM) CD audio streaming with no CPU intervention.