Connect Azure SQL Database to SSMS with Azure AD Authentication
Question
You manage an Azure SQL database that allows for Azure AD authentication.
You need to make sure that database developers can connect to the SQL database via Microsoft SQL Server Management Studio (SSMS)
You also need to make sure the developers use their on-premises Active Directory account for authentication.
Your strategy should allow for authentication prompts to be kept to a minimum.
Which of the following should you implement?
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.
/answer/img0047700001.jpg)
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.) Reference: https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/sql-database/sql-database-aad-authentication-configure.md.
To allow database developers to connect to the Azure SQL database using their on-premises Active Directory account for authentication and minimize authentication prompts, you should implement Active Directory integrated authentication. Therefore, the correct answer is C.
Active Directory integrated authentication allows clients to authenticate to an Azure SQL Database using their domain-joined computer's credentials rather than using SQL authentication. This authentication method uses the Windows security infrastructure to authenticate the user, and then passes that identity to Azure SQL Database. By doing so, users can connect to Azure SQL Database using their on-premises Active Directory account without having to enter a separate username and password.
To configure Active Directory integrated authentication, you need to perform the following steps:
- Configure Azure AD authentication for the SQL database.
- Create a server-level firewall rule to allow access to the Azure SQL Database.
- Enable Active Directory integrated authentication on the SQL database.
- Add the domain user accounts or groups to the SQL database.
- Configure the SQL Server Management Studio to use the current user's Windows credentials for authentication.
Once Active Directory integrated authentication is enabled, the developers can connect to the SQL database using SSMS and their on-premises Active Directory account. They will not be prompted for authentication credentials, as their Windows credentials will be used for authentication automatically.
Azure AD tokens (option A) and OATH software tokens (option D) are not relevant to this scenario as they are used for different types of authentication. Azure Multi-Factor authentication (option B) can enhance security by requiring an additional factor of authentication, but it does not help with the requirement to use on-premises Active Directory accounts for authentication or minimize authentication prompts.