As you can mount the drive in macOS Recovery Mode but the drive is not showing up in a standard boot, the login items, disk errors, and third-party software could be the culprit.
The reasons are various when external hard drive is not mounting on Mac Sonoma, you should identify the possible causes, and troubleshoot the issue step by step. There shouldn't be a connection issue as it can be mounted in macOS Recovery Mode, then, here is what you can try:
- Repair the not mounting drive in Disk Utility in Recovery Mode.
- Boot Mac into Safe Mode to check and repair errors.
- Check and move Login Items that could cause the drive not to mount.
- Uninstall suspicious third-party apps that may prevent the drive from mounting.
- Recover data from not mounting the drive and reformat the drive.