DP-203 Data Engineering on Microsoft Azure – Design and Implement Data Security Part 3

7. Lab – Azure Synapse – Auditing

And welcome back. Now in this chapter I want to go through the auditing feature which is available in Azure Synapse so you can enable the auditing feature for an Aziosql pool in Azure Synapse. This feature can be used to track the database events and write them onto an audit log. The logs can be stored in an Azure storage account or in a log Addicts Workspace or even Azure Event Hubs. Now we will stream our auditing data onto a Log Addicts Workspace. This helps in maintaining compliance. It helps to gain insights onto any sort of anomalies when it comes to your database activities. Auditing can be enabled at the data warehouse level, that is at the SQL Pool level or at the server level.

If you enable it at the server level, then it will be applied onto all of the SQL pools that reside on the server. Now I’ll show you how easy it is to go ahead and enable auditing for your SQL pools in Azure Synapse. Now here I am in my synapse workspace. Now before that, let me go ahead and in all resources. Let me create a new resource. I’m going to create a Log Antics Workspace so that our logs can be directed onto that Workspace. Now a Log Antics Workspace is a feature that is available with Azure Monitor.

This actually gives you a central logging place in which you can direct logs from various resources. In Azure. You can then use the custom query language to actually query for the data in the Log Antics Workspace. You can also create alerts based on the data that gets accumulated in that Workspace. So that was just a quick note on the Log Antics Workspace. Let’s go ahead and create a resource based on the service. So I’ll hit on create. Now here I’ll choose my resource group. I’ll give a workspace name. I’ll choose my location has North Europe. I’ll go on to next. So the pricing is pay as you go. I’ll go on to next. I’ll go on to review and Create and let me create the Log Antics Workspace. This will just take a minute or two. Now, if you go on to your Synapse Workspace, if you scroll down, there is something known as your SQL or thing in another tab. If I go on to my dedicated SQL Pool and here if I scroll down, we have an auditing feature that is available here as well.

So we can enable auditing either at the Synapse level, the Workspace level, or at the dericate SQL Pool level. Now, once we have our Log Antics Workspace in place, I’ll go ahead on to the resource. There is a lot that you can actually do with the Log Antics Workspace. At this point in time, we only want to send our audit log onto the Log Antics Workspace. So here for my Synapse workspace I’ll enable SQL auditing and here I’ll choose Log antics. I’ll choose my subscription. I’ll choose the workspace that we just created and let me click on Save. So now it will start sending this data onto our Log Antics workspace. Now please note that it could take around 15 to 20 minutes before you can actually see some data in your Log Antics workspace.

So I’m going to come back after some time now. I’ve come back after some time now if I scroll down and if I go on to the logs section, let me just hide this or close this. Close this, just open this. And here you can see there is a table under Log management that’s known as SQL Security Audit events. So if I close this and if I type in SQL so I can see my table, if I just run this hazardous. So this is based on a particular language. I said it’s the custom query language here. Then you will see all of the information about the SQL audit events. And then here you can create different sort of queries and then create alerts based on this query. So let’s say you want a SQL administrator to be notified in the case of any sort of event. You can actually define this here in the Log Anticipation walk space.

8. Azure Synapse – Data Discovery and Classification

Now in this chapter I want to go through the Data Discovery and Classification feature that is available in Azure Synapse. So this feature provides capabilities for discovering, classifying, labeling and reporting these sensitive data in your databases. The Data Discovery feature can also scan the database and identify columns that contain sensitive data. You can then view and apply the recommendations accordingly. You can also apply sensitivity labels onto a column. This actually helps to define the sensitivity level of the data that is stored in the column. So let’s go on to Azure to see this Data Discovery and Classification feature. So here in Azure, I’m in the resource for my dedicated SQL pool. Now here when I scroll down, there is something known as Data Discovery and Classification.

