question

EnterpriseArchitect avatar image
0 Votes"
EnterpriseArchitect asked OuryBa-MSFT commented

Enable AD DS authentication for Azure SQL with OnPremise AD ?

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://docs.microsoft.com/en-us/azure/storage/files/storage-files-identity-ad-ds-enable)

116682-image.png

Thank you in advance.


azure-sql-databaseazure-ad-domain-servicesazure-ad-authenticationazure-ad-authentication-protocolsazure-ad-hybrid-identity
image.png (30.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreasBaumgarten avatar image
1 Vote"
AndreasBaumgarten answered EnterpriseArchitect commented

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://docs.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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AndreasBaumgarten yes, that is how I did it with the GUI from Azure Portal.
Looks like there is no way for the Azure SQL to know or recognize the OnPremise AD DS.

0 Votes 0 ·
OuryBa-MSFT avatar image
1 Vote"
OuryBa-MSFT answered EnterpriseArchitect commented

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

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

Regards,
Oury

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

that's great, @OuryBa-MSFT which section shows what Azure object can be joined to on premise AD DS for the Kerberos authentication?

0 Votes 0 ·
OuryBa-MSFT avatar image
1 Vote"
OuryBa-MSFT answered OuryBa-MSFT commented

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://docs.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:

118684-image.png

  1. Then you can set an Azure AD Admin for the logical SQL server. Steps are here: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#azure-ad-admin-with-a-server-in-sql-database

  2. Create a contained user FROM EXTERNAL PROVIDER with the name of the Azure AD security group as shown here: https://docs.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://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-overview and https://docs.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


image.png (72.1 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EnterpriseArchitect Did you had the time to review the above answer. Please let us know if that answer your question or solved your issue.

Regards,
Oury

0 Votes 0 ·

Hi Oury,
Let me try it for the Azure SQL Server and Azure SQL DB Instance.

1 Vote 1 ·

Sure @EnterpriseArchitect Please let me know the result.

Regards,
Oury

0 Votes 0 ·
Show more comments