DP-300 Microsoft Azure Database – Implement compliance controls for sensitive data Part 2

4. 36. implement data change tracking – Change Data Capture

In the previous video, we implemented data change tracking CT. However, that didn’t tell us what a particular item went from or to. If we want that, then we’ll have to use a different technology called Change Data Capture CDC. However, I cannot do it on the database as it exists because it is a bit too low of a level. We have currently got it on the Basic pricing tier, and that won’t work. So I could upgrade it, do the Change Data capture, and then I’ll have to remove the change data to capture to go back down to Basic. Or what I could do is create a database just for this one particular video. So I’m going to create a single database, and it’s going to be called DP 300 Database Two.

It’s going to be on the same server and let’s see what configurations we need. Well, it can’t be on the basic level, it could be on the standard level, but only insofar as we get it up to a minimum of S Three. So s zero won’t work. So this, as you can see on screen, the minimum that you can use change Data Capture. Now you can use change data capture on Azure SQL Database. Azure SQL managed instance and SQL Server on a virtual machine.

So I’m just going to leave it as that. So s three. This is the minimum. I’m going to add my current IP address and I’m going to add some sample data. So let’s create that and I’ll pause the video until it’s finished. Right, well, that’s been done. So I’ll go to this new resource which is on a standard S Three, and if I go into my server behind the database, you can see that there are now two databases, two as your SQL databases. So this latest one, database Two.

So make sure you’re on Database Two when you have a look at this. So, first thing you need to do before you can enable CDC Change Data Capture for a table, just like Change tracking, you need to enable it for the database. And you do that with SP CDC enabled DB, then you can enable it for a table. Now, to have run this command, you need to be a sysadmin. Now, what happens is it creates additional objects. So if I go into table and refresh, you can see no change immediately. But if I go into system tables here, we can see the additional objects. Now, you can only use this, by the way, on user databases and not system databases. So now it’s enabled for a database, I can now enable it for a table. So here we have an example of this command. So. Sys SP. CTC enable. Table So some of the arguments are source schema. So that’s the bit before the dot source name. That’s the table name, in this case, a role name. So this database role is used to gain access to the Change data. Now, notice I don’t actually have a role called New Role. It simply does not exist. If I go into roles, I don’t have that role. So it’s going to create it and then the captured column list.

So what do you want to monitor? So, columns to be captured, you need to capture the primary key. In this case. Address, ID. And you can separate them with commas. You can’t use encrypted columns, by the way. So let’s run this and that turns it on for this particular table. Now I can see what my configuration is by using Sys SPCDC help change data capture. So you can see that I have Sales Art address, and I got this index here, got the role name here. And over on the right hand side we’ve got what we’re capturing. So what I’m going to do is just change one row. So here we have address, ID nine. The city of Buffalo. I’m going to update it so that Buffalo is spelled with one L, not two.

So, simple update statement. So after that’s done, you can see we now have one L in Buffalo. So now I need to find out what the changed roles are and we can see what they are using this. So, I’m declaring two variables as binary and I’m getting what’s called the LSN. This is the log sequence number, so the earliest one and the latest one, and then finding out all of the changes. So let’s run that and you can see that we have a change. It is Operation Four. That means update. One is delete to insert. Three also means update for address. ID nine and the City of Buffalo.

So now if I set it back, so this is a second update as opposed to a rollback, then we have a second operation and we see where it’s going. So now it’s a barthel with two LS. Now, just a word about this formula. It is a custom SQL formula. It has been made by the CDC and it actually has, right at the end the name of the table. So it’s not just one function for all tables, it’s one function for one particular table. And you can see the arguments from two. And I want all in this case. So if I would need to disable it from a table, I can use this exactly the same in terms of the first two schema for disable as enable, just one difference for the last. I say I want to disable all instances.

So I might have one instance giving me City, one instance trapping something else. Here I can say I want all of them, and then finally I can disable it for the database. So, this is how you implement data change tracking. So it’s supported in all versions of Azure that we’re talking about. It tracks historic data. It needs a minimum of one Vcore or 100 DTUs or Ettus. And because of that requirement, it can’t be used in Azure SQL Basic or Standard Tier, where we’re talking about S Zero, S one, S two.

And it can’t be used in an Azure SQL database elastic pool, where you’ve got the Vcore of less than one or the EDTUS of less than 100. You can see on the screen all of the various formulas you need. You need to enable it for a database, then for a table, and then at any point, you can ask, what has changed?

5. 37. perform a vulnerability assessment

In this video we’re going to perform a vulnerability assessment. So this is an online database, so it’s possible that some things may go wrong in terms of security. So vulnerability assessments are fairly important. As your Defender for SQL costs around $15 per server per month. So when you consider that some of the databases we’re looking at, for instance, this stack standard S Three, is costing something around $180, that isn’t exactly a huge addition. It is, of course, if you’re using a basic version, but you should only be using Basic and S Zero for testing purposes, really. So let’s go into Security and Security Center in the SQL database. So we’ve got no recommendations to display at the moment, but not a split. At the top, Azure Defender for SQL enabled at the subscription level.

So I’m going to click on Configure next to it. So I’ve already selected my subscription, it’s already on. I’m going to select a storage account. So here you can see these are the storage accounts in the selected subscription and in UK South, if you want weekly scans, you can switch periodic recurring scans to on. And you can see it will generally be on the day that vulnerability assessment has been enabled and saved. So if you enable it and save it on a Wednesday, then you will get an email on Wednesdays.

Now you can send scan reports to a particular address, so I’m going to send it to an address like this. You can also send email notifications to admins and subscription owners. So let’s click save. So saving the settings. So there it is, saved. So I’m going to go back into my database now to view details of the finding. Then again, it’s the same place, Security Center, and we can scroll down to the vulnerability assessment findings and we can click on view additional findings in the vulnerability assessment. So I’m going to do a one off scan. I’ll just click scan. As you can see, last scan time completely blank. So I’ll just pause the video until it’s finished. It only took a few seconds. And you can see there are four findings that it has done. I have got 30 passes, but I’ve also got four fails, one of which is at a high risk. So I can click on any of these and I can get more information about it.

Here we have server level firewall rules to be tracked and maintained at a minimum. And you’ve also got information, additional information about it, and how we can remediate or remediate. Now we have at the top approval’s baseline. So if I was to click on that, then I would say this is not actually a failure, this is fine and it will go into the past section. I can also say, no, this isn’t part of the baseline at all if I want to. So I’m setting this as the baseline, go back into my vulnerability assessment. So next time that I scan it. You can see there are pending baseline changes. Run a new scan. This particular item will go into the past section. And here we are. So I’ve got 31 passes.

I can also click on scan history, so I can see what the history of my vulnerability assessment is, and I can also export the scan results to Excel, so I can have my copy on my computer and I can go, okay, this is the result as of this particular date. Let’s have a look at it compared to previous results. So this is how you can perform a vulnerability assessment, so you can set it up to do it automatically.

If we go to top here as your defender for SQL and configure a storage account and periodic recurring scans, and we can also have a look at the vulnerability assessment findings, do a one off check, click on scan, we can scan history and we can have the export scan results. So that is performing a vulnerability assessment.

• Category: Uncategorized