On-prem SQL Server, Azure AD, allowing end users to authenticate via Azure AD

Question

Hi-

My current setup is an on-prem SQL and all my clients are (freshly) connected to AzureAD..no more on-prem domain. <g class="gr_ gr_58 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-del replaceWithoutSep" data-gr-id="58" id="58">Before,</g> ODBC connections just used Trusted_Connection=Yes and that worked great through their AD authentication.  Now that the old AD is decommissioned, clients are just temporarily connecting via a SQL username and password until we figured out how to authenticate via Azure.

How do I allow users to authenticate with their new AzureAD\[e-mail] account to the SQL server?  Using SQL Server 2016.  I explored setting up a gateway to Azure, but then the connections will go up to the cloud, then back down...I want their connections to remain local, just authenticate through the cloud.

Help would be greatly appreciated.

Thanks,

Mike

Answer 1

Hi Leicham,

In your scenario, you want to use Azure AD authentication to authenticate to a On-Premise SQL Server. Right?

If so, as I know for current On-Premise SQL Server, it only supports Windows Authentication and SQL Server Authentication. Windows authentication depends on Kerberos (or NTLM), which needs an Active Directory domain to authenticate the user in. Azure Active Directory does not handle Kerberos tokens. 

Thereby, I'm afraid it is not supported to use Azure AD authentication to authenticate to a On-Premise SQL Server right now.

Thanks,
Xi Jin.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

I think you shoud have a look at this:

Integrate your on-premises directories with Azure Active Directory

Integrate on-premises Active Directory domains with Azure Active Directory

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform MCSE Data Platform
MCSM Charter Member, MCITP Charter Member etc.
www.SarpedonQualityLab.com (Founder)