Describe authentication and identities
Both on-premises SQL Server installations and SQL Server installations within Azure Virtual Machines support two modes of authentication: SQL Server authentication and Windows Authentication. When using SQL Server authentication, SQL Server-specific login name and password information is stored within SQL Server, either in the master database, or in the case of contained users, within the user database. Using Windows Authentication, users to connect to the SQL Server using the same Active Directory account they use to log into their computer (as well as accessing file shares and applications).
Active Directory authentication is considered to be more secure because SQL Server authentication allows for login information to be seen in plain text while being passed across the network. In addition, Active Directory authentication makes it easier to manage user turnover. If a user leaves the company and you use Windows authentication, the administrator would only have to lock the single Windows account of that user, instead of identifying each occurrence of a SQL login.
Azure SQL Database similarly supports two different modes of authentication, SQL Server authentication and Microsoft Entra authentication. SQL Server authentication is the same authentication method that has been supported in SQL Server since it was first introduced, where user credentials are stored within either the master database or the user database. Authentication via Microsoft Entra ID allows the user to enter the same username and password, which is used to access other resources such as the Azure portal or Microsoft 365.
As mentioned above, Microsoft Entra ID can be configured to sync with the on-premises Active Directory. This option allows users to have the same usernames and passwords to access on-premises resources as well as Azure resources. Microsoft Entra ID adds on additional security measures by allowing the administrator to easily configure multi-factor authentication (MFA).
With MFA enabled on an account, after the correct username and password is supplied, a second level of authentication is required. By default, MFA can be configured to use the Windows Authenticator application, which will then send a push notification to the phone. Additional options for the default MFA action include sending the recipient a text message with an access code, or having the user enter an access code that was generated with the Microsoft Authenticator application. If a user has MFA enabled, they have to use the Universal Authentication with MFA option in Azure Data Studio and SQL Server Management Studio.
Both Azure SQL Database for SQL Server and Azure Database for PostgreSQL support configuring the server that is hosting the database to use Microsoft Entra authentication.
This login allows the admin access to all of the databases in the server. It is a best practice to make this account a Microsoft Entra group, so access is not dependent on a single login. In the above image, that group has been called DBA team. The Microsoft Entra Admin account grants special permissions and allows the account or group that holds that permission to have sysadmin like access to the server and all of the databases within the server. The admin account is only set using Azure Resource Manager and not at the database level. In order to change the account or group, you have to use the Azure portal, PowerShell, or CLI.