WEBVTT

0:00:02.940000 --> 0:00:08.820000
 Auditing in Azure at the control
 plane level is useful.

0:00:08.820000 --> 0:00:12.140000
 It's important, but it's only
 really part of the picture.

0:00:12.140000 --> 0:00:14.380000
 I'm just seeing what's going
 on with my resources.

0:00:14.380000 --> 0:00:17.320000
 Oftentimes, I need to
 look deeper than that.

0:00:17.320000 --> 0:00:21.140000
 I need to see what's happening with
 the actual data plane level.

0:00:21.140000 --> 0:00:25.960000
 That is what we're going to
 talk about in this video.

0:00:25.960000 --> 0:00:31.360000
 Specifically, I'm going to take the
 Azure SQL database as an example of

0:00:31.360000 --> 0:00:32.420000
 data plane auditing.

0:00:32.420000 --> 0:00:37.400000
 Keep in mind that there are other data
 plane auditing options or environments,

0:00:37.400000 --> 0:00:41.800000
 Cosmos DB, storage, account,
 data lake, etc.

0:00:41.800000 --> 0:00:44.320000
 But this is one of them and I think
 it's fairly common, so I think it's

0:00:44.320000 --> 0:00:48.660000
 worthwhile taking a look at how we
 would work with this and set up.

0:00:48.660000 --> 0:00:51.840000
 I'm going to talk about what Azure
 SQL auditing is, and then I'm going

0:00:51.840000 --> 0:00:55.060000
 to demonstrate Azure SQL auditing.


0:00:55.060000 --> 0:00:57.580000
 So what is auditing?

0:00:57.580000 --> 0:01:00.120000
 Let's draw this out a little bit.

0:01:00.120000 --> 0:01:01.500000
 SQL auditing in particular.

0:01:01.500000 --> 0:01:09.300000
 Well, we know that we've got this concept
 of a separation of data plane

0:01:09.300000 --> 0:01:11.220000
 and control plane.

0:01:11.220000 --> 0:01:28.500000
 So I've got my control plane,
 and I've got my data plane.

0:01:28.500000 --> 0:01:34.260000
 If I have a SQL database,
 I really have two things.

0:01:34.260000 --> 0:01:43.280000
 I've got an Azure SQL server instance
 and an Azure database, SQL database.

0:01:43.280000 --> 0:01:55.140000
 So this is my SQL server and
 this is my SQL database.

0:01:55.140000 --> 0:01:57.460000
 We're planning there, but oh well.


0:01:57.460000 --> 0:02:00.620000
 All right, so I've got a SQL server,
 I've got a SQL database.

0:02:00.620000 --> 0:02:09.760000
 Now, at the control plane level, my
 Azure functionalities such as, let's

0:02:09.760000 --> 0:02:18.080000
 say, creating this, so new, dot, dot,
 dot, SQL dot, dot, whatever that

0:02:18.080000 --> 0:02:24.900000
 is, that is going to go
 into the activity log.

0:02:24.900000 --> 0:02:31.100000
 And typically also go
 into Azure Monitor.

0:02:31.100000 --> 0:02:34.020000
 Now, the data plane, however, if we
 think about that, there are things

0:02:34.020000 --> 0:02:36.520000
 that happen at the
 SQL server level.

0:02:36.520000 --> 0:02:43.700000
 So for example, possibly creating logins
 or deleting logins, a possibly

0:02:43.700000 --> 0:02:49.780000
 working and setting different
 database settings.

0:02:49.780000 --> 0:02:52.780000
 And also database activity.

0:02:52.780000 --> 0:03:01.500000
 All of these things can occur and be
 monitored, and can be audited at

0:03:01.500000 --> 0:03:05.000000
 the SQL server level.

0:03:05.000000 --> 0:03:12.620000
 But then I also have activity
 within the database itself.

0:03:12.620000 --> 0:03:17.100000
 I'm sure why I capitalize activity,
 but we'll keep it consistent.

0:03:17.100000 --> 0:03:19.340000
 I've got database activity.

0:03:19.340000 --> 0:03:22.940000
 If I set up auditing at the SQL server
 level, it's going to audit database

