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:16,600 Hey everybody, welcome to episode 88. 4 00:00:16,600 --> 00:00:21,220 This is one of those special episodes where we sort of interview people about a very specific 5 00:00:21,220 --> 00:00:22,680 topic, so there's going to be no news. 6 00:00:22,680 --> 00:00:24,480 It's also just myself here this week, Michael. 7 00:00:24,480 --> 00:00:30,080 Again, part of the reason is because the guests we have this week, we have Shirat Unni and 8 00:00:30,080 --> 00:00:35,520 Raoul Garcia, and they're both colleagues of mine in the Azure SQL Database team. 9 00:00:35,520 --> 00:00:40,240 And this week, we're here to talk about best practices for securing SQL databases, SQL 10 00:00:40,240 --> 00:00:47,040 server databases, both Azure SQL database, SQL MI, SQL managed instance, SQL in VMs as 11 00:00:47,040 --> 00:00:50,760 well in virtual machines, so IaaS instances of SQL server. 12 00:00:50,760 --> 00:00:53,240 Before we get stuck into the actual content, gentlemen, why don't you introduce yourself? 13 00:00:53,240 --> 00:00:54,840 Shirat, why don't you go first? 14 00:00:54,840 --> 00:00:58,320 Yeah, thanks Michael for having me here. 15 00:00:58,320 --> 00:01:05,220 About myself, I am the principal engineering security manager for Azure Databases Red team. 16 00:01:05,220 --> 00:01:13,160 I have about a decade or more of experience in all offensive security, so I can talk today 17 00:01:13,160 --> 00:01:18,620 a lot about the different types of attack techniques and mostly cater to Azure databases 18 00:01:18,620 --> 00:01:22,880 and SQL in specific and talk about what are different things that I've seen. 19 00:01:22,880 --> 00:01:24,400 And I am Raoul Garcia. 20 00:01:24,400 --> 00:01:30,680 I'm currently working on static analysis using CodeQL and cryptography at Microsoft. 21 00:01:30,680 --> 00:01:38,360 But for more than a decade, I was doing security testing and security assurance for SQL server. 22 00:01:38,360 --> 00:01:44,520 And since then, I have been helping Red teams and Blue teams attacking and defending SQLs. 23 00:01:44,520 --> 00:01:49,280 All right, so let's move on to our topic, which is, as I mentioned, securing SQL databases 24 00:01:49,280 --> 00:01:53,000 and actually more accurately looking at it from almost like an attacking perspective, 25 00:01:53,000 --> 00:01:54,000 right? 26 00:01:54,000 --> 00:01:58,240 You know, what things we see attackers doing and how we can help mitigate those and some 27 00:01:58,240 --> 00:02:02,760 of the best practices that we can employ to mitigate those attacks. 28 00:02:02,760 --> 00:02:05,120 Because, you know, attacks are going to happen, right? 29 00:02:05,120 --> 00:02:08,720 The big difference is, are you compromised? 30 00:02:08,720 --> 00:02:09,720 Attacks are going to happen. 31 00:02:09,720 --> 00:02:12,400 So we need to make sure that the compromised part doesn't happen. 32 00:02:12,400 --> 00:02:14,200 So let's talk about some of those topics. 33 00:02:14,200 --> 00:02:18,360 So let's start with a subject that is near and dear to my heart. 34 00:02:18,360 --> 00:02:19,360 And that is least privilege. 35 00:02:19,360 --> 00:02:22,960 And in fact, one of the things about least privilege is that it's one of the cornerstones 36 00:02:22,960 --> 00:02:24,240 of zero trust, right? 37 00:02:24,240 --> 00:02:27,120 So in the case of Microsoft, we have three main pillars. 38 00:02:27,120 --> 00:02:28,960 One of them is operating at least privilege. 39 00:02:28,960 --> 00:02:32,920 And what that really means is just operating during the tasks that are required. 40 00:02:32,920 --> 00:02:38,040 That includes users and processes at the minimum amount of privilege to get the job done and 41 00:02:38,040 --> 00:02:39,840 absolutely no more. 42 00:02:39,840 --> 00:02:44,760 So Shirat, why don't we start with you on some of the best practices around operating 43 00:02:44,760 --> 00:02:47,640 as SQL databases at least privilege? 44 00:02:47,640 --> 00:02:53,040 So to your point, Michael, least privilege is something that we hold very close to our 45 00:02:53,040 --> 00:03:00,640 hearts and every discussion we have around security fixes or security bugs, we are most 46 00:03:00,640 --> 00:03:06,580 of the times emphasizing how a least privilege could have avoided a lot of the disaster. 47 00:03:06,580 --> 00:03:15,280 So with SQL databases in particular, what a lot of times happens is you see the front-end 48 00:03:15,280 --> 00:03:22,720 web application or an app service that needs to talk to the database having a very high 49 00:03:22,720 --> 00:03:24,200 privilege, right? 50 00:03:24,200 --> 00:03:31,220 A lot of times what we overlook is the fact that while the application only needs to do 51 00:03:31,220 --> 00:03:38,440 a very few tasks, like maybe reading data or updating specific tables, we are granting 52 00:03:38,440 --> 00:03:45,480 high privileges like to an extent where we give sysadmin access to the database user 53 00:03:45,480 --> 00:03:49,320 that is connecting by the application, right? 54 00:03:49,320 --> 00:03:58,320 Now not only is the fact that the application compromise can now kind of make the problem 55 00:03:58,320 --> 00:04:05,520 worse because the database user is high privileged, so you can do a lot of fancy things that we 56 00:04:05,520 --> 00:04:10,480 talk about in the next part of our podcast. 57 00:04:10,480 --> 00:04:17,100 The biggest challenge is the fact that we are now giving an attacker an opportunity 58 00:04:17,100 --> 00:04:20,320 to do more with the database credentials, right? 59 00:04:20,320 --> 00:04:26,360 To add to that, there are times where we see that the database service itself is running 60 00:04:26,360 --> 00:04:28,000 with high privileges, right? 61 00:04:28,000 --> 00:04:35,440 Which is bad, again, in the case where you're able to execute commands as the database service. 62 00:04:35,440 --> 00:04:43,960 I mean, what comes to my mind as an attacker is we are always trying to see how can we 63 00:04:43,960 --> 00:04:50,440 move from point A to point B and does that give us additional privileges? 64 00:04:50,440 --> 00:04:56,240 If yes, we always take that direction because that's where we get the most back for our 65 00:04:56,240 --> 00:04:57,240 bug, right? 66 00:04:57,240 --> 00:05:05,080 And in case of both SQL Server database privileges and SQL Service privileges, what we have found 67 00:05:05,080 --> 00:05:11,800 often is that there is more than needed privileges assigned and that's always bad. 68 00:05:11,800 --> 00:05:18,360 I completely agree and I would like to add one more detail that is particular to SQL 69 00:05:18,360 --> 00:05:19,560 Server. 70 00:05:19,560 --> 00:05:28,960 When you have a scenario where the SQL user needs to execute elevated functions that is 71 00:05:28,960 --> 00:05:36,200 beyond read or write data, for example, you need to execute certain administrative commands 72 00:05:36,200 --> 00:05:38,280 from the application. 73 00:05:38,280 --> 00:05:45,880 Instead of having to connect with a highly privileged user for that purpose on your application, 74 00:05:45,880 --> 00:05:51,480 one tool that is at your disposal is store procedures and signatures that will allow 75 00:05:51,480 --> 00:05:57,800 you for a very controlled escalation of privileges from a low privileged user to do exactly the 76 00:05:57,800 --> 00:06:01,120 operation that you need and nothing else. 77 00:06:01,120 --> 00:06:08,760 And that's unfortunately a tool that is often overseen when looking at how to mitigate and 78 00:06:08,760 --> 00:06:12,840 lower the privileges on a system. 79 00:06:12,840 --> 00:06:18,160 So an example there would be, let's say a user has read and write access to one table, 80 00:06:18,160 --> 00:06:22,680 let's just make something up, but they need to do like one specific task as well, a little 81 00:06:22,680 --> 00:06:25,760 bit of tasking just for a very small amount of time. 82 00:06:25,760 --> 00:06:29,720 So you can actually write a store procedure, digitally sign it, and then grant the user 83 00:06:29,720 --> 00:06:31,880 access to that store procedure. 84 00:06:31,880 --> 00:06:36,760 And then so that user or role or something can actually execute that store procedure, 85 00:06:36,760 --> 00:06:38,080 but that's all they can do, right? 86 00:06:38,080 --> 00:06:42,000 They can't do anything else elevated and whatever that particular store procedure can do, it 87 00:06:42,000 --> 00:06:44,480 can do and it can do it in a very, very constrained way. 88 00:06:44,480 --> 00:06:47,720 So is that kind of the pattern that you're thinking of, Raul? 89 00:06:47,720 --> 00:06:49,440 Yes, exactly. 90 00:06:49,440 --> 00:06:54,200 And that will help you to control exactly what are the privileges of that user instead 91 00:06:54,200 --> 00:06:58,720 of granting full DBO access to that user. 92 00:06:58,720 --> 00:07:04,800 You just need to grant permission to run that procedure that you mentioned and the signature 93 00:07:04,800 --> 00:07:09,480 itself will give the DBO like privileges for that operation. 94 00:07:09,480 --> 00:07:10,480 Yeah. 95 00:07:10,480 --> 00:07:14,400 And the key thing about the signature, right, is the one it verifies that that particular 96 00:07:14,400 --> 00:07:19,080 store procedure is trusted because of signature stuff, but also that it's not been tampered 97 00:07:19,080 --> 00:07:20,080 with, right? 98 00:07:20,080 --> 00:07:24,000 So someone can't come in and modify the store procedure and then get it to do stuff above 99 00:07:24,000 --> 00:07:25,000 and beyond. 100 00:07:25,000 --> 00:07:27,720 So, and again, signature stuff, right? 101 00:07:27,720 --> 00:07:28,720 That's exactly the idea. 102 00:07:28,720 --> 00:07:29,720 Yeah. 103 00:07:29,720 --> 00:07:35,360 I know very few people who actually know that, to be absolutely honest with you. 104 00:07:35,360 --> 00:07:41,500 So yeah, if you're out there and you think you need to grant special privileges broadly 105 00:07:41,500 --> 00:07:49,200 to users, seriously consider this signatures on store procedures that have very specific 106 00:07:49,200 --> 00:07:53,340 requirements and then put an access policy around that store procedure, restricting the 107 00:07:53,340 --> 00:07:57,080 store procedure access to Jim and Mary or whatever you need to do. 108 00:07:57,080 --> 00:07:58,080 Fantastic. 109 00:07:58,080 --> 00:08:03,720 The funny thing about least privilege is that if you run a sysadmin, then everything just 110 00:08:03,720 --> 00:08:04,720 works, right? 111 00:08:04,720 --> 00:08:05,720 Well, of course it works. 112 00:08:05,720 --> 00:08:06,720 You're sysadmin. 113 00:08:06,720 --> 00:08:07,720 What the heck do you expect? 114 00:08:07,720 --> 00:08:09,040 But so does the attacker's code. 115 00:08:09,040 --> 00:08:10,040 That's the problem. 116 00:08:10,040 --> 00:08:14,440 If you get compromised, then the attacker's code runs at sysadmin too. 117 00:08:14,440 --> 00:08:19,240 So if you take another color from zero trust, which is assume breach. 118 00:08:19,240 --> 00:08:22,520 So if you assume breach and the environment is compromised, now what? 119 00:08:22,520 --> 00:08:25,440 Well, the attacker's running as a sysadmin. 120 00:08:25,440 --> 00:08:28,560 So that's absolutely a pathological example. 121 00:08:28,560 --> 00:08:32,640 So yeah, least privileges is such an important design principle. 122 00:08:32,640 --> 00:08:34,160 I mean, it's been around since the seventies. 123 00:08:34,160 --> 00:08:37,960 I mean, the original Salser and Schroeder paper from the seventies. 124 00:08:37,960 --> 00:08:39,560 I'll put a link to that if no one's ever read it. 125 00:08:39,560 --> 00:08:41,520 If you're not ready, you should look at it. 126 00:08:41,520 --> 00:08:43,160 And in there is least privilege. 127 00:08:43,160 --> 00:08:47,200 So it's been around for almost 50 years. 128 00:08:47,200 --> 00:08:49,120 The next one is, I don't see this a lot. 129 00:08:49,120 --> 00:08:53,920 I mean, I have seen it with a couple of customers where they actually run the process. 130 00:08:53,920 --> 00:08:57,200 Now I'm not talking about connecting to SQL server. 131 00:08:57,200 --> 00:09:04,520 I mean, people actually run the SQL server process in a VM or even on prem as an administrative 132 00:09:04,520 --> 00:09:05,520 account. 133 00:09:05,520 --> 00:09:13,240 Now by default it runs as what's referred to in windows as a virtual service account. 134 00:09:13,240 --> 00:09:17,720 And it's MS, it's like anti-service MS SQL server if I remember correctly. 135 00:09:17,720 --> 00:09:19,080 And it's not an admin. 136 00:09:19,080 --> 00:09:21,800 It's very least privilege accounts, a custom account. 137 00:09:21,800 --> 00:09:28,080 But yeah, if you're running the process itself elevated, then that's also opening you up 138 00:09:28,080 --> 00:09:29,880 for issues as well. 139 00:09:29,880 --> 00:09:35,080 So Shirat, you want to have any comments there about issues you've seen or the dangers of 140 00:09:35,080 --> 00:09:39,580 running the SQL process as an elevated account? 141 00:09:39,580 --> 00:09:46,840 One of the things that you would do as a red teamer is once you have any sort of initial 142 00:09:46,840 --> 00:09:54,960 access, you're looking to pivot into other systems or exfiltrate as much data or credentials 143 00:09:54,960 --> 00:09:59,680 so that you can use it for your further pivoting. 144 00:09:59,680 --> 00:10:07,160 So with SQL service running as system, which is godlike privileges on your windows operating 145 00:10:07,160 --> 00:10:10,120 system, a lot of things can be done. 146 00:10:10,120 --> 00:10:16,000 We will talk about this in a little bit usage of things like XP command shell or SQL agent 147 00:10:16,000 --> 00:10:17,240 jobs. 148 00:10:17,240 --> 00:10:25,320 But SQL server does have some powerful tools which can be used to run OS commands. 149 00:10:25,320 --> 00:10:31,040 And one of the things that you can do as a system on windows is essentially read memory 150 00:10:31,040 --> 00:10:35,680 or dump memory of the system so you get credentials of the network. 151 00:10:35,680 --> 00:10:40,060 And once you have a sufficient privileged network credentials, you can then laterally 152 00:10:40,060 --> 00:10:46,340 move within the network compromising more machines. 153 00:10:46,340 --> 00:10:55,180 So it makes the attacker's life a lot more easier having system process compromise because 154 00:10:55,180 --> 00:11:02,480 that gives an attacker a very solid initial access and also ways to establish persistence 155 00:11:02,480 --> 00:11:04,040 on your network. 156 00:11:04,040 --> 00:11:10,040 It all sounds very dangerous, but it all happens because we think that it's easier to just 157 00:11:10,040 --> 00:11:14,720 run something as system rather than going back and doing the investigation of like, 158 00:11:14,720 --> 00:11:17,000 okay, why exactly should I run this as system? 159 00:11:17,000 --> 00:11:18,400 Is there a better way to do this? 160 00:11:18,400 --> 00:11:21,320 Again, going back to principle of least privilege. 161 00:11:21,320 --> 00:11:28,120 Yeah, I would like to make an emphasis on that principle of least privilege. 162 00:11:28,120 --> 00:11:33,480 Twenty years ago, and I'm dating myself here, but 20 years ago, running SQL server as local 163 00:11:33,480 --> 00:11:36,720 system was unfortunately very common. 164 00:11:36,720 --> 00:11:44,440 And thankfully, thanks to the new defaults that Michael mentioned, that's no longer the 165 00:11:44,440 --> 00:11:48,280 case, at least not as often as it was 20 years ago. 166 00:11:48,280 --> 00:11:53,600 But for the few people who are still running as local system or other privilege account, 167 00:11:53,600 --> 00:12:01,200 please consider the scenario that Sarah just mentioned and make sure that you minimize 168 00:12:01,200 --> 00:12:08,320 privileges, find what you really need and make it work accordingly instead of just looking 169 00:12:08,320 --> 00:12:09,320 for the easy solution. 170 00:12:09,320 --> 00:12:11,960 Actually, I want to go one step further than that. 171 00:12:11,960 --> 00:12:14,200 My advice would be just stick with the defaults, right? 172 00:12:14,200 --> 00:12:20,040 Unless you have a really, really good reason to change the SQL server process identity, 173 00:12:20,040 --> 00:12:21,040 just don't. 174 00:12:21,040 --> 00:12:22,040 Just stick with the default. 175 00:12:22,040 --> 00:12:23,040 Things just work. 176 00:12:23,040 --> 00:12:27,480 If there's some funky requirement, then evaluate it, look at what changes you might need to 177 00:12:27,480 --> 00:12:29,120 make and understand the risks. 178 00:12:29,120 --> 00:12:33,960 But for the most part, the defaults are more than adequate for the vast majority of users 179 00:12:33,960 --> 00:12:35,520 of the environment out there. 180 00:12:35,520 --> 00:12:40,880 By the way, this only applies to SQL server on-prem, SQL server in a VM, in the case of 181 00:12:40,880 --> 00:12:44,680 Azure SQL database, which will come onto at the very, very end, you don't control the 182 00:12:44,680 --> 00:12:45,680 process identity. 183 00:12:45,680 --> 00:12:49,360 It's all controlled by Azure and it is running at least privilege. 184 00:12:49,360 --> 00:12:51,720 So to round out the least privilege section, I just want to talk about managed identities 185 00:12:51,720 --> 00:12:57,280 really fast and then we can move on to some historically common attack vectors. 186 00:12:57,280 --> 00:13:02,720 Don't underestimate the value of running a connection out of a virtual machine, for example, 187 00:13:02,720 --> 00:13:08,080 into SQL server or say an Azure function app and using managed identities, allowing that 188 00:13:08,080 --> 00:13:11,520 managed identity to have just least privilege into the environment, into the SQL server 189 00:13:11,520 --> 00:13:12,520 environment. 190 00:13:12,520 --> 00:13:13,800 You don't need to persist. 191 00:13:13,800 --> 00:13:18,320 In fact, we may want to actually want to touch on this real quick before we move on to, I 192 00:13:18,320 --> 00:13:21,960 suppose it really is like a common attack vector and that is connection strings. 193 00:13:21,960 --> 00:13:26,520 If you have a connection string into SQL server, which you do, an application has to have a 194 00:13:26,520 --> 00:13:27,520 connection string to connect. 195 00:13:27,520 --> 00:13:31,200 There's a whole bunch of options in there and one of them is to use SQL authentication 196 00:13:31,200 --> 00:13:32,880 with a username and password. 197 00:13:32,880 --> 00:13:35,680 Well, the problem is where do you store that username and password, right? 198 00:13:35,680 --> 00:13:36,680 Do you put it in the connection string? 199 00:13:36,680 --> 00:13:38,920 Well, if it's in the connection string, where do you store the connection string? 200 00:13:38,920 --> 00:13:40,160 You know, it was on disk. 201 00:13:40,160 --> 00:13:43,320 Well, how do you protect this connection string from being compromised? 202 00:13:43,320 --> 00:13:48,800 Because if an attacker now has that connection string, the attacker now has an identity and 203 00:13:48,800 --> 00:13:50,520 a password. 204 00:13:50,520 --> 00:13:55,200 So another way of doing that is to use a managed identity and that way the identity and the 205 00:13:55,200 --> 00:14:00,080 credential is actually managed by Entra ID, which was Azure Active Directory. 206 00:14:00,080 --> 00:14:01,080 So let's just take an example of a VM. 207 00:14:01,080 --> 00:14:05,040 Let's say you've got a Node.js application running in a virtual machine and it connects 208 00:14:05,040 --> 00:14:06,040 to SQL server. 209 00:14:06,040 --> 00:14:09,560 You could actually run the VM as a managed identity and have it such that when the Node 210 00:14:09,560 --> 00:14:14,680 application calls out to SQL server, it actually uses the managed identity of the virtual machine. 211 00:14:14,680 --> 00:14:16,000 So there's no credential stored anywhere. 212 00:14:16,000 --> 00:14:17,960 It's all managed by Entra ID. 213 00:14:17,960 --> 00:14:19,560 That is the correct way of doing it. 214 00:14:19,560 --> 00:14:21,640 That way there is no credential on the box. 215 00:14:21,640 --> 00:14:25,880 So if you go back to zero trust again, assume breach, in the environment, if you whack the 216 00:14:25,880 --> 00:14:30,760 environment, the attacker is now in the VM, there is no credential there. 217 00:14:30,760 --> 00:14:32,040 There's none whatsoever, right? 218 00:14:32,040 --> 00:14:36,720 It's all because it's managed by Entra ID, what was Azure Active Directory. 219 00:14:36,720 --> 00:14:40,360 So that's always, in my opinion anyway, when I'm looking at threat models, both customer 220 00:14:40,360 --> 00:14:46,040 and internally, when it comes to client authentication, I'm basically always looking for managed identities. 221 00:14:46,040 --> 00:14:51,040 So Shirad, do you have any comments there about connection strings and usernames and 222 00:14:51,040 --> 00:14:52,040 passwords? 223 00:14:52,040 --> 00:14:53,040 Yeah, definitely. 224 00:14:53,040 --> 00:14:57,880 I mean, I can talk a little bit about the username, password usage, and I'm sure we 225 00:14:57,880 --> 00:15:01,720 can talk a lot more about cred stuffing and other attacks later on. 226 00:15:01,720 --> 00:15:08,320 But one of the most common things is once you have access to an app service or a web 227 00:15:08,320 --> 00:15:11,880 app, you are going through the code base, right? 228 00:15:11,880 --> 00:15:14,480 Because that's one of the things to do to find additional credentials. 229 00:15:14,480 --> 00:15:20,300 And to your point, if it's the SQL connection string with username and password, that gives 230 00:15:20,300 --> 00:15:22,400 me access to one other server. 231 00:15:22,400 --> 00:15:27,520 So I'm essentially pivoting from one server to web server to like a SQL server now, right? 232 00:15:27,520 --> 00:15:29,440 That's a great amount of success. 233 00:15:29,440 --> 00:15:35,440 And also the bad things about passwords, as you touched upon, is the fact that once it's 234 00:15:35,440 --> 00:15:42,040 compromised, there is a possibility that the same pattern of the password or the same password 235 00:15:42,040 --> 00:15:44,280 is used in other services. 236 00:15:44,280 --> 00:15:50,880 Now, it's not probably very shocking to say this, but we do know that password reuse is 237 00:15:50,880 --> 00:15:52,400 very common, right? 238 00:15:52,400 --> 00:15:56,000 Especially for services that are backend, right? 239 00:15:56,000 --> 00:16:01,120 Because people always assume that, hey, this is an easy enough password that I'm going 240 00:16:01,120 --> 00:16:09,260 to set for all of my service A. So password reuse is something that can give me additional 241 00:16:09,260 --> 00:16:15,520 access to other services within an enterprise or organization. 242 00:16:15,520 --> 00:16:21,320 And all of those attacks that I just talked about can be mitigated by using something 243 00:16:21,320 --> 00:16:23,440 like a managed identity. 244 00:16:23,440 --> 00:16:29,000 Much harder for an attacker to get hold of them unless they are actually on that resource. 245 00:16:29,000 --> 00:16:35,280 And even if they do, it's very highly unlikely that they can pivot from one resource to another 246 00:16:35,280 --> 00:16:40,760 resource because managed identity ties down to just one resource and dies with that resource. 247 00:16:40,760 --> 00:16:44,880 I swear that red teamers can never put a sentence together. 248 00:16:44,880 --> 00:16:47,120 No conversation without using the word pivot. 249 00:16:47,120 --> 00:16:51,080 It's like, tell me you're a red teamer without telling me you're a red teamer, right? 250 00:16:51,080 --> 00:16:57,080 The moment they use the word, like a lateral traversal or pivot, I know you're on a red 251 00:16:57,080 --> 00:16:58,080 team somewhere. 252 00:16:58,080 --> 00:16:59,080 So yeah. 253 00:16:59,080 --> 00:17:01,280 Okay, Raul, do you have any thoughts on that? 254 00:17:01,280 --> 00:17:02,520 Oh, yes. 255 00:17:02,520 --> 00:17:09,360 One thing to also consider is what I have seen in many cases is applications that use 256 00:17:09,360 --> 00:17:13,880 SQL login and passwords never rotate the password. 257 00:17:13,880 --> 00:17:16,760 So that password is there forever. 258 00:17:16,760 --> 00:17:21,800 And if that application, well, if the SQL server is connected to the internet, it's 259 00:17:21,800 --> 00:17:28,600 a huge opportunity for an attacker to try to find your password over time. 260 00:17:28,600 --> 00:17:32,160 And since it never changes, you're just helping adversaries. 261 00:17:32,160 --> 00:17:39,280 On the same page, one thing that we should always consider is the SA account on SQL server 262 00:17:39,280 --> 00:17:40,480 on premises. 263 00:17:40,480 --> 00:17:48,820 If you're using SQL authentication, by default, there's an SA account that is all powerful 264 00:17:48,820 --> 00:17:50,160 in SQL server. 265 00:17:50,160 --> 00:17:58,700 There's an option that you should really consider using that is to disable the SA account. 266 00:17:58,700 --> 00:18:05,160 So even if tools out there are trying to crack the password for SA, they will not succeed 267 00:18:05,160 --> 00:18:06,560 because the SA is disabled. 268 00:18:06,560 --> 00:18:08,000 I shall go one step further. 269 00:18:08,000 --> 00:18:10,260 You should probably even treat that as a honeypot, right? 270 00:18:10,260 --> 00:18:14,560 If you see people, if you got alerting turned on and the logs show that people are trying 271 00:18:14,560 --> 00:18:23,120 to touch SA, then you know full well there's probably either Sherat doing it or it's a 272 00:18:23,120 --> 00:18:24,120 bad actor, right? 273 00:18:24,120 --> 00:18:26,160 So I would use that as a honeypot at that point. 274 00:18:26,160 --> 00:18:27,160 What do you think? 275 00:18:27,160 --> 00:18:33,840 I do remember it will be on the audit records when you look at them. 276 00:18:33,840 --> 00:18:35,800 Even if it's disabled, it should still show up. 277 00:18:35,800 --> 00:18:39,480 Yeah, because it's an invalid login attempt, right? 278 00:18:39,480 --> 00:18:40,480 Yep. 279 00:18:40,480 --> 00:18:44,280 If not as a honeypot, it should at least give you an idea of how often you're attacked. 280 00:18:44,280 --> 00:18:45,280 Yeah. 281 00:18:45,280 --> 00:18:48,040 Because it's not a matter of if, it's a matter of how often. 282 00:18:48,040 --> 00:18:49,040 All right. 283 00:18:49,040 --> 00:18:51,600 Let's use a word that Sherat has used a dozen times so far. 284 00:18:51,600 --> 00:18:55,080 Let's pivot the conversation to common attack techniques. 285 00:18:55,080 --> 00:18:58,240 At least those that are historically common. 286 00:18:58,240 --> 00:18:59,760 Let's start with everyone's favorite. 287 00:18:59,760 --> 00:19:01,720 I'll let you go first this time, Raul. 288 00:19:01,720 --> 00:19:03,920 What about XP underscore command shell? 289 00:19:03,920 --> 00:19:06,680 A favorite topic of mine. 290 00:19:06,680 --> 00:19:14,400 XP command shell is a really interesting standard procedure that allows you to execute arbitrary 291 00:19:14,400 --> 00:19:19,840 commands on the operating system using the SQL server account. 292 00:19:19,840 --> 00:19:24,240 Unfortunately, it's mostly used by attackers. 293 00:19:24,240 --> 00:19:32,840 I have seen very few cases where customers and serious people use it for something productive 294 00:19:32,840 --> 00:19:35,640 that cannot be done in other ways. 295 00:19:35,640 --> 00:19:40,720 It's disabled by default, but you should still monitor any usage of it. 296 00:19:40,720 --> 00:19:49,400 Going back to somebody compromises your SA account or says admin user, they can enable 297 00:19:49,400 --> 00:19:54,480 it again and they can use it to run whatever they want, unfortunately. 298 00:19:54,480 --> 00:19:55,480 Yeah. 299 00:19:55,480 --> 00:19:56,840 I agree with Raul. 300 00:19:56,840 --> 00:19:59,440 It's one of the favorite tools as a Red Teamer. 301 00:19:59,440 --> 00:20:05,040 I'm saying this because when you actually get access to a database, you have limited 302 00:20:05,040 --> 00:20:08,880 set of commands that you can run using T-SQL commands. 303 00:20:08,880 --> 00:20:12,080 You're limited to what T-SQL is exposing. 304 00:20:12,080 --> 00:20:19,360 A lot of it has to do with manipulating tables or doing mostly queries that help us manipulate 305 00:20:19,360 --> 00:20:20,360 data. 306 00:20:20,360 --> 00:20:21,360 That's pretty much it. 307 00:20:21,360 --> 00:20:29,040 With something like XP command shell, it opens up the door to expand it to operating system 308 00:20:29,040 --> 00:20:36,280 commands, which is quite interesting as an attacker because now you can do more things 309 00:20:36,280 --> 00:20:43,240 using SQL Server, be it things like dumping a process memory or getting your own command 310 00:20:43,240 --> 00:20:48,760 and control on that server by executing your own binary, downloading binaries onto the 311 00:20:48,760 --> 00:20:54,800 box or even exfiltrating data from the box to your remote server. 312 00:20:54,800 --> 00:21:00,040 All of these can be done because you can run the OS command. 313 00:21:00,040 --> 00:21:05,760 We talked about it a little bit earlier, depending on what privileges you're running the SQL 314 00:21:05,760 --> 00:21:15,000 Server or SQL Service as on your VM or on-prem, it gives me a different set of privileges. 315 00:21:15,000 --> 00:21:20,680 If I have system access, then I can probably run all possible OS commands and compromise 316 00:21:20,680 --> 00:21:26,720 the entire box or potentially get ideas to compromise the network itself. 317 00:21:26,720 --> 00:21:27,960 Pretty nifty tool. 318 00:21:27,960 --> 00:21:32,880 One other thing that I do want to add is by default, even though it's disabled, what I've 319 00:21:32,880 --> 00:21:40,160 seen a ton of times is that the user that is running the app service has privileges 320 00:21:40,160 --> 00:21:45,440 to go back and enable this XP command shell by setting the configuration, which is as 321 00:21:45,440 --> 00:21:46,440 bad. 322 00:21:46,440 --> 00:21:50,080 Again, going back to least privileges. 323 00:21:50,080 --> 00:21:55,520 Disabled is great, but make sure that nobody has enough privileges to enable it back again. 324 00:21:55,520 --> 00:21:58,720 Yeah, I'll tell you a quick story and then we'll move on to another topic. 325 00:21:58,720 --> 00:22:04,200 Back in the day, it's a long, long time ago, I had a customer working with SQL Server and 326 00:22:04,200 --> 00:22:06,240 the configuration was absolutely awful. 327 00:22:06,240 --> 00:22:09,880 Basically, they were running a processor system and they had the XP underscore command shell 328 00:22:09,880 --> 00:22:13,160 enabled and one day they couldn't boot the machine. 329 00:22:13,160 --> 00:22:18,720 The reason is someone who was really, really evil actually deleted, this is a flashback, 330 00:22:18,720 --> 00:22:22,040 but deleted boot.ini from the machine. 331 00:22:22,040 --> 00:22:24,320 You can't just go deleting boot.ini, right? 332 00:22:24,320 --> 00:22:30,680 Well, you can if the process deleting the file is running at system and XP underscore 333 00:22:30,680 --> 00:22:34,720 command shell is available with just a simple Dell space slash boot.ini. 334 00:22:34,720 --> 00:22:37,480 Yeah, so they couldn't boot the system. 335 00:22:37,480 --> 00:22:42,080 It was easy enough to fix, but the point was made, right? 336 00:22:42,080 --> 00:22:45,720 Don't run elevated and don't enable XP underscore command shell. 337 00:22:45,720 --> 00:22:49,360 Neither of those have been the default for a long time, right? 338 00:22:49,360 --> 00:22:53,920 We don't enable XP underscore command shell by default and we don't run elevated by default. 339 00:22:53,920 --> 00:22:55,840 The process identity doesn't run elevated by default. 340 00:22:55,840 --> 00:22:57,560 So yeah, it's magnificent to see. 341 00:22:57,560 --> 00:23:02,600 All right, let's pivot again to something that's near and dear to Raoul's heart, which 342 00:23:02,600 --> 00:23:04,160 is SQL Agent. 343 00:23:04,160 --> 00:23:12,960 Yes, and the way to think about SQL Agent is scheduler for running automated tasks, 344 00:23:12,960 --> 00:23:18,440 but a lot of times people forget that those tasks could be also operating system commands, 345 00:23:18,440 --> 00:23:19,440 right? 346 00:23:19,440 --> 00:23:25,360 Like running PowerShell scripts, again, under the context of a service. 347 00:23:25,360 --> 00:23:31,320 I forgot if it's going to run under the SQL Server context or under the agent context, 348 00:23:31,320 --> 00:23:37,640 but it's still one of those services running arbitrary commands on your machine. 349 00:23:37,640 --> 00:23:41,800 And a lot of people forget about that part. 350 00:23:41,800 --> 00:23:49,000 And it's again a tool that I have seen being abused many times and it's easy to hide because 351 00:23:49,000 --> 00:23:54,160 all the tasks are on MSDB and it's automated. 352 00:23:54,160 --> 00:24:00,120 It's not even something that I have to connect anymore in order for it to execute. 353 00:24:00,120 --> 00:24:02,820 I just set it and forget about it. 354 00:24:02,820 --> 00:24:11,160 So it's very important to use the SQL Agent role-based access control for managing the 355 00:24:11,160 --> 00:24:12,800 agent roles. 356 00:24:12,800 --> 00:24:18,920 They have different roles for managing and adding new tasks, to view the tasks or nothing 357 00:24:18,920 --> 00:24:19,920 at all. 358 00:24:19,920 --> 00:24:23,680 And in most cases, people should not have access to those tasks. 359 00:24:23,680 --> 00:24:27,120 Yeah, I absolutely agree with Raul. 360 00:24:27,120 --> 00:24:33,320 SQL Agent is pretty powerful in the sense that you can schedule jobs that includes running 361 00:24:33,320 --> 00:24:36,600 both command exec as well as PowerShell, right? 362 00:24:36,600 --> 00:24:41,560 It's pretty nifty for admins and it totally makes sense why the admins would use it. 363 00:24:41,560 --> 00:24:47,120 But to Raul's point, we have to make sure that these jobs cannot be created, managed 364 00:24:47,120 --> 00:24:50,400 or modified by any user, right? 365 00:24:50,400 --> 00:24:59,040 I mean, it's fine if you really do have to use agent jobs to do automation, of course, 366 00:24:59,040 --> 00:25:02,060 but the RBAC is very important, right? 367 00:25:02,060 --> 00:25:07,120 Making sure that we know who is running these roles and also to some extent, if there is 368 00:25:07,120 --> 00:25:14,240 a way to go back and audit if it's run according to the schedule that you want or if there 369 00:25:14,240 --> 00:25:16,400 was any change done to the job. 370 00:25:16,400 --> 00:25:17,720 And they're audited events, right? 371 00:25:17,720 --> 00:25:21,360 Like if someone goes and modifies a SQL Agent task or adds one or deletes one or whatever 372 00:25:21,360 --> 00:25:25,160 changes the identity under which one works, that's an audited event. 373 00:25:25,160 --> 00:25:33,640 So I strongly recommend reviewing the tasks in the MSDB table themselves. 374 00:25:33,640 --> 00:25:35,920 Don't rely exclusively on auditing events. 375 00:25:35,920 --> 00:25:41,360 All right, let's change another topic, which is one of my favorite topics and that's SQL 376 00:25:41,360 --> 00:25:42,360 Injection. 377 00:25:42,360 --> 00:25:44,040 Unfortunately, we still see SQL Injection vulnerabilities. 378 00:25:44,040 --> 00:25:48,040 I thought this is something that's long dead, but nope, absolutely not. 379 00:25:48,040 --> 00:25:52,800 It's almost like 2005 called and want their vulnerabilities back, but we still see SQL 380 00:25:52,800 --> 00:25:53,840 Injection vulnerabilities. 381 00:25:53,840 --> 00:25:58,800 People are rushing their products to market and they're building code that queries back 382 00:25:58,800 --> 00:26:02,960 in databases and they have SQL Injection vulnerabilities in them. 383 00:26:02,960 --> 00:26:03,960 It's not a bug in the database. 384 00:26:03,960 --> 00:26:05,560 I just want to point this out. 385 00:26:05,560 --> 00:26:06,560 Database just sees a query, right? 386 00:26:06,560 --> 00:26:10,360 And essentially what you're doing is you're building up a SQL statement that has some 387 00:26:10,360 --> 00:26:13,800 untrusted data and you're probably using something like string concatenation to build up the 388 00:26:13,800 --> 00:26:14,800 SQL statement. 389 00:26:14,800 --> 00:26:20,120 And the problem there is that if the attacker controls that string, then they can make the 390 00:26:20,120 --> 00:26:23,880 query basically do whatever they want within some confines. 391 00:26:23,880 --> 00:26:27,520 So yeah, we still see SQL Injection and it doesn't matter what your language is. 392 00:26:27,520 --> 00:26:31,480 You could be using the latest funky languages that everyone loves like Go and Rust, talking 393 00:26:31,480 --> 00:26:34,840 to your favorite backend database and still have SQL Injection vulnerability. 394 00:26:34,840 --> 00:26:42,360 So it all boils down to using what are often referred to as parameterized queries or placeholders, 395 00:26:42,360 --> 00:26:48,240 depending on the library you're using, where the SQL statement is built up safely. 396 00:26:48,240 --> 00:26:50,440 It's also really important to understand what the attacker controls. 397 00:26:50,440 --> 00:26:54,280 So if the attacker controls part of the string that's used to build up the SQL statement 398 00:26:54,280 --> 00:26:58,760 or part of the data that goes into the SQL statement, you need to make sure that you're 399 00:26:58,760 --> 00:27:02,880 using good input sanitizing as well as parameterized queries. 400 00:27:02,880 --> 00:27:07,240 Sherat, do you want to explain any more about that or add some more details? 401 00:27:07,240 --> 00:27:08,240 Yeah. 402 00:27:08,240 --> 00:27:10,880 I mean, that'll be really quick about this one. 403 00:27:10,880 --> 00:27:14,840 You have summarized it pretty well and covered most of the points. 404 00:27:14,840 --> 00:27:20,000 I mean, the thing with SQL Injection is regardless of what language you're writing your code 405 00:27:20,000 --> 00:27:25,240 and ultimately you are going to write the SQL statements to query data. 406 00:27:25,240 --> 00:27:30,040 And I've said this before many times that when you write that SQL statement or when 407 00:27:30,040 --> 00:27:36,000 you do a code review, there is absolutely nothing wrong with the code because all it's 408 00:27:36,000 --> 00:27:39,480 doing is it's select statement querying data. 409 00:27:39,480 --> 00:27:46,480 It looks absolutely fine and works absolutely fine, except for the part that you are accepting 410 00:27:46,480 --> 00:27:50,400 untrusted user input to be part of the code, right? 411 00:27:50,400 --> 00:27:54,320 When you're not parametrizing your query, which is bad. 412 00:27:54,320 --> 00:27:59,360 Unfortunately, we see SQL Injection even to this date. 413 00:27:59,360 --> 00:28:08,840 And the real factor here is the statement actually works no matter how you look at it. 414 00:28:08,840 --> 00:28:14,680 And it's very hard to tell if that is a security bug or not unless you really know about SQL 415 00:28:14,680 --> 00:28:15,680 Injection. 416 00:28:15,680 --> 00:28:21,360 So a lot of this is about education and awareness that how do you separate data from code and 417 00:28:21,360 --> 00:28:22,600 what is the best way to do that? 418 00:28:22,600 --> 00:28:23,600 Yeah. 419 00:28:23,600 --> 00:28:26,320 Let me add to that and I'll hand it over to Rahul. 420 00:28:26,320 --> 00:28:28,760 You bring up a really important point there. 421 00:28:28,760 --> 00:28:31,640 If you're doing functional testing, the SQL statement works, right? 422 00:28:31,640 --> 00:28:36,320 You're doing a nice, normal, everyday, happy, happy path query and everything just works. 423 00:28:36,320 --> 00:28:38,560 The problem is in the face of untrusted input. 424 00:28:38,560 --> 00:28:39,560 That's why it's so important. 425 00:28:39,560 --> 00:28:44,200 One of the core developer skills, when I'm talking to developers, one of the core skills 426 00:28:44,200 --> 00:28:47,560 and one of the core things they have to understand is what is the attacker control? 427 00:28:47,560 --> 00:28:52,200 And if the attacker controls incoming data that you then use to build up a SQL statement, 428 00:28:52,200 --> 00:28:57,680 that data is essentially toxic waste and you need to treat it as toxic waste and do all 429 00:28:57,680 --> 00:29:03,080 the appropriate mitigations and defenses and cleansing of that data and then ultimately 430 00:29:03,080 --> 00:29:07,800 parameterize queries to make sure that the statement is safe. 431 00:29:07,800 --> 00:29:11,560 One of the definitions that I like to use for a secure system is a system that does 432 00:29:11,560 --> 00:29:13,600 what it's supposed to do and nothing else. 433 00:29:13,600 --> 00:29:20,420 The problem is in the face of untrusted data and string concatenation to build up SQL statements, 434 00:29:20,420 --> 00:29:26,840 you can now start to have unexpected functionality like dropping tables or return more data in 435 00:29:26,840 --> 00:29:30,440 the result set that was unintended. 436 00:29:30,440 --> 00:29:36,280 It's that extra functionality that has now basically given you a security vulnerability. 437 00:29:36,280 --> 00:29:41,440 I agree 100% a lot of it's education, use of good libraries, use of correct libraries, 438 00:29:41,440 --> 00:29:45,160 but there is something else that we need to look at and this falls squarely into Raoul's 439 00:29:45,160 --> 00:29:49,560 current domain and that is static and dynamic analysis. 440 00:29:49,560 --> 00:29:54,600 So Raoul, one thing that you've been working on for the last quite some time is CodeQL. 441 00:29:54,600 --> 00:29:59,440 Do you want to just give our audience a really quick overview of CodeQL and explain how it 442 00:29:59,440 --> 00:30:02,800 can help them with detecting SQL injection vulnerabilities? 443 00:30:02,800 --> 00:30:03,800 Absolutely. 444 00:30:03,800 --> 00:30:12,960 CodeQL is a static analysis tool that the idea is it will instrument your code, either 445 00:30:12,960 --> 00:30:18,840 if it's interpreted codes like JavaScript or if it's a compiled code, it will instrument 446 00:30:18,840 --> 00:30:27,000 what you're compiling it and create a database that you can query and detect patterns in 447 00:30:27,000 --> 00:30:28,000 your code. 448 00:30:28,000 --> 00:30:34,440 And more specifically, the real power of CodeQL is data flow analysis. 449 00:30:34,440 --> 00:30:41,560 So you can track the, we call it taint data from an external source, for example, a web 450 00:30:41,560 --> 00:30:48,240 request, et cetera, that will ultimately flow into a SQL command. 451 00:30:48,240 --> 00:30:52,520 And if it's coming from an untrusted data, if it's considered tainted, you better know 452 00:30:52,520 --> 00:31:00,680 how you are sanitizing and protecting against SQL injection and we have written queries 453 00:31:00,680 --> 00:31:01,680 for that. 454 00:31:01,680 --> 00:31:07,400 They're publishing in GitHub, actually, Microsoft and GitHub. 455 00:31:07,400 --> 00:31:11,120 We continue working on that and making improvements. 456 00:31:11,120 --> 00:31:14,560 So I would like to invite people to try CodeQL. 457 00:31:14,560 --> 00:31:20,040 If you like, it will help you to detect the flaws on the application because as we have 458 00:31:20,040 --> 00:31:25,280 already mentioned, this is not a problem on the database itself, it's a problem on the 459 00:31:25,280 --> 00:31:26,280 application. 460 00:31:26,280 --> 00:31:31,960 And it's important to assume that there are bugs on your application. 461 00:31:31,960 --> 00:31:32,960 It's a mistake. 462 00:31:32,960 --> 00:31:34,400 It's easy to miss it. 463 00:31:34,400 --> 00:31:42,200 Actually, my Pentastain team friends make fun of me because I'm a one trick pony. 464 00:31:42,200 --> 00:31:45,760 Every time that I compromise a server, it's through SQL injection. 465 00:31:45,760 --> 00:31:49,760 That's the first avenue that I use, but I keep using it because it works. 466 00:31:49,760 --> 00:31:52,240 We always find something. 467 00:31:52,240 --> 00:31:54,440 It's very easy to miss. 468 00:31:54,440 --> 00:32:00,560 And I would like to emphasize that this is also the reason why we have been talking about 469 00:32:00,560 --> 00:32:02,480 least privilege so much. 470 00:32:02,480 --> 00:32:08,680 If you constrain what you can do when the account is compromised, when the application 471 00:32:08,680 --> 00:32:13,440 has an error like this, you will have a better time than if you're using sysadmin. 472 00:32:13,440 --> 00:32:16,480 Yeah, I really want to stress this point. 473 00:32:16,480 --> 00:32:18,200 Actually, there's two points I want to stress. 474 00:32:18,200 --> 00:32:20,360 The first one is the least privilege aspect. 475 00:32:20,360 --> 00:32:26,520 Again, if the connection is elevated and you have SQL injection, the SQL injection vulnerability 476 00:32:26,520 --> 00:32:30,240 can now probably start accessing and doing things above and beyond what a normal user 477 00:32:30,240 --> 00:32:32,640 should do. 478 00:32:32,640 --> 00:32:38,280 You might constrain the users to certain types of SQL, but it doesn't matter if the SQL injection 479 00:32:38,280 --> 00:32:39,280 vulnerabilities. 480 00:32:39,280 --> 00:32:43,200 All this thing, all that we've talked about so far, all sort of meshes together. 481 00:32:43,200 --> 00:32:46,480 They all relate to each other or they become worse. 482 00:32:46,480 --> 00:32:51,000 So, if you don't have least privilege or you have elevated process or you have XP underscore 483 00:32:51,000 --> 00:32:57,160 command shell, catastrophic if you had SQL injection, elevated process, XP underscore 484 00:32:57,160 --> 00:32:59,960 command shell, and connectors SA. 485 00:32:59,960 --> 00:33:03,920 You're just asking for extreme damage of your data. 486 00:33:03,920 --> 00:33:07,640 This is the least privilege aspect with SQL injection is critically important. 487 00:33:07,640 --> 00:33:10,720 The other thing I really want to point out is CoQL is not just SQL injection. 488 00:33:10,720 --> 00:33:17,760 One of the beauties of CoQL is that the queries are literally queries. 489 00:33:17,760 --> 00:33:23,200 You're querying, as Raoul mentioned, essentially when you compile the code with CoQL, they 490 00:33:23,200 --> 00:33:26,480 build up a database that can be queried. 491 00:33:26,480 --> 00:33:29,040 It's really cool because you can literally write your own rules. 492 00:33:29,040 --> 00:33:34,040 I'm not going to say that, hey, writing your own rules is dead easy, but it sure is a lot 493 00:33:34,040 --> 00:33:39,360 easier than having to pay a customer or company $100,000 to write a new rule for your static 494 00:33:39,360 --> 00:33:41,520 analysis tool. 495 00:33:41,520 --> 00:33:46,480 If you have a public GitHub repo, you can use CoQL for free. 496 00:33:46,480 --> 00:33:50,080 You can download CoQL and run it on your own code locally as well. 497 00:33:50,080 --> 00:33:51,080 All right. 498 00:33:51,080 --> 00:33:52,800 So, let's switch attentions now. 499 00:33:52,800 --> 00:33:55,960 Start to sort of think about wrapping this up with a couple of topics. 500 00:33:55,960 --> 00:34:01,480 Sharad, I know you have some thoughts about how attackers gather credentials. 501 00:34:01,480 --> 00:34:10,200 One of the things that is very common for attackers is to do credential stuffing. 502 00:34:10,200 --> 00:34:18,560 One of the common services, if you do a bit of research on the internet, is SQL servers. 503 00:34:18,560 --> 00:34:25,360 A lot of times what attackers do is harvest credentials through various sources, either 504 00:34:25,360 --> 00:34:30,480 through previous data breaches or through other channels. 505 00:34:30,480 --> 00:34:36,480 Once they have these username password lists, they're just going after various services, 506 00:34:36,480 --> 00:34:38,920 including SQL servers. 507 00:34:38,920 --> 00:34:46,520 What we have found is oftentimes SQL servers, unfortunately, are exposed to the internet, 508 00:34:46,520 --> 00:34:54,120 which I can say with some level of confidence that you do not ever need to expose your SQL 509 00:34:54,120 --> 00:35:01,400 server to the internet because I don't think the customer or any of your services or customer 510 00:35:01,400 --> 00:35:04,960 is directly talking to the SQL server to fetch raw data. 511 00:35:04,960 --> 00:35:10,640 There may be some cases, but most times it's some front-end application that is querying 512 00:35:10,640 --> 00:35:12,720 SQL server as a backend. 513 00:35:12,720 --> 00:35:20,000 So, there is very less use cases for SQL server to be exposed to the internet. 514 00:35:20,000 --> 00:35:27,520 We talked about this a little earlier as to how attackers go after the SA account. 515 00:35:27,520 --> 00:35:34,800 SA account, high-privileged, gives you admin access to SQL database and server. 516 00:35:34,800 --> 00:35:40,880 Attackers try to find reuse passwords or a combination of passwords that are breached 517 00:35:40,880 --> 00:35:47,200 to compromise SA account that are exposed on the internet. 518 00:35:47,200 --> 00:35:58,800 Now, the good thing with Azure SQL databases is when you create your own Azure SQL databases 519 00:35:58,800 --> 00:36:03,640 by default, it's not exposed to the internet. 520 00:36:03,640 --> 00:36:08,080 You get to choose how you want to expose your server. 521 00:36:08,080 --> 00:36:10,480 There are multiple ways you can do this. 522 00:36:10,480 --> 00:36:17,800 You can say, hey, I want to expose this to all Azure services, meaning any Azure VMs 523 00:36:17,800 --> 00:36:25,760 or app services can connect to my SQL databases, or you can explicitly add SQL server firewall 524 00:36:25,760 --> 00:36:28,520 rules, which lives in the master database. 525 00:36:28,520 --> 00:36:36,320 At the same time, you can also granularly control SQL database firewall rules. 526 00:36:36,320 --> 00:36:43,680 This is saying, hey, I want these databases to be accessible by only these apps or by 527 00:36:43,680 --> 00:36:44,680 only these VMs. 528 00:36:44,680 --> 00:36:51,640 This is a great level of protection that you can add to Azure SQL databases. 529 00:36:51,640 --> 00:36:58,960 Do not expose it to the internet and have allow listed IP addresses from where your 530 00:36:58,960 --> 00:37:01,240 SQL server is accessed. 531 00:37:01,240 --> 00:37:04,360 I think this is really important, the Azure SQL database aspect. 532 00:37:04,360 --> 00:37:10,160 This is the classic shared responsibility model where some of the security is managed 533 00:37:10,160 --> 00:37:12,240 by Azure so that you don't have to. 534 00:37:12,240 --> 00:37:17,240 For example, things like patching, anti-malware, monitoring, that's all taken care of by an 535 00:37:17,240 --> 00:37:18,240 army of people. 536 00:37:18,240 --> 00:37:19,240 That's their job. 537 00:37:19,240 --> 00:37:20,240 That's all they do. 538 00:37:20,240 --> 00:37:23,240 It's not like people on a part-time schedule. 539 00:37:23,240 --> 00:37:25,000 This is precisely what they do. 540 00:37:25,000 --> 00:37:28,960 But also the fact that things like least privilege, like the process runs at least privilege or 541 00:37:28,960 --> 00:37:33,360 the backend processes run at least privilege, the fact that certain functionality is not 542 00:37:33,360 --> 00:37:34,360 enabled by default. 543 00:37:34,360 --> 00:37:39,760 For example, Xpandr score command shell is not there. 544 00:37:39,760 --> 00:37:42,960 If I remember correctly, Raul, SQL agent is not there, right? 545 00:37:42,960 --> 00:37:43,960 Correct. 546 00:37:43,960 --> 00:37:44,960 SQL agent is not there. 547 00:37:44,960 --> 00:37:48,160 Xp command shell is not there. 548 00:37:48,160 --> 00:37:54,400 There's many other features that are mostly used by attackers and not by normal customers 549 00:37:54,400 --> 00:37:59,280 that are not present in Azure SQL databases, which is fantastic. 550 00:37:59,280 --> 00:38:04,840 It is, and yet people still manage to be able to build industrial strength solutions without 551 00:38:04,840 --> 00:38:05,840 those components. 552 00:38:05,840 --> 00:38:07,840 Of course, that includes SQL mail, everyone's favorite. 553 00:38:07,840 --> 00:38:13,520 I don't know anyone who's used SQL mail, but that's another discussion for another day. 554 00:38:13,520 --> 00:38:15,640 That copath isn't there, right? 555 00:38:15,640 --> 00:38:18,160 That's just so incredibly important because it can't be attacked. 556 00:38:18,160 --> 00:38:21,400 For example, SQL mail, if it's not there, it can't be attacked. 557 00:38:21,400 --> 00:38:22,640 Same with the other ones. 558 00:38:22,640 --> 00:38:27,600 The fact that you can also, by default, Azure SQL database does not connect to the internet, 559 00:38:27,600 --> 00:38:32,360 has no listening ports, you must actually connect it to something at some point. 560 00:38:32,360 --> 00:38:39,000 That could be, for example, the backend of an Azure function app, or perhaps, let's say 561 00:38:39,000 --> 00:38:44,360 back to my Node.js example, you might have a Node.js application running on VM, for example, 562 00:38:44,360 --> 00:38:47,960 and the SQL server, the Azure SQL database is listening on that VM. 563 00:38:47,960 --> 00:38:50,920 You could put a, I'm sure I'm correct if I'm wrong here, but you can actually put a rule 564 00:38:50,920 --> 00:38:56,040 in place that says traffic into that Azure SQL database must come from that, say, Azure 565 00:38:56,040 --> 00:39:01,160 function app or that IP, the backend of the Azure function app or the backend of the virtual 566 00:39:01,160 --> 00:39:02,160 machine. 567 00:39:02,160 --> 00:39:03,160 Is that correct? 568 00:39:03,160 --> 00:39:04,160 Yeah, that's correct. 569 00:39:04,160 --> 00:39:09,480 You can specify the IP addresses of your VM where your Node.js app is running or your 570 00:39:09,480 --> 00:39:13,360 function app and allow access to only that specific source. 571 00:39:13,360 --> 00:39:14,360 What about VNAP? 572 00:39:14,360 --> 00:39:16,400 Yeah, that's another great thing. 573 00:39:16,400 --> 00:39:19,100 I mean, now that you brought it up, we can talk about it. 574 00:39:19,100 --> 00:39:26,440 You can also have your SQL server in a virtual network, meaning you have your Node.js app. 575 00:39:26,440 --> 00:39:32,940 I'll take that as an example, which is running on a VM, which is also on a VNAT. 576 00:39:32,940 --> 00:39:39,280 What you can do is instead of connecting it over the public IP address, you can now have 577 00:39:39,280 --> 00:39:45,480 these two VNATs or these two services connect over the VNAT using private IP address, all 578 00:39:45,480 --> 00:39:47,040 through Azure Backboard. 579 00:39:47,040 --> 00:39:52,600 That basically gives you some more flexibility in the sense that you don't really have to 580 00:39:52,600 --> 00:39:54,960 maintain an allow list. 581 00:39:54,960 --> 00:40:00,740 You could add other resources on that VNAT and they all will have access to the SQL databases 582 00:40:00,740 --> 00:40:01,740 and vice versa. 583 00:40:01,740 --> 00:40:02,740 That's an important point, right? 584 00:40:02,740 --> 00:40:04,120 Because they're all listening. 585 00:40:04,120 --> 00:40:09,800 For example, 10 dot blah, blah, blah, blah, blah address range is non-routable. 586 00:40:09,800 --> 00:40:12,800 Everything that's on that VNAT can talk to everything else on the VNAT. 587 00:40:12,800 --> 00:40:15,380 There doesn't need to be any specialized rules, any network. 588 00:40:15,380 --> 00:40:19,160 You can add extra rules if you want around network restrictions, but you're getting a 589 00:40:19,160 --> 00:40:22,240 degree of isolation just because it's on its own private VNAT. 590 00:40:22,240 --> 00:40:23,800 Yeah, exactly. 591 00:40:23,800 --> 00:40:28,800 All you have to now look at is the network architecture to understand the connectivity. 592 00:40:28,800 --> 00:40:35,920 It's less about managing a list of rules that is somewhere hard to read. 593 00:40:35,920 --> 00:40:38,160 Well, hard to maintain too, right? 594 00:40:38,160 --> 00:40:39,160 Yeah. 595 00:40:39,160 --> 00:40:40,640 It's just one more thing to maintain. 596 00:40:40,640 --> 00:40:41,860 All right. 597 00:40:41,860 --> 00:40:47,320 Let's bring up our last topic and that is Defender for SQL. 598 00:40:47,320 --> 00:40:48,520 Huge fan of Defender for SQL. 599 00:40:48,520 --> 00:40:50,640 I'm a huge fan of all the Defender products. 600 00:40:50,640 --> 00:40:52,600 I think they're magnificent. 601 00:40:52,600 --> 00:40:59,040 Defender for SQL allows you to protect in real time against certain kinds of issues. 602 00:40:59,040 --> 00:41:02,880 For example, it can detect SQL injection. 603 00:41:02,880 --> 00:41:08,640 It can also detect if there's some malicious behavior going on such as exfiltration that 604 00:41:08,640 --> 00:41:09,640 doesn't look normal. 605 00:41:09,640 --> 00:41:13,920 Raul, if I remember correctly, it also has an assessment capabilities. 606 00:41:13,920 --> 00:41:14,920 Yes. 607 00:41:14,920 --> 00:41:24,040 It's the pull that ability assessment feature on SQL Defender for Azure SQL databases. 608 00:41:24,040 --> 00:41:27,960 Actually I wrote the original set of rules for that. 609 00:41:27,960 --> 00:41:33,200 It has evolved since then, thankfully. 610 00:41:33,200 --> 00:41:40,280 It's a feature that allows you to detect who are your principles, what permissions they 611 00:41:40,280 --> 00:41:45,280 have, if there's any change on the database configuration, on the tables that you have, 612 00:41:45,280 --> 00:41:47,080 permissions that you have. 613 00:41:47,080 --> 00:41:53,920 So it's a very useful feature to keep track of any changes on your database. 614 00:41:53,920 --> 00:41:54,920 Yeah. 615 00:41:54,920 --> 00:41:57,000 Actually, just a point of clarification. 616 00:41:57,000 --> 00:42:04,800 Defender for SQL will also work against SQL Server running in a VM on Azure, on GCP, and 617 00:42:04,800 --> 00:42:06,680 AWS, which is actually kind of nice. 618 00:42:06,680 --> 00:42:10,000 So it's not just looking at Azure instances. 619 00:42:10,000 --> 00:42:14,400 It can look at AWS and GCP, IaaS components as well, which is really good. 620 00:42:14,400 --> 00:42:15,400 All right. 621 00:42:15,400 --> 00:42:17,240 Let's start to bring this thing to an end. 622 00:42:17,240 --> 00:42:23,560 Gentlemen, one thing we always ask our guests is if you had just one thought to leave our 623 00:42:23,560 --> 00:42:25,760 listeners with, what would it be? 624 00:42:25,760 --> 00:42:27,640 Raul, why don't you go first? 625 00:42:27,640 --> 00:42:32,080 The one thought that I have will be there's no silver bullet. 626 00:42:32,080 --> 00:42:38,480 You have to consider all aspects, least privilege on the service account, the user that you're 627 00:42:38,480 --> 00:42:47,520 using to connect, the permissions on the database, be aware of SQL injection, be aware of your 628 00:42:47,520 --> 00:42:53,140 tasks, what information you have on your SQL agent tasks, et cetera. 629 00:42:53,140 --> 00:42:56,280 So it's not just protect one area and you're done. 630 00:42:56,280 --> 00:43:00,040 You have to look at it as a whole. 631 00:43:00,040 --> 00:43:06,800 And reducing the text-service area, for example, using Azure SQL databases that by default 632 00:43:06,800 --> 00:43:13,800 has a significantly reduced text-service area is a huge benefit. 633 00:43:13,800 --> 00:43:20,400 Also monitoring everything good that Defender will bring you is something that you have 634 00:43:20,400 --> 00:43:21,400 to consider. 635 00:43:21,400 --> 00:43:23,720 Yeah, thanks for having me here, Michael. 636 00:43:23,720 --> 00:43:27,600 It was a great discussion with Raul as well. 637 00:43:27,600 --> 00:43:33,440 So one of the things that I would like to close with is to assume breach at every layer. 638 00:43:33,440 --> 00:43:40,120 We talked about a lot of the different attacks and one thing that you can do, actually everybody 639 00:43:40,120 --> 00:43:45,920 can do who is part of the team, is to come up with different things that you can think 640 00:43:45,920 --> 00:43:48,560 of if a particular component. 641 00:43:48,560 --> 00:43:54,380 It doesn't have to be just the front end or the application that is exposed to the internet. 642 00:43:54,380 --> 00:43:59,440 It can be your middleware, it can be a network appliance that is in between, or it could 643 00:43:59,440 --> 00:44:02,760 be the backend or it could be the SQL database. 644 00:44:02,760 --> 00:44:11,600 We ask ourselves at every competent level or every identity or user that is involved 645 00:44:11,600 --> 00:44:15,760 in that, this includes even the DevOps credentials. 646 00:44:15,760 --> 00:44:20,600 We ask ourselves the question of, hey, what happens if there's a breach here at this point? 647 00:44:20,600 --> 00:44:25,680 I like what Raul put it as well, there is no silver bullet, but at least what we can 648 00:44:25,680 --> 00:44:33,100 understand is what will be the blast radius if any of those identities or components are 649 00:44:33,100 --> 00:44:34,660 compromised. 650 00:44:34,660 --> 00:44:41,920 So knowing the level of damage to some extent is always, always beneficial in the security 651 00:44:41,920 --> 00:44:42,920 world. 652 00:44:42,920 --> 00:44:48,360 So knowing how far an attacker can get can oftentimes be very, very bad. 653 00:44:48,360 --> 00:44:49,360 So I just want to wrap this up. 654 00:44:49,360 --> 00:44:51,640 This has been a great conversation. 655 00:44:51,640 --> 00:44:57,180 To our listeners, just be aware that these two guys, they live and breathe securing both 656 00:44:57,180 --> 00:45:02,120 our own instances of SQL Server, but also work on just making sure that customers deploy 657 00:45:02,120 --> 00:45:03,600 things correctly and so on. 658 00:45:03,600 --> 00:45:08,320 They really do come at it from a real world attack perspective. 659 00:45:08,320 --> 00:45:11,400 If you think I'm evil, you should see these guys in action. 660 00:45:11,400 --> 00:45:12,960 So with that, let's wrap this up. 661 00:45:12,960 --> 00:45:14,920 Again, thank you so much, gentlemen, for taking the time out. 662 00:45:14,920 --> 00:45:17,840 I know that you're both incredibly busy. 663 00:45:17,840 --> 00:45:20,640 To all our listeners out there, we hope you found this episode useful. 664 00:45:20,640 --> 00:45:24,240 I like to think this is a little bit different than some of the other episodes. 665 00:45:24,240 --> 00:45:26,080 Stay safe and we'll see you next time. 666 00:45:26,080 --> 00:45:29,400 Thanks for listening to the Azure Security Podcast. 667 00:45:29,400 --> 00:45:36,220 You can find show notes and other resources at our website, azsecuritypodcast.net. 668 00:45:36,220 --> 00:45:41,080 If you have any questions, please find us on Twitter at AzureSecPod. 669 00:45:41,080 --> 00:45:46,400 Background music is from ccmixtor.com and licensed under the Creative Commons license.