DP-300: Administering Microsoft Azure SQL Solutions Certification Video Training Course

DP-300: Administering Microsoft Azure SQL Solutions certification video training course by prepaway along with practice test questions and answers, study guide and exam dumps provides the ultimate training package to help you pass.

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's DP 300 requirements don't go into a huge amount of detail as to the Azure security that you might need. It just has a limited number of things that we'll be looking at. So we'll be starting with 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 Auth N, and authorization, also known as Auth Z.

So authenticity is a part of who you are. So in other words, if I log in, how can I make sure that it is me logging in and providing support to other people? Authentication should be separate from authorization, which is what you can access. So that's quite different. Now, if we have a look at this authentication, you can see we can use Windows authentication.

If you are on a Windows machine or in a Windows domain, Windows authentication is ideal. 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 in your Active Directory. So if I go into the portal and look for your Active Directory, which is also an acronym for Aid, you can see that we have our tenant. So this is our organization, which has users. And then if I click on the users here, you can see that I've got multiple users setup from previous courses that I have created.

So why might you want to use Azure Active Directory as opposed to SQL Server authentication? It's just a username and password, with SQL Server authentication potentially sent in plain text, whereas this method is much more secure and can use additional methods to ensure it's you. Now, if you are used to using Windows Server, you'll know that there's a Windows Server Active Directory that is different from Azure Active Directory, even though they've got the same Active Directory. However AAD. The Azure version can synchronise with the on-premises Windows Server Active Directory. Now, we have already come across the ActiveDirectory, 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 after I 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 ActiveDirectory and have a look at authentication methods, you can see that at the moment it supports things like the Fiddle security key. So, fiddle with the security key; this is hardware that you have on your computer. So you can see that Microsoft authentication is extremely strong. 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 for a temporary access pass that is not necessarily as strong as it is for onboarding and 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 passthrough authentication. So if we start with Federated Authentication, if you want to integrate with an existing federation provider, then you can use Federated Authentication. Assume I had another app and wanted to use it with a federated authentication login to Azure Active Directory.

So this can also be used if you have a sign-in requirement that is not natively supported by Azure Active Directory. There are only cloud-based identities. So this is when you want Azure Active Directory to handle sign-in completely in the cloud. So when you say ActiveDirectory, you take care of it completely. And then there's pass-through authentication. That concludes the list of events. So, if you don't have a sign-in requirement that isn't natively supported by Active Directory, and you want to do something else, perhaps you want to enforce user-level Active Directory security policies during sign-in, perhaps because you don't have integration with an existing federation provider.

So you'll probably hear these three different terms for "cloud-only identities," which is what we're using here. hybrid identities that support cloud authentication, maybe using a password hash or, as we said, a pass-through authentication. And we've also got hybrid identities that support federated authentication. Let's take a look at some of the other ways we can log in.

Well, if you've got an app running on an Azure Virtual Machine, then you can use passwordless authentication. 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 one that's not joined, then you have certificate possibilities. You can create a certificate, and the app can connect to your Azure data using that. So that's what apps are. 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 that is not domain-joined, then you can either use integrated authentication or you can use 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 AED, you can see I've got these users, and you can give a new guest user, a new user, or a bulk invite. You've got plenty of other things—per user, multifactor authentication, etc.

And so for the new user, you need to provide their identity, their groups, and their roles. In other words, what can they do to gain authorization and then sign in and provide additional information? So that can all be done there. Now, I already have some AIDS users, so I don't actually need to create one there. If I did create one, Azure will give you an automatically generated password, by the way.

If I want to delete someone, I can simply click on their name and then click Delete User. This is where you can manage all of your users. So Azure Active Directory authentication So if you look for it in the portal, you can see that you can manage all sorts of things in it. And we're only going to be looking at the tip of the iceberg in this particular course with regard to how it reacts with SQL Server. So, from here, you can add users, groups, and other authentication methods, among other things.

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. By the way, this isn't going to work, 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 username. So I'm going to have Jane here. So Janet and I will put this in hard brackets. So there is our user and we'll say from external provider. That is to say, obtain it from Active Directory or Azure. Active directory. Now you can say other things. So, with whatever the default schema is and whatever the default language is, And you can also say a few other things. But in essence, we've got the createuser name of the user from an external provider.

