@Anky
Thank you for your post! I’ll post my findings below.
How the authentication works:
When it comes to SQL authentication, it's straight forward since a user’s credentials are stored/created/authenticated within the SQL server itself. Based off my knowledge, I believe you can leverage TDE, to help secure this information, if needed.
Azure AD Authentication with a hybrid identity authentication methods:
Password hash synchronization (PHS) – Your passwords are stored in the form of a hash value representation, of the actual user password. Azure AD Connect sync extracts your password hash from the on-premises Active Directory instance. Extra security processing is applied to the password hash before it is synchronized to the Azure Active Directory authentication service. Passwords are synchronized on a per-user basis and in chronological order. How PHS works.
Pass-through authentication (PTA) – This feature allows your users to sign into both on-premises and cloud-based applications using the same passwords. How PTA works.
Federation (AD FS) - Federation is a collection of domains that have established trust. How AD FS works.
If you need help choosing the right authentication method for your Azure Active Directory hybrid identity solution.
I hope this helps!
Thank you for your time.
----------
If any reply/answer helped resolve your question, please remember to "mark as answer" so that others in the community facing similar issues can easily find the solution.