DP-300 Microsoft Azure Database – Configure database AuthN and AuthZ by using platform and database tools

1. 23. configure Azure AD authentication

Hello. And in this section, we’re going to start looking at how we can implement a secure environment. Now, as I said before, this course DP 300 requirements don’t go into a huge amount of detail as to the Azure security that you might need. It just has limited things that we’ll be looking at. So we’ll be starting at how we can configure authentication in Azure Active Directory. Now, before we start, I want to talk about the difference between authentication, also known as Authen, and Authorization, also known as authZ. So authentication is who you are.

So in other words, if I log in, how can I make sure that it is me logging in as opposed to other people? Authentication should be separate from Authorization, which is what you can access, and that’s quite different. Now, if you have a look at this authentication, you can see we can use Windows authentication. So Windows authentication is great if you are on a Windows machine or in a domain with Windows.

However, with Azure, unless you’re using a virtual machine on SQL Server, that’s not going to happen. SQL Server authentication, that is using a username and password. But it’s these and a few more that I want to talk about is your Active Directory. So, if I go into the portal and look for Azure Active Directory, which is also acronymed to AAD, you can see that we have got our tenant. So this is our organization which has got users.

And if I click on the users here, you can see that I’ve got multiple users set up from previous courses that I have created. So why might you want to use Azure Active Directory as opposed to SQL Server authentication? Well, it’s just a username and password, SQL Server authentication sent in plain text potentially, whereas AAD much more secure and can use additional ways of making sure it’s who you are. Now, if you are used to using Windows Server, you will know that there is a Window Server Active Directory that is different to Azure Active Directory, even though they’ve got the same Active Directory.

However AAD. The Azure version can synchronize with the onpremises Windows Server Active Directory. Now, we have already come across the Active Directory and that is when we log in. So if I just sign out and log in again, this is logging into Azure Active Directory.

So I’m going to log in as Me, put in my password. But it may be that I’ve also asked it to ask me for additional things. And if I go back into Azure Active Directory and have a look at authentication methods, you can see that at the moment it supports things like the Fido Security Key. So, Fido Security Key, this is a hardware that you have on your computer. So you can see really strong authentication, Microsoft authentication. Again, this is strong authentication. It is a phone app, so you can have it on your mobile phone. It also supports text messaging and a temporary access pass. So again, both of these are considered additional forms of authentication, but the text message is stronger because you need to have your phone to hand temporary access pass, not necessarily as strong.

This is for onboarding and for recovery. So these things can be configured in your Azure Active Directory. Now, there are three different ways that you can authenticate in. You’ve got cloud only identities, Federated authentication, and pass through authentication. So if I start with Federated authentication, if you want to integrate with an existing federation provider, then you can use Federated authentication. So suppose I had another app and I wanted to use that app with a login to a jury Active Directory that is Federated authentication. So this can also be used if you have a sign in requirement which is not natively supported by your Active Directory. There is cloud only identities.

So this is when you want as your Active Directory to handle signing completely in the cloud. So this is when you say Active Directory, you take care of it completely. And then there’s pass through authentications. That’s all of occasions. So when you don’t have a sign in requirement not natively supported by Azure Active Directory. And when you want something else, maybe you want to enforce user level Active Directory security policies during sign in, maybe where you’ve got no integration with an existing Federating provider. So you’ll probably hear these three different terms cloud only identities, which is what we’re using here. Hybrid identities which support cloud authentication, maybe using password hash or as we’ve said, a pass through authentication. And we’ve also got hybrid identities that support Federated authentication.

Now, let’s just have a look to what other ways of logging in we can do. Well, if you’ve got an app running on an Azure virtual Machine, then you can use passwordless authentication. So that’s basically the equivalent of Windows authentication. You’re already there. If you’ve got an app running on a non Azure machine that is domain joined, then you can use managed identities. If you have got one that’s not domain joined, then you’ve got certificate possibilities. You can create a certificate and the app can connect to your Azure data using that. So that is apps. If you’ve got an old app where you can’t change the connection string, by the way, you’re probably stuck with SQL Server authentication. However, if you’re using an admin tool such as Microsoft SQL Server Management Studio on a computer which is not domain joined, then you can either use integrated authentication or you can use an interactive authentication with a multifactor authentication.

So that’s what MFA stands for, multifactor Authentication. So if you want to create a new user in AAD, then you just go to users within your tenant. So within AAD, so you can see I’ve got these users and you can give a new guest user or new user, or you’ve got bulk invite, you’ve got plenty of other things you’ve got per user, multifactor authentication, and so for the new user, you need to provide their identity, their groups and roles. In other words, what can they do so their authorization and then whether they can sign in and additional information.