So we don't need to create a password or anything because we already created one when we created 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 identity in Azure Active Directory identity.And there we go. Execute the work, find an 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 simply right-click on this and select disconnect. And let's connect again using Azure Active Directory. So I'll put in my name and connect.

I'm having trouble logging 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 already have 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 SQLServer, not the database, but the server on the portal. And then I went to the Azure Active Directory setting on the server. So I need to set up 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 "DB Honor" 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, incidentally, that there is a support-only Azure IT 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, 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; 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 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 Jane. I am going to add another user. So I'll copy this here, and I'm going to put Susan in. So create user from external provider. And there we are. command was successfully completed.

So now if I try connecting to this using Azure Active Directory and I look for Susan and I enter her password, you can see that the login failed. Now, this is not a particularly good error message (18456). And if I show you the details, state one when you look it up, it simply means I can't give you any more information and you can spend hours figuring 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, this is in Jane. We can see unrestricted views. If I expand views here, there are no views here. So what's going on? Well, in this chapter, 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 do that 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 administrator.

So what's the difference between them? Well, both of them can create users based on SQL Server authentication logins. By containing database users, they can both create something similar to what we did with Susan. but based on SQL Server authentication without logins. The only thing this one isn't capable of is The Azure Active Directory admin can create a database user based on the Azure Active Directory users and groups, which is what we have done just here.

3. 25, 113. configure security principals – roles

In the previous video, we solved the authentication problem by adding Susan to my database. But now I can't see any tables. Well, maybe it's just a mistake. Maybe it's just not there graphically. So I'll write, click here, and go to a new query. So this is in my Sunner, and I'll just go to Star and select this one's Lt. Delta sales address from there.

No, the select permission on this object was denied. So not only can I not see it in my tree view, I've got no permissions. Okay, so what can I do? One option is to assign Susan to a role. So if I click on roles here in the database, you can see that there are lots of different types of roles, and it's very important that you know what they mean. DP 300 for the exam, so DB Ownerdatabase Owner This is a fixed database role with most configuration, without all configuration, and most maintenance activities in an Azure SQL database. So some activities may require other permissions. This now includes the database permission to Drop. As you can see, it is extremely important. So I am going to add this member to this role. So what I need to do is alter the DB owner and add this member.

Okay, so that's done. And now, if I refresh this database with Susan's login, you can see that we now have access to all of the databases. And if I run this query now, you can see we can do that. Now if I want to remove her, I drop the member. As a result, DB owners are extremely dangerous. It gives far too many permissions unless you need to do so. So maybe I can add something a bit lower than all of that. And you can see that if I refresh this, all of those tables disappear. So what do the other roles do? Well, there is security administration; you can modify role memberships for custom roles only, and you can manage permissions. OK, that doesn't sound too bad. A big warning on this one because you can elevate your own permissions.

Because you can manage permissions, you can manage your own and therefore add additional permissions for yourself. These bottom two are another one to be cautious of. As a DB Access administrator, you can grant and revoke database access for logins and groups. DB backup operator. This is not applicable to an Azure SQL database. It's there just for compatibility purposes. You can backup the database, but it's managed by instances and virtual machines. DDL admin, you can run a DDL command. So we're talking things like create, alter, and drop. With the DB Data Reader, you can read all data from all user tables and views. That sounds better. So if I add that and let's take a look, I'll have access to everything once more.

So if I select "Star Brilliant," maybe I want to add an extra item. So, now that I have a primary key, I can write and click Edit the top 200 rows, and here we are. And I will add an extra item at the bottom. Everything is read-only. I can't do that because I have been given a data reader. If I wanted to do that, I'd have to be assigned Data Writer as well. So that is the ability to add, delete, or change data in user tables, and all user tables at that.

So now you can see it's not saying "Read Only." A DB deny data writer is the inverse of this DB data writer. And there's also a deny data reader. Now that means that you cannot read old data or add, delete, or change data in old user tables. Assume I didn't want this, so I'll delete this member and remove it from the reader. So now again, I can't do anything that none of the tables have access to. So what you might be wondering is, is there something a bit more granular that you can do? So, for instance, maybe I want somebody to have access to one particular table. Well, that is indeed possible, but not with these fixed database roles. "Public," by the way, is everybody.

