DP-300 Microsoft Azure Database – Upgrade strategy, configure database authentication, and filegroups Part 2

3. 24, 112. create users from Azure AD identities (MIs and VMs)

In this video we’re going to look at how we can create users from Azure Active Directories from this perspective of the managed instance and the virtual machine. So first of all, we can create users using create user name of user from external provider. We’ve had a look at that in Azure SQL database. Now we can create a user not from Azure Active Directory identity as follows.

You can say create login name of login with a password so that’s a login to the entire instance, but that doesn’t get you into any particular databases. And then you can say create user for login. So if I change what I’m looking at to the DP 300 Mi notice by the way, I’ve got access to model msdb and Tempdb in the Mi, which I don’t have. When I was looking at the Azure SQL database and run this, then if I go into security logins and refresh we can see we have got the My login there. And if we’re going to this database and going to security users and refresh there, we can see the My login. Now the My login won’t have any actual permissions, so we’ll have to add whatever permissions you would need. But we have done that previously.

Now you can see that the logins and users are kept separately. The logins are kept in the master database and the users in the individual database. So Microsoft says it’s good practice to actually go to the master database, create the login there and then go to the database and create the user there. So let’s see how we can create a user from an Azure Active Directory identity. So you can see very similar to what we have done previously, create user from external provider. Now we’re creating a login from an external provider and then we’re creating a user.

So let’s concentrate on the login first, but we just create the user from the login. So let’s execute and we’ll find it does not work. So after 45 seconds we have an error message and it says that it can’t be found. Now that’s not entirely the right answer. It’s really that they have not been connected the managed instance and there’s your Active Directory. Let’s just have a look at our managed instance here. And if I go down to Active Directory admin part of settings you see managed instance needs permissions to access the AAD. So click here to grant those read permissions. So yes, I’m going to grant those read permissions and now I can do as before, so I can set an admin. So I’m going to set Jane as the admin save.

Now I’m going to log it in as Jane. So we connect using an Azure Active Directory and this is going to be Jane and I have to log in here as well. And there I am logged in as Jane. So now as Jane I can create a new query. And so if I go into here, copy this in then now I can create the login from the external provider and now I can create the user based on that login. However, if I try to somebody who is not logged in as Azure Active Directory user, so I’ve just logged in as an SQL Server authentication, then you can see that once that connection has been made, that I can add additional logins as requested. And so now if I change this so I’m adding a user authentication here, I can do that.

Now what can Logins do? Logins can do SQL Agent management and job executions, database backup and restore operations, auditing, trigger logon triggers and set up server brokers and Dbmail. So you can see it’s fairly easy to create users for measure Active directories, but there’s a little bit of permissions that need to be set up first. And you do that by going into the SQL managed instance in the portal, going to Active Directory admin, and then you’ll see a sign at the top if you don’t have the right permissions. And then you can set up your Active Directory admin just like we did with Azure SQL Database. With the virtual machine, you don’t necessarily have access to the Azure Active Directory, so you wouldn’t be able to use the from external fighter. However, you can create a login with a password.

You can also put in check Expiration and check policy on and off. So that specifies whether the password expiration policy that’s check underscore Expiration or the complexity requirements that’s check underscore Policy should be enforced. One final thing, if you want to check which server principles you have got, then you can select Star from Server principles. So that will give you a list of all of the principles, including Susan that we have just created. And there’s Microsoft and there’s also my Login, which we’ve also previously created. So we’ve previously had a look when we’re looking at the Azure SQL Database at Sys Database principles, because the managed instance and SQL Server on a virtual machine has got a server that you can configure with a lot more options. Here we can have a look at Sys Server underscore principles as well.

4. 111. manage certificates using T-SQL

In this video we’re going to look at how we can manage certificates. Why would you need a certificate? Well, apps running on a nonazure machine that are not domain joined will need some way of saying it’s me, some way of authenticating and the recommendation for this rather than using a username and password is to use a certificate. So to create a selfsigned certificate so is just one where you are just saying it’s me. You can use create certificate name of the certificate encryption by password and you give a complicated password, a complex password.

Now if you don’t specify the encryption by password then the private key is encrypted using the database master key. Now we can also say with subject so that is a field in the certificate metadata and you can specify an expiry date. So here is the end of the year 2029. You can also have a start date. Now both of these are in UTC or GMT, so coordinated Universal time. Now if you don’t specify a start date then start date defaults to the current date. If you don’t specify an expiry date then it’s one year after the start date. So let’s create that and that’s done. Now this certificate is stored in the master database. If I go down to security and certificates there you can see the certificate name so we don’t have too many things you can do to it. You can delete it if you want and you can do that also using drop certificate. Now if you are bringing your own key or by or K as you might see it called, then the Azure key vault can store customer managed certificates.

Now to restore a previously created certificate then you can use create certificate from file and then you’ve got the file path there. Azure SQL database by the way does not support creating a certificate from a file or using private key files like I’ve got here, but it is supported in managed instance and SQL Server on virtual machines. So you can see we have a private key as well as a certificate and a description. Now you can alter the certificate but this is only for the private key here. So we can remove private key or we can set a new private key. So while you can do things like change the password, you can’t change the subject or the expiry date using that you need to drop the certificate and then recreate the certificate. Another way of creating a certificate is from an assembly, a DLL file and you can see that users from executable file as opposed to from file.

So this is how we can manage certificates. So you can do a self signed certificate and by default as you can see it’s stored in the master database or you can use one from a file, a previously created certificate with a private key and you can decrypt by password. You can create one from a DLL file, but you can only use alter certificate to alter the private key. So, for instance, I wouldn’t be able to alter this certificate and alter this password? You’d have to drop it and create a new one because this is a self signed certificate.

5. 26, 113. configure database and object-level permissions using graphical tools

In this video we’re going to have a look at how we can configure security principles for the M I in VM, the managed instance and virtual machines. But I want to start by looking back at the Azure SQL database. We had all of these database roles and we could create a new database role using TSQL. However, if I tried to go new database role that would just get me to the TSQL, there is no GUI, no graphical user interface there, there is for the Mi and VM. So if I go down to the same place, database roles right, and click a new database role, then we do get a Gui.

So I can type in a new role name and I can go to very securables so I can click search and I want a specific object and I want an object type table and I want to browse the tables. So DBO my table and then I want to add some security to it. So I want to grant an altar, I want to grant an insert, I want to deny a select. Now this is much easier than if I had to do this in the Azure SQL database and we’ve also got a script up here, so if I wanted to repeat it, then I can script all of this to a new query window and execute that however I want. Now just have a look at the things that you can secure. If I go back into new database role and have a look at the search, I can secure all objects of these different types.

So we’ve got databases, so databases if I try and secure a database you can see that we’ve got access to alter any and things include application roles, assembly, asymmetric, keys, certificate contract, full text catalog, message type, remote, surface binding, we’ve got database roles, routes, schemas, search, property lists, services, symmetric keys and users. Now, even if you don’t know what all of those are, you can see that there’s an awful lot of things that you can secure at the database level if you look what else we can secure.

So again, I’ll go to all objects of the type and expand this list. You can see we can secure schemas types, both user defined data types and table types. We can have aggregate functions, inline scalar, table valued functions, stored procedures, queues, synonyms, tables, views and external tables as well. So it’s quite a comprehensive list and it can all be done graphically and then you can script it to a new query window if you so wish. So this is actually for the Mi and VM, a good way of being able to configure database and object level permissions using graphical tools.