1 00:00:00,000 --> 00:00:09,700 Welcome to the Azure Security Podcast, where we discuss topics relating to security, privacy, 2 00:00:09,700 --> 00:00:13,280 reliability and compliance on the Microsoft Cloud Platform. 3 00:00:13,280 --> 00:00:17,460 Hey everybody, welcome to episode 81. 4 00:00:17,460 --> 00:00:20,700 This week is one of those special episodes. 5 00:00:20,700 --> 00:00:27,140 This week I am with a colleague of mine, Sravani, who's here to talk to us about auditing in 6 00:00:27,140 --> 00:00:30,560 SQL, the SQL Server and various other SQL related products. 7 00:00:30,560 --> 00:00:34,480 So there will be no news, we'll wait until our next episode and we'll go through the 8 00:00:34,480 --> 00:00:37,460 latest and greatest Azure Security news. 9 00:00:37,460 --> 00:00:40,200 So Sravani, thank you so much for joining us on the podcast this week. 10 00:00:40,200 --> 00:00:42,680 Hi Michael, thanks for having me here. 11 00:00:42,680 --> 00:00:47,840 I have been in the podcast in the past when we were talking about the Windows authentication 12 00:00:47,840 --> 00:00:51,960 feature for Azure AD principles for SQL managed instance. 13 00:00:51,960 --> 00:00:59,080 It's nice to be here and I have been working with Microsoft for 11 years now and it's total 14 00:00:59,080 --> 00:01:03,720 15 years of experience in SQL Server and SQL Database. 15 00:01:03,720 --> 00:01:12,120 I worked on almost all flavors of SQL, starting SQL Server 2005 till SQL 2022 and then the 16 00:01:12,120 --> 00:01:17,340 SQL on Cloud, the Azure Database and the SQL managed instance. 17 00:01:17,340 --> 00:01:24,200 So I primarily work as a program manager for SQL security features and own SQL auditing 18 00:01:24,200 --> 00:01:26,840 for all flavors of SQL. 19 00:01:26,840 --> 00:01:31,880 Along with that, I also work on some of the authentication related features for SQL managed 20 00:01:31,880 --> 00:01:32,880 instance. 21 00:01:32,880 --> 00:01:39,880 Apart from security, high availability and performance are some of my areas of interest 22 00:01:39,880 --> 00:01:45,320 and have done lots of work as an escalation engineer in those pillars. 23 00:01:45,320 --> 00:01:47,440 So yeah, that's about me. 24 00:01:47,440 --> 00:01:48,440 Fantastic. 25 00:01:48,440 --> 00:01:51,480 So let's start off with a really basic question. 26 00:01:51,480 --> 00:01:55,520 When we're talking about logging and auditing here, which products are we talking about 27 00:01:55,520 --> 00:01:57,920 and what kind of events are we talking about? 28 00:01:57,920 --> 00:02:03,960 Like, are we talking about SQL control plane, SQL data plane, Azure control plane, Azure 29 00:02:03,960 --> 00:02:04,960 data plane? 30 00:02:04,960 --> 00:02:07,880 What sort of logging are we sort of worried about here? 31 00:02:07,880 --> 00:02:08,880 Okay. 32 00:02:08,880 --> 00:02:12,400 So today we are talking about SQL Azure Database. 33 00:02:12,400 --> 00:02:13,880 So Azure SQL Database. 34 00:02:13,880 --> 00:02:20,960 So the auditing, as I said, auditing works for all flavors of SQL, the on-prem SQL server 35 00:02:20,960 --> 00:02:28,760 starting SQL server 2008 till SQL server 2022 and the cloud products, the Azure SQL Database 36 00:02:28,760 --> 00:02:30,820 and SQL managed instance. 37 00:02:30,820 --> 00:02:36,600 So today we will majorly focus for Azure SQL Database, which is slightly different compared 38 00:02:36,600 --> 00:02:41,520 to SQL managed instance and on-prem SQL server, how it works. 39 00:02:41,520 --> 00:02:49,560 And for Azure SQL, we have data plane auditing and for control plane, which is from the portal, 40 00:02:49,560 --> 00:02:56,120 any operations that you perform from the portal, which comes under the control plane and those 41 00:02:56,120 --> 00:03:02,000 operations are logged in the activity logs, which are available in the Azure monitor. 42 00:03:02,000 --> 00:03:09,320 So today from SQL, we do not audit any control plane operations at the portal level, but 43 00:03:09,320 --> 00:03:14,800 we offer the data plane auditing for the Azure SQL Database. 44 00:03:14,800 --> 00:03:22,440 So for SQL SQL server and other flavors of SQL, the auditing itself is built differently 45 00:03:22,440 --> 00:03:26,800 to meet security compliance, regulations and requirements. 46 00:03:26,800 --> 00:03:32,580 So how it works is basically we support wide range of action groups. 47 00:03:32,580 --> 00:03:35,600 So there are a lot of action groups that we support. 48 00:03:35,600 --> 00:03:41,960 For example, there is a batch completed group, which is responsible to capture all actions 49 00:03:41,960 --> 00:03:43,920 related to our database. 50 00:03:43,920 --> 00:03:47,700 It could be any batch that is executed against the database. 51 00:03:47,700 --> 00:03:56,420 So this is majorly focuses for any DMLs, DDLs, which are executed inside the database. 52 00:03:56,420 --> 00:03:59,240 So this is one of the action group that we support. 53 00:03:59,240 --> 00:04:03,560 Along with that, we support, as I said, we support wide range of action groups. 54 00:04:03,560 --> 00:04:08,400 For example, schema related changes, there are certain action groups, but if there are 55 00:04:08,400 --> 00:04:15,160 any changes related to users, groups, permissions, there are action groups to capture that information. 56 00:04:15,160 --> 00:04:21,040 Similarly, we have certain action groups where if you want to know if there are any failed 57 00:04:21,040 --> 00:04:26,320 logins, successful logins, so we have a dedicated action groups for that. 58 00:04:26,320 --> 00:04:31,120 So first of all, why do we support wide range of action groups? 59 00:04:31,120 --> 00:04:38,320 To provide flexibility to customers to configure auditing based on their security requirements. 60 00:04:38,320 --> 00:04:44,480 Our customers has like different set of wide range of servers, the cloud services, right? 61 00:04:44,480 --> 00:04:50,040 So some might be related to medical, some might be related to finance. 62 00:04:50,040 --> 00:04:55,000 So there are different organizations and each organizations will have their own security 63 00:04:55,000 --> 00:04:57,720 and compliance requirements to meet. 64 00:04:57,720 --> 00:05:03,160 So in order to provide the flexibility and collect only the data that they need to collect, 65 00:05:03,160 --> 00:05:07,380 we provide the support to have a lot of action groups. 66 00:05:07,380 --> 00:05:13,320 And along with the action groups, we also support actions like if you want to capture 67 00:05:13,320 --> 00:05:20,720 like a select, update, insert, delete against any specific table or a database or by a specific 68 00:05:20,720 --> 00:05:21,760 user. 69 00:05:21,760 --> 00:05:28,760 So this is like a kind of filters that they can configure while configuring the auditing. 70 00:05:28,760 --> 00:05:31,920 So this is what we do for all flavors of SQL. 71 00:05:31,920 --> 00:05:40,080 However, when it comes to Azure SQL database being a PaaS service, the first thing is we 72 00:05:40,080 --> 00:05:43,000 don't enable auditing by default. 73 00:05:43,000 --> 00:05:49,300 Auditing customer has to enable the auditing from the server blade in the Azure portal. 74 00:05:49,300 --> 00:05:54,320 So when it comes to Azure SQL, we support two types of auditing. 75 00:05:54,320 --> 00:05:56,080 One is a server level auditing. 76 00:05:56,080 --> 00:05:59,600 The other one is a database level auditing. 77 00:05:59,600 --> 00:06:05,000 The name itself tells you like the server level is basically when you configure it, 78 00:06:05,000 --> 00:06:07,960 all the databases has the auditing enabled. 79 00:06:07,960 --> 00:06:12,800 And if you do not want to configure a server level audit, like if you have like specific 80 00:06:12,800 --> 00:06:18,320 databases for which you need to enable auditing, then you can just go ahead and enable the 81 00:06:18,320 --> 00:06:20,280 database level audit. 82 00:06:20,280 --> 00:06:24,160 These are the two configurations that we support for Azure SQL. 83 00:06:24,160 --> 00:06:30,440 And what happens is when you configure auditing, you need to write the logs to a target, right? 84 00:06:30,440 --> 00:06:36,320 So I mean, traditionally for on-prem servers, what you do is you configure auditing and 85 00:06:36,320 --> 00:06:42,180 then write the audit logs to your one of your log folders on your Windows machine. 86 00:06:42,180 --> 00:06:47,620 So what happens with Azure SQL is we support three types of targets. 87 00:06:47,620 --> 00:06:52,040 So one, you can write the audit logs to the storage account. 88 00:06:52,040 --> 00:06:56,960 And two, you can write the audit logs to log analytics workspace. 89 00:06:56,960 --> 00:07:00,840 And three, you can also write the audit logs to event hubs. 90 00:07:00,840 --> 00:07:06,840 So why we support these three different targets is again to provide flexibility to customers 91 00:07:06,840 --> 00:07:11,440 so that they can choose what targets they want to use for their auditing. 92 00:07:11,440 --> 00:07:16,640 What I see is most of the customers use multiple targets for certain databases. 93 00:07:16,640 --> 00:07:21,520 They just store it in the storage account and use their own mechanisms to review these 94 00:07:21,520 --> 00:07:22,600 logs. 95 00:07:22,600 --> 00:07:27,640 And most of the customers use event hubs and log analytics because that gives you kind 96 00:07:27,640 --> 00:07:33,420 of Azure security insights to understand what is happening on the server. 97 00:07:33,420 --> 00:07:36,220 So these are the different targets that we support. 98 00:07:36,220 --> 00:07:42,280 So like I said, for Azure SQL database, once the database, once the server is created in 99 00:07:42,280 --> 00:07:46,680 Azure, customers go and enable auditing from the portal. 100 00:07:46,680 --> 00:07:51,460 When they enable, they choose either server audit or database audit. 101 00:07:51,460 --> 00:07:55,980 And then they choose this one of these targets to store these audit logs. 102 00:07:55,980 --> 00:07:57,940 So this is how it works. 103 00:07:57,940 --> 00:08:03,220 And when it comes to Azure SQL, we have a default auditing. 104 00:08:03,220 --> 00:08:09,100 So when you enable it from the portal, we always enable the default audit, which is 105 00:08:09,100 --> 00:08:16,560 having three action groups, which are default, that is database successful logins, action 106 00:08:16,560 --> 00:08:21,320 group, and failed login action group, and the batch completed group. 107 00:08:21,320 --> 00:08:25,900 So these are the three action groups that we configure by default. 108 00:08:25,900 --> 00:08:32,700 So in case if customer has a requirement where they need to capture a different set of action 109 00:08:32,700 --> 00:08:38,700 groups, that is when they have to do a custom audits for their Azure SQL. 110 00:08:38,700 --> 00:08:43,660 So for performing custom audits, we do not have a UI today. 111 00:08:43,660 --> 00:08:49,020 You have to use PowerShell ARM templates from the portal. 112 00:08:49,020 --> 00:08:53,780 You can use one of these client tools to configure custom auditing. 113 00:08:53,780 --> 00:08:59,700 And when they configure custom auditing, they can configure specific action groups, specific 114 00:08:59,700 --> 00:09:00,820 actions. 115 00:09:00,820 --> 00:09:08,580 If they want to do filters like a specific user or a schema or a database, there is a 116 00:09:08,580 --> 00:09:13,140 filter called predicate expression, which they can use while using PowerShell, and they 117 00:09:13,140 --> 00:09:15,700 can have a custom audit configured. 118 00:09:15,700 --> 00:09:20,780 So this is like an overall picture how it works for Azure SQL. 119 00:09:20,780 --> 00:09:22,540 And this is all about the data plane. 120 00:09:22,540 --> 00:09:27,700 So anything that comes from the client to the Azure SQL database in the data plane is 121 00:09:27,700 --> 00:09:30,980 audited using this auditing. 122 00:09:30,980 --> 00:09:36,300 And for the control plane, like I said, the activity log, which is provided from the ARM 123 00:09:36,300 --> 00:09:39,580 from the Azure would be your control plane audit log for now. 124 00:09:39,580 --> 00:09:41,500 So you said a couple of things at the very beginning. 125 00:09:41,500 --> 00:09:43,420 I just want to make sure everyone understands. 126 00:09:43,420 --> 00:09:45,060 So DDL is data definition language. 127 00:09:45,060 --> 00:09:48,580 So that's like create table, create schema, that sort of stuff, right? 128 00:09:48,580 --> 00:09:51,620 And then DML is data manipulation language. 129 00:09:51,620 --> 00:09:55,780 So select star, insert, update, that kind of stuff. 130 00:09:55,780 --> 00:10:01,460 And then the other thing you mentioned was that you're sort of bucketing SQL queries 131 00:10:01,460 --> 00:10:03,140 into action groups. 132 00:10:03,140 --> 00:10:04,700 So what is an action group? 133 00:10:04,700 --> 00:10:11,020 Action group is basically a set of actions that are executed inside your database. 134 00:10:11,020 --> 00:10:16,400 So for auditing, like I said, we bucketized these actions into these groups so that if 135 00:10:16,400 --> 00:10:22,500 customer wants to configure auditing, they can choose what should be the action group 136 00:10:22,500 --> 00:10:24,020 that I want to configure. 137 00:10:24,020 --> 00:10:32,820 Like I said, for example, I mentioned about batch completed group, which is a server level 138 00:10:32,820 --> 00:10:35,740 as well as a database level action group. 139 00:10:35,740 --> 00:10:40,500 And this is basically when you configure this specific action group, what happens is an 140 00:10:40,500 --> 00:10:45,600 event is raised whenever any batch is executed inside your database. 141 00:10:45,600 --> 00:10:50,180 So any operation that completes execution raises this event. 142 00:10:50,180 --> 00:10:54,820 And then this event is captured and returned to the audit logs. 143 00:10:54,820 --> 00:11:01,580 So for auditing in the backend, we kind of use extended events architecture where all 144 00:11:01,580 --> 00:11:06,900 these events are captured based on the actions and action groups that you configure for the 145 00:11:06,900 --> 00:11:07,900 database. 146 00:11:07,900 --> 00:11:14,220 We have various action groups like batch completed group, batch started group. 147 00:11:14,220 --> 00:11:16,740 And then we have something related to databases. 148 00:11:16,740 --> 00:11:21,640 Like if there is anything changed, you can configure database change group. 149 00:11:21,640 --> 00:11:24,020 Like I said, database logout group. 150 00:11:24,020 --> 00:11:29,540 If a specific user is logged out from a database, you can configure this database logout group 151 00:11:29,540 --> 00:11:31,740 to audit that specific event. 152 00:11:31,740 --> 00:11:32,740 Okay, thanks. 153 00:11:32,740 --> 00:11:34,940 So that makes a lot more sense now. 154 00:11:34,940 --> 00:11:41,300 You mentioned at the beginning that for Azure SQL database, auditing is not enabled by default. 155 00:11:41,300 --> 00:11:46,540 What's interesting about that is there's been in the news just recently, nation states involved 156 00:11:46,540 --> 00:11:49,220 with compromised accounts. 157 00:11:49,220 --> 00:11:54,180 One thing that sort of came out of that is a lot of customers didn't have certain kinds 158 00:11:54,180 --> 00:11:56,000 of auditing enabled. 159 00:11:56,000 --> 00:12:01,020 So now we're seeing a lot more impetus on people using auditing. 160 00:12:01,020 --> 00:12:08,100 So is it fair to say that because it's disabled by default, that we really need to have more 161 00:12:08,100 --> 00:12:10,660 people enabling auditing in Azure SQL database? 162 00:12:10,660 --> 00:12:12,460 Is that a fair comment? 163 00:12:12,460 --> 00:12:13,860 Absolutely, Michael. 164 00:12:13,860 --> 00:12:15,180 That's a fair comment. 165 00:12:15,180 --> 00:12:22,780 Why we don't enable by default is even though the auditing is a built-in feature, the targets 166 00:12:22,780 --> 00:12:25,140 are the cost for customer. 167 00:12:25,140 --> 00:12:29,580 And depending on their workloads, we don't know how much data they are going to generate. 168 00:12:29,580 --> 00:12:35,460 Like if you have a heavy OLTP system and I do not want to take a decision on behalf of 169 00:12:35,460 --> 00:12:39,060 customer because the targets are going to be pretty costly. 170 00:12:39,060 --> 00:12:44,220 We have customers who have the audit log is like four times of their source database size 171 00:12:44,220 --> 00:12:48,300 and they are just not aware of that because it's the default audit and it just audits 172 00:12:48,300 --> 00:12:50,080 everything in the database. 173 00:12:50,080 --> 00:12:53,100 So that is why we don't make it default. 174 00:12:53,100 --> 00:12:56,560 We want customers to choose what targets they want to use. 175 00:12:56,560 --> 00:13:01,580 They want to do based on their cost, based on their compliance requirements and things 176 00:13:01,580 --> 00:13:02,580 like that. 177 00:13:02,580 --> 00:13:09,300 So absolutely, like once the database is deployed in Azure, customers should review the auditing 178 00:13:09,300 --> 00:13:14,380 configuration and see if they want to enable it to meet any security requirements. 179 00:13:14,380 --> 00:13:17,960 Yeah, I just want to make sure I get this 100% correct. 180 00:13:17,960 --> 00:13:24,500 So you can actually have a relatively fine grained audit policy rather than just auditing 181 00:13:24,500 --> 00:13:26,400 absolutely everything. 182 00:13:26,400 --> 00:13:28,900 Could you put a policy in place as an example? 183 00:13:28,900 --> 00:13:33,500 Let's say you decided that you only want to, I don't know, I'm making this up as I go along. 184 00:13:33,500 --> 00:13:40,060 Let's say you've got 20 offices and one particular office is potentially in a hostile environment. 185 00:13:40,060 --> 00:13:41,460 Again, I'm making this up. 186 00:13:41,460 --> 00:13:46,980 You could audit just stuff coming from that DNS range or that IP address or at least something 187 00:13:46,980 --> 00:13:50,100 to identify that environment or those users. 188 00:13:50,100 --> 00:13:52,060 Can you go to that level of granularity? 189 00:13:52,060 --> 00:14:01,220 Yeah, so there is a way to filter those activities and the specific users, specific groups. 190 00:14:01,220 --> 00:14:06,700 So as I said, today, one of the challenges that we have for Azure SQL database is we 191 00:14:06,700 --> 00:14:11,260 do not have a UI where you can go ahead and configure a custom audit. 192 00:14:11,260 --> 00:14:16,980 So the default audit basically collects the batch completed group, which collects pretty 193 00:14:16,980 --> 00:14:19,660 much everything that happens in the database. 194 00:14:19,660 --> 00:14:25,740 So if you want to have this specific, more precise auditing, we have to use the custom 195 00:14:25,740 --> 00:14:32,380 auditing, which is either you configure it using ARM templates or using partial commands. 196 00:14:32,380 --> 00:14:39,820 So I did publish a blog, a blog, a blog, which has more commands with each scenarios and 197 00:14:39,820 --> 00:14:46,260 examples like if you want to filter a schema, filter a user, filter a database or filter 198 00:14:46,260 --> 00:14:48,020 a certain statements. 199 00:14:48,020 --> 00:14:52,740 So I have given like certain examples like how to configure this predicate expression 200 00:14:52,740 --> 00:14:53,740 and filtering. 201 00:14:53,740 --> 00:15:00,260 But you know what the good news is, we have this in pipeline to have a custom UI so that 202 00:15:00,260 --> 00:15:04,820 customers do not have to do these commands and then configure auditing. 203 00:15:04,820 --> 00:15:06,700 So that work is in pipeline. 204 00:15:06,700 --> 00:15:11,540 I do not have the timelines, but soon we will have a custom configuration and available 205 00:15:11,540 --> 00:15:12,540 for auditing. 206 00:15:12,540 --> 00:15:18,460 But till then, customers have to do it manually using partial commands or ARM templates. 207 00:15:18,460 --> 00:15:23,700 Until I read your blog post, I didn't even realize that there was a way of producing 208 00:15:23,700 --> 00:15:27,460 sort of smaller log files by essentially filtering them. 209 00:15:27,460 --> 00:15:28,940 I didn't even know that existed. 210 00:15:28,940 --> 00:15:33,060 And also it's funny, a lot of people think that the extensive functionality in any Azure 211 00:15:33,060 --> 00:15:37,260 service is what's available in the portal and that is simply not true. 212 00:15:37,260 --> 00:15:41,460 Often the most common things are in the portal, but some things are not in the portal, but 213 00:15:41,460 --> 00:15:44,700 they can still be accessed through like you say through PowerShell or through the CLI 214 00:15:44,700 --> 00:15:48,100 or through an ARM template or a bicep template or something like that. 215 00:15:48,100 --> 00:15:49,100 That's good to know. 216 00:15:49,100 --> 00:15:53,580 Yeah, again, I didn't know that until I actually read your post on that. 217 00:15:53,580 --> 00:15:55,620 Anything else people should know about logging? 218 00:15:55,620 --> 00:16:00,100 I mean, what about things like our other database products like SQL MI for example, SQL Managed 219 00:16:00,100 --> 00:16:01,740 Instance? 220 00:16:01,740 --> 00:16:08,980 So one of the difference that I would like to call out between, so SQL Server, SQL MI, 221 00:16:08,980 --> 00:16:12,820 today you can configure auditing using T-SQL statements, right? 222 00:16:12,820 --> 00:16:17,280 So which is like customers are pretty much comfortable with. 223 00:16:17,280 --> 00:16:21,840 So for Azure SQL database today, you cannot configure auditing using T-SQL. 224 00:16:21,840 --> 00:16:26,340 So that is the one of the challenge that we are also trying to fill up and see how can 225 00:16:26,340 --> 00:16:33,300 we enable support for T-SQL so that this kind of filtering becomes more easy for SQL database, 226 00:16:33,300 --> 00:16:34,300 right? 227 00:16:34,300 --> 00:16:35,300 Azure SQL database. 228 00:16:35,300 --> 00:16:39,860 So this is one challenge I would say that is something that we are trying to fill up. 229 00:16:39,860 --> 00:16:47,180 So today for SQL Server and Managed Instance, you can configure all auditing using the T-SQL 230 00:16:47,180 --> 00:16:53,280 commands while for SQL database, Azure SQL database, we cannot do that using T-SQL. 231 00:16:53,280 --> 00:16:57,380 So customers have to use these PowerShell and ARM templates. 232 00:16:57,380 --> 00:16:59,940 So this is one of the difference. 233 00:16:59,940 --> 00:17:05,860 And the other one is, so when we spoke about action groups, right? 234 00:17:05,860 --> 00:17:12,620 There are server level action groups and database level action groups that we support for SQL 235 00:17:12,620 --> 00:17:15,300 Server and even for SQL Managed Instance. 236 00:17:15,300 --> 00:17:21,120 But for Azure SQL, today we support only database level action groups. 237 00:17:21,120 --> 00:17:27,440 So initially this is a SQL database and we enable support for all database related action 238 00:17:27,440 --> 00:17:28,440 groups. 239 00:17:28,440 --> 00:17:33,060 But there are certain server level action groups which we do not support for Azure SQL 240 00:17:33,060 --> 00:17:34,060 today. 241 00:17:34,060 --> 00:17:39,900 So again, this work is also in pipeline and we are working on fixing these feature parity 242 00:17:39,900 --> 00:17:48,380 gaps between Azure SQL and SQL Server to ensure both all flavors of SQL have the same kind 243 00:17:48,380 --> 00:17:50,460 of support for the action groups. 244 00:17:50,460 --> 00:17:55,820 So but yeah, not many customers asked for this, but there are customers who need this 245 00:17:55,820 --> 00:17:58,700 support for the server level action groups. 246 00:17:58,700 --> 00:18:04,260 For example, if there is a login failure at the server level, they would like to know 247 00:18:04,260 --> 00:18:07,140 like which database, which login failed. 248 00:18:07,140 --> 00:18:12,620 So this is something again, we are currently working and soon it should be available as 249 00:18:12,620 --> 00:18:14,420 well for customers. 250 00:18:14,420 --> 00:18:20,060 And just one more thing that I would like to call out is by default for Azure database 251 00:18:20,060 --> 00:18:25,900 today, we capture only the first 4K characters of the SQL statement. 252 00:18:25,900 --> 00:18:29,940 So we do not roll over the records to the next record. 253 00:18:29,940 --> 00:18:37,180 So in SQL Server, what we do is if the SQL statement has more than 4K characters, we 254 00:18:37,180 --> 00:18:40,340 roll over to the next audit log record. 255 00:18:40,340 --> 00:18:48,660 So for Azure SQL, why we do this is to consider the performance and to ensure there is not 256 00:18:48,660 --> 00:18:51,140 too much of logging or overhead. 257 00:18:51,140 --> 00:18:53,420 By default, we do not write this. 258 00:18:53,420 --> 00:18:59,980 But if any customers has a workload that will have queries that are more than 4K characters 259 00:18:59,980 --> 00:19:04,200 in limit, we still enable this feature from the backend. 260 00:19:04,200 --> 00:19:06,260 This can be enabled from the backend. 261 00:19:06,260 --> 00:19:12,500 However, we are again working on a feature where we want to make this default and at 262 00:19:12,500 --> 00:19:15,760 the same time have the better performance in place. 263 00:19:15,760 --> 00:19:22,500 So these are some of the differences when compared to SQL Server and Azure SQL. 264 00:19:22,500 --> 00:19:27,940 And all these differences are already documented in our public documentation. 265 00:19:27,940 --> 00:19:29,540 But I just wanted to highlight them. 266 00:19:29,540 --> 00:19:31,660 Just sort of switching tacks a little bit now. 267 00:19:31,660 --> 00:19:36,860 So querying these logs, first and foremost, what sort of permissions are there around 268 00:19:36,860 --> 00:19:39,620 it to restrict access to the log files? 269 00:19:39,620 --> 00:19:42,300 And also, how do you query these logs? 270 00:19:42,300 --> 00:19:46,140 What functionality exists to actually go in and find out what happened? 271 00:19:46,140 --> 00:19:47,540 That's a great question, Michael. 272 00:19:47,540 --> 00:19:52,780 So first of all, we write these logs in a different format. 273 00:19:52,780 --> 00:19:59,540 That is.xel format, which is basically an extended event log format that is native to 274 00:19:59,540 --> 00:20:00,820 the SQL Server. 275 00:20:00,820 --> 00:20:04,540 So today, how customers view these logs? 276 00:20:04,540 --> 00:20:07,220 For Azure SQL database, there are three ways. 277 00:20:07,220 --> 00:20:14,820 One, in Azure portal, when you configure auditing for your SQL database, when you go to auditing 278 00:20:14,820 --> 00:20:19,100 blade, there is a view audit logs option. 279 00:20:19,100 --> 00:20:26,740 And that view audit logs will give you within a timestamp, what is the audit log look like. 280 00:20:26,740 --> 00:20:29,900 And that is like preview of the audit logs. 281 00:20:29,900 --> 00:20:36,260 We don't expose all columns of the audit log, but some of the columns which we feel 282 00:20:36,260 --> 00:20:37,580 they are important. 283 00:20:37,580 --> 00:20:43,060 So it tells you what event happened, when it happened, by whom it happened, and what 284 00:20:43,060 --> 00:20:44,580 is the change that is done. 285 00:20:44,580 --> 00:20:49,500 So these are the columns that we publish in this preview of the audit logs. 286 00:20:49,500 --> 00:20:54,740 And this is available in the Azure portal once the auditing is configured. 287 00:20:54,740 --> 00:20:59,420 Though we say the logging is asynchronous, I will say that pretty much you will see the 288 00:20:59,420 --> 00:21:03,720 logs are available as soon as you made some change in the database. 289 00:21:03,720 --> 00:21:06,440 So that is the one way to view it. 290 00:21:06,440 --> 00:21:12,000 The second one is there is something called Azure security insights. 291 00:21:12,000 --> 00:21:20,860 So we provide a dashboard view of Azure security insights and tells you what exactly happened, 292 00:21:20,860 --> 00:21:28,300 like if there were any fail logins, successful attempts, number of users accessing the database. 293 00:21:28,300 --> 00:21:35,620 So this comes when you have auditing configured to log analytics or Event Hub as a target. 294 00:21:35,620 --> 00:21:42,020 So when customers use these are the targets, they can also use this SQL security insights 295 00:21:42,020 --> 00:21:48,140 that comes from the Azure portal and which tells you like how many action groups are 296 00:21:48,140 --> 00:21:56,020 executed, how many databases are there, and how the databases are distributed by IP addresses 297 00:21:56,020 --> 00:21:59,860 and what kind of SQL principles access the database. 298 00:21:59,860 --> 00:22:02,820 So this is like a security insights that we provide. 299 00:22:02,820 --> 00:22:08,400 And this is a nice great overview to tell you overall picture in terms of what is happening 300 00:22:08,400 --> 00:22:11,340 on the server for a given time period. 301 00:22:11,340 --> 00:22:17,180 And the third way, which is basically the native way that we have been using for several 302 00:22:17,180 --> 00:22:23,620 years is using a T SQL function, which is fn get audit data. 303 00:22:23,620 --> 00:22:30,260 And this function supports reading multiple audit files and gives you a view like what 304 00:22:30,260 --> 00:22:35,320 exactly what are whatever the data is captured in a given time period. 305 00:22:35,320 --> 00:22:40,460 So you can either read one log file or you can read multiple log files. 306 00:22:40,460 --> 00:22:46,020 It basically takes inputs like what is the log file and how many files you want to read. 307 00:22:46,020 --> 00:22:52,060 And then you just have to run this query in your management studio connecting to any database. 308 00:22:52,060 --> 00:22:55,900 All you need is access to your audit logs wherever they are stored. 309 00:22:55,900 --> 00:23:01,260 They may be locally on your system or maybe they are sitting in a storage account. 310 00:23:01,260 --> 00:23:03,660 All you need is access to those log files. 311 00:23:03,660 --> 00:23:08,860 And once you have the access and if you have a required permissions to run this function 312 00:23:08,860 --> 00:23:12,460 inside SQL, you can just read the audit logs. 313 00:23:12,460 --> 00:23:17,180 And this is just a T SQL and customers can just do filtrations, whatever filters they 314 00:23:17,180 --> 00:23:23,100 want to apply for a specific database user time period, anything. 315 00:23:23,100 --> 00:23:24,740 So that is how they can view. 316 00:23:24,740 --> 00:23:30,100 Apart from all this in SSMS also there is an option when you have a server audit configured 317 00:23:30,100 --> 00:23:36,160 and when you right click there is a view audit log option that we provide. 318 00:23:36,160 --> 00:23:42,240 So there either you can view the audit logs of the existing server or even you can open 319 00:23:42,240 --> 00:23:46,900 an audit log which is sitting in a storage account or some other target. 320 00:23:46,900 --> 00:23:52,660 You can just go to the path, open the audit log and it gives you a view in the SSMS as 321 00:23:52,660 --> 00:23:53,660 well. 322 00:23:53,660 --> 00:23:57,980 So these are the different ways customers can access these logs and view the audit logs. 323 00:23:57,980 --> 00:24:02,580 Yeah, I knew about the function that was available because I ran it a couple of times and the 324 00:24:02,580 --> 00:24:06,060 funny thing is it produces so much information. 325 00:24:06,060 --> 00:24:12,020 I was just absolutely blown away just how much data is there in those logs including 326 00:24:12,020 --> 00:24:15,740 stuff I have no idea even what the columns mean to be honest with you but here it is 327 00:24:15,740 --> 00:24:16,740 what it is. 328 00:24:16,740 --> 00:24:20,420 So before we sort of wrap this episode up is there anything else, any other things that 329 00:24:20,420 --> 00:24:23,420 people should know about before we move to the end of the podcast? 330 00:24:23,420 --> 00:24:28,700 So one important thing is for Azure SQL Database auditing is not a default feature. 331 00:24:28,700 --> 00:24:33,420 You have to enable it and then choose what targets that they want to configure and they 332 00:24:33,420 --> 00:24:39,740 should also aware of the default audit action groups which basically captures everything. 333 00:24:39,740 --> 00:24:45,980 So if that is not what customers want they can go ahead and have a custom audit configuration 334 00:24:45,980 --> 00:24:53,860 so that they can have a more crisp and more detailed logging based on their security and 335 00:24:53,860 --> 00:25:00,180 governance requirements and we have everything updated in our public documentation and if 336 00:25:00,180 --> 00:25:05,940 there is any feedback, if there is any piece of information that is missing we will keep 337 00:25:05,940 --> 00:25:06,940 adding it. 338 00:25:06,940 --> 00:25:11,660 They can just go to GitHub and report any information that is missing and we will be 339 00:25:11,660 --> 00:25:13,380 happy to assist there. 340 00:25:13,380 --> 00:25:18,900 But yeah, please review the auditing configuration for all your Azure SQL Database and enable 341 00:25:18,900 --> 00:25:20,900 wherever it is required. 342 00:25:20,900 --> 00:25:24,140 Alright so one thing you know as you have already been on the podcast, one question 343 00:25:24,140 --> 00:25:28,740 we always ask our guests is if you had just one final thought to leave our listeners with 344 00:25:28,740 --> 00:25:29,940 what would it be? 345 00:25:29,940 --> 00:25:34,620 I would say please turn on your auditing for Azure SQL Database so that you don't miss 346 00:25:34,620 --> 00:25:35,620 any important information. 347 00:25:35,620 --> 00:25:40,220 I think that is a perfect ending and I really want to sort of add my two cents there as 348 00:25:40,220 --> 00:25:45,340 well that there is a lot of nation state activity going on right now and there always is and 349 00:25:45,340 --> 00:25:49,140 the lack of audit logs is proving problematic. 350 00:25:49,140 --> 00:25:52,740 So yeah, if you are running Azure SQL Database or any product for that matter make sure that 351 00:25:52,740 --> 00:25:57,540 you are auditing especially the data plane and obviously the control plane as well. 352 00:25:57,540 --> 00:25:59,860 So with that let's bring our episode to an end. 353 00:25:59,860 --> 00:26:04,180 So Vani, thank you so much for joining us this week and to all our listeners out there, 354 00:26:04,180 --> 00:26:05,940 stay safe and we will see you next time. 355 00:26:05,940 --> 00:26:09,300 Thanks for listening to the Azure Security Podcast. 356 00:26:09,300 --> 00:26:16,140 You can find show notes and other resources at our website azsecuritypodcast.net. 357 00:26:16,140 --> 00:26:20,980 If you have any questions, please find us on Twitter at Azure Setpod. 358 00:26:20,980 --> 00:26:41,020 Background music is from ccmixtr.com and licensed under the Creative Commons license.