So when you get access to the database, you have access to the "public database" role, which gives you absolutely nothing. What's new in Azure SQL Database, and if you're used to them in the on-premises version, are two additional database roles that aren't visible here. That's because they only exist in the master database.

So the master database has a lot of system tables and views and that sort of thing. So if I go into security and roles and database roles, you can see that there are two additional items. DB Manager, you can create or delete databases, so it will connect as the database owner, the DBO, and then, in Login Manager, you can create or delete logins in the master database. So that's the equivalent of the security administrator on an on-premises SQL Server, but this is for the master database. Now you can get a list of all the database roles with the still-existing procedure for the "Help" role.

So SP underscore "Help Roll," and that gives you this list. Now there's also role-based access control in Azure, so that's usually abbreviated to RBAC, or Role-Based Access Control. And if you're going to the IAM, that's the access control. You can add role assignments. And there are free role assignments I particularly want to talk about.

So SQL, DB Contributor, and SQL Server Contributor allow you to manage SQL databases and servers, but you don't actually have access to the contents of the databases themselves. You can't also manage their security-related policies. That would be the SQL Security Manager. So you can manage the security-related policies on Azure for SQL servers and databases, but not actually have access to them. In other words, if you don't have incidental access to this type of thing while deploying your databases, Azure will use a server administrator. So you may hear that term. It's a principle in the Azure SQL database.

Now, if you're wondering when I'm going to be mentioning server-wide login permissions, well, I'm not going to do so with regard to the Azure SQL database because you don't actually have access to the underlying server. So we'll be talking about that when we're looking at managed instances and virtual machines. So this is how you can configure security principles with regard to roles. So change the name of the role, then add or remove the member and the member's name.

So we have DB owner and DB security administrator, which you must pay special attention to and not delegate to anyone else. And then we've got data. Reader: data Writer: denied data Reader deny Data Writer DDL Admin, so running DDL commands requires Access Admin to add or remove database access for logins and groups and then back up the operator, which is not usually necessary in an Azure SQL database.

4. 25, 113. configure security principals - GRANT/REVOKE/DENY

Now, suppose we wanted Susan to have access to one particular table but not all of them, like a data reader would give you.

So let's say we wanted sales to address how we could do this instead of using roles. We could just say, "Well, I want you to grant this particular permission," so we could do that with a grant. So I want to grant select permission to an object and then two columns. So it's this particular object. So I'm granting it to her and to Susan.

So if I run that and notice there are no notables that we can see, no user tables, But now that I've run that, we now have access to the exact one that I've just been added to. So if I go in here, I can now run the select statement, so I can't run this select statement on any other table; it just doesn't work. Select permission was denied for the object. Now, it's a bit confusing because it's not actually true. And I'm looking at the word "denied." How I would write this is "select permission that was not granted."

So how can I say, "Okay, I don't want to grant this permission; I want to do the reverse?" But that would be "revoke," "revoke," "select" on the object. So now, if I do this, if I refresh, I can't see this table anymore, and this execute doesn't work. But what does denying do? "Deny" means definitely not. So let's just go through this again. Grant means yes.

Revolt means to remove the yes, whereas denial means to say no. And a no is preferable to a grant. So if I have, for instance, a security role, maybe I've got a security role where I've got the data reader. So let's add to this data, readers, and that will get me everything. So refresh this. So Susan should have access to everything. If I now say deny this selection, then I have a role that says yes, granted, and I've got a denied role that says no.

So what happens? I can no longer select it, and this time it's true. It hasn't been denied. Now, you'll see this error message when it's been denied or when it's currently in the state of revocation; revocation just means somewhere in the middle. How do I get rid of a denial? I reboot it. So revolt just means no permissions. It does not imply that there are no two permissions. It's basically the same as no comment. So it removes yeses and noes, but only insofar as you have direct permissions.

If you obtained the permission from somewhere else, such as being assigned the role of DB datareader, revocation will not override a role, grants, or any other permissions. So now I've got access to it because, even though I have no comment, I have it through this particular role. So this is the principle of how you can grant, deny, or revoke permissions. In the next video, we're going to have a look at what those permissions can be.

Prepaway's DP-300: Administering Microsoft Azure SQL Solutions video training course for passing certification exams is the only solution which you need.