What is difference between SQL Authentication and AD Password Authentication for Azure SQL?

Question

What is difference between SQL Authentication and AD Password Authentication for Azure SQL? How both works and which one more secure and recommended as per Microsoft for Azure SQL Login?

Answer 1

@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.

Answer 2

Thanks @Ronen Ariely
However My question is more relevant when choosing for SSIS. I can use AD Authentication or SQL Authentication.
If I have both option, which one provides me better security?

Answer 3

Good day,

What is difference between SQL Authentication and AD Password Authentication for Azure SQL?

The short answer, is that it is a bit like on-premises (at least the way it is working for the user), accept the fact that you do not really control the server but only a logical server.

• SQL authentication based on username and password
• You must have one (and only one) LOGIN which is serves as the logical server admin.
• Azure Active Directory authentication based on identities in Azure Active Directory.
• You can have as multiple Azure Active Directory users with full permissions

Please check if this short doc helps you

https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage#authentication-and-authorization

which one more secure and recommended as per Microsoft for Azure SQL Login?

You cannot have Azure Database without SQL authentication Admin, which mean that this is not an option to ask X or Y (since X is a must)

You should secure your database by using other features like allow only specific IP to connect, Use VPN, and so on...

check this document for more information:

https://learn.microsoft.com/en-us/azure/azure-sql/database/security-overview