AZ-204 Microsoft Azure Developer Associate – Implement Azure Security part 3

11. AZ-203/ 204 – Working with the Key Vault – .Net – Managed Service Identity

Hi and welcome back. So now let’s look at the program on how we can actually get a secret from the keyword using the managed service identity. So this program is actually going to run on an Azure virtual machine. I’ve already spun up a virtual machine for the purpose. Now, this is the keyword Uri. So I’m not mentioning any client ID, any client secret, just the Uri of the secret, which I need to fetch. That’s it. I then go ahead and create a new token provider. So in this token provider, it actually goes on to the local metadata for the virtual machine and then generates a token which can then authenticate itself with the Aze or keyword service. That’s all I have to do. So here I have a virtual machine that’s already in place. Now, for this to work, we have to first go ahead on to the identity of the virtual machine.

We need to make sure that the system assign identity is on. And let’s click on Save. Let’s click on yes. Once this is done, we have to go on to the Azure Keyword service. We have to make sure that we now assign some permissions to the identity which has been assigned to the virtual machine. So let’s go ahead and add an access policy. Let’s go on to secure permissions, I can get and list. Let’s select the principal. Let’s now search for our demo VM machine so we can see that it’s now there. And this is only because we have enabled the system identity for the virtual machine. Let me go ahead and hit on select. Let me click on Add again. Don’t forget to click on Save.

Right, so that’s done. Now, on this virtual machine, I’ve already gone ahead and copied the binaries for the program which I just showed you. So if I just run the application, you can see you’re getting the output has desired. So in this code, we didn’t add any isa client ID, any client secret. The identity is automatically authenticating itself with the Azure Keywall service. It gets the access token. This program is automatically being authenticated because the system manage identity which is assigned to the virtual machine has now authenticated itself using an access token onto the Azure Keyword service. Right. So this marks the end of this lab.

12. AZ-203/ 204 – Lab – Managed Service Identity – Azure Web App

Hi and welcome back. Now, in an earlier chapter, we had seen how we could use the managed service identity on a virtual machine. We then had code which would run on the server, and that code would basically get a token. And the virtual machine using that managed service identity will then authenticate itself on to the Azure Keyword service and then fetch a secret. Now, let’s do the same thing, but this time let’s use an Azure Web App. So let’s use the Azure App service. So over here I have simple code. So I just have an ASP net application in this page, in the page load, I’m just calling a Get Async method. In that method, I’m basically again getting a token on to the Azure Keywall service, and I’m fetching the value of a secret known as Demo Secret.

So if I go onto Azure, if I go on to my App service so currently I have one demo MSI App in place. I also have an Azure Key Vault, which is also in place. So if I go on to the Azure Key Vault, let me ensure that I have a secret. Again, I’ll mention the secret has password. Click on create. So that’s done. So let me go on to Visual Studio. Let me go ahead and publish this web application. So I’m going to publish it to my Azure web app. So I’ll select an Azure app service. An existing one. I’ll choose my demo MSI app. Click on OK and go ahead.And then click on Publish. Now, once the publish is complete, I’ll go on to my Web App. Now, this service also has an identity setting. So let’s go on to that again, let’s go on to System Assigned. Turn it on. Click on save. Click on yes.

Now, once the system assign manage identity is in place for our web app, let’s go on to the Azure Keyword service. Let’s go on to access policies. Let’s ensure that we add an access policy again, for the secrets management operations of Get and List, let’s select our principle. So this should be our demo, MSI. App. So let’s choose an App, click on select, click on Add. Make sure to click on Save. Right, so this is done. Now let’s go onto our App. Let me go on to that web page. And here you can see you’re getting the value of the secret. So now the Azure Web App, it’s using its manage identity to actually authorize itself to use the Azure Keyword service. Right? So just another example of the managed service identity.

13. AZ-203 – Lab – SQL Server Encryption

Hi and welcome back. In this lab, let’s look at some of the encryption techniques which is available for Azure SQL Server. So, first, we have transparent data encryption. Here the database data, any associate backups and log files are all encrypted for you. Now, when the data is actually fetched from the database itself, it is automatically decrypted for you. So, even though the data has been encrypted on the underlying physical infrastructure, when you are going ahead and accessing the data within the database itself, the feature will automatically decrypt the data for you and then give it to the user. Now, this feature of Transparent Data encryption is automatically enabled for Azure SQL databases.

For Azure SQL managed instance, you have to enable it manually. Now, you can use a key that’s spread by Azure for the encryption, or you could create your own key in the Azure Keyword Service. Now, next, we have another feature known as Always Encrypted. This feature is used to protect sensitive data in your database. Here, the data is protected at rest. Also, it is encrypted when it is moved from the client and the server and whenever the data is in use. When you use the Always Encrypted feature, the sensitive data will not appear in plain text. You can enable the always encrypted feature with the help of SQL Server Management studio.

