Moan and everybody's II'm not Langley, Dieter developer consultant been working with SQL server. Quite awhile now. Basically, the data warehousing kind of by structuring the data at performance tuning and there's my contact details. So if you want to contact me about anything this in this talk feel free.
So today, we are going to be talking about SQL server encryption so essentially how can we protect the data so?
Throughout this talk we're going to go through a little bit about what is encryption and then we're going to have a little look at actually how encryption works just a kind of basic guide is supposed to be a kind of relatively intro session. So I want to make sure that everybody understands the terminology that's involved so that as we look at how we do things.
And we're talking about when you look at what we want to protect and we're going to look at why we want to protect it. So you know, there's various reasons that will look at later but you know, most people think of I've got to protect my data because of GDP are and things like that. These days and then we're going to look at how we can actually use.
Tools within SQL server to protect that data.
So what is encryption well. Microsoft we've got a nice definition on their web page for SQL server encryption is the process of obfuscate ING the data by use of a key or password essentially that means that a little bit like when you've got file that you want to protect any file system is it fall or something you can add a password to that or it could mean that we're using uh.
If we think of encryption algorithms. We tend to think of. They have a bit length. And that is essentially the length and the size of the key so if you're talking about. Maybe something sort of relatively modern and it's a 4096 bit key. That's quite a large thing for you to remember so it's not really a password that would be used on a regular basis.
In crip shun is that we essentially make that data useless without the corresponding key if you don't have that key you can't access the data. So it should protect it for us, but the one thing that encryption doesn't solve is our access control problems, so SQL server. Obviously has quite a rich way of controlling.
Permissions system, we've got rolls. We've got permissions, we can apply that to users and groups in order to give people access encryption isn't going to solve that problem for us, we can use that and some technology in SQL server that allows us to make encryption work within the confines of that system.
We solve that problem for us and we need to make sure that we're protecting data in terms of access controls the way we should be separately, and what do we actually need to encrypt?
Say will move into that in a second, but why do we need to encrypt data? Where we mentioned GDP GDP? Are is obviously come in. It's made quite a difference to the way that people think about. That data and data loss. So the thing with GDPR? Is is quite have you find if you lose data especially sensitive data PAI?
Can be up to think is the higher one of 10,000,000 pounds or 4% of global revenue so if you get this wrong and you lose potentially sensitive information on your customers that could be a big problem. It could hit you financially quite hard PCI compliance is in.
Handle and handling anything to do with credit cards. You will need to go through PCI checks. That's why many companies try and use third parties for processing credit card details and things like that, and it's why we should never be storing credit card details directly in our database. But if you do store things to do with credit cards. Then you will have to go through that check and that would all need to be encrypted.
Once the data or documents. I'm thinking about things here that are maybe trade secrets. At the company doesn't want to lose or it could be you have a list of customers where you've got leads coming in and your business wouldn't want those leads to disappear and go into your competitors hands. You want to protect those.
Losing data means that your reputation of your company can be damaged so it may well be that you think well. We're just running. I don't know it could be running a website that just something like a blog might be that you think well if I lose customer details. Maybe I lost all of the.
Users table with the account names and passwords on it wouldn't make much difference. The thing is most users don't have particular good password hygiene. They tend to reuse their details. So if you lose something like that, and somebody is use the same password for that bank account. They won't blame themselves for their bank account getting used in their money disappearing.
The data and it came from your breach. It will be your fault. So it can give you quite a lot of problems with reputation damage it can also come from management you get some managers who suddenly they read something somewhere and they decide that they want to encrypt absolutely everything and we'll see why that can cause problems and then the other.
Suddenly find that you're being asked to encrypt all your data is a reaction to a data loss incident or something like that, or see at that point, maybe the horses so bolted already. But at least you're then going through and working out where you actually need to change in order to make things better.
And then what do we actually want to protect?
Well, it's basically 3 types of data that we're going to be trying to protect we have our data at rest. This is in SQL server. This is the data files. The log files and our backups. We want to make sure that the data on desk is protected in such a way that if those files were to go missing or they were to be stolen somebody can't use them we have day.
This is very similar to when you connect to your bank and we use HTTPS. We want to make sure that when I'm speaking to some other third party that the data going between us can't be intercepted in red.
Uhm but this also will touch on this a little bit later. This can also be influenced by your infrastructure in your application architecture. And then the final thing that we want to protect this data from Sysadmins dated from insiders so people who are Dbas, who are super users have the ability.
Like to know SQL server, so it maybe they think well. That's not too much of a problem for us. We trust our dbas. However, if you're a company that is providing storage and services to third parties and their data is in your dentist later or you're managing their cloud accounts. They probably don't want your DBA seeing their sensitive data so that's uh.
Need to be able to basically have a segregation of duties people can manage the server, but they can't read the data.
So we're going to look at a little bit at the types of encryption because this is kind of the underpinning of how everything works. 3 types of algorithms that we use for encryption. We have symmetric keys. There's a couple of examples of some of the algorithms. There we have asymmetric keys again a few examples and we have cryptographic hash algorithm algorithms.
So we look at Symmetric Keys first so the example here we can see that a single key is used to encrypt the data and then the same key with the same algorithm is used to decrypt the data. The issue here with this kind of encryption is that if I'm going to send some data to one of you.
We need to have a copy of that key and the problem. There is if that key is an encrypted when I send it to you? How do I know somebody helps hasn't managed to grab a copy of the key and therefore if they can intercept the data. They can intercept the key, as well, and then the encryption that we've applied is essentially meaningless.
Symmetric algorithms tend to be based on a type of algorithm. It called block ciphers. So essentially what we're doing is we're taking your your large chunk of data the key that we use will have a certain length. We measure it in the number of bits and we will break that data into chunks that are the same size as the block or slightly smaller.
The smaller is we could add some random putting such that if you try and encrypt some repeatable data. It would make it look a lot more random an essentially for each one of those blocks. We do an axe or operation with the key and then we do. The Next One and the next one and the output is the encrypted data. The axle operation is very simple. It's very fast, it's uh.
We use so it's a very quick way of doing things we don't really need too much in the way of extra operations within CPU's to help this work.
So as we mentioned we have this issue of I want to encrypt something within symmetric key but I need to get that key to you. This is where we use a symmetric key algorithms also known as public key cryptography. So the idea here is that when we generate a pair of keys public key and private key.
Ascentia Lee I have a public key that I can give to everybody. So when I send it to one person who I would like to send me some data encrypted. I don't mind if the entire world has that public key, as long as I keep my private key to myself that's absolutely fine. The public key is used to encrypt data using the algorithm and the private.
With the same algorithm to decrypt the data and the way that these work is it doesn't actually matter, which way round to use the keys in terms of putting it through the algorithm. The algorithm is basically it works both ways. However, for some of the more common one that we use algorithms based on RSA you would be using for.
That TLS is based on this, the way it works is that in order to make this very difficult problem, so that people can't work out the private key from the public key. It uses the idea that factoring big numbers is very, very difficult. You take 2. Prime numbers as your private keys. You multiply them together and you end up with a much bigger number.
Isn't prime that's your public key trying to workout? What those prime numbers are in order to derive the private key from that is an incredibly difficult operation. If you're using large keys with RSA you could be talking. You know hundreds and hundreds of years and very fast, hardware in order to sort of go.
And this, this idea that we're able to have a function that goes one way very easily, but doesn't go back the other way, and takes a massive amount more computing power is called a trapdoor function so if anybody is interested? How will it look on Wikipedia? There's some really interesting explanations of the Maths.
I'm for RSA uhm, yeah, it depends on which implementations you're using I haven't got any concrete numbers on that. But if you give me a shout after we can have a little look.
Uhm so yeah, as we've noted this is the idea of this is that we can secure communications between parties so that's symmetric key that I wanted to use to actually encrypt the data because it's very quick. I can send that to somebody encrypted with my private key publicly sorry.
With your private key and you can go OK. I've got that now I can encrypt data and send it back to you so typically when we talk about you know you connect into your bank. We have what we call a key exchange. They send their public key to you. You send your public key to them and then at that point we can exchange data and keys securely.
And then finally certificates people have obviously I would imagine heard of certificates. Often the thing that you see when you're browsing the web and you get a green tick. When you're on HTTPS or a red tick. If somebody is done something a little bit strange in the middle and certificates are based on a symmetric key algorithms rhythms. They are a wrapper around an asymmetric key that.
So we're talking it would add your domain of your website. It would add an expiry date a valid from Day, it would say who the owner of that certificate is and we also then use the key that belongs to the certificate to sign that certificate once it's created such that it can't be messed with if we change one of those details it would change the signing hash.
Therefore, the certificate wouldn't match so when I want to validate the certificate. I use the public key and I basically encrypt the certificate with the public key and see what comes out of it. And if it matches what the the certificate contains then I know it's correct.
So we were talking about their public exchange his situation where I want to send some data to someone. I know I send my public key to then they send their public key to me.
So I want to send them some data so I reduce their public key to encrypt the data. I would then use my private key. We talked about the fact that the algorithm. You can put the keys through in whichever order you want. So I would use my private key to then encrypt the already encrypted data. Well, this is doing is it's essentially adding a signature to it.
I used my private key to encrypt this data, the only key that can be used to decrypt that data. The second time is my public key so that means that if you've got my public key and you know it came from Maine. You guaranteed that whatever data. I've sent to you is coming from a nobody else can have messed with that in the mid.
Yeah, that's important because that anybody else might have the other public key from John.
And then when he wants to decrypt it. He uses my public key to decrypt the data first to take that verification encryption off and then he decrypts the actual data using the private key in order to get the data back out of it.
And then the final type of algorithm that we've got is cryptographic hash algorithms. So these algorithms are designed to take an input and give you a fixed length output. So this is really useful way of being able to verify data is what it is for example, if you download SQL server for Microsoft on their website they?
I think it's now Shorty 56 hash for that. That data you can run a hashing algorithm on the data that you've downloaded and if the too much. You know that perfect if they don't match. Maybe there's some kind of malware has snuck into that thing is, you've downloaded it. So it's a way of verifying that data is what it is they should be.
My functions Ascentia Lee because of the way these work. It's very similar to our symmetric key algorithm where we break the data into a block and then apply a key on to that block? What happens here is the initialization initialization vector that key that we start with is kind of fix.
So we take a block and we axor it with a fixed. Key the output of that is an excellent with the next block and the next block and the next block until we keep going. If the file is not quite quite the right length. It gets parted out towards the end again with a fixed padding that's all declared inside the algorithm so that as we move from machine to machine it doesn't change.
Is that we can re compute the same thing and it's always the same length and it's one way if we're taking data and were essentially with each pass losing some of that information. It's very difficult for me to take something that is 128 bits. Maybe and get back data. That's 4 megabytes because I've lost that date as I've gone there's.
Back.
However, there is one problem with us. If we don't use an algorithm that gives us enough randomization within that function. We could put 2 inputs in and we could end up with the same hash value. This is called a collision so some of you may know a little bit more about cryptography. MD 5 was considered secure.
Before when it was demonstrated that they could get a hash. Now it's relatively trivial to generate MD 5 hashes concerned in the hardware advancements. Sharwan is considered don't use it for new stuff. It's not as secure as people would like there's been some demonstrated attacks against it, so if you're doing anything you tend to stick to shark 25.
Maybe shot 12.
Uhm and then as we touched on is used for integrity checks. It's used for digital signatures. So you can guarantee that something is what it is. We also use it for authentication. So when we talked about, we store passwords in our database because we've got an application where users need to be able to login. We shouldn't be able to we shouldn't be.
But in the database in plaintext, they should be stored as hashes and will touch on that a little bit later and explain how that works.
And so, yeah, quick summary on what the particular specific ones are used for symmetric keys are used to protect your data that might be using a private key or password your asymmetric keys are typically used to protect the symmetric keys that you're using to protect your data either in transit or in order to lock them within SQL server.
Great certificates and then the hashes we've just gone through.
So how does this work within SQL server. We've talked about the fact that when we create a? When we want to encrypt data. We would typically maybe user password in order to look things up so it may be that we've got a certificate protected by a password. That's symmetric encryption protecting that password so that nobody else can use it.
That doesn't sound like he's going to work, particularly well in contacts to the database. We don't really want to be in a position where every time we decrypt the data the user has to provide a password for it, so SQL server abstracts. This back out into the permissions model that we have within SQL server, and that we used to we grant particular permissions against the keys that.
On the way this works is that?
Um essentially at the top up here. We've got the Windows operating system. It has an API that is designed to protect keys for us. SQL server has a master key so this is at the service level and that is protected by the Windows operating system so at the point when you run the installer this will get set up for you.
That we have the option to create a database master key so this happens within each database that you would like to protect it when you create that you create it with a password. But you can then open that key using the same password and tell it that that password can actually be stored and it can the key can be protected by the service key, as well now.
Anybody who is a sys admin on the box has the ability to open that key because the service will give them the credentials in order to do that. They don't need to enter a password at that point anymore and then from that, we can use that to create certificates. Asymmetric keys and symmetric keys that are protected by this hierarchy, so that means that we're able.
We give people the ability to open the public part of any case, they were talking certificates and asymmetric keys. If they have you. Definite definition permissions on it and if we're looking at a symmetric key or a certificate or also for the symmetric case where we want that private part that part that does the decryption.
Control.
That makes sense to everybody.
So certificates in SQL server, essentially certificates. We can create we can import and we can export them. This important different sort subtlety to be noted that whilst we can create a symmetric keys. Instead of certificates in SQL server. We cannot export asymmetric keys and symmetric keys in C.
Cool, server, so if you want to create a symmetric key and then use that on another database so that you can send data from one database to another then you would need to create them. The same and it would take the same create statement for that which includes a password to initially set that up and an initialization vector the initialization. Vector also has to be the same.
That's what's happening with random number generator? Is it generates the keys underneath but the ability to be able to export them is useful because if we want to be able to use these for certain features where we may be looking at HARDR scenarios, and we need a certificate on multiple machines. The fact that we can exploit these certificates me.
Set up lots of utility is to create the X 509 certificates at SQL server users there an industry standard and.
When many people think of kind of the certificates that we often see and use in terms of maybe the context of accessing secure web pages. They are what we call CA sign that's a typical authority signed so it means that there's a trusted authority that has said this person is who they are, and they've signed the certificate to guarantee that certificate is good in term.
Sharing data within SQL server. We don't need our certificates to be signed SQL Server is not going to check that it's not actually going to do anything with that, so we can use self signed certificates in this case, it won't check the expiry date either. But something that I've been sort of told is a useful way of setting them up by some dbas I know is that?
Expiry dates on the certificates were using and we're in an enterprise situation where we need to roll those certificates. Overtime and keep replacing them if you set the expiry dates. You can see, those in one of the DMV's and it gives you dbas really nice. Easy way of knowing which ones they need to sort of role at certain times.
Um certificates when we create them are within the context of a particular database. That means when we back up that database. Those certificates is still within the database. If we restore it somewhere else they are still within that database and they are trusted by the database key above them, if that's how we set them up, which ME.
Thing as we restore database to another machine and that that database. Key is protected by the service master key, then all of our permissions will work again properly.
Um on 2012 they added the ability to export a certificate using a binary blob and import it back using a binary blob. That's a really useful feature if we ever need to get a certificate into multiple databases. It's not something we cover here, but you can use certificates for signing stored procedures in awe.
Extra apply extra permissions to that certificate runtime. There's a talk on that yesterday for anybody who missed it probably be on the website and essentially that means that if you need to be able to provide kind of cross database query ability using that feature.
Tiff Ocate invite databases and you can use that feature for that and then as we chatted about with the encryption keys before to get the public key event of a certificate we use. View definition and we grant that against certificates to particular roles or users and to get the private keys. We need view definition and control.
Say what will days will work through the options available for us to encrypt data within SQL server, and will look at them kind of in chronological order because it kind of shows us how the product is evolved overtime will have a little look at what that encryption is actually trying to protect starting with 2000.
Thousand words we have the ability to apply SSL to our connections. This isn't turned on by default by default. SQL server will quite happily accept an unencrypted connection from anybody so once you set this up. There's no code changes involved and it's going to protect the data as it is in transit between the claw.
However, that could be a web application. It could be management studio. It could be any client that is connecting into your database self signed certificates can be used for this. The problem with that is very much like you know when you connect to your bank if that certificate isn't signed and trusted with a certificate that the OS.
But then we're in a position where somebody could intercept that data and just add a new certificate into that chain in order to try and make you the client think that the server is sending you the right data and it may well not be. But if you're working in an enterprise organization where you have organize.
CA certificates that pushed onto all the devices. You could use one that signed by that and then that would give you the same ability. But you don't need to go by. One of the CA signing authorities. The interesting thing here is that in terms of an architectural point of view, if your SQL server is in a DMZ and it's firewalled off.
Not available to anything else, sometimes it's actually easier to put the SSL certificates into the routers in front of it. If you know that you're completely secure in that network architecture. The guys who are doing your networking stack. They used to using SSL certificates. They used to making sure that as they expire. They got rolled over and and all of that and actually.
They just a simpler operation to do that. SSRS is horrendous for setting up certificates in similar way out, the box. It doesn't use anything I've worked with quite a few network engineers who just put it into the router for me and because we knew that the only thing that could see that server was the Rita. It made our lives way way easier.
Then in 2005, they added column and row level encryption, so this is designed to protect our data at rest. It does, that by using some functions within SQL server. So we have ones that allow us to use a password ones that allow us to use a asymmetric key symmetric key and certificate.
As we've discussed protecting your data using a symmetric key is much, much faster than using an asymmetric key so that's kind of the recommended route encrypt bypass phrases. Gent generally tends to be problematic. Becaus if you're not using SSL in someone sends a query to the server that's got the passphrase insider that could be sniffed it.
Sure, if you put that inside your stored procedures and you use in CI in CD and all of your your code is actually in source control, then your keys are in source control? How many people. Here are using source control for the database good number of you so yeah, you wouldn't want those keys to be.
Same source control and then the other issue is how do you control that between different environments you've got your dev environment your integration environment you production environment? Ideally, you want different keys in different environments. And if it's inside the code that can be really difficult to manage so ideally. We want to be using the encrypt by key decrypt by key and then the auto ones are actually using.
Tricky that is protected either by an asymmetric key or certificate and it automatically opens that for you and then does the decryption so it's just the kind of syntactical sugar over the top of it?
If you do go this way, and use these the one thing to be aware of especially if you're not on 2016 year is 2016 deprecated many of the algorithms that were in use from 2016 onwards. These are the only 3 that are valid. So if you've been using other things that may make your upgrade path slightly more difficult.
And essentially the way this is working is as data goes into the column. The column is declared as a binary. We push the data in using one of these functions. It turns it into a binary and if somebody has permission to open that key and get the private key or the symmetric key they can then decrypt using the same funk.
In order to get the data back if they don't. They just see a binary blob, but it's not going to be too useful to them, but it does mean that does mean that you need quite a lot of code changes in order to make this work anyway. You're going to query it. You need to have made sure that things are set up for that and the other thing to know is that your sysadmins super users on that box.
They serve a level to open essentially any certificate that is protected by the service master key so unless you've used encrypt and decrypt by passphrase. They are going to be able to see your data if they want to and obviously as we spoke about the encrypt by and decrypt bypass phrase can be problematic for other reasons.
Then, 2008, they added TD TD is designed to protect your data at rest, so probably as we look at this, you'll see kind of chronologically, the invite this sort of environment of how they're thinking about encrypting encrypting your data in protecting your data, which changing so this is designed to be completely.
Your code you need to make no code changes and the way it works is essentially encrypts your data files your log files in your backups and doesn't do file string so if you're using filestream are not many people are then it won't do that, and on 28 in fact until 20.
Anyway, that you could actually encrypted backup.
The way it works, essentially there's loads of documentation examples on this. But we use in a server level certificate in order to protect the key within the database that then unlocks the rest of the database. That means in ADRRHA scenario. You need to make sure that those keys that allow you to open the database are in all the correct place.
It also adds some for the organization. Some implications around managing those keys. 'cause without those keys. You can't use that data at all. Everything is protected. It doesn't protect anything from those super users again. They CP users can go in the database and look at anything they like yeah.
Note on that again talking about development process and environments. How many people were stored databases from production down into Dave in some kind of way.
How many of you using TD?
You're using TD in production and you have certificates that Anura store in that same database directly into your development database. That means your certificate needs to be in-depth. That means that there are way more power users who suddenly have access to the certificate that is used to protect your TD data that.
We need to consider maybe restore it to a different box take off the encryption format. Do any work that you need to do in order to anonymize data and then move that onto your development server from a slightly more secured box.
Backup encryption so this was added in 2014 and this gives you pretty much the same as what you would get with the backup encryption you get in TD. The difference here is that we can use both the certificate or an asymmetric key to encrypt. The backup the problem with that is that as we discussed earlier asymmetric keys can't be exported.
Still, better off using the certificate for that on the certificate little bit like TD must be available on another box in order to restore it to that box.
In 2016, they added compression support so before 2016, both for TD backups. There was no compression. So if you decided to turn this on and you were using backup compression. You'll find your backups get a lot larger lot quicker. Problem was, there was bugs in the RTM version. There's a good blog post, there from Brent Ozar that explains what the issue.
As long as you're on the right. See you for the RTM version or in service pack. One you should be OK. It's just something to be aware of if you're not patched the right level.
And then from 2016 they added the newest version of kind of feature for encryption within the product. So this is always encrypted and the idea of this is that we are looking at end to end encryption of specific columns. So you take an entire column within that database and that color is protected and.
Did rest it's protected in transit and it's protected from Sysadmins people who've got power user ability? How does that work well? What we actually do is instead of encrypting data in the database. They've move that up into the client drivers so the data is encrypted and decrypted within the.
Connecting to SQL server.
So how does that work in terms of a store in keys? Where we have to have an external Keast store so you can use the Windows Certificate store you can use Azure. Key vault can use a hardware security module that should be completely separate from your SQL server. If you're running that on the same box is your SQL server, you're not really.
In terms of the architecture.
So essentially this 2 keys are involved on this when he set up you create a column master key and that column master key is used to encrypt a column key essentially and that column key is used to encrypt the data so within your database we store a copy.
The K used to encrypt the data but it's encrypted with that column master key so it's never in plaintext within the database. There's no way you can use that particular key to unlock your data unless you get the color master key and the color master key is stored in the external certificate store so as long as you've got permission to be able to go and get.
Then query the data correctly.
So here's a kind of diagram of how it worked. The reason Microsoft diagram that looks very similar, but they don't have the certificate store on the bottom and I just felt it may be slightly clearer, so in this case. We've got a user he wants to run a query against our database with an always encrypted column.
The SSN in this case is the one that's encrypted so they send their normal query free to the database theADA.net library driver is enhanced to support always encrypted. It's a newer version that knows how to do this and it communicates with the database in order to go and get the.
Encryption key from the database because it can check to see whether that column is encrypted. The database sends that column encryption key back to the driver and then it requests from the certificates store or the key vault the color master key which it uses to decrypt that column encryption key once it has that column.
It will then take the predicate on the where condition and it will encrypt it in order to give us a new version of the query where the data is actually encrypted such that we can do an index seek into it.
Data and they would simply get the binary version of that.
So with always encrypted there are actually 2 types of column encryption. There is deterministic and there is randomized now. You might be thinking well? Why does that matter and how does that work?
Wouldn't randomized always be slightly stronger so essentially deterministic means that if I take the same value throughout the column for example, say I have somebody's name multiple times within a single column. The name Bob for example.
That would always give main the same encrypted value if I use deterministic. Where was when we use a randomized encryption setting within this and we set it once for the entire column? What we would get is for each value. We'd get slightly different value now is important because there are certain situations in which we might be.
Look at that encrypted data and use frequency analysis for example, to workout. Maybe what things are so good example of this might be we've got a column that stores bucket information for employees as to how much they are. Maybe we bucket up. It's up into you know every 10000 so you've got people who earn 10 to 20,020.
Thousand 30,000 to 40,000 if you look at the distribution of how much people learn with an organization within an organization will typically get a Bell curve, so if you look at the frequency of how much things occur. You can probably if you know the average wage for your company gas at which bucket belongs to that.
And then you'll be able to look at the frequency and say, well, I know the 2 sides to that side. The 2 to that side to to to that side and then you could look at those users and you could say well. I think that person and roughly this bucket and I earn in a slightly different bucket and you might be able to work that out, so in that case that somewhere where you want to use randomized encryption the problem with a random.
Is it causes he some extra issues because it's randomized and we've got different. Values for each thing we can't use that for joints. We can't use that for indexing because if the the value. That's the same occurs in different places within the index. I can't index seek into anymore. I'm going to get scans. I can't use that for vlookups same reason.
To our vlookups instead of doing a scan and I can't use it for grouping because all the values are different so those are operations that still happen within the SQL server engine as opposed to happening on the client side and it's just the some limitations in there, depending on which one you choose so you need to be careful with that.
The other thing that's important to note with all of this is that code changes are required to implement this we need to essentially set up the columns to be encrypted using a specific column key and that column key is encrypted by the color master key. That means that the SQL server needs to be able to know where the key stories in order to go and get the public.
Of the column encryption key in order to be able to encrypt the column encryption key inequality predicates just generally for searching for data and not supported so you can't do things like saying, You know, I would like all of the customer.
But that name starts with a big or something like that because essentially that index once it's encrypted is in a different order to what it would normally be those values aren't stored as plaintext and therefore the name Bob and the name Bell could be completely opposite ends of your index so you can use equality predicates.
For that Direct Valley, but the inequality ones aren't supported some data types unsupported essentially there's a couple mentioned there. Anything that lives in lob storage isn't supported and there's a good simple talk blog post on some of the bits and how to implement this and it shows you some some interesting ways of using it.
And one of the most important kind of drawbacks. With this one is is very powerful. It gives you a lot of things that you would want to do in one nice neat package, but it requires that all of your clients have a new enough version of that driver. That means if you've got any old legacy applications that using odbc to can.
You can't use this because that particular client won't be able to connect and decrypt your data.
And then finally one that will look at here. It's not really a feature of such of SQL server. But we touched on it with the hashing at the beginning is how passwords are stored within the database so.
In 2018, the biggest list of usernames with passwords that was found with 717000000 unique email addresses and password hashes that is a big, big list. And there were a lot of data breaches in 2018 and one of the most common.
Things that happens when companies have a data breach is that the people who've got inside the network will try to get enough commissions to be able to dump tables that contain usernames and passwords for logging into websites. The reason being is because many people re share those passwords across multiple sites so.
Say that you're thinking well, we're not important and it's not going to matter. But if people have reused that for their banking sites for many other things, it could be really problematic for them. So how should they be stored. But we should be using hashing to store those in the database as you can see here we should be using salted hashes so assault.
Say, a random piece of data that we append to the password before we calculate the hash that is randomized. For every single user within our database, and that means that if I've use the same password as somebody else. The calculated hash is different for everybody, so in terms of you know how that needs to be.
If we were to lose a table that contains a username hash and assault within the the fact that the salt is suddenly public. If we've used a strong enough hash shouldn't be a problem because people are going to have to compute or and try and compute the hashes and brute force set for every single user within that.
Shut down.
Um there's an interesting website here. The Gist Github, one. There is a guy who publishes how quickly he can calculate hashes on a high end set up with I think it's 108 sorry GTX 10 80s on there, so MD 5 which we talked about.
Considered relatively insecure, these days, he can calculate 200,000,000,000 hashes per second on that setup sharwan which is considered by some people to be appropriate for hashing passwords. He can compute 69,000,000,000 hashes per second so if we're talking about somebody taking this list and trying to brute force.
To workout what peoples passwords are as a big number B Crypt, which is one of the most recommended ways of storing passwords. These days and hashing them using B. Crypt is an algorithm that essentially compared to the other 2 it works by essentially doing this recursively so ads.
By going over the same hash over and over again, you produce the Hush Hush, it again and again and again and again and again? So I think the default work factor that they suggest is a work factor of Tan. He tested it with a work factor 5, which I believe is around about 10000 iterations and that means that he can only calculate.
Thousand hashes per second on his insane setup, so if we're talking about how difficult we want to make it to for somebody to kind of like brute force, your users passwords list. You want to be using the right algorithm.
The important thing to note here is when I say be crypt is an algorithm where we are doing that iteration. This shouldn't affect you guys with your SQL servers with your web apps with anything like that because the way that we want to implement. This is when somebody goes to log in. They put their details into your web page. You hit send.
With that password already entered that web page on the client side should be connecting to your web server, which connects to the database, the salt is sent back to the client and at that point. The cash is calculated and it sent to the database that is how the whole thing authenticates it compares the 2 hashes that sent back the past.
Clear text and all of the work in terms of us using something like Bcrypt is done on the client side. You don't need to worry about this putting extra load onto your database or your web applications.
Um so this isn't a particular kind of SQL server feature. It's something that is stored in the database is something that's incredibly important to protect and it's something where you really need to understand as somebody who is involved with the database lay.
What your developers have done and why they've done it and it could be that they've implemented it one way for 5:10 years ago and nobody's ever touched it since so maybe it's not through choice that we've got a bad algorithm. That's being used to store these things. Maybe it's just that.
About it and decided to change it for the last. However, many years. There are a few clever ways that you can kind of make sure that this is an easy. There are easy ways to upgrade this if it's a problem for you. If anybody wants to kind of have a way to do that speak to me afterwards 'cause. We're starting to get a little bit short on time.
And then the final link at the bottom here is a really interesting article on essentially the ways that people take these big password lists and try to actually get the passwords back out of them in reverse engineer. If anybody is interested in it. It gives you really good overview of the tools. They used within Lennox to actually autumn.
And and do this 'cause you might think well it would just be a pure brute force of sort of approach. What actually happens is they tend to use very large dictionaries and they use character substitution with that, so if you take a dictionary of all of the words with in English and huge dictionary of names and then for every character that'S&I you.
Explanation mark in a one if you're able to compute compute 200,000,000,000 hashes per second. You can try almost every combination. For every single thing within that dictionary for you know a given hash within few seconds usually.
So.
I want.
So one more thing that would touch on it is a SQL server.
Pizza functionality available from 2016 onwards is dynamic data masking so this is one that has been used a fair bit. A lot of people think it's protecting the data. But this one is not actually encryption. This is essentially a way of making sure that when we send query results back to.
That that data is masked out in such a way that they can't see the full that piece of data so a typical example. That is used by Microsoft is SS NS in the states people like to keep their SSN relatively a secret because it's used for so many services so when you return it, it's uh.
Maybe we put access in for the middle 4 digits or something like that, so the users can't see them.
Uhm the thing here is that the data that can be read by Sysadmins because they've got permission to see the data without the function being applied and it susceptible to brute force attacks because the data is not actually encrypted. What happens when we apply a where clause of predicate to the query road running it will go and look for it so.
Thing where ever played a mask to the first 2 digits. And I know the first 2 digits or what was the numbers then I can create a query that says select star from the table where the first digit is like zero and then I can put within that first digit is zero. I could union that to another one that says where it's one.
Where is 2 and another one that says where it's 3 I can do that then for the second digit and I can join them together and now I know exactly what those first 2 digits were even though when I look at the full data. It's messed up and there's a blog post, there that shows you the way that that gets pretty forced.
So, in summary the most important things here that we need to know are what you need to protect so we're looking at are we going to protect data at rest data in flight. Where are we trying to protect data from people who are privileged users we need to know the types of data that we're trying to protect are we trying to protect some Pi some sense it.
Specific data for compliance as we've seen this trade offs between all of these things so if we turn on TD for example, there is some extra CPU overhead on a modern system. It's pretty negligible. Most people won't notice it unless you're absolutely maxed on CPU already.
But there is potentially a performance impact as we decrypt and encrypt things. It changes the way that we can use our data. If we want ease of use and our data is encrypted. This certain things that we can and can't do with that data now until we've decrypted at and it also gives us implications on Ark.
We might have to change code to implement this. But if we've got an OLTP system where we've encrypted lots of data then if that data sensitive to being enough to be encrypted within your OLTP system. It probably should be encrypted there and then the other thing that we've got to consider is just those backup keys the backing up of.
Protect things so for example, if you're using TD and you're in adyar situation where everything needs to be moved to another box. You don't want to be in that situation where you've got a really good backup that people have been testing, but all of a sudden you found you've lost your keys for it and you can't use a database at all.
And then finally there's a little summary here of essentially what versions of SQL server. Things are available in and what how we're protecting that data and whether or not, we need Kay changes.
And then finally if everybody could fill out their feedback forms before they leave that would be awesome. Please don't think you can leave it till the end of the day and Honda Mall in I've been helping for the last few days. We put them in envelopes and it's much easier. If we can get them all at the end of the session, so that we don't all have to stay behind and sort them all out at the end of the day.
And then there is my contact details. If anybody would want them. If anybody's got any questions for me. We've hit the time limit so if you wait for me just outside I'll take questions out there simply because the next speaker. They've gotta come in and get set up and their helpers need a bit of space to get everything ready. Thank you.