>> In the previous video,
Bob talked a lot about identity in access management
for Azure SQL Database and Azure SQL Managed Instance.
In this video, we're going to take a deeper look at how you can
configure some of these things and start to give people access.
What you're seeing here is the Azure portal,
and specifically I'm looking at
the logical server for my database.
Now on the left-hand side,
you're starting to get familiar with some of these options,
but under settings there's an Active Directory admin.
Now in here I can go and I can select, "Set Admin".
Now note for Azure SQL Database,
I can only have one Azure Active Directory admin.
Now once I find myself,
I can go ahead and select and then go ahead and save,
and don't forget to save.
Now once it saves,
I'm going to be able to log into
this Azure SQL Database logical server using
SSMS and using whatever way
my organization has set me up to connect.
So for example, in here,
you can see that there are few options
universal with multi-factor authentication,
Azure Active Directory with password,
and Azure Active Directory integrated.
Now, depending on how
your organization has configured your environment,
you might be able to authenticate with any of these methods.
For me, I'm going to use
universal with multi-factor authentication.
So here what I'm going to get is that prompt
to login and authenticate.
So now I'll be able to login using SSO,
but I'm going to sign in using a username and password since
I'm actually running this on a virtual machine.
Since this is multi-factor authentication,
the next thing that's going to happen is I'm going
to get a notification on my phone,
and now I'm able to connect.
What we're seeing now is I'm seeing
the same things I was seeing before,
and I'm going to actually go ahead
and make it this one, that connection.
So we've got that here and we're already connected.
Now that we have an Azure Active Directory admin,
the next thing we might want to do is give other people access.
Now what you're seeing here should
look just like it would in SQL Server.
So the first thing I'm going to do is I'm going to create
a login called Application User and give that login a password.
Now note that during this I have to connect to the master.
So you have to be an admin that has
access to all of the databases in
the database server as well as
the master to be able to perform this type of operation.
Now if I switch contexts back to the database,
I can create a user in this database.
So I'm going to go ahead and run that,
and then just like in SQL Server,
until I give the user db_datareader or db_datawriter,
they aren't going to have access to read or write that data.
Now, let's take a look at
a slightly different scenario around contained database users.
Now, Bob talked about this idea of
contained database users without
having to create a contained database in Azure SQL database.
So what I'm going to do is I'm going to go ahead
and create Bob loves Tennessee.
This is an ongoing joke because if you know Bob at all,
you would know that he is not a Tennessee Titans fan,
but I am and I'm running the demo,
so here we are.
Now I'm going to go ahead and try to login.
Actually before I login,
I have to give Bob access to read or write that data.
So I'm going to go ahead and run that one too.
Now, we can go ahead and try to connect as Bob.
So I'm going to go back to SQL Server authentication.
Specify Bob loves Tennessee,
and also specify the password.
Now note that since Bob is a contained database user,
he only has access to this one database.
So he's actually not even going to be able to
authenticate to the main server.
So we're going to go ahead and see that.
Login failed, so he's not even able to
see all of the databases in the server.
I'm going to go ahead and enter that database,
AdventureWorks 0602, and now he should be able to connect.
Now you can see what Bob sees
and he should be able to read the data.
If we take a look at this out here,
what you'll see is the different ways that I've connected,
so as two admins,
either a SQL Server admin using
CloudAdmin or as the AAD admin myself,
and then as Bob.
Now the final thing I wanted to show you,
which is a little bit different than on-prem,
is adding access to other existing Azure AD users.
The key here is really this T-SQL syntax from external provider.
So I can actually add Bob creating a user
Bob or using his Azure Active Directory account,
and then just like before,
he won't have access until
I give him access to read or write that data.
In this video, you saw
a brief survey of the various options regarding
authentication and Azure Active Directory
admins in Azure SQL Database.
Stay tuned for the next video.