Now, when you’re working with the Always Encrypted feature, what happens in the background? So, the Always Encrypted feature will first create something known as a column master key. It will then create a column encryption key out of that column master key. And the column encryption key will be used to encrypt a column within a table in your Azure SQL Database. Now, the key itself, the column master key, and the column encryption key can be stored either in a Certificate store, a window certificate store, or it can be stored in the Azure Keyword Service. Now, if you’re going to be using the Azure Keyword Service to store the key, you have to ensure that the user has the right permission.

So, these are the following permissions which need to be allowed for the user during the encryption process. It will actually ask you to log into a zero, and if you don’t have or the user doesn’t have the right permissions, the encryption process will fail. Now, once the encryption is complete so let’s say an application now needs to connect to the database and fetch the data from the encrypted column. So there are classes and methods in place which can allow you to decrypt the data when it’s fetched from the database. Now, let’s go on to is here. Let’s look at transparent data encryption and also look at the Always Encrypted feature. So here we are in Azure.

Now, let’s go ahead and create a SQL Server first so we can go on to databases, choose SQL Database, select the Resource Group, give a database name. We have to create a new server. So we just have to mention the server details which will be used to host the database. Please make sure to make a note of the admin login details which are specifying over here. Because you will need this to log into the database server. I won’t use an elastic pool. As of now, I’ll go on to Configure database. I’ll just choose the basic tier for the database which is just for demo purposes. On the additional settings, I’ll go ahead and install or ensure that the sample database is installed so the Adventure Works will be created as a sample database.

I won’t add any tags on the review and create let’s go ahead and create the database. Now, once the deployment is complete, if you go on to all resources so we now have a SQL database and we have the SQL Server which is hosting the database itself. Now, if you go on to the server itself, if you want to go and actually log into the server and see the database, you first have to ensure that you set the firewall settings. So you have to ensure that your workstation has the ability to connect to the database server. By default, there is kind of a firewall in place which denies all connections onto the database server. Now automatically it detects what is the client IP address you’re working from. So currently, this is the client IP address of my workstation. So I can just go ahead, click on Add Client IP and then click on Save.

So now this is going to go ahead and ensure that this rule is added to the firewall and this will allow us to connect to the database server. So here I have SQL Server management studio in place. I can go ahead and click on Connect to a database engine. So here I need to give the server name.If I go on to the overview, I can copy the server name. From here, I can then enter the server name and the authentication details. Click on connect. Once you’re connected, you can see your demo database. So now remember that it will have the Adventure Works data. So if you expand the tables, you will see the tables as part of the Adventure Works database. Now if you go back onto Azure, if you go on to now the database itself, there’s something known as transparent data encryption.

So remember, this ensures that when your data is stored on the underlying physical infrastructure in the Azure data center, it will automatically be encrypted at rest. Now by default, the data encryption is already enabled for the Azure SQL Database. Now let’s go on to the concept of using the always encrypted feature. So this will ensure that even when you’re viewing the data, the data in the table itself is encrypted. Now for this, we’re going to make use of the Azure keyword resource. So let’s go ahead and create a new resource let’s search for Key Vault. Let’s choose the keyword service.

Click on create. I’ll ensure the Keyword service is in the East US region because our SQL Database is also in the same region. I’ll just give a vault name and select the Resource Group. I’ll go ahead and click on Create. Once a key vault is in place, let’s go on to the Resource. Now I want to go ahead onto the Access Policy section. So currently we have the default user which is our admin login, which is given permissions onto the Keywall service. Now we have to ensure that for the key permissions, we have the necessary key permissions. So let me enable or select all of that and then let me click on Save. So when we enable the Always Encrypted feature from SQL Server Management Studio, we are going to log in with our admin account details since we do have the permissions.

Now in Access Policies, the SQL Server Malma Studio will have the ability to create the encryption keys which can then be used to encrypt the data. Now, if we go on to SQL Server Malma Studio, let us go on to the Customer table. So let me just select the rows. So now let’s say that we want to encrypt the Email address column as part of this table. So we don’t want users to see the email address. When users view the data on the table, it will actually be shown in an encrypted fashion. Obviously, when an application is trying to access the data, the application will then decrypt the data and use it accordingly. So how can we use Azure Keyword? Or how can we use the Always Encrypted feature to encrypt the data in this column in this table? So let’s right click the Customer table. Let’s choose encrypt columns.

