I chat with Microsoft MVP Chris Bell about database security and some seemingly cavalier attitudes some organizations have about keeping data safe. We hit some of the methods SQL Server has to protect your data. Chris also unveils his security analyzer you can use in your environment to see what areas might need to be addressed.
Show Notes
Chris on Twitter
Security Compliance Check Script
Getting Alerted when an account is created
SQLCruise
*Untranscribed introductory portion*
Carlos L. Chacon: I have with me, Chris Bell, who is the owner of WaterOx Consulting that he started in 2012. He spent eighteen years in various roles in the public and private companies, working with data.
In 2012 he was a finalist for The Exceptional DVA of the Year award, and is coordinating the Washington, DC Pass events. He was recognized as a Microsoft MVP in 2014. Chris, welcome to the program.
Chris Bell: Thanks for having me.
Carlos: Fill in any holes for us. What did I miss? Is there something maybe of a more personal nature that you want to share? ton
Chris: Boy, what did you miss? Let’s see. As you said, WaterOx Consulting, that’s my consulting company, that’s me. We also have the SQL Saturday in Washington, DC coming up in December.
Carlos: If you are in the DC area and need a good technical or SQL Server related event to go to, definitely check out the SQL Saturday in DC in December.
Chris: We have a lot of fun with it too. We try to push the boundaries every time of what we do and how we have it. Make it a fun event bringing speakers in from all over the world actually.
Carlos: I have been up to that event a couple of times, and I know that people will find it very interesting. Definitely an opportunity to learn something. Chris, today we want…[crosstalk][laughter]
Chris: [inaudible 00:35].
Carlos: Yes. Today we want to talk a little bit about security. I wanted to have you on the show today, Chris, because I go to you ultimately with a lot of my security questions. By my count…[crosstalk][laughter]
Carlos: …By my count, you’ve gotten over 25 sessions on security at different events, and many of your blog posts are centered around security. One of the things I know that you are working on and will soon be released, and that is your Compliance Audit Check Program. You have this Compliance Audit Check Program that you’d like to make available for free. Right?
Chris: Yeah, absolutely.
Carlos: For those who are looking to understand a little bit better about how their environments might or might not be in compliance, there is going to be a number of checks that are available. Again, this is somewhat still in development, but he will be looking for some people to give some feedback on.In the show notes today, we’ll make sure that everyone has the link to the current Compliance Audit Check that’s available. You are welcome to check that out, give Chris some information or some feedback on how it’s being used, and potentially maybe what you’d like to see in there.
Chris: Definitely what you would like to see. I’m going to hijack this real quick then.
Carlos: Go ahead.
Chris: The concept of this, it’s one of the things that a lot of people forget about compliance, or forget about security, it’s kind of a bad thought. It’s one of those gaps in the industry. Everyone is going like, “Oh, we make it all run fast and works well. Look at the pretty colors. Security? Oh, we’ll wait about that. That is not the concern right now.”Compliance as well, compliance is a big deal. Look at all the data breaches that has been happening, but then the other problem of that are some of the systems are complying, just compliance rules aren’t tight enough.
I think it’s really important to know where you stand when it comes to something like compliance. Even if it’s not required of you or your company to be compliant, it’s worth knowing where you stand on that playing field.
I know that my podcast, [inaudible 3:47] , if anybody wants to go listen to that one. On mine, I talked with Sean McCullen of MidnightDBAs. I do, in my podcast, try to bring up when I do interviewing folks like this, talking about the lackadaisical treatment of security breaches and how it happens.
He had a great line when, “It’s not, if it’s happens to you, it’s when it happens to you.” I’d say that I was going to go out, and I looked at different tools that are available and they range anywhere from 600 to 3,000 or 4,000 dollars, per SQL Server instance.
Carlos: Very expensive.
Chris: It gets really expensive just to do whether a bunch of simple checks that you can really go on and just do yourself. When I say a bunch of, I mean literally hundreds if not thousands of checks being done.I thought, “It would be kind of neat, like [inaudible 4:40] blitz, and some other folks do different checks, that do these kind of things. Let’s create something like that but for compliance and security checks.”
I started working on that, piecing together, do want to get that released. Hopefully, I’ll get that out sooner than later. It’s one of those, “Oh, that is so simple,” until you actually sit down to right one of this tools and make it work right. I am writing it all in just T-SQL script to make it easier for anybody who wants it to see or wants to contribute, it’s very simple how it’s done.
No special component, dot net or DLL or [inaudible 5:16] , none of that kind of stuff, just written plain T-SQL. A lot of places should be able to use it as well. It’s kind of a hope. Security is important. Why should you have to pay out the notes, to know where you are in this playing field, to make sure you are compliant or you are secure enough.
At least your system isn’t sitting up there fully exposed, to be grabbed and everything taken and walked away.
Carlos: You mentioned that sentiment and there does seem to be kind of a lackadaisical approach about security, about compliance. Almost a bit of an afterthought. I think the standard operating procedure, if you will, is to give user a little bit of permission.If they get in there, you bump up the permission. [laughs] Maybe make them an administrator certainly in you database environment, maybe even on the box. Maybe there’s that mentality there of, “Well, my active directory will require them to change their password every X number of days, or has to be a certain security complexity level.”
Chris: We all know that “welcome dot one,” becomes “welcome dot two…”
Carlos: [laughs]
Chris: …then 3 and 4, so you just figure out how many months a person’s been there and you probably know their password. Right?
Carlos: Right. I have not tried that. You’ve just given me a little idea there, Chris. Oh boy.[laughter]
Carlos: There’s that idea that maybe we don’t pay attention to it as much. I think a little bit of it is, it requires some planning to understand what level of access, from an application perspective, our users need.The being able to grant that to them. Unfortunately, I think another component there is we may give a little too much. We may rely a little too heavily on our third-party vendors to implement some of that security, so my pools…
Chris: Let’s never assume that the third-party tools actually test the security. Unless it’s actually a security device for testing security. Even then, don’t trust them. You can’t. If you look at it anybody out there that’s working with software development.What’s a higher priority? Hit the release date, or make sure it’s secure? It’s hit the release date. Security takes the backseat, kind of like, “Hey, no one’s going to dig into it.”
Odds are, which is true, nothing is going to happen, but if you leave it wide open, you deserve what you get.
Carlos: My experience is my pool of third party apps. I would probably say maybe about a third of them have some basic security implementations. In the form of, I create a role, I create a user, I assign permissions to that role, based on what I’m able to do.The updates or deletes or things like that. Users based on what their function is at the organization are then placed in a role. Even some things simple as that. I don’t see a lot of organizations doing that.
Chris: It’s much easier to create one user idea and one password that everybody can use. Because when you set the access once maintain once. A lot of applications do that. You may log into the application, but in the back end it’s got a standard SQL log.It’s using same one for everybody connecting to the database. Makes it really hard to track down who’s doing what, but it doesn’t matter because the application works.
Carlos: In that instance hopefully, I guess you’re relying on the application then to do some of the auditing, as far as who is doing what.
Chris: One would assume. [laughs]
Carlos: Even from the management side, there was an organization that you and I were trying to do some work with. We had some initial discussions with them, presented some information to those responsible for compliance.It’s very interesting that ultimately the compliance officer, the words actually came out of his mouth and he said, “Well, we have insurance if there’s a breach.” I’m like, “Really?”
Chris: It’s just got to be somebody else’s problem.
Carlos: You had some interesting numbers about…All of a sudden I’m drawing a blank on that, health insurance company that was breached.
Chris: The whole..Now you made me forget it.
Carlos: [laughs]
Chris: …Empire Blue Cross Blue Shield, Anthem.
Carlos: Anthem…
Chris: They’re all under same parent. Anthem. I was just throwing the ideas around. They had a breach. It was about 80 million customers had their information compromised. We’re not going to say “stolen” or “taken.” “Compromised” is the word they use.Basically it means they walked out the door, somewhere, and somebody’s got it. Their quote “immediate response,” which I received three months later in the mail, was, “We’re going to buy every single one of you, we’re going to automatically sign you up for credit monitoring and personal information monitoring.”
“Someone stole your Id, someone stole your credit card information, we’re going to pay for all that, we’re going to give you that free of charge for a year.”
Because we all know our data is only going to be used for 12 months. What kind of got me with that is, if you sit down and think of the cost, even if they’ve got a really big discount let’s say a dollar per person per item. You’re looking at two different products that are being given here. We are even getting less than that, but let’s stick with the dollar because it’s an easy number to calculate.
That’s $160 million that the insurance company most likely paid out. Not the health insurance company, but the actual company that they have for the data breach insurance.
They’re paying $160 million to buy this stuff to give to everybody, when there are steps that could have been taken in-house for far, far less monetary involvement to have secured things a little bit better.
What happened there was a little bit different where, from what I understand, someone got a hold of a DBA password. That comes down to, “Whoa, how do you deal with that?” Because the DBA has access to everything but the DBA shouldn’t have access to everything. DBA should have access to what they need to do and there should be other steps that could be put in place.
I just think that wasn’t looked into fully. As they say, “Our sophisticated security system.” Sophisticated now that someone got the DBA password, got in, and did everything they want to do. A little bit tricky there.
That’s one of those cases where you got up to an executive level or whatever, sat there and read that little report given to them that said, “We have a highly sophisticated system because we spent lot’s of money on it ten years ago…”
Carlos: [laughs]
Chris: “Nothing’s changed since then,” or whatever it was. There’s this kind of thing. You can try blame management for a lot of stuff, when generally the stuff trickles up that case of, “Yeah, yeah. It’s all OK.”
Carlos: We’re data professionals. Folks listening to this are interested in data, managing their data. What would be some good first steps that they could take to help implement some security, or to review potentially their environments?
Chris: The second thing that’s potentially important, review. To know what’s really good, know what’s going on. Who can access what? Do you need to be compliant? Are there other regulations you have to follow? Know these things first. If you know these, then at least you can start to make a plan of what to do.Security enact is kind of a touchy subject. It’s one of the reasons a couple of the talks that I do don’t have demo’s, because if I show you how to do something odds, are you’re going to try to do that right away. You may have to be compliant somewhere and if you this you break things, you break compliance.
Some of your company loses certifications, or whatever it is. You really have to know what you’re doing, and what’s available, and figure out what path you have to go down to get things secured properly.
Carlos: That was my thought is taking time to figure out what is needed. We’ve all been there, where we’re trying to set up a new application, third party. They’ve come in, and you’re just trying to give them reader-writer rights, for example, while they get in there. Then you escalate their permission, or they say, “We need administrative privileges on the box…”
Chris: Being installed as SA.
Carlos: That’s right. I think even asking the question, Can you help me understand what it is your system needs, from the elevated privilege account. So that I can potentially assist you in a different way, or at least that I’m aware as a person who will be responsible for maintaining the application, and know more about the system.
Chris: Yes, absolutely. I always sit there when I go somewhere and someone has got an application [inaudible 15:22] “Oh, it needs SA account.” I was like, “Why?” that’s what they say. My response is, “No, I am a DBA,” that’s what we say. Deny everything. It’s this thing where it comes in and people are like, “No, we need this application, we should do this.”You want give this application, and this third party, whatever they write, permission to control the entire security server including creating logins, changing security, doing stuff with back ups all that. You want to give them that full form of access to the server. If it’s the only thing on the server, fine. Who cares?
Or you’re running only one application, on one database, one instance of SQL Server, probably not how it’s set up. Not nowadays with virtualization, you’re probably going to say, “I got this massive SQL Server and it’s running 50 databases on it for all these little applications.”
These applications usually don’t run into terabytes in size. They’re 10, 20, 30, 40 gig, and maybe up to 80, 90 and that kind of thing.
It gets tricky in there knowing what has to be done. That goes back to when you’re in a shop that’s trying to get applications out, and you’ve got deadlines and time schedules you’ve got to hit. Security, if you just open it wide up, “Oh wow. Look, it works. I don’t have to worry about whatever I wrote. Look, it does it in the data base.”
Sure it has unbridled, unrestricted access to everything in the system. A lot of these third party, or even in-house, you skip that step of, “Well we’ll just run as essay because it does what it needs to do. We don’t have to spend time and effort now to lock it down correctly and prevent it from doing excessive things in the system.”
Carlos: In your recent blog post, you talk a little bit about a way that data professionals can know when accounts get created in their environments.
Chris: Now I did, cool.
Carlos: You did, and you provide an example of a server-level trigger, but I think that concept of knowing if someone else who you’ve potentially given access to maybe your vendor. Maybe someone else maybe some other person in your organization, and then they create a user and assign permissions. This is that idea of knowing your environment.
Chris: Yes. Be aware of what’s going on. Dealing with knowing what’s going on with your SQL Server being DBA, or not being a DBA. It doesn’t matter. If you’re dealing with SQL Server at all, it’s an interactive experience. Just like driving is. Believe it or not, driving is an interactive experience.It’s not just sit there, set cruise control, grab your phone, check up on Facebook or Twitter. Check Twitter see if it’ll help, reply to everybody. No, don’t do that stuff. It’s an interactive experience. Be aware of what’s going on around you.
In your database, things like having this, “Trigger, password crazy, triggers are bad.” No, they can be good if you know what they’re doing, and they’re set up properly.
Use the tools that you’ve got, get other tools if you need to, but ideally use the ones you’ve got, learn what they can do. Keep track of things like, “Look, I have a new user suddenly. There’s suddenly somebody with new permission.”
“Oh, somebody changed one of the store procedures in the system. That didn’t go through out process. Why did that change?” These types of things like that, be aware of what’s going on. That’s like knowing there’s a semi barreling down on your right side. Maybe you shouldn’t change lanes right then.
Pay attention to what’s going on. It may not be right here within the 10 feet of your car what’s going on, but know what’s coming up. Don’t look at the 20 feet in front of your car, at only the bumper in front of you.
Look down the road, as far as you can, and see what’s going on. It’s almost a proactive thing. [laughs] Be a little proactive with what you’re doing and know what’s going on, because as it was said all through the ’80s by GI Joe, “Knowledge is power.” Understand what’s going on.
Carlos: GI Joe can’t be wrong, right?
Chris: No, absolutely not. Knowledge is power.
Carlos: That’s right. We actually make that a part of our server [inaudible 19:46] process now, adding that server-level trigger, for when new accounts get created. It sends us an email, and luckily it doesn’t happen all the time, but it has from time to time. I get an email and you’re like, “What in the world is going on?”
Chris: Yeah, not only that but, “Who the heck made the account?” Because if you’re the DBA, and you’re responsible for doing these types of things, it may come down to the whole, “Wait, how did somebody else make an account? Oh, that application. It has SA access. It can create your account. Somebody found this little plug-in for it and now they’re creating accounts.”
Carlos: Exactly. We’ve talked a little bit about right access to systems, right knowing that information. One of the concepts that is emerging as security or compliance, is this concept of data at rest. So when we talk about data at rest, what does that mean?
Chris: It means on the drive.
Carlos: On the hard drive?
Chris: [laughs] Yes, sorry to be blunt, but that’s what it is. Data at rest is when it’s sitting still, which is on the drive.
Carlos: What tools are available for us to be protecting that data at rest?
Chris: If we’re talking SQL Servers…We can talk generically, encrypting the drive. That’s a base level. It may already be done. It works great until someone logs into the server and can just copy everything off the drive, because they can see the encrypted drive. [laughs]When you get into SQL Server, you start to have some options there like “transparent data encryption” is a great one. It does take a little bit of resources, because you’re asking SQL Server to think more.
Once you add in encryption, or hashing, or anything like that of any type with SQL Server, the CPU process is going to go up. It has to. It has to think now. Well, has to think more. Nothing comes for free.
Transparent encryption data works nicely because you get a very simple setup, get your certificate and keys set up real quick, turn it on. System in behind the scenes goes to what’s on the hard drive and encrypts the entire data file itself. Then it encrypts log files.
It also encrypts tempdb. You have to kind of watch for that. If you have a really stressed tempdb and decide I’m going to turn on TDE, you might start to see some performance issues with that. It also encrypts the backups automatically, so that’s another way the data sits at rest is your backup.
The data’s not moving around, it’s not doing anything, it’s just sitting there at rest. This also puts a level of encryption on the backup.
What does that gain for you, how does that help protect? If somebody grabs your data file, your log file, your .mdf, .ndf, or your .ldf, or your backup, or your transaction logs. They can’t do anything with it unless they have the certificate and the keys that you set up initially.
Which is one of the reasons that if you do set up TDE, don’t store the keys and certificates with the backups.
Carlos: [laughs]
Chris: Put them somewhere separate. Off the machine too. Get them off the machine.
Carlos: The number one rule of starting to set up encryption in SQL Server, don’t lose the keys.
Chris: Don’t lose the keys. Actually, as soon as you’ve done anything that requires any kind of encryption, a couple of keys are automatically created in SQL Server. You want to make sure you get those backed up.Because if you have to rebuild the server, it’s really nice to go to a top level and say, “Yeah, I’m going to restore my service master key, and once I do that, all my other keys trickle down and it’s OK. Because I’ve recreated my encrypted environment.” There’s some really interesting diagrams out there.
People can Google for it, for Microsoft SQL Server encryption levels and keys and that. It’s like a bull’s eye with different lines cutting through it, is the one that I like, but you’ve got three or four, possibly even more key levels that can work their way down through.
All down to finally getting through to your data in SQL Server. On top of that, there’s also what’s on the server itself, which is a couple of layers of security.
Then your network, and the security on top of your network. Security isn’t one of those, “I’m going to learn it in 10 minutes” things. It gets intimidating at times, too. I think that’s one of the other reasons it doesn’t get implemented a lot, because a lot of people…That’s not their focus. It is intimidating just how much is there.
Carlos: Sure, in fact I had another colleague of mine, we were talking about encryption and that was his recommendation. He said, “Look, my recommendation is get somebody who does security on a regular basis because it’s something you don’t want to mess up.” [laughs]
Chris: Yeah, it’s very easy to go and flip a switch, and suddenly nobody can do anything. Or flip the switch and everybody can do everything. I think there’s a fear factor in there with a lot of people. It’s like, “Wait, wait, now you’re telling me I should protect stuff, but if I mess up, I can’t restore any of it. I don’t like this. I’m not going to do it. It’s too much of a risk.”
Carlos: It might be what you call an RGE event, right?
Chris: Isn’t it Grant that came up with that one?
Carlos: I don’t know.
Chris: I think it was Grant. That’s awesome.
Carlos: Resume Generating Event.
Chris: Yes, RGEs. There’s a lot of those. [laughs]
Carlos: We’ve talked about database encryption at the whole database level, the instance level, right? We can also encrypt at the column level, or table level. There may be reasons or models that we would want to do that instead of encrypting everything.
Chris: Absolutely, there’s cases that you want to get down more…Security gets very granular. Even now, with 2016, with some of the stuff with the row access they’re talking about adding in. Which we’ve had ways to do, just kind of clunky to work around it, but there are ways to do it. You can restrict a specific value in a cell on a single column on a single row.You can restrict access to that to certain people. It gets that granular. We can say, “We can protect the whole thing. The whole server and TDE and lock everything down, but we can also go down to this specific table, this specific value in this specific row, and lock that down, too.”
You can get very granular in there. There’s multiple ways to do that. There’s certificates, there’s keys, there’s passwords. Again, that chart shows all the different levels of security you can put in depending on how tight you want to get with that.
Cases where you might want to do that, let’s say we are using TDE, because TDE is at rest. The thing with transparent data encryption is that first word, “transparent.” If you have access to the database, you see everything in it, plain text. Just as it’s stored.
Maybe you’re working somewhere, you’ve got TDE so nobody can grab your data files, you’ve locked all your backups. They can’t grab those and just throw them on some other server somewhere. Great, but if they can log into your server, like Anthem had the DBA, you could just log in. You get into that and you got TDE in place, everything looks just normal.
Why not write a couple queries, dump all the stuff out, and there you go. You can walk away with it. It didn’t matter that you had TDE. You’ve got to start looking at specific information to be protected inside that table or that database. Let’s say, social security numbers, a real common thing that’s taken. Credit card numbers.
Those you can go down to the point of putting encryption on that column, and only giving keys to those that need access to that information. Even if somebody logged in, transparent data encryption is there, they can log in. They can see everything plain text, but they don’t have the key to this column, that column is now scrambled, they can’t understand it.
Carlos: Today, we want to leave you with the impression that there is no one-size-fits-all for security.
Chris: No, there absolutely isn’t.
Carlos: It will depend on your environment, but there are options out there for you. There are some very smart people working on this.
Chris: There’s no silver bullet, is how I tend to put it. No magic button. Click, I’m secure. It just doesn’t happen. I see that there’s four different components dealing with security with SQL Server. The first one is the actual communication, so SSL, TLS.When I’m sending stuff across the wire, where I actually sniff the packets on the network and show you everything plain text traveling around, even from a TDE system. Even from an encrypted column, I show you plain text is moving around. How do you protect the connection?
How do you protect the data at rest is part two when we get in there, and not even at rest, but even in that column in there. You got your connection, you got the database you’re protecting, getting down to the cell level, with the column encryptions we talked about.
Get down to that detail level. I’ve protected my data streaming, I’ve protected my data at rest. I’ve protected my sensitive data in the database. A forth level that’s often forgotten about, and I think it’s going to start to play a bigger role in here, is extensible key management, EKM.
That’s basically an outside source, outside of SQL Server source, that generates our keys and our certificates, and relays them to us. When you look at things like Anthem, someone got the DBA password. Oh, no, they can get at everything. Including the keys that are generated and stored in SQL Servers, the DBA. You’ve got access to all of them.
You can make backups and restores and if you can figure out the password. Or there are some ways you can work around it, but If I’m using extensible key management where this other piece of software or hardware is generating my keys.
I could do things like say if the DBA comes in remotely from a non-approved IP address, I don’t recognize where they’re coming from, they can’t access the EKM system. Which means they can’t have the keys.
Even as a DBA, if someone took your account and got in there, they still can’t decrypt those columns with that sensitive information.
It’s those steps like that, when you start combining these four components together. The streaming, the at rest, the in the database and, who can get to my keys and how they get to my keys . Once you get those four in there, you start to get a pretty tight security system in there.
Carlos: Thanks for the conversation today, Chris. I think this is going to wrap up our security portion. We’ve covered some great information. In trying to provide a little bit of value to folks, get some ideas on what other folks are doing.What’s your favorite SQL tool? It can be paid or free, but something that you’re using, and why you like it.
Chris: The ones that I really like are the Redgate suite of tools. That’s not just because I’m a friend of Redgate. I’m a friend of Redgate because I love the tools. Prompt, SQL Prompt, is fantastic. If you’re not using that, go fight to get it, because it makes life so much easier for writing your code and anything that you’re doing.
Carlos: Is there a certain memory or experience that…
Chris: Favorite DBA story-type thing. Boy, those are always hard. It’s hard to narrow those down. When I went independent, when I separated off from the corporate world and went independent and had that first client.I was actually contacted by an online retail company that had new product coming out and they were expecting for 100 times the amount of traffic hitting their system. Not a hundred percent growth. A hundred times the growth.
It was one of those, when you talk to him, he was like, “OK 100 percent no, no, no. We didn’t say percent. A hundred times the traffic. If we had a thousand people coming per day, we’re now going to have 100,000 people.” I was like, “Wow, OK.”
They basically said, “Well, we want to make sure our system can handle it.” Let’s get in one of those things, the first thing you’d step into, is like, OK.” We just certainly looked at that. I think they were out in Vegas, so I got to go spend a week in Vegas on their time.
I was actually able to get that together, make it happen. They’ve been going three years now. They haven’t had a single glitch at all.
Carlos: We’re here at our last question. Before we do that, let’s hear about another opportunity folks can take advantage of. Thanks Chris for the discussion today, I hope that folks have found it valuable, I know I have. My last question is, if you could have one superhero power, what would it be, and why would you want it?
Chris: Of course you ask that infamous question, right? Many choices. Good thing it’s a superhero power so it can be as off the wall as you want. [laughs]
Carlos: There you go.
Chris: You know what? I’d say “teleportation.”
Carlos: Very nice.
Chris: I think that would do it.
Carlos: This is like the Harry Porter kind or…
Chris: It doesn’t matter what kind just…
Carlos: …Or the “Star Trek…”
Chris: …To be able to go, “Pop.” There, I’m wherever it is. Star Trek isn’t really a super power. Your ability to sit there and just, “Pop,” there. “Pop,” now I’m over there. The reason is, it will be a lot easier, and cheaper, to get to clients in advance.
Carlos: [laughs]
Chris: Teleportation will be that superpower.
Carlos: Very good. Thanks again Chris, I had a great discussion.Chris: Thanks for having me.
Carlos: Yeah, it was also great fun, and compañeros, I hope to see you on the SQL trail.