0:03:22.940000 --> 0:03:25.080000
 activity across the entire server.


0:03:25.080000 --> 0:03:30.120000
 If I just want to audit activity within
 a specific database or specific

0:03:30.120000 --> 0:03:35.040000
 databases on a particular server, then
 I can set up logging or auditing

0:03:35.040000 --> 0:03:38.440000
 at the database level, database
 activity level.

0:03:38.440000 --> 0:03:39.980000
 All right, and that's
 pretty much it.

0:03:39.980000 --> 0:03:45.100000
 And that's what you're going to
 see with any auditable resource.

0:03:45.100000 --> 0:03:47.960000
 And again, you think about your data storage
 resources that are particularly

0:03:47.960000 --> 0:03:49.400000
 platform as a service.

0:03:49.400000 --> 0:03:52.780000
 You're going to have auditing
 capabilities for those.

0:03:52.780000 --> 0:03:55.040000
 And then of course, also again,
 you have the control plan.

0:03:55.040000 --> 0:04:00.420000
 Let's take a look at the actual configuration
 of auditing for a SQL server

0:04:00.420000 --> 0:04:03.060000
 and a SQL server database.

0:04:03.060000 --> 0:04:09.980000
 All right, I am currently in
 the blade for a SQL server.

0:04:09.980000 --> 0:04:11.280000
 An Azure SQL server.

0:04:11.280000 --> 0:04:16.400000
 Now this is kind of the old school DTU
 architecture, but you would find

0:04:16.400000 --> 0:04:20.260000
 the same thing with the
 other billing options.

0:04:20.260000 --> 0:04:23.680000
 And you can see that I've
 got auditing right here.

0:04:23.680000 --> 0:04:26.320000
 And it's really pretty simple.

0:04:26.320000 --> 0:04:27.900000
 Do I want to audit?

0:04:27.900000 --> 0:04:32.220000
 Yes, I do. And when I audit, where
 do I want that audit information to

0:04:32.220000 --> 0:04:36.440000
 go? And you can see I've got storage,
 log analytics, event of both of

0:04:36.440000 --> 0:04:37.300000
 those are in preview.

0:04:37.300000 --> 0:04:39.880000
 So I wouldn't use those
 as production right now.

0:04:39.880000 --> 0:04:43.260000
 Okay, I'll store that
 into storage.

0:04:43.260000 --> 0:04:48.780000
 The pick. A subscription.

0:04:48.780000 --> 0:04:53.840000
 And then let's go ahead and pick.

0:04:53.840000 --> 0:04:58.180000
 We'll go with that one randomly.

0:04:58.180000 --> 0:05:00.960000
 Please don't be that random when
 you pick your storage account.

0:05:00.960000 --> 0:05:06.280000
 And retention right now, the retention
 is forever, or I can go up to 3

0:05:06.280000 --> 0:05:12.060000
,285 days. We'll go with 320 days, which
 is not particularly meaningful.

0:05:12.060000 --> 0:05:14.500000
 But anyways, there we go.

0:05:14.500000 --> 0:05:18.040000
 And then I can save.

0:05:18.040000 --> 0:05:24.240000
 And that's it. I've now set up
 auditing on the server level.

0:05:24.240000 --> 0:05:32.640000
 Now, if I were to go to the databases
 associated with this, I could go

0:05:32.640000 --> 0:05:39.300000
 into a database and I could further
 audit at the database level.

0:05:39.300000 --> 0:05:41.500000
 Go in here to audit.

0:05:41.500000 --> 0:05:45.540000
 It's going to tell me that I'm
 auditing at the server level.

0:05:45.540000 --> 0:05:51.640000
 A plus, I actually have this particular
 database auditing to both storage

0:05:51.640000 --> 0:05:54.660000
 and to a log analytics.

0:05:54.660000 --> 0:05:57.480000
 So I can actually view
 the audit data there.

0:05:57.480000 --> 0:06:06.080000
 Now, I also have another database.


0:06:06.080000 --> 0:06:08.520000
 And if I get auditing here.

