Connect to Azure SQL Database with SSMS using Azure AD Authentication
Question
You have a hybrid configuration of Azure Active Directory (Azure AD).
All users have computers that run Windows 10 and are hybrid Azure AD joined.
You have an Azure SQL database that is configured to support Azure AD authentication.
Database developers must connect to the SQL database by using Microsoft SQL Server Management Studio (SSMS) and authenticate by using their on-premises
Active Directory account.
You need to tell the developers which authentication method to use to connect to the SQL database from SSMS. The solution must minimize authentication prompts.
Which authentication method should you instruct the developers to use?
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B. C. D.C
Azure AD can be the initial Azure AD managed domain. Azure AD can also be an on-premises Active Directory Domain Services that is federated with the Azure
AD.
Using an Azure AD identity to connect using SSMS or SSDT
The following procedures show you how to connect to a SQL database with an Azure AD identity using SQL Server Management Studio or SQL Server Database
Tools.
Active Directory integrated authentication
Use this method if you are logged in to Windows using your Azure Active Directory credentials from a federated domain.
1. Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Active
Directory - Integrated. No password is needed or can be entered because your existing credentials will be presented for the connection.
2. Select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to.
(The AD domain name or tenant ID option is only supported for Universal with MFA connection options, otherwise it is greyed out.)
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershellThe correct authentication method for the developers to use to connect to the Azure SQL database from SSMS in a hybrid Azure AD environment is option C: Active Directory - Integrated.
Explanation:
When connecting to an Azure SQL database from a Windows 10 computer that is hybrid Azure AD joined, the computer will have a device identity in Azure AD. This identity is used to authenticate to Azure AD and obtain an access token. To enable authentication to the Azure SQL database, the access token must be passed to the database server.
Option A (SQL Login) is not the correct answer because it requires the developers to create a SQL login account with a password, which is not consistent with the requirement to use their on-premises Active Directory account. Using SQL Login also increases the risk of credential theft and is not recommended.
Option B (Active Directory - Universal with MFA support) is not the correct answer because it requires additional configuration and support for MFA, which is not necessary for this scenario.
Option D (Active Directory - Password) is not the correct answer because it requires the developers to enter their Active Directory password manually, which is not ideal for security reasons and does not minimize authentication prompts.
Option C (Active Directory - Integrated) is the correct answer because it enables the developers to use their on-premises Active Directory account to authenticate to the Azure SQL database. The authentication process is seamless and minimizes authentication prompts. With Integrated authentication, the computer's device identity is used to authenticate to Azure AD, obtain an access token, and pass it to the Azure SQL database. This process is transparent to the developers and does not require any additional configuration or credentials.