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

Anky 121 Reputation points
2020-07-20T18:28:18.793+00:00

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?

Azure SQL Database
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,102 questions
0 comments No comments
{count} votes

Accepted answer
  1. JamesTran-MSFT 36,346 Reputation points Microsoft Employee
    2020-07-21T16:52:43.027+00:00

    @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.
    13117-phs.jpg

    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.
    13118-pta.jpg

    Federation (AD FS) - Federation is a collection of domains that have established trust. How AD FS works.
    13119-federation.jpg

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,081 Reputation points
    2020-07-20T21:18:38.657+00:00

    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

    0 comments No comments

  2. Anky 121 Reputation points
    2020-07-20T21:28:55.267+00:00

    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?