Enable AD DS authentication for Azure SQL with OnPremise AD ?

Question

People,

How can I integrate and configure the Azure SQL so that I can grant access for the OnPremise Synched AD security group access and then enforce Azure AD MFA when login from SSMS ?

There is no AD DS (OnPremise join command like described in https://learn.microsoft.com/en-us/azure/storage/files/storage-files-identity-ad-ds-enable)

Thank you in advance.

Answer 1

Hi @EnterpriseArchitect ,

the link you posted in your question is for Azure file shares and AD DS authentication. Not for Azure SQL.

Maybe this link is helpful:
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 2

Hi @ EnterpriseArchitect Thank you for posting your question on Microsoft Q&A . Please also review this article. It explains this in more details

https://learn.microsoft.com/en-us/azure/architecture/reference-architectures/identity/

Regards,
Oury

Answer 3

Hi @EnterpriseArchitect

1. You need to ensure that your on-premises Active Directory is synchronized correctly with Azure Active Directory. This is covered in https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview#trust-architecture, If this AD sync / connect step is successful, at the end, you should be able to find their AD security group, in Azure AD, with the source as “Windows server AD” as shown in the example below:

2. Then you can set an Azure AD Admin for the logical SQL server. Steps are here: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#azure-ad-admin-with-a-server-in-sql-database
3. Create a contained user FROM EXTERNAL PROVIDER with the name of the Azure AD security group as shown here: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#create-contained-users-mapped-to-azure-ad-identities. It is important to use the name of the group, not the email address.
   SQL DB MFA details are here: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-overview and https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-configure. Most of the MFA configuration however is Azure AD specific, and as I mentioned initially, should be done by a Azure AD tenant administrator. For more details you need to contact Azure AD experts if you have specific challenges or questions.
   Hope that helps

Regards,
Oury