Enjoy the Podcast?
DBAs, developers, security consultants.
How easy is it to hack a SQL Server? In this session, we’ll see examples on how to exploit SQL Server, modify data and take control, while at the same time not leaving a trace.
We’ll start by gaining access to a SQL Server (using some “creative” ways of making man-in-the-middle attacks), escalating privileges and tampering with data at the TDS protocol level (e.g. changing your income level and reverting without a trace after payment), hacking DDM, and more.
Most importantly, we’ll also cover recommendations on how to avoid these attacks, and take a look at the pros and cons of new security features in SQL Server 2016.
This is a demo-driven session, suited for DBAs, developers and security consultants.
Why I Want to Present This Session:
Hacking anything is easy. Securing it is even easier. Why not spread the word and raise security awareness?
Brent Ozar: So in the last session of today’s GroupBy, we are welcoming André Melancia and he’s going to talk about – I think this was the top voted session too, for the entire GroupBy for April – hacking SQL Server, so take it away Andrè.
André Melancia: Okay, thank you Brent. It’s a bit scary to – for that happen to be the most voted so it’s a bit stressful. First of all, thank you very much for having me. It’s great, I’ve been seeing the last sessions, the last session, and the sessions before recorded, so yes, this is the last session, this is hacking SQL Server and I should mention this is not illegal. What we’re going to do here is not illegal, so this is important to say.
We’ve been doing this session for quite a long time, this is – I actually raised the version number for the demos, so this is like 26 or 27, something like that. So every time is different and sometimes things that used to work and we used to be able to hack stuff, they stopped working and then we changed it around, something else just stopped working as well, so maybe we’ll see a few examples of stuff that should be a bit better in terms of security, but most of the things are actually things you can actually configure. So these are not actual security bugs, these are people bugs.
So okay, so first of all, who am I? Okay, so my name again is André Melancia, I’m a Microsoft certified trainer, I do mostly SQL Server and Azure training. I also do consulting and I own a company which is called Lunar Cat, that’s the symbol for the company, which is my cat’s paw. My cat actually has a paw that has the different toe like that, so I actually decided to use it because it’s just boring to hire a designer right.
We basically do cat stuff. If you go to the website, all you see is a message saying cats are sleeping or something like that, because I haven’t had the time to do the website, because like most people, we do stuff for someone else and we forget to do stuff for ourselves. Okay, so if you want to contact me, my contacts are in my very, very simple webpage, at andy.pt Just go there, you can connect with me on Facebook, Twitter, LinkedIn, whatever or just send me an email and I will try to reply as soon as possible. Okay, so that’s about it for me, and let’s start the session. So as I mentioned, this is a hacking session, but technically for law enforcement, we just say this is a security session because we don’t want to say we’re doing anything wrong.
Okay, so the problem is – this I figured out some time ago, the problem is not with the software, the problem is with people. So people cannot configure stuff correctly. If you configure stuff correctly in terms of security, you’re going to do fine, you’re not going to have a problem. Most people just do the default installation, they just don’t care, then they come into problems, they get hacked, then they get fired mostly, so don’t do that. Think about what you’re doing, think about the stuff you’re using, think about security flaws it might have, think about configuration, but basically, this is your fault as a person, not the computer’s fault, okay?
So Microsoft software is just perfect, it has no bugs whatsoever. Okay, so let’s give this one a try. So if you guys have any questions, I would ask Brent to relay the questions as we go along. Two or three demos, don’t leave the questions to the end, just keep asking, and Brent, over five, ten minutes period I’ll just ask for some questions and if you have any questions I prefer to answer them as they go along.
Brent Ozar: Okay.
André Melancia: I will have to say this, this slide is very important. So the first time I did this session, I actually called it NSA Secrets, and I used to have problems with this, so every time I went to the airport people looked at me funny, I didn’t know what it was. I actually got frisked a lot, so just to be safe, this is not NSA Secrets anymore, this is called Hacking SQL Server. That’s about it, but still the demos we are going to do have to do with NSA, have to do with MI6, okay, so they’re not real, they’re fake, they’re completely fictitious, but still they exist.
So basically one or two years ago I actually did some sessions that had MI6 examples like James Bond. I’m a real huge fan of James Bond, and then the next session we started to do with the hacking stuff, that I actually got the NSA Secrets, which basically was the NSA are the bad guys, they sniff on everyone, so they’re the great bad guys that we can use for these examples, and the good guys, the ones that actually save the world, we all know this, we’ve seen the films, the good guys are James Bond guys and the MI6 guys. So they are the guys that are going to get hacked, right? So NSA bad guys, MI6 good guys, so let’s go with that for the demos.
Okay, so a few requirements for the demos, I will make the demos available for you I promise, this weekend I will make them available on my website. Currently, it’s just a single page but it’s going to be a single page with a small link in the bottom that says demos here, and you can actually see the demos for this session and some of the other sessions before. So basically what you need is – for some of these demos, actually SQL Server 2000 might just work, but you have low-level security, you have dynamic data masking, and some other stuff and for that you need 2016 obviously, because they only come around for 2016.
In terms of editions, I think they will work on basically every edition around and most of these I will not mention but if I don’t mention it, they have worked at least for 2012, maybe before. And I am using in my computer actually vNext, not 2016, still the last vNext version because I haven’t had time to update it, but I think they work pretty much the same. Okay, so the first problem we have is something we call man in the middle. So hopefully every computer guy knows about this, and man in the middle is a kind of attack you can have where you have – I’m not sure Brent, can you see my mouse?
Brent Ozar: Yes.
André Melancia: Okay, so where you have a victim, and that victim actually tried to connect to a server, so if the victim is female – if actually the attacker is a female, you can’t have a man in the middle, you have a woman in the middle. So the politically correct term is person in the middle, but since technically you can’t say that, I’m still happy with man in the middle, so who cares?
Okay, so basically you have a victim, a victim usually connects to a server, this can be a SQL Server connection, this can be a web server connection or any other types of connections, and then you have an attacker somewhere, the one in red, and the attacker tried to hijack the connection in some way. I’m going to show you a list of possible ways, I’m not going to get a lot into that because there are tons of ways you can do this. I’m just going to show you the easy way you can do that with SQL Server.
Okay, so basically just to contextualize this, with SQL Server, SQL Server has a protocol called TDS. So this is not new, this is really old stuff, about at least 30 years I would say, and TDS stands for tabular data system – tabular data stream, and this was actually invented by Sybase, which were the guys that invented SQL Server in the beginning, then they actually got together with Microsoft, something happened, Microsoft got it, Sybase actually spinned it off ASE and it’s been there ever since. So this works really well with ASE as well, so it’s kind of the same thing. Although the demos we’re going to do will probably work with a lot of stuff like Oracle or MySQL or even a web server if you don’t protect it well enough.
So obviously I hope you guys know this, the default port is 1433, that’s the default port for the default instance in SQL Server, and there is a debate and I’ve seen a post by Denny Cherry, I think it was a few months ago, maybe late last year, where we talked about the possibility of changing the default port to something else to make it more secure.
Obviously the big problem here is you have to think where your attacks come from, so if your attacks come from the internet, that might be a good idea, it takes a little while for the attacker to do his thing, but eventually he will attack you so it’s only a matter of time, but the biggest problem is your biggest attack actually comes from inside your own company most of the time. So you have employees that just don’t like what they’re getting paid, something like that, employees that just don’t like the boss, they’re going to get fired, something like that. So something’s going to happen and in most cases, the biggest problem we have is not outside hacking, it’s hacking from someone in your own company that’s going to cost you money. So one of the options here is okay, do we change the default port? Yes or no, so Denny Cherry actually has a post about that. I can’t seem to find the link but it’s there somewhere, where he actually talks about this and the answer is obviously no. Doesn’t make any sense, because, in the end, security by obfuscation doesn’t really make any sense because you’re going to crack it eventually, right?
So one of the examples of that is actually Azure. You have – for instance, you have virtual machines in Azure using port 3389 for remote desktop and in the beginning, those ports were actually big numbers, like 40,000, 50,000, and apparently Microsoft figured out it wasn’t worth the hassle, so they just decided to use that because eventually, you would figure out that was remote desktop, right? So it’s easy enough to figure out. So basically it doesn’t really make any sense to change the port just for security reasons. It might actually be practical for other reasons but it doesn’t make any sense for security reasons.
So let’s talk about the man in the middle kind of thing, so if you are an old guy like me that actually used SQL Server 2000, 7, 6.5, stuff like that, you probably know this existed. So up to SQL Server 2000, which means 2000, 7, 6.5 and all the way up to the beginning, which I haven’t tried, I started on 6.5, the login packets – the first packet that you got on the TCP connection was actually encrypted. So basically, you could see the username and you saw kind of a weak hash for a password. You just got the hash, you just took it – you tried to hack it and it was easy enough to hack the hash. So that was a big issue with security in terms of SQL at the time, so they completely solved this problem.
So the problem was solved, so this was the first one was then the login packet, so they solved the problem in 2005 in a great way. So they actually encrypted the login packet, the login packet is perfect now, but everything else isn’t encrypted. That is by default obviously, if you want to solve SQL Server by default, the remaining packets stay unencrypted, so basically, this is what you have all the way up to now.
So I actually haven’t tried it on 2017, I tried it on the last vNext, which I have on my computer, and this still happens then, so by default it does not encrypt the connections, which it should. But let’s go ahead with this, so okay, there are a lot of ways you can try man in the middle. This applies to not only SQL Server connections but everything else, so the hard way is if you are a system administrator guy, you can try any of these ways.
The easiest way obviously is to just disconnect the cable to Ethernet boards on the attacker’s computer and get the two informations there, and that’s the basic way of all – obviously, there are more sophisticated attacks that a system administrator should know about. But I’m a lazy guy, this takes a long time, I’m really lazy to do that, so there is an easier way. An easier way is okay, so basically what – we need to understand this, so we have the victim, we just want the victim to stop using the original connection and go to the attacker’s connection.
So there is an easy way, just change the host file on the victim. So that’s really, really easy. So yes, the question you might ask is ever since you have Vista – not XP, but Vista, you need administrator privileges to change the hose file. Yes, but think about this: you are the administrator – the domain administrator or something like that, you obviously have those kinds of permissions so we can do that. So if work inside that organization and you have permissions for that, okay, that’s done. You can crack it, but again, even if you’re not, you can just send some sort of email to the guy. So that the guy clicks on something and then a pop-up of a window appears that says you want to try this, yes or no, and everybody says yes, right? Because that’s one of the biggest flaws that you have with a user account control is people just say yes, they don’t read what’s in it. So if people actually click on it, and most people actually do, even computer guys, you’re going to get hacked and the host file is going to be different. So that is one way you can divert the traffic from the victim to the attacker, so imagine you have a server called server, and you have an attacker with IP 220.127.116.11, so you just say on the host file, on the victim’s computer that this server actually has the 18.104.22.168 IP address, so that goes to the attacker directly and the victim doesn’t know about it, so that’s a way.
One of the easiest ways you can do this with SQL Server specifically, so this is generic, but SQL specifically, is to change the aliases. So that has the exact same needs, so we need to be an administrator for that, but you an actually go to the configuration manager, you can go the real old client configuration tool which existed up to SQL Server 2000 or 2005, something like that, or you can just go to the registry. So this is the cook book, you can actually use it. So I’ve done the homework for you, this is the way you can actually do it. Because I know you guys are as lazy as me, so here we go. Okay, so let’s try one of these.
So this is the first demo, let’s hope this works because demo gods and stuff like that, so let’s try this, so, first of all, I’m going to show you the SQL Server configuration manager, and one of the things we can see here at first is the aliases, so I’m going to use two. I’m going to use this one, AndySQL3, which is a virtual machine somewhere in the Cloud, I’m going to explain that later. I’m just going to use it for a small demo and afterwards, we’re going to use something which I call local12345, which basically is a connection to my local host, to port 12345, which I would simulate that as the attacker’s port. So basically the attacker so far has done his job changing either the aliases or the host file or something like that to make sure the attack actually goes to the attacker. And the attacker will be called local12345, just for us to know where we’re connecting to.
Okay, so this is the first demo, and yes you can see I’m a big fan of James Bond. I’m also a really old school guy, so you probably know what this is. Yes, so this is good stuff. Actually, last week I did a virtual chapter for PASS, for Portuguese – when I actually got a lot of kitties and cats and stuff like that, so apparently, this is the thing I’m going to use from now on. Okay, so the demos go like this, so basically, all you have to do is create a database; which in any database, no special requirements, just create it. They’re created already and I’m going to drop everything that might just exist before, so before I have to connect with.
So Hobs is actually my computer, the big cat, fake cat with the kid, because my computers are actually themed like cartoons or whatever. So this is going to fail obviously, so this is obviously something that I want to do. Just to clean up anything that I might have in that database before because I’m too lazy to just drop the database and recreate it.
Okay, so let’s create a database – a table, which has some fields, an ID, which is a sequential ID for the films, then the film name, the year of the film, the guy who stars in the film, Sean Connery most of the time, Roger Moore, and then – I actually repeat the column because I’m going to need it for demos and just have the first name to be different, then I’m going to have a payment column which we’re going to see as well. So I’m actually going to run this and see if it works. So obviously I have a table here, which in total has 26 records and we’re going to use that in the future for some stuff. Okay, so one of the demos we’re actually going to do is this, so I’m actually going to connect – I’m actually going to try to use Wireshark, so Wireshark is here already. I filtered that to the specific ports that SQL Server uses and some of the ports I’m actually going to use, so I’m just going to start this here.
So I’m actually having to connect to a different computer, so there is limitation with the Windows kernel, which tells you that you cannot sniff the packets that go from your computer to your own computer. So that is not a flaw, that’s actually a very good thing in terms of security, but it’s terrible in terms of demos. So I’m going to have to connect to a machine in Azure just for this specific one, so hopefully, that’s connected.
So somewhere here I actually got packets but I’m going to clear them up and just start them again, because that’s just trash for me. So one of the things I do not recommend you using is IntelliSense, so just turn off IntelliSense because otherwise you’re going to get a lot of trash in terms of packets because this is not a limitation on SQL Server, just the Management Studio because Management Studio is going to try to connect to the database over and over again, every time you move your cursor, to try to figure out stuff for you, so you don’t want that. So you’ve seen the table, it’s just a normal table. Now, I’m going to try to use it just with the demo. So one of the things I should mention is all these x’s here are just to path stuff so we can find it faster in terms of the sniffing stuff.
So one of the stuff we’re going to do is – okay, I’m just going to stop because I don’t want anything else, and one of the Xs are actually here, so this is a way I get to actually be easier to find the stuff. So you have a sniffing comment here, and then you have a select something somewhere down here. So SELECT star, something like that, get that, something like that, which is basically the query we did here. So first problem that you see, by default SQL Server actually has packets encrypted and you can see not only the results but you can actually see the query that’s being sent to the server. And one of the other problems that you have is all the comments that you include in the query go there as well.
So this is a problem for two reasons: first reason, you’re actually adding stuff to the packets, so you don’t need all this stuff, so we should actually reduce that to make the queries go faster. This is not a security issue, just a performance issue, because as you can see, all this stuff actually doesn’t fit on a single packet, so I have to send two packets just to get a reply. If I just sent this stuff here, it would be much simpler. So the second problem is that some people actually have comments like this. So I’ve seen comments like this, which someone says, you should try this with user something, and password something, and obviously if you are a computer guy, you know that people have passwords like you know, your favorite team 12345, or your favorite team 2017, if not, 2016, 2015, 2014, something like that. So it’s actually very predictable to be able to figure out what the password is for a person, so don’t. So just keep it as safe as possible, don’t make comments like this where you include the password or even the user.
You’re supposed to try okay, because for 2005 on, you don’t have the username, but if you put the username here then you know what the username is. Obviously, if you do something like – you try to get current user as a result of a query, you obviously get the name of the current user, so that’s a problem as well. So try to avoid doing that in a production environment.
Okay, so this is the query that you have, but you also have the reply. So the reply actually sees stuff like Daniel Craig, up there you see something like License to Kill, something like that, so all the information is here, and the stuff that you don’t see, which is a lot of dot dot dots are the integer stuff, but basically this protocol, it’s well-known so everybody knows what’s in here, so everybody can see exactly what’s in this protocol.
Okay, but this is using Wireshark, but the problem is I can actually see the information but I can’t do anything with it. So one of the things I actually try to do is there is stuff for this in Linux, but there isn’t stuff for this in Windows, so I want to change the packets, but I want to change the packets on the fly. I don’t want to change it and see it later, I want to change it and see it now.
So I actually created sort of a proxy thing, I just got some code from the internet that did this for TCP stuff and I changed it a bit to add more stuff, so basically I’m going to connect – I’m going to sniff on my local host port 12345, and then redirect all the connections to port 1433, also on my local host. I’m actually going to change some stuff, so one of the stuff I don’t like is George Lazenby, great guy, but you know, you can actually change some stuff. So maybe Brent, you are an American, you are the agent that’s going to try to infiltrate this. So you are Brent, this is Austin Powers or something like that, so also an American, technically an American, tries to speak English – British English, but still the guy is an American, and definitely probably like the worst Bond ever. That was a terrible film.
I’m glad someone did a different Casino Royale again, so basically that’s it. That was actually kind of a comic kind of thing, not really an action thing I would say. Okay, so – let’s not get into that – so we have the proxy running here, and now I’m going to try to make this so you can actually see this stuff. So the second demo, I’m actually going to connect this to the attacker’s computer, so I’m going to change the connection all the way up to the attacker’s computer, which is on port 12345, and right now – sorry about the beep, somehow it happened, I’m not sure where it comes from but I think it only happens once.
So you have stuff like declare edition, stuff like that. This is stuff that Management Studio actually asks you, but it is okay. So currently all you see in the black stuff on the left is actually stuff from the attacker, so we are going to see what the attacker is seeing. So let’s try to run this, and if I try to run this, the connection is telling me I’m connecting to the right computer, which is going all the way to the attacker but apparently I don’t know that. The attacker knows that but I don’t know that. So just a curious fact, I’m going to try to change the database, just to figure out what the database is, I’m going to actually change this – sorry about the noise.
I’m not sure if this helps, okay – I’m going to change this to GroupBy, okay, it’s lower now I think – so basically this is all the stuff you had to do just to figure out a list of databases and then select the database for the connection. So okay, we’re here and now we are on the right database and we are on the right server but still the attacker is sniffing all our traffic.
That’s okay, we’re not aware of this, now we’re going to try to do the same query as before, but just run it and something red happens somewhere in the attacker’s window. So what’s going to happen here – I forgot one thing, I forgot to add spaces, sorry about that. So we actually have Brent Ozar – I’m not going to change that – so I forgot to actually add spaces to remove that stuff but basically [inaudible] have David Niven and George Lazenby, which is still there at the end, sorry about that Brent. But still we actually changed the information somehow.
So if you think about this, this is the MI6 database and currently, Brent, which is an agent for NSA actually infiltrated the MI6 database, which is okay but the thing is, it’s there. If you do some sort of a report, you are authorized to go to England and do whatever kind of secret stuff you have to do to spy or do British stuff, but still it doesn’t change the database. It only changed the information that the client got to see.
So next step – one other thing, so this is useful for a multitude of things, so if you try to do this with for instance, the payment column, we actually have a payment column which says Sean Connery, which actually got paid all this money, and if you see the payment column, something actually happened here, and somewhere Sean Connery actually got a billion whatever, kind of, zeros it has, so I actually change this as well. It doesn’t really show but I changed it and the one below I didn’t.
So how did I do that? I actually changed it in binary form somewhere down here, so I actually said it has to be the number, so you actually have to send numbers. One prefix with Sean and the other one with the – I think it’s Daniel Craig, something like that, and I actually said if you see these writes, this integer, you actually change that to a billion Euros or Dollars or whatever it is. So that’s one way you can actually do that.
So this is still not enough because basically, we’re changing information the client sees, but the information in the database is still the same. So let’s try to change this and okay – and let’s do the same query again, or actually a different one, which actually says escalation example demo, so that’s the only thing it’s going to say and if you ask me why I have the get date here, it’s for you guys to see I’m not faking this. There is no trick, this is happening in real time, this is just happening in European, Portuguese, Greenwich time kind of thing.
So I’m not supposed to see this right, so technically I was supposed to see something that said escalation example demo, but in fact I actually got something else. So what exactly did I get? So I actually changed it around again, and whenever I saw escalation example demo, or something else, this is entirely up to you. You can actually do this anywhere else, so I actually created a login called Austin Powers, with a weird password, and changed the guy to be a sys admin and add it to the sys admin role, and then I actually sent the shagadelic exploit successful kind of message.
So basically this was easy enough to do, and to confirm that this actually exists, this was the login before, I’m going to do a refresh and now I actually have Austin Powers here, which is also a great secret agent, perfect guy, real funny guy, doesn’t know how to dance but great guy.
So if you have server roles, you’re actually a sys admin here now. So what is the problem here? The problem here is okay, so you were a sys admin and obviously, this is also kind of the permission that you have; if you were not a sys admin you would not be able to raise and use it as sys admin as well, so that is also a security concern. Don’t give too much permissions to people, but the point is I actually changed the information. I’m now sys admin, I can actually log in with that username and that password, and change whatever I want in that database. So basically, the database was just hacked. If that user is strong enough to execute commands, you’re actually able to expand that attack all the way up to your active directory, change whatever stuff on a different server. You might be able to do that, depending on the level of security that your SQL Server is configured to do. So the problem is, if you configure it correctly, you’re not going to have these problems, but if you configure it just by default, these problems are going to happen.
Okay, but this is how you crack it. How do you solve it? So easy way to do it, so you just go here to the protocols here in the configuration manager, and basically, you just say force encryption yes. You can actually do it on the connection string in the client side, that’s exactly the same thing. Either one being active and the connection is encrypted forcefully. So this is still not enough – just to mention this is still not enough because this doesn’t guarantee that you’re actually talking to the wrong server. That is also a possibility because the attacker can actually fake a different server with the same tables and stuff like that. You actually connect to their encrypted, but it might not be the right server, so you should always include the certificate. I’m not going to go into that because that’s a bit boring, but it’s easy enough to include the certificate and then make sure the authenticity of the computer you’re connecting to is the real one.
Okay, so one of the things you have to do, which is normal, is to just restart the server, and while I’m going to do that, I’m just going to stop this thing here, otherwise it’s going to crash, and just correct one of the things I should have corrected, which is – I’m doing this in binary terms, so I should have some spaces, the strings should be the same size.
So hopefully this is done by now, I’m going to start it again, and now I’m going to try this one again. So if I try to connect to it again – so this is normal because the connection was dead, but I’ve started the connection again, but all I see here is complete gibberish, so I don’t get to see anything. This is just the encrypted stuff, so it’s in bytes, you cannot understand, so the attacker is going – has that connection going through him, but he cannot see exactly what’s happening there, so that is actually a good thing.
So if I try to do this, the information is still there, so it’s unchanged so we’re back in business again so it’s completely safe now. Okay, so this is the first demo I would say, this is the first demo – Brent, do you have any questions for me in terms of this first demo?
Brent Ozar: Do people said in the GoToWebinar Q&A that they were scared by how easy that was. W. J. Anderson asks, “Sorry if I missed it but do you have to have the default SA user enabled for this to work?”
André Melancia: No, definitely not. You can actually use this on any user, it does have a free to be sys admin – for you to create a user that’s a sys admin, yes, then you do have to have some sort of privileges, security admin, sys admin, something like that, but if you just want to hack it in the first demo, not take over, if you just want to change the information like doing insert and update, then you do not have to be a system administrator to do that. you just have to have permissions on that specific table. Simple as that.
Brent Ozar: And then Vioral says, “How much overhead will the encryption have in terms of performance?”
André Melancia: Actually have that question a lot of times, so that is actually a good question. That kind of depends on the information that’s going around, but mostly the first part of the connection is the worst part. So if you have certificates, if you do the connection and the keys go around and then you have the validated certificate and all that, it takes a while, so for the first query that you have in a connection, that’s going to take a while. I can’t really tell you how much but it’s – okay, maybe 20%, 30% more that it should, but from there on, queries go maybe 1%, 2% more than they should in terms of time that they take to encrypt and unencrypt. It’s not significant.
If you have a recent server, that’s kind of irrelevant. You should just ignore it, but yes the initial connection does take you longer, so that is the only issue I would say in terms of speeds.
Brent Ozar: Alright, and then one more – Daniel says, “Are you going to talk about certificates and verifying the name of the SQL Server you’re connecting to?”
André Melancia: No. I have a slide for that, I have a slide for that. So yes, even if you don’t use certificates, just say yes where it says force encryption. Sometimes you might have a situation where you can’t do that because maybe you’re connecting with a really old server to that or really old client that still uses 2000 kind of drivers or something like that, and then they’re not compatible with 2005, that is a possibility that you have. And in that case you should do that on the client side on the connection string for the more advanced clients, but in 99% of the cases, that doesn’t exist anymore, so I would just say force encryption, yes at all times. That will solve your problem, and – so for you to validate client certificates and all that stuff, just take a look at these links that I put here. They’re really boring to present on really but you should really read them, and yes, they are boring sorry – but just read the links in here and you will get up and running with getting that on a server and making sure that’s secure for you. And yes, occasionally you should maybe just do a Wireshark on your network to see if something else is encrypted, so that’s probably a good option. I’m not sure if you have any more questions?
Brent Ozar: No, that’s good, keep going.
André Melancia: Okay, so I’m just going to mention in a second always encrypted, so always encrypted doesn’t work like this, so always encrypted doesn’t work on the connection, it actually works on the information inside the connection, so it’s complementary, so if you have – so basically the concept here, I’m going to make this simple because it’s a bit more complicated than this.
If you have a client, the client actually have some keys, there are some keys on the client, some keys on the server, just see one hour, two hour, whole day session on this because you really need it to completely understand this, so I’ll just make this simple, but basically the client side – this drive here, ddr.library actually encrypts the information there, but it does not encrypt the information on the connection thing. It just encrypts the information on the column thing, so if you have a column, which in the example here is social security number, basically when you send the information there, so when you do an insert or an update, you actually say yes, I want to send the information there, but the information goes there already encrypted.
So the other columns go unencrypted but that specific sensitive column actually goes encrypted directly to the server. Why? Because this assumes that you cannot trust your DBA with the information. So only the developer and the DBA – you need to have the two roles to be able to have the information and steal the information. So that is actually a good thing, so you should use always encrypted, that is a good thing but it has problems. That’s what I want to talk to you about, so this, as I mentioned, is my column, and you can have two options in a column. You can actually make them deterministic or random. So you see the colors here, if it’s random it’s green, so it’s good.
So every time you say something like ABC, it goes all the way up to the encrypted parts on the server and actually has some bits and bytes which are different every time. So every time you say ABC, it’s different every time on the server, but when it comes back, the client will actually decrypt that, so you need to encrypt it on the client side and you got AVC every time because it sorted something like that, and it works. It just works. Think about it as it just works, but there is a problem for that. So if you have a million records on a column or a million records on the table, you will have a problem with using WHEREs. So if you say WHERE something equals to ABC or WHERE something equals to a specific country, you’re going to have to send all the rows all the way up to the client so we can decrypt them and get them. That’s where deterministic comes along, so deterministic says, if you encrypt ABC, that’s going to say bits and bytes but it’s going to be the same bits and bytes every time. So that helps in terms of sending the information all the way up to the server that says, I want all the clients to say ABC, that’s okay, but ABC is already encrypted when it goes to the client and it’s compared encrypted. So deterministic works for that, but you should hack it somehow, right? There is a way to hack it, so you can actually understand it from the redness in the deterministic part how to hack it.
So if you have random stuff like in the last column, it’s different every time so I’m actually using the country column and try to always encrypt the country column, and it’s different every time, so if you want to get clients from Portugal or from the UK or from whatever it is, you will need to get all the information, but if it is deterministic you can just say I want something from Portugal, so you got all four records that are in red here, which is fine, but there is a problem.
Let’s imagine you have some companies that go online that say 70% of our clients are in the UK. Okay, so you just have a table of clients, 70% of the records have the same bits and bytes, so yes, those guys are from the UK, and then you take those statistics and see the rest of the guys. So from social engineering, from marketing, from whatever stuff you can get information, you can actually figure out, counting the number of records, what the records mean. For instance, if you have a record that says for instance UA, so UA is Ukraine, so if you have a record that says Ukraine, then you will immediately know all the records that are from Ukraine. So that’s easy enough to do, so that’s the way you hack it. You hack it through deterministic, actually repeat stuff and with repetition you can actually figure it out. Okay, so that’s always encrypted. Any questions about this?
Brent Ozar: Nope, you’re good there.
André Melancia: Okay, so instant file initialization and be aware that this is not an error. So, Brent, you are from the US and you guys use Z’s, the rest of the world, including Portugal, actually uses Z, but in English, we actually say it with an S, so this is going to be a rebel kind of thing. So instant file initialization, if you install it, this is not new, this is actually quite old, but if you install it with 2016, you actually have a wizard kind of thing that says do you want to include the grant perform maintenance stuff privilege – so that you can have instant file initialization and if you really don’t need it, don’t use it.
The reason for that is, imagine you have a hard disk, your hard disk has some information which is old information and then you get that hard disk and you add a normal file, a MFD file or something like that to the hard disk and you use instant file initialization. If you do that, you’re not going to clean that information, the information is still going to be there from the previous data that you have there, so what can you do about it? It’s simple enough. Just take a copy of it and then try to see whether it with a hexadecimal editor or something like that, try to recover the information. So the recommendation here is always – just don’t use instant file initialization. It’s a security risk because you don’t know what kind of information you have on the hard disk previously.
So that is a security risk, but yes, there are situations where you really, really need it. The same way as before, you might need some deterministic stuff because you really, really needed that in terms of not sending all the information to the client, so it’s a trade-off. You have to decide in terms of security what you have to do. So instant file initialization just avoid it. Okay, so any questions with this one?
Brent Ozar: Yes, there was a – Daniel asks, “What kind of permissions does a user need to view raw data inside an MDF file? If they get ahold of the MDF file.”
André Melancia: So that would depend on the user that SQL Server is running on, so in most cases that I’ve seen, if you are the domain administrator, you just get to see everything. So that’s because it’s not configured properly, this hasn’t got anything to do with instant file initialization, this has to do with how you configure the user – actually here, the example is good here. So you have the account name, you should always have a separate account name for each of the services and all that, just be careful with all that to make sure that a normal administrator would not have access to the normal stuff SQL Server has. But basically if you have access to the computer, you would get, as an administrator, or just have access to the physical hard disk or the physical backups actually, then you would get the information that was previously there when you created the file with instant file initialization. This is not just with that obviously, if your system administrator have those kinds of privileges, you can actually use a hexadecimal editor to show off your stuff.
I used to have a demo about that but they actually fixed it, or broke it, actually broke it – they broke my demo because it started putting out a weird error. I have to see that with Microsoft, but that’s a different thing. That was a really bad demo anyway, so who cares.
We have transparent data encryption, this is something around 2008, so this is not new, and the concept here is a bit different. So before we actually were worried about the information in the first case, the information that was encrypted over the wires, so that’s important, the second case was we need to encrypt information that’s on specific columns so the DBA does not see it and with TDE it’s a bit different, so with TDE you actually encrypt a whole database on your system, but it has some problems.
So some of the problems is your server has to have logical access to the keys. So if you are the server guy, if you are the SQL Server, then you have to have access to the information and you have to have access to the keys, and that is a problem because most people just don’t care and they put the keys just next to the MDF so that’s just stupid, don’t do that obviously. So obviously sometimes you don’t enough money to have different storages and different options, sometimes that is a problem, but the stupidest point I had is when a guy just says, okay I’m really scared that I’m going to lose the keys, so I’m just going to have a USB drive, I’m going to put the keys in the USB drive.
That’s a really good idea, but put it in a safe where no one can touch it. Just don’t put it in the drawer where you have cookies and stuff like that and it’s open so your colleagues can actually go and get cookies for you, and don’t do that because most people do that, they just go there, they open it, they take the keys and just get some sort of – you can just go there to the hard disks even if that’s encrypted, you have the keys, you have the MDFs and NDFs and you just do it. So obviously that doesn’t make any sense.
So how do you hack it exactly like that? So fear is the biggest part, and one of the other problems is sometimes if you are a system administrator or a DBA, if you have access to the MDFs and the NDFs and the LDFs, then you probably have access to the keys as well because no one cares about separating those in terms of security. So that is one of the big problems. So this is basically what I mentioned. And by the way, TDE is actually transparent, as the name says, so if you don’t have any of the security options I mentioned before, over the wire this is going to go unencrypted, so that is a problem. Be careful with it, this is not exclusive, either of these are not exclusive. You can actually use all of them together, so that is one of the options you can have. I’m not saying use all of them, but use some of them. Just don’t think this is someone else’s problem.
So backup encryption, just to mention this and then I’ll ask for questions, I really like backup encryption because I haven’t packed it yet. So just use it. So Brent, do you have any questions so far?
Brent Ozar: No, a couple people were asking could I use volume encryption or at the SAN in order to get past instant file initialization risks?
André Melancia: Well, that’s SAN’s problem outside, so that would depend on the hardware manufacturer outside; that depends on how they allocate the new files that you ask for. So if they allocate it, make sure that’s clean. That in terms of instant file initialization, if they make sure that’s clean when they supply that to the server, let’s put it like that, then the instant file initialization is not a problem. Otherwise, if on the origin, on the physical place where it had the storage, that’s not being done and the SQL Server actually sees stuff that’s – with the old information, then that is a really bad idea to … actually, cares about that. But you will probably have to configure it.
Okay, so let’s talk about dynamic data masking, because we will like this, because people think that’s a really great security feature. People actually present about that, until we figure out that somehow you can actually hack it really easily, so somewhere somehow, Microsoft says that’s not a security feature because that isn’t a security feature, that’s something to help you out, but that’s not an entire security feature.
So how do you do it? You just create a table, this is by column again so you just say okay, my first name is masked with a function that says okay, so I get to see the first character and then you have all Xs with the remaining ones and I don’t get to see any characters in the end. That’s what partial means. Default just adds Xs, email just looks like an email and you have some other fractions as well and this is the original information, and when you use dynamic data masking, it looks something like this.
So this has some sort of permissions, you need some permissions which says select is not enough, you need a new permission which is new in 2016, which is called the unmasked permission. So that unmasked permission complements the selects and you need that to be able to see the information. The problem is the unmasked permission is not by table, it’s not by column, it’s by database. So I’m not sure who’s bright idea that was, but it doesn’t make any sense, and that should tell you that’s not a security feature, that’s just something to make it simple for some people. So good things about this is if you don’t have the unmasked permission, and if you try to do backups with this, the backups go with all Xs like this one and so you’re not backing up any stuff whatsoever, so that’s really bad. Another way that’s actually good if you come up with dynamic data masking, is to prepare some information to send to the development team or something like that, because that masks all the information so that’s actually a good way to do it, although there are much better ways to do it, obviously. But I have done it like that because I’m lazy.
So be careful because backups, if you use these backups, you will have to have the unmasked permission as well. The backup user will have to have the unmasked permission, so there are hacks into this obviously.
So hacks are the best stuff to do and obviously the best example of unmasking for these is you know, Scooby Doo, so let’s try this. So you know the demos, you know the tables, all the same tables, dynamic data masking, so I’m just going to repeat all this to make sure the demo works correctly. The same table we saw before, still need to connect to it obviously. Okay, because I turned off the server and on again – so the table is here, so it’s all fine, no problems.
So I’m actually going to create an agents table and the agent table will have seven rows, which are the rows you know, Daniel Craig, David Niven, George, whatever, Sean Connery, whatever, so I’m going to need that later on, so I’m just going to add the dynamic data masking stuff and I’m going to say that specific column, which is called Bond actor, will have a partial mask, which says I’m going to show the first two characters, then I’m going to add the dot dot dot kind of thing that you see here, and then you have zero characters in the end, so that’s all you’re going to see.
Then I’m going to get grant select on everyone to be these tables because I’m just not going to care, I’m lazy in terms of security, which is basically the exact opposite of what I’m trying to show you here. Before I didn’t show because the demo didn’t need it before, but I actually created some users, so you have M, which is the woman or the guy, depending on the film, which is the boss, then you have Q, which is the porter master, which is the guy who does the gadgets, he has access to nothing whatsoever because he doesn’t know, and then you have one for each of the normal actors.
So let’s try this, I’m going to grant the unmask to M, and then I’m just going to see it as database owner, so I get to see everything, so it’s 26 rows down here, so it’s perfectly normal. If I try to execute it as M, I get to see the same thing because I just granted the unmask on that database to M. However, if I try to use it as Sean Connery, I will not be able to see that column which has Bond actor, so I only get to see SE, which I don’t know what it is. Obviously, it’s Sean Connery in the next one, but I don’t know what it is in terms of data.
Okay, so first exploit, which is funny enough, what am I going to do? I’m just going to say where that specific column like Roger, so as you can see only R and O are valid, the other ones I’m not supposed to know about, but apparently it said RO are valids. So I have seven rows here with Roger Moore, so Sean Connery is actually seeing Roger Moore’s rows, which is okay, but he was not supposed to see that. He was not supposed to understand that’s Roger Moore. So this could be Roger Moore, this could be Roger Rabbit, I’m not sure so far, but I need to try to dig into this a bit more.
So that is a problem, why is this a problem? So to contextualize this, I’m going to show you the query plan and if you see the query plan, this is – I’m not executing that user by the way, just as a DBO, so here you actually limit that with a WHERE clause like Roger Moore, something like that, and then dynamic data masking is actually done on this side, close to the selects. But I’m actually filtering this here, so I’m filtering that before it gets masked, so that’s basically the hack basically.
So it’s easy enough to do it, so let’s try this second one. So the second one is close enough, but – it doesn’t work because I included the execution plan, sorry about that, so let’s remove the execution plan, hopefully this works – let’s just un-revert this because that’s usually the problem with my demos because I execute – it comes up with an error and then I forget to revert, so make sure this works really well.
So basically what I’m seeing here is I’m going to do an inner join with that table, which had the actors before, and I’m going to join on the row that’s supposed to be encrypted. What I come up with is the new row from the other table, which actually says this one equals to this one, which is valid. So if you just do an inner join with a specific clause, table A equals to table B on a specific row, you can actually see the information.
So you have to get most of the information and the information from here is exactly the information from here. But I already guessed who the Bond actors were, so I guessed it, so that’s not valid if you have random information. That is actually perfect if you have an integer from one to a million, just get a table from one to a million and solve the problem from there, who cares, and you can actually join them and work it out. If it’s a date, get all possible days from 19,000 all the way up to now and just join them and we’ll get it to work, but strings are different.
So a few months ago, I was actually talking to Dimitri from About SQL Server, and he actually helped me with this demo. He showed me his demo, I did it a bit differently because his demo had a lot of stuff and I just simplified it, but his stuff is better clearly, but I simplified it just with some of the letters.
So I actually got a table here full of letters, so from A through Z and space, that’s the only letters I’m using, so that’s all I need. Dimitri’s one actually includes all the characters, which is clearly better, but we’re not going to need it so just use it like that. If you need some more, just use some more. And then actually inner join, character by character on that specific column, so you only have 14 inner joins here because that’s the amount of characters – the maximum amount I have, but I’m actually lazy but if you see here, you can actually see Sean Connery here, and then you can actually concatenate that if you want to, but I’m lazy.
So all the information is here, so basically even if you have a table with any number of characters on that specific column, it’s no problem, you can actually crack it. So that’s done, so easy enough to crack it. However, I’ve shown that in the execution plan, you can do that in the WHERE and in the FROM clause, but if you try to do that in the SELECT clause, that’s not going to work. And it actually shows you something a bit different, so it’s kind of the same demo but on the select clause.
So the problem is, it actually adds Xs, weird kind of Xs, not the same kind of dots we set out to do, so he actually figured out we’re trying to hack it and he decided to just add Xs. So you can do it in the where, you can do it in the from, you can’t do it in the select because that’s after dynamic data masking has been applied. Okay, so that’s the demo for dynamic data masking, I’m not sure if you guys have any questions?
Brent Ozar: Yes, there was – let’s see here. They said – Teddy says, “Could you use this technique to unmask dates using joins on dates or date tables?”
André Melancia: So I would say for dates it’s quite easy, just add possible dates to a table. If you include hours, minutes, seconds and stuff like that, then yes, the complexity increases but just multiply by 24 then multiply by 60, you can multiply by 60 and yes, you have a big, big table and yes, you can do that. The only thing you can’t do with this, and you might be able to, are floats because the same problem with float might actually be represented slightly different, so that might not work, but I would say in most cases it will. But that might not work in floats, but okay, but yes, in most cases this will work and this is the generic case, so if you try character by character, this actually works for varchar, nvarchar, and all another type like binary and stuff like that. It will work for that as well.
Brent Ozar: That’s it, there you go.
André Melancia: Okay, so SQL Server 2016, I mentioned this line here because all this stuff is actually from 2016 on all editions, so this is mainstream now. So people will use it and people will screw it up using it, so be careful with it. Okay, so low-level security also has bugs, so – and I know we’re over one hour, I hope that’s okay.
Brent Ozar: You’re okay, you have up to 90 minutes.
André Melancia: Okay, so let’s try this. So low-level insecurity, so always the same demos, still the same demos and I’m going to speed this up because I still have one other demo to prepare besides this one, so it’s always the same thing and I’m actually going to create – so running this again, so usual stuff – so the table is there, make sure it’s there, 26 rows, perfect.
So before doing low level security, I’m actually going to create a view and the criteria is going to be – it’s close to a SELECT star, basically it’s a SELECT star for all effects and I’m actually going to save the criterias – if I am that specific user, if I am Sean Connery, I will only be able to see Sean Connery’s missions. If I am Roger Moore, I won’t be able to see Roger Moore. Otherwise, if I’m M, I will be able to see every mission whatsoever. If I’m Q, I’m screwed, I don’t get to see anything.
Okay, so I’m just going to do that, grant select to public, and create that view. Then start to take a look at it, so if I am M, I’m going to get to see everything, which is fine. 26 rows, so down here, 26 rows. If I am Sean Connery, I only see seven rows, this is terrible, god save the queen – yes, so seven rows. If I’m Roger Moore, also seven rows, it happens.
So I’m still hoping to see Sean Connery in a new Bond film, it would be worth it. That’s my favorite Bond. So if I am Q, I’m not going to be able to see anything, so that’s fine, but this is still a view. Remember, I’m actually looking at the view thing, so with low-level security, I’m going to do exactly the same thing, I’m going to be lazy with permissions and all that, recommended stuff, create a schema just for the functions or predicates that you use and then you actually create a function that looks exactly like this, that takes on the agent fields, which is the field I’m going to verify.
So this is basically a function, it basically returns – if this is valid, it returns this thing. Otherwise it doesn’t return anything and for low level security to work, I’m going to have to apply a security policy to a table and I’m going to say I’m going to apply the security policy, I’m going to use that function which I created on this agent field here, which I’m going to supply as a parameter, and this is important – state equals to one, which sometimes people don’t realize that’s important.
So state equals to one and now I’m going to test the same thing but directly in the table. So as expected, M sees everything so 26 rows, no problems here, Sean Connery and Roger Moore will only be able to see seven. If Sean Connery ever sees me he’s going to kill me for doing this crappy voice that doesn’t look like his.
So you are Q, you are actually looking directly at the table but you don’t get to see anything, so that low-level security is actually good for that. That’s good for a number of scenarios including migration from really old tables and really old systems that need to see specific stuff, so you can actually use low-level security for that as well. Not as a security feature but as a legacy for the future as well, so let’s try this again, but removing the security policy, turning it off actually.
So if you are Q, then you have the table again, so the table is completely clean by now, so let’s try and hack this thing. So currently disabled, so if you are Q, and this is currently disabled – if you try to do this query, which I’m going to show, I’m just going to take film year and subtract 1969, number like any other, so if a film goes to 1969, that’s going to be zero and divide one by zero, that’s going to be an error, a divide by zero error.
So what’s going to happen is I’m going to see the first films, this is actually by chronological order, so 67 here, but that one you’re not going to see because that raised an error, but that’s without the security policy. Now, let’s turn on the security policy, and that’s solved. No problems whatsoever, so in terms of security, that’s completely solved by now. So that was a problem that you used to have with CTP1 or something for 2016, CTP2 kind of solved it, CTP2.0 or something like that. So that solved it so you think, they solved the problem, you can actually figure out that this column actually has 1969, so you can actually hack it, trying to get all possible combinations, yes you could. And you can actually do combinations like I did with dynamic data masking to try and solve that problem, yes, you can do that, but this is not a security feature anymore, is it? No, it isn’t or is it? So let’s try this again.
Now I’m going to do this with the WHERE clause, so it works so far here, so this one actually worked. No error was thrown, so the security policy is on. Now I’m going to try this but doing the same but in a WHERE clause and somehow the error still exists, which is bad. So I don’t get to see anything, which is a bit more secure than seeing some information and then not seeing anything at all. So it is a bit better, but still I get to see that an error was thrown with 1969, so I know 1969 is a valid value for that field, for at least one record.
So yes, it is possible to hack, it is possible for me to try all possible combinations and try to figure out what information is here. It doesn’t limit itself to numbers, you can actually maybe just try to compare that with characters, because characters are number as well, from zero to 255, so it is possible to try that, so it is a hacking possibility. So low-level security, that’s it. Any questions about this?
Brent Ozar: No, Daniel said that he just built a solution with this last weekend so he was worried about what you would see.
André Melancia: Yes, so I was actually confident about low-level security and the night before SQL Bets. I actually included the last demo, which I’m going to do afterwards and Miguel [inaudible], great guy that actually works in the UK now. We actually spent some of the time we should be partying in the disco party a little bit, we actually came up with the last demo and we figured out that low-level security was actually easily hacked like this and we came up with a few extras somewhere along the way, so thank you very much Miguel, that was really important.
Also thank you Dimitri, which helped me with the dynamic data masking as well. That’s also important. Okay, so let’s try to go ahead and this is the last demo I would say, and yes, this is weird because everybody knows the guy. Okay, so the last demo is actually bit different, so people have done this before, so I’ve seen people like Paul White do this before 2011 or something like that, and Paul Randall has actually done this as well, has actually shown a lot of information like this as well.
Me and Miguel, we actually came up with it in a different way but we still figured out something slightly different than Paul White but it’s worth mentioning that Paul White, Paul Randall actually did stuff in this area here, so it’s worth mentioning. Brent, you probably did stuff like this as well, so let’s see this.
So try the exact same thing again, so just clear the tables, create a new table, so the table itself is irrelevant, I’m not going to care about the table contents, it’s just a table like any other. By the way, some links here for you to look at, interesting links if you want to look at them.
So one thing I’m going to do is I’m going to grant select on every one just because it’s simpler for me and if you guys are at home, you can actually take this piece of code and you have to create two new connections, so I’m going to create two new connections, and I’m actually running this. This is a different connection – so usual stuff, how many times have we done this? Okay, so I’m going to execute this Roger Moore, I’m actually going to – what am I going to do? I’m just going to take SELECT star from that table, put that into a temporary table called the hack table and limit that to his own stuff like Roger Moore stuff and this is done already, so I’m actually going to do this for Sean Connery, so I’m first going to connect so I don’t screw this up because I have to change it that way.
Sometimes this actually works because I forget in the database the same information is there as well, but it’s not for the demo. Not this demo in particular, so if I execute this to Sean Connery, I also have a table with hack table stuff, so the table is called hack table. So now I have two hacked tables, which are okay, they coexist together in the tempdb stuff, no problems there whatsoever, so if I’m the database owner and I try to see that table, I get nothing because the table doesn’t exist. It only exists for the other two connections. So – and by the way, this doesn’t have to be Sean Connery or Roger Moore, I just put them just to be different but technically it’s by connection only, so the rest is irrelevant.
Okay, so let’s try to hack this. So if I’m Q, I’m going to try to run this so I’m not going to get a lot into this, but if you go to sys.objects and try to get stuff like that says hack, if you go to sys.partitions, if you go to all columns, and I’m just going to run this as Q, so remember, Q is just the user I just created, has no permissions whatsoever, so normal user. So if – I might actually be able to see this stuff, so what do I see now?
So I see two hacked tables, only two, could be a thousand, depending on the number of users that you have. They are different in terms of name, so this is sequential numbering, so usually this stuff is bigger but this is sequential, but they have an ID here, so this is the ID that’s important for you to go all the way up to the next information. So you have object ID here as well, so you have two tables again, one has seven rows, which was the Roger Moore stuff, and the Sean Connery actually wanted it all, so he has 26 rows. So, so far a user which is not supposed to have any information whatsoever already knows about this for the other users, so this is important. Then he actually knows those tables actually have these columns here for the first table, and for the second table actually the same columns, which is dated the same, but it could be different. So they’ll know exactly what type of – what kind of – if this is an integer, if this is a character stuff, what’s the maximum length, all that stuff, so he has this information.
This is not enough to hack it, to know everything about it, but it’s enough to infer some stuff. So you can actually figure out that some stuff is valid from that, so be careful with that because any user can actually see this.
So let’s move on. So you need a bit more privileges for this, I haven’t explored this that much to be able to figure out exactly what the right privilege is for this, but at least if you are a system administrator who can actually see this. But at some point in time I will explore exactly what privileges you need in terms of detail to figure out this.
So there is something undocumented, just go to Paul Randall’s link that I included there, that has all the information on these undocumented DBCC stuff, so there is DBCC stuff that said DBCC ins, where you should just say tempdb, which is the database. You can include the ID for table that you want, and in this case I’m just going to do a top one because I have to do one table at a time, either one is enough. They are kind of the same for the demo purposes, and I’m actually going to get one of the IDs and I’m going to say minus one.
In this case, I’m not exactly sure what minus one is, I don’t really remember, but Paul Randall’s post actually says that, so I’m actually going to run this – and by the way, I’m going to run this with table results, so I’m going to run this into this table here, which I created. This is not all billion fields, I just said this is nvarchar MAX, so the rest is BIGINT, so I just don’t care, I just want a table where I can fit all the information without having to worry about what specific data types its field is. I just don’t care.
So I’m just going to run this, and this is the table I came up with. So I’m using the table with that specific ID to look into that and then it actually comes up with three rows. This is the page file ID, so this is the file number for the file one of the information stored in tempdb and this is the page number inside that file. All the other information I’m not going to talk about that because it’s not very relevant for this, but this information so far is. So I’m going to take this information here and then I’m going to use DBCC trace on and 3604 is a trace flag where you can actually turn some of the DBCC results that actually go to the logs, instead of going to the logs you can actually see them on the screen.
So there is a lot of information for this, actually [inaudible] told me about this, so thank you very much for this information as well. And we’re going to get a cursor that goes all the way up to the three records, this could be much more because the table is actually quite small. This could actually be more, so I’m going to go through all these records and see the information.
So I’m going to see three sets of records, so the first one isn’t that interesting, so not interesting stuff, although you can see – you can actually have stuff here that might be hackable for some other purpose. So this is the information that you can actually see on the – sometimes on the hard disk. So the second one isn’t that interesting as well, some interesting stuff but not that interesting, but the really interesting one is the last one.
Be careful, when you do this demo this might not be the last one for you. It is the last one for me, it might not be the last one for you. So I’m just going to add some more stuff here, some size here, this seems to be difficult – okay, so I’m just going to go up and you start to see some information and this is not good. But suddenly you see some bits and bytes and hexadecimal dump that says live and let die, Roger Moore.
So we’re beginning to see information that’s actually on the table, but the information here is kind of raw, it’s ugly to see it. So wouldn’t it be nice for us to have a way to get the information out? Yes, just move on to the next records. The next record actually described that by ID, by field, so film ID nine, film title something, film year something, so you have all the information here. It actually says physically where that is stored in this information here, so slot zero, column one, column two, whatever.
It actually says where it is, but you actually see the record. So if you move on to the next one, you have more hexadecimal stuff, the dumps, then you see the second records. Man with the golden gun, whatever, so remember we’re actually using the Roger Moore table, so this actually shows only Roger Moore films. So yes, that is a way for you to hack it. You can actually hack temp tables.
So what is the recommendation for this? The recommendation for this is I’m not going to say not use temp tables, you should obviously, because some of the things actually need you to use temp tables, but if you have critical information in temp tables, do not use temp tables. If they are small enough, do not use temp tables, just put that into a table variable, which is stored in memory, not going to be stored in disk.
Brent Ozar: I was going to say, make sure you’re talking to small enough because as soon as people put any sizable rows in there.
André Melancia: Exactly, so if it’s small enough, just put it as a variable, if it starts to get bigger, use shrewds, but you’re not supposed to have that much sensitive information in tempdb, in the temp tables.
But if you are, you have to be substituted some other way. So it is important for you to know that this can happen in terms of security, and then you decide what you want to do. So it’s better to know than just do so it’s safe, it’s not. Okay, so in terms of this one, I think that’s it, and I’m not sure if we have any questions for this one.
Brent Ozar: Yes, if you want – Serge says, “What should I do about transactional replication? I have transactional replication, how should I secure encrypt to that?”
André Melancia: That is actually a good question. Actually, that’s a scenario I haven’t thought about.
Brent Ozar: Yes, by default usually all the data is unencrypted in your distributor and all that, wherever you’re replicating it to.
André Melancia: Actually there is something I’ve been meaning to try and figure out what exactly happens there, so I still don’t have a suggestion for that but that is a good point. I will try to come up with something and see if I do some – add some demos to try to hack it or something like that, and maybe do some sort of recommendation on what’s the best practice on – for you to secure that. So currently sorry, I have no information at the moment.
Brent Ozar: Gotcha, okay cool. I think that’s it. Alright, cool.
André Melancia: Okay, so maybe talk about one other stuff, so the demos are out, I have no more demos, just a few slides. So SQL injection, this is here in the session just because of one thing, because if you are a DBA, you might be screwed by the developer guy, so I am a developer, I am a DBA, I am a sys admin, I do everything, so I always get screwed by myself. But if you are just a DBA, you have to be careful about developer guys that aren’t you. So you know about SQL injection, so if you have information here, you can actually add to the string where – the URL string, you can actually add some information here like a drop table or some sort of other information – zero equals to zero, to get more information like on a log in form or something like that.
So that is a problem, how do you solve it? You just force your developers and you insist on your developers to use a prepared statement. So that’s easy enough, just use prepared statements that actually forces you to use a specific data type on each of the parameters, so that makes sure that this does not happen to you.
So I’m not going to talk a lot about SQL injection, most of you guys already know about this, this is just worth mentioning because sometimes we just forget about it, but not a big information about that that I have, no demos about that.
Okay, so just to add some other stuff, so permissions, all other stuff. There was a guy called [Rick inaudible] at Microsoft, really great guy, he actually did this for SQL Server 2014 and 2016, so if you zoom this, I’m not going to do that because this is a really low resolution image, this is actually PDFs for this and I will make one available as well. So if you look at where I have my mouse here, this is the small stuff where it says select permission, insert permission, update and delete permissions.
They’re all here in this small square. Everything else like on the left there are like server permissions like sys.admin and stuff like that, and other permissions here as well, so it is good for you to know all the permissions that exist in SQL Server. There is also a version, 2014 and 2016 version of this browser. Maybe some other version before, I’m not sure, but they are slightly different but in most cases they’re kind of the same.
So take a look at them, study them, figure out what exists in terms of permissions because that might be important for you to secure your installation. Okay, so we’re about to finish, I’m just going to say one or two conclusions then add some – any questions that anyone has.
So always encrypt the traffic, obviously, the first demo would not be possible if traffic was encrypted, that makes perfect sense. I have done that which was to expose ports – it wasn’t 1423, it was something else in Azure, just for the first part of the first demo, just don’t do that. Use a VPN, use something else, just do not expose ports to the internet because obviously, that was the SQL slammer in 2003. That completely screwed up the entire internet not because there were a lot of SQL Servers, but because the SQL Servers that got infected, they were online and then they sent out a whole number of packets that completely screwed up the routers.
So that was a really big problem with them, and there was a patch for SQL slammer six months before that attack and no one installed it, no one really cared, so that is important to install and obviously you wouldn’t be able to do – create a user which was a sys admin if you weren’t a sys admin as well. So least amount of permissions possible, like if you have WordPress or something like that, it requires you to have permissions to create tables and stuff like that when you’re creating installation. This is usually on MySQL but it’s kind of the same problem, so you can actually say that, just use the system administrator, create whatever you want then lower that to the minimum possible that you have, because you’re lazy, I’m lazy, we’re all lazy.
So just do it the lazy way but then make sure you secure it afterwards. After it’s installed, secure it as well. So SQL injection, be careful, used prepared statements obviously, and then finally they are all perfect – the features are all perfect but you should install the service packs – that’s the biggest security problem you have of all, so install them, and finally if you have any questions I am here for you guys.
Brent Ozar: Wonderful, I think every – several people said they’re really excited about it and thank you very much. Kyle says, “Definitely very interesting demos and is in his top three from this GroupBy, but now that it’s 5:30 on a Friday he has to crack open a cold one.” If anybody’s got questions feel free to ask in Slack or in GoToWebinar, although I know a lot of folks are pooped out at the end of the day. Looks like things are coming to a close. I wanted to ask you, how did you get started working on this stuff?
André Melancia: I actually tried this in the year 2000, we had to do some gate waits to pass some information, so I got to see it and last year I actually got to try this at SQL Saturday here in Lisbon because I had to change my session and I talked to Andre Batista and I said, I can actually try this and I did the first demo in four hours, so funny enough, the crazy stuff is this still is a thing. This still is a security issue so kept it up.
Brent Ozar: Martin says, “Will the scripts be available?”
André Melancia: Yes, so I promise you guys this weekend I will make – I will put a link on my website. The website is just perfect, it’s just a single page with my contacts, but I will put a button there which says link to presentations, and I will make that presentation and some other older presentations available. So I will do this presentation in the next month and a half in some places, mostly in Kiev and I’ll be in other places as well doing different presentations, so I’ll make all these presentations there as well, but always look for the most recent presentation because that usually is the presentation you need. I will make the other ones available as well but the most recent one is always most correct one.
Brent Ozar: Perfect, that’s it. Well, thank you so much, Andy for presenting here at GroupBy. Fantastic session, lots of people are excited, and thanks everybody for hanging out with us today and have a great weekend everybody.
André Melancia: So Brent, thank you very much for hosting GroupBy, it’s a really amazing initiative. Thank you very much to you, thank you very much for Antonio Juarez who has helped me out in technical terms here at Microsoft, thank you very much to Microsoft here in Lisbon as well for letting me use the room all the stuff that I used and finally thank you very much for you guys that watched this presentation. Talk to me at Slack, talk to me anywhere, email whatever and I’ll be there for you guys and we’ll see you around. Thank you very much.