So that can all be done there. Now, I already have some AADS users, so I don’t actually need to create one there. If I did create one, Azure would give you an auto generated password, by the way. Now if I wanted to delete anybody, then I can just click on it and click Delete User as well. So this is where you can manage all of your users so as your active Directory authentication. So if you look for it in the portal, you can see you can manage all sorts of things in this. And we’re only going to be looking at the tip of the iceberg in this particular course with regards to how it reacts with SQL Server. So you can add users, you can add groups, you can add authentication methods and lots more things from here.

2. 24. create users from Azure AD identities

Now in this video, we’re going to create SQL Server users from Azure Active Directory identities. So I’m going to log in with my SQL Server authentication. This isn’t going to work, by the way, but I’m just going to show you what’s not going to happen. So here we go. And what I need to do is just say create user. So I need a user user name. So I’m going to have Jane here. So Jane at and I’ll put this in hard brackets. So there is our user and we’ll say from external provider. So that is saying get it from Active Directory or Azure. Active directory. Now you can say other things. So with the default schema being, with the default language being whatever, and you can also say a few other things, but at its essence, we’ve got create user name of user from external provider.

So we don’t need to create a password or anything because we’ve already created a password when we are creating the user in Azure Active Directory. So I’m just going to press Execute and that’s fine. So this is how you can create a user from an Active Directory in Azure Active Directory identity. And there we go. Execute works fine and the principle cannot be created. Only connections established with an Active Directory account can create other Active Directory accounts. So the problem is I logged in with SQL Server authentication. Okay? So let’s disconnect that. So just right and click on this and disconnect. And let’s connect again using Azure active directory.

So I’ll put in my name and connect. Having trouble signing you in. Okay, I haven’t got a user for this particular user name. I’ve got a user for my DP 300 name. So hang on. I need to create a second Active Directory user in SQL, but I can only do that if I’ve already got a first Active Directory user in SQL Server. But how do I create that first one if I can’t log in using an Active Directory Server? I hope you can see the problem. However, it is resolvable if we go into my SQL Server. So not the database, but the server on the portal. And in the server I go to the Azure Active Directory setting. So I need to set an admin here. So once I do that, then I can use that admin to log into SSMS and then create that user. So I’m going to select Jane to be my user.

So she is going to be an administrator. She’s going to have what’s called the DB owner database roles. So do be careful who you actually put here. So there we go. And save. Okay, that’s done. And you’ll notice instantly there is a support only a Joe Active Directory authentication for this server. So in other words, I could deselect or disable SQL authentication. I won’t be doing that. But now let’s go back into here we’ll go into our Active Directory here I’ll open up a second instance and go into the Azure Active Directory user, so you can see the name of the user.

So now I’m going to log in as Jane, who is the administrator Connect, and I get to sign into my account here. So I’ll type in my password and click Sign In and there we go. I am now signed in as Jane, and because she is the owner of the database, she can do basically whatever she wants. So select Star from you can have more than one owner, by the way, but you can only have one administrator here in the Azure Active Directory admin. So let’s see if I try and add a second one. If that works, you can see it replaces the existing one. And I don’t want that to happen, so I’ll just set it back. So now we have got Jane. I am going to add another user.

So I’ll copy this into here and I’m going to put Susan in. So create user from external provider. And there we are. Command successfully completed. So now if I try connecting to this using Azure Active Directory, and I look for Susan and I put in her password, you can see that the login failed. Now, it’s not a particularly good error message 18456. And if I show the details, state one when you look it up, just means I can’t give you any more information and you can spend literally hours working out what the problem is. If you’ve got that, then here’s my possible solution. We click on options and go to Connection properties. And here we’ve got Connected Database, and the default database is shown. Now, if we change that to the name of the actual database and then Connect, we find that we can indeed connect. So it’s just a small problem, but boy, is it difficult to actually work out what to do. So if I expand the database, then we can see the DP 300 database.

Excellent. I can expand it and we can see tables. Excellent. Just like this, I can expand tables and there are no tables that we can see. Similarly, if I expand views so this is in Jane. We can see three views. If I expand views here, no views here. So what’s going on? Well, in here we have dealt with authentication, so who am I? But we’ve not dealt with authorization. What can they see? And at the moment, they can see nothing. And we’ll be looking at how to deal with authorization in the next video. Just one final thing. When I logged in initially with my SQL Server authentication, I was an SQL Server administrator. Now we’ve created another administrator. This is an Azure Active Directory admin. So what’s the difference between them? Well, both of them can create users based on SQL Server authentication logins. Both of them can create similar to what we’ve done with Susan, contain database users, but based on SQL server authentication without logins. The only thing that’s this one can’t do that this one the Azure Active Directory admin can is create a contained database user based on the Azure Active Directory users and groups, which is what we have done just here.

• Category: Uncategorized