SQL Server Authentication Mode

Windows authentication Mode:

In Windows authentication mode SQL server validates the account name and password using the Windows principal token in operating system meaning the user identity is validate by windows not SQL server. Windows authenticate mode is default authentication mode.

Pros:

1. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration.

2. Its Simplify account administration because Windows groups can be created at the domain level and a login can be created on SQL server for the entire group.

3. Single password access to all resource

4. User do not have to specify a login name and password to connect to SQL server.

5. One of the main benefits of Windows authentication is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place Active Directory.

Cons:

1. DBA need access in Active Directory otherwise he or she may not be aware of users being added to AD group.

SQL server Authentication:

In SQL server authentication, logins are created in SQL server and the access authenticate by SQL server not by Windows

Pros:

1. Allows old legacy third party application to connect to SQL server where SQL server authentication require.

2. It supports mixed operating system environment where all users are not authenticated by Windows domain.

3. Allow users to connect from unknown or untrusted domain.

4. Allow SQL server to connect web-based application where user create their own identities.

5. Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset SQL Server logins.

Cons:

1. Users connecting using SQL Server Authentication must provide their credentials (login and password) every time that they connect.

2. User need to remember multiple password

3. SQL Server Authentication cannot use Kerberos security protocol.

4. Windows offers additional password policies that are not available for SQL Server logins.

5. This method is less secure because the encrypted SQL Server Authentication login password must be passed over the network at the time of the connection. Some applications that connect automatically will store the password at the client. These are additional attack points.

6. If anybody quit, the user access needs to block in each instance where in Windows authentication mode blocking user in AD will block user access in every database.

Active Directory - Universal with MFA support

Multi-Factor Authentication (MFA) is extra safeguard in Azure to access data. It delivers strong authentication with a range of easy verification options-phone call, text message, smart cards with pin, or mobile app notification-allowing users to choose the method they prefer.

Active Directory - Password

Azure Active Directory Authentication is a mechanism of connecting to MicrosoftAzure SQL Database by using identities in Azure Active Directory (Azure AD). Use this method for connecting to SQL Database if you are logged in to Windows using credentials from a domain that is not federated with Azure, or when using Azure AD authentication using Azure AD based on the initial or the client domain.

Active Directory - Integrated

Azure Active Directory Authentication is a mechanism of connecting to MicrosoftAzure SQL Database by using identities in Azure Active Directory (Azure AD). Use this method for connecting to SQL Database if you are logged in to Windows using your Azure Active Directory credentials from a federated domain.