0:06:08.520000 --> 0:06:14.740000
 Now, server level auditing for
 this database is not turned on.

0:06:14.740000 --> 0:06:19.440000
 It's disabled, but auditing is enabled
 for this particular database.

0:06:19.440000 --> 0:06:20.800000
 So it's off at the server level.

0:06:20.800000 --> 0:06:22.460000
 It's on at the database level.

0:06:22.460000 --> 0:06:26.660000
 And I'm writing that audit
 log to a storage account.

0:06:26.660000 --> 0:06:28.420000
 Same storage account.

0:06:28.420000 --> 0:06:30.880000
 The question is, of course,
 OK, we have this.

0:06:30.880000 --> 0:06:32.360000
 How would I actually use it?

0:06:32.360000 --> 0:06:33.920000
 Well, that's pretty simple.

0:06:33.920000 --> 0:06:38.560000
 Now, one thing I can go and I can view
 this in whatever destination, any

0:06:38.560000 --> 0:06:41.120000
 of these destinations I pick
 can integrate there.

0:06:41.120000 --> 0:06:45.460000
 But it also has its own nice
 little interface for this.

0:06:45.460000 --> 0:06:47.080000
 I'm looking at database audit.

0:06:47.080000 --> 0:06:51.500000
 Now, if I select to server audit, there's
 nothing because I'm not auditing

0:06:51.500000 --> 0:06:53.200000
 at the server level.

0:06:53.200000 --> 0:06:58.040000
 But at the database level, I have
 all of these batch completed.

0:06:58.040000 --> 0:07:03.280000
 And that's because I was goofing
 around with Management Studio.

0:07:03.280000 --> 0:07:06.800000
 And so here, for example,
 is an RPC.

0:07:06.800000 --> 0:07:14.140000
 This was a background query that was
 being run by Management Studio.

0:07:14.140000 --> 0:07:16.040000
 I can also go up here.

0:07:16.040000 --> 0:07:19.980000
 And if I look, this item, this
 is an insert statement.

0:07:19.980000 --> 0:07:23.840000
 So I'm looking at the actual details
 of an insert statement that I ran

0:07:23.840000 --> 0:07:26.580000
 against this database.

0:07:26.580000 --> 0:07:28.760000
 And there you go.

0:07:28.760000 --> 0:07:30.900000
 I've got a horribly
 written select star.

0:07:30.900000 --> 0:07:32.380000
 Never want to select star.

0:07:32.380000 --> 0:07:34.000000
 A few other things.

0:07:34.000000 --> 0:07:36.940000
 And you get the idea.

0:07:36.940000 --> 0:07:43.440000
 I'm seeing all of the activity I can
 load more that has occurred on this

0:07:43.440000 --> 0:07:45.300000
 particular database.

0:07:45.300000 --> 0:07:50.140000
 And this was only up and
 active for a few minutes.

0:07:50.140000 --> 0:07:54.880000
 And you can see the amount of
 audit data that's being held.

0:07:54.880000 --> 0:08:00.520000
 One thing to keep in mind when you are
 auditing, that is taking up space.

0:08:00.520000 --> 0:08:02.020000
 It's taking up resources.

0:08:02.020000 --> 0:08:05.900000
 So you definitely need
 to be aware of that.

0:08:05.900000 --> 0:08:09.460000
 That it's simply a matter.

0:08:09.460000 --> 0:08:11.060000
 Oh, I'm moving that all around.

0:08:11.060000 --> 0:08:12.560000
 Sorry about that.

0:08:12.560000 --> 0:08:16.480000
 It's a matter of understanding
 that nothing is free.

0:08:16.480000 --> 0:08:22.340000
 The amount of data that this is generating
 is, I think, relatively small,

0:08:22.340000 --> 0:08:25.180000
 but it is something that you
 would want to monitor.

0:08:25.180000 --> 0:08:27.760000
 And certainly I'm not discouraging
 you from auditing.

0:08:27.760000 --> 0:08:32.640000
 I just always want to think about when a
 cost element is going to be applicable,

0:08:32.640000 --> 0:08:34.100000
 we need to make sure that
 we're aware of that.