Let me hide this. Now here it is giving a recommendation. It is saying, we have found five columns with classification recommendations. If I click this, if I scroll down so it’s telling me that these are the tables that contain potential sensitive information. So it has gone ahead, it has looked at the data within the tables in the schema and it has given this recommendation. Here it is giving a sensitivity label and what is the information type? If you want, you can also manually add a classification. So if I click on Add Classification here, so I can choose my Schema, my table, my column, and I can choose from the existing information types that are available. I can also choose from the sensitivity labels that are also mentioned here.

For now, I’ll go ahead and select all of them, right? I’m selecting all and I hit on accept selected recommendations. Let me then click on Save. So now if I go on to the overview, I can see now that what is the distribution when it comes to my sensitive data. So over here, you are trying to use the Data Discovery feature and classification feature that is available in the dedicated SQL pool before we leave, if I hit on Configure here, here you can actually create your own sequel sensitivity label. And you can also manage the information types. So there are all the inbuilt labels, there are all the inbuilt information types. But if you want to create your own, this is something that you can do.

9. Azure Synapse – Azure AD Authentication

Now, in the next set of chapters, I want to discuss the feature of Azure ad authentication that is available when it comes to Azure Synapse. Now, Azure Active Directory is your Identity Store in Azure. Here you can define users, you can define groups, you can define applications. So these are users that could belong to your organization. So you can create users based on who you have in your organization. They can log in with those credentials onto Azure.

This is based on the Irony store in Azure active Directory. And then using something known as role based Access control, you can give them access onto resources as part of your Azure account. Now, in Azure Synapse, when we have our SQL pool in place, our dedicated SQL pool, which is our data warehouse, we have been connecting via SQL authentication.

So that means we are defining SQL based users and logins and then connecting onto our SQL pool. But since Azure Synapse and Azure Active Directory is part of the Azure ecosystem, azure Synapse has the ability to also enable Azure ad authentication for your SQL pool. So this means that you don’t need to create separate users, sequel based users.

Instead, if you have users in your organization already defined in as your Active Directory, then you can give access to those same users onto your dedicated SQL pool. So this helps to lift somewhat of that maintenance overhead of having users define both in terms of SQL authentication and users define it as your ad. Instead of that, you can just use the identities that are created in your Azure ad Directory in your tenant. So that is what we are going to see in the subsequent chapters. How do we work with Azure ad authentication?

10. Lab – Azure Synapse – Azure AD Authentication – Setting the admin

So here we are in Azure. Now if I just expand this, I can go on to the service of Azure Active Directory. So when you actually create an Azure account, you will have Azure Active Directory in place. You will have a default tenant in place here, if you go on to users here, you can define different users in Azure Active Directory. Here I have my main user as my Azure admin account, and then I also have some other users defined as well.

If I go on to my Synapse Workspace, there is something known as a SQL Active Directory admin. So here my Azure admin account is the current Active Directory admin. You can also set another user as the administrator for your Synapse Workspace. So here if I click on Set admin here, you can search for users that are defined in Azure Active Directory. So I do have a user defined as SQL user A. This is a user that’s defined in Azure Active Directory. So I could also make this particular user has my administrator. So here I’m changing the administrator. So this is something that you can do at this point in time. I am not defining this user as the SQL administrator.

I’ll just discard these changes so that we have your admin account back in place. I’m actually going to show you how do you create a user in your database based on an existing Azure Active Directory user in the next chapter. So now my Azure admin account is defined as the Active Directory administrator. Now here in SQL Server Management studio, so I am logged in as a SQL admin user. Remember, this is SQL based authentication. Now I can connect onto my database engine. Here. I can choose my same synapse workspace. And here in the authentication I’m going to choose Azure Active

Directory Universal with MFA. And here let me put in my Azure admin account details and hit on Connect. Just give my password. And now you can see we are now connected onto Azure Synapse Workspace. We can see our dedicated SQL pool and we can see all of our tables. So now we are connected with an identity that is set up in Azure Active Directory. Let’s move on to the next chapter, wherein will define a new user in Azure ad and give that user access onto Azure Synapse dedicated SQL pool.

• Category: Uncategorized