>> Have you been wanting to migrate to
Azure SQL Managed Instance but
felt block because you use
Windows Auth today in your organization?
In this episode, learn how you can use
Windows Auth in Azure SQL Managed Instance,
how it works, how to get it set up,
and even a cool demo.
This week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today we're going to be talking about
an exciting topic that I
think you-all will be really interested in,
especially if you're migrating to Azure SQL.
For that I'm bringing on Sravani.
Sravani thanks you so much for joining us
again on Data Exposed we're so happy to have you back.
Today you're going to be telling us about
Windows Auth for Azure SQL Managed Instance,
and how you might go about setting that up.
I'm really excited to learn more about
this like how we could start with
just what is it and why are
customers asking for something like this?
>> Sure and thank you for having me back.
We're going to talk about Windows Authentication
for SQL Managed Instance.
We just went GA a couple of weeks ago,
so we have customers who
started modernizing their applications data DoS.
Along with that, they also started modernizing
their identity management by shifting
their active directories to the Azure AD.
Azure AD Authentication already works for
Azure SQL and Managed Instance today and with SQL 2022.
We started supporting Azure AD Authentication
and we have different ways of authenticating
Azure AD users like fast-forward or
universal with MFA or Integrated Authentication.
However we still have customers who cannot migrate to
Azure AD users or they cannot
start using Azure AD Authentication
because of certain dependencies.
What we are trying to solve with
this feature is basically customers who has
native Windows Authentication mechanism
to access their applications and the SQL instances in a secure,
orderly and customers who cannot
use the Azure AD Authentication due to some dependencies
on their legacy drivers or some application dependencies
or they cannot recompile the libraries, things like that.
Though we have a Azure SQL
with Integrated Authentication, like I said,
there are some limitations like they cannot set up
the Azure AD federation workflows
or they might have to recompile it with modern libraries
or simply their applications are not compatible.
With the existing Azure AD Authentication options,
we started supporting Windows Authentication which is
the native authentication which
SQL customers have await for several years,
and this Windows Authentication helps
you to work with legacy drivers,
which does not have a built-in support for Azure AD
and it also works for both domain joined machines,
client machines, Azure AD joined client machines.
It also works in your double-hop scenarios like where you have
your applications running in IIS within impersonation,
run as a service or something.
The Windows Authentication supports all these scenarios.
How you've set it up is very simple,
they don't have to make any changes at the application level,
which is the great thing to call out and all they have to do
is an initial infrastructure setup which is something
like setting up some Kerberos tokens in
their client machines and then setting up
something in the SQL Managed Instance from the Azure portal,
so the initial infrastructure setup is a onetime activity
and it's pretty simple and
the documentation is very clear with the steps.
One, the first step is obviously to synchronize
the Active Directory users with the Azure Active Directory,
I think this is something most of our customers
already have as they are modernizing their identity.
Then there are two flows
that walks where this Windows Authentication,
one is the traditional incoming trust flow,
and the second model is the modern interacting flow.
Based on the client machine from which
you are trying to connect to the SQL Managed Instance,
you have to establish one of
these flows for your Windows Authentication to work.
For example, traditional incoming crust,
this is something that
our customers known for several years because it works through
the Kerberos Authentication Model and customers have to
set up the Kerberos Proxy for the Azure Active Directory.
We already have a propulsion model which is provided.
All they have to do is download the partial module,
provide the parameters and
create the Kerberos tokens with the Azure Active Directory.
This works for the Active Directory joined
machines and it has to be Windows Server 2012 and above.
So for the modern interactive flow
if your client is a Azure AD joined on a hybrid joined or
other AD joined machine and if
the operating system is Windows 10 21H1 and above,
in that case, they don't have to do anything,
we just have to enable our local policy
to allow Kerberos token during the login.
Once the local policy is configured,
the modern interactive flow works just fine.
Only clause for this is the applications that can have
an interactive flow where you can provide the login,
that is you can set up this flow.
Customer has both options to
configure and then once the client machine is
configured with the required Kerberos tokens
and the local policies,
they should be able to connect to
this SQL Managed Instance using Windows authentication.
From the SQL MI part all they have to do is you
have to create the service principals which
is again so it's principal names and you also
have to provide the admin consent for
the Managed Instance in your Azure Active Directory.
This is how the workflow works so
your client machine will be falling in one of these two buckets,
it will be either Windows 2022 or Windows 10.
Then it is either Active Directory joined,
hybrid joined or just a Azure AD joined machine.
Based on the client machine,
you just have to set up one of these two items either to
establish incoming trust flow
or to establish more than interacting flow.
>> Nice. That's definitely going to
be a helpful chart for thoughts.
>> Yes, absolutely and how it works is basically,
once the client sends the request to the Active Directory,
first it obviously searches within Active Directory
and finds that this is not a valid account,
and then it checks for that suffix and see whether it
is coming from any trusted objects
like from the Kerberos TLN list.
Once it validates that,
the request is sent to the Azure AD Kerberos to
get the TGT and once we have the ticket,
granting ticket, that is how we achieve it to
Managed Instance and then the connection is established.
We are achieving this Azure
Active Directory Kerberos Authentication support, and today,
I think that Kerberos
from Azure AD supports couple of applications,
one is the Managed Instance and other one is Azure File Store.
We were able to collaborate with
the team and able to achieve this,
and it just worked fine.
Obviously I guess I have pictorial view of this,
how it works, so you have the client,
you have Managed Instance and you have Azure AD.
Once the request is sent,
the Kerberos ticket is encrypted,
Kerberos ticket sent to the Managed Instance
and it is exchanged with
Azure AD and we get Azure AD token and
the user is authenticated with the Managed Instance level.
With this, customers can
start migrating data applications to Azure AD users.
This is to boost the Azure AD Authentication
further and help our customers to use Azure AD users.
>> It seems like there's a couple of
steps, but at the end of the day,
Windows Auth is really going to help folks,
especially as they're migrating,
as they're going through this modernization process before
they can just go to full Azure AD only.
It really cool stuff.
Thanks for sharing with us,
it's exciting but it's in general availability now.
I love if you have some things to take
a look at how this works and how it looks.
>> Absolutely. Let me quickly share my screen.
Before I take you to my client machine,
this is my SQL Managed Instance that I have in my portal.
From SQL Managed Instance,
all I have to do is to configure service principals. One moment.
You have to go to this Identity tab where you
have to assign this system,
assign service principal so by default it is off.
Once you configure this to on,
the service principals will be created for your Managed Instance,
which can be used to authenticate using Kerberos protocol.
So service principals is something that SQL customers,
we have been hearing this was several years that is now
the Windows authentication works across domains.
Once you do that from the portal,
then let me take you to my client machine.
This is my client machine,
this is just to verify what client I'm running again.
If you see I have a client machine that has
joined a domain, AADSQLMI,
and if you see this is the Azure AD joined client so it's yes.
From this machine, I'm just connecting to
my Management Studio database engine and if you
see this is my Managed Instance and you can see that I'm using
Windows Authentication and I'm able connect,
so I just connected with the Windows Authentication.
Not only this, you can also
now launch the tools that you have been
using for several years with SQL like SQL Profiler,
Extraneity Win which actually
dependent on the Windows Authentication.
Now they pretty much just works
fine with the Managed Instance as well.
You can use this for
our troubleshooting purpose using Windows Authentication.
All these client tools which are dependent on
the Windows Authentication just works fine.
If you see I also have
my BC where I can
show you that Kerberos trust where it is established,
so if you see this is my domain.
So this is the PowerShell module that you have to use.
Here I'm trying to get the Kerberos Server details
for this AADSQLMI domain.
It is for a specific principal name and if you see
the CloudTrustDipsay so there is
a clustered object that is created with the Azure AD,
KDC service and this is
the Cloud domain name that is AADSQLMI.net.
All you have to do is create
this Kerberos token and once this is created,
you should be able to connect from your client machine
to the Managed Instance.
>> Cool. Awesome. Thanks so much Sravani for showing us
Windows Auth for Azure SQL Managed Instance
that you've been taking us one step deeper to say,
this is how you actually configured it.
This is what it looks like.
This is the PowerShell module that we've created to
make it that much easier for you to get set up and configure.
I just want to thank you for coming on the show.
To our viewers, we'll put some links in
the description so you can get started,
get hands on, learn more,
set this up in your organization.
If you like this episode, go ahead and give it a like,
give us a comment and let us know what you think
about Windows Auth for Azure SQL Managed Instance,
and we hope to see you next time on Data Exposed.
[MUSIC]