So now we’ll be presented with a wizard. Let’s go on to next. Now we have to select what is a column we want to encrypt. So since we want to encrypt the email address, let’s choose that. Let’s choose the type as deterministic. Go on to next. Now we can store the key either in a Window Certificate Store or in the Azure Keyword Service. So I’m going to be using the Azure Keyword service. It will ask us to sign in. So I’m going to sign in with the Admin user account and password. Once we’re signed in, we’ll select the Azure Keyword. Go on to next. We’ll go on to Next and click on Finish. So now it’s going to go and do three tasks. Now it will probably ask you to log into your account again.

So it’s going to perform three tasks. It’s going to create the column master key, it’s going to create the column encryption key, and then it’s going to go ahead and encrypt the data in the column. Now, once the encryption operation is complete, you can go ahead and again execute the query to get all the data from your underlying table. And now you can see that the email address is encrypted. So you can’t see the email address anymore. So the Always Encrypted feature is now using the keys, which are stored in the Azure Keyword, to encrypt the data in this column. Again, please know that if you have an application, it has to use the correct decryption techniques to decrypt the data and then work with it. Right, so this marks the end of this lab.

14. AZ-203 – Lab – Azure SQL Server – Dynamic Data Masking

Hi and welcome back. Now in this lab, we are going to look at the data masking feature, which is available for Azio SQL Server databases. So, with the data masking feature, the data in the database table can be limited in its exposure to nonprivileged users. In order to implement data masking, you will create a rule that would mask the data. Based on the rule, you can decide on the amount of data to expose to the user. Now, there are different masking rules which are available. So you have the credit card masking rule. This is used to mask a column that contains credit card details. Here, only the last four digits of the field are exposed. Next, you have the email here, the first letter of the email address is exposed and the domain name of the email address is replaced.

You then have custom text. Here you can decide which characters to expose for a field. And then you have the random number. Here you can generate a random number for the field. Now, let’s go on to Azure. Let’s see how we can implement data masking. Now, in Azure, what I’ve done is that I’ve just provisioned a new database called New DB. Again, this has the Adventure Works tables in place. Now, if you want to implement dynamic data masking, you can go on to that feature over here and you can go ahead and add a mask. Now, one quick thing to note is that when you add a mask to master data in a particular table, if you are the administrator of the database, you will still be able to see all of the data without the mask.

This is only for non privileged users. So when you add a mask, it’s only non privileged users who can actually not see the data based on the mask which has been implemented for that column. So, what we’re going to do in our database is we are going to create a new user, so that when we login has a new user, we can see that the mask has been implemented. So here I’ve gone ahead and refreshed the databases on our server. So now I have the new. DB database as well. Again, if you look at this database itself, you will see that it has the Tables of Adventure Works. So first, let’s go on to security. Let’s go on to logins. Let’s create a new login. I’ll execute the query. So now we have a login user in place. Now let’s go ahead and create a new user onto our new DB database.

Based on that login, let’s ensure that we add the data reader role. So this user can only read the data in the database. So let’s execute the query. So that’s also done. Now, if you go on to the tables. So let’s go on to the customer table. So currently, remember, we are viewing the data as the demo user. So this is the admin of the database itself. Now what we’re going to do is that we’re going to add a data mask on to the email address column and the phone column. So now you can see that as the admin user, you can see all of the data as it is. Let me go ahead and also Login has the new user. So let me click on Connect. So now I’m Connected has the new user let me go ahead and select the rows again so you can see the email address and the phone columns are as they are now if you go on to a zero now we have our Dynamic data mask let’s go ahead and add a mask.

Let’s choose a sales. Lt. Schema. Let’s choose the customer table for the column, let’s choose the email address and we have the different masking fields formats so we have the default value, the credit card value, the email, the number and the custom string. Let’s choose the email, let’s click on Add and let’s click on Save. Now the mask has been applied if you go back on to SQL Server. So now you can see after refreshing the query that the email address has now been masked if you try to view the data as demo user, let’s go ahead and select the data. So over here you can see the email address column is hazardous so this mask is only applied for non privileged users. Now let’s say for the phone column we basically want to mask the middle three digits and we want to leave the first three and the last four exposed to the user so we can add a custom masking for this.

So if you go on to a zero, let’s go ahead and add a mask. I can choose Sales Lt choose Customer this time we’ll choose the phone field or the phone column. Now in the masking let’s choose a custom string. So we want to expose the first three characters and the last four and for the padding string we can add this let’s click on Add and let’s click on Save. Now if you go back on to SQL Server, let’s execute the query for the new user. So now you can see that the masking has been applied even on the phone column. So the first three and the last four characters are exposed and the middle characters have been padded with what we specified as a pairing string, right? So this marks the end of this lab in which we looked at Dynamic data masking.