Designing secure Azure SQL Database solutions | OD32

Designing secure Azure SQL Database solutions | OD32

Show Video

[Music] hi my name is Michael Howard I'm here to present od32 designing secure as your SQL database Solutions this is a a pretty fun presentation we go through pretty quickly through a whole bunch of security capabilities that we have in Azure SQL database I'm also the co-author of a book that came out last year end of last year called designing and developing secure as your solution that a lot of what we talk about in this session we're going to much greater detail in the book so the main objectives of this I was going to put an agenda here it's not really an agenda it's really it's a bunch of objectives that I sort of want to I want to achieve the first one is just some basic elements of secure design like what does it mean when you're thinking about designing a secure system and secondly is how to correctly choose some of those mitigations and some of those technologies that we have in Azure SQL database to help build more Secure Solutions before we get sort of stuck into this I really want to make an important point and that is you've got to really understand what mitigations are provided by various security Technologies you know if you want to like you know use a or b or c whatever they happen to be what are they actually mitigate like what are they really fix because sometimes you may actually not use the right mitigation and so hopefully this will help clear up a lot of the uh so the differences between the various mitigations that you have at your disposal within with our database products so before we get stuck into that let's take one a little step back and let's let's actually add some structure to all of this like how do we think about security when we're when we're designing a system this is actually lifted straight from the book as you can see probably chapter 13. um and when you're thinking about you know what mitigations we're going to use what security Technologies we're going to use you really have to think about one what security property do they provide and what plane right so there are two planes right there's the control plane the data plane the control plane is the management plane right how do you manage these systems and data plane is essentially the mitigations that come into play for your users using the system then you have all these different things you need to think about like authentication and authorization and audit and then the various types of cryptographic controls there's another session called OD 31 and actually go into more detail into the three cryptographic control three sets of cryptographic controls and then finding Network isolation so the next slide if we look at sort of the the security features that are provided by the various Azure SQL database products or SQL database products for that matter you'll see they've got things like for authentication for Server authentication we have TLS for clients authentication you can actually use Azure active directory or you can use SQL login I'm not going to go into all of these in any detail because we're going to explain some of the key ones as we move forward but as you can see there's actually a whole bunch of security technologies that you have at your disposal and here's here's some more as well right so um cryptography on The Wire cryptography and using them finding Network isolation again it's really important to understand which mitigation which security technology comes into play at which plane control plane or data plane and also what it's actually trying to solve so let's go and look at the very first one one of my favorites which is TLS alongside all my other favorites so TLS or transport layer security what was it often referred to as SSL back in the day by the way TLS is essentially an ietf ratified version of SSL that's really all of this SSL was originally invented by Mozilla and then finally the internet engineering task force took it over and it became TLS um what the single most important feature that TLS actually offers which actually surprises a lot of people is serve Authentication if you ask anybody like what does TLS actually do they'll often say oh well you know it protects my credit card on The Wire which is true but you have to have the serve authentication first you can't have a secured Channel until you know who you're talking to and that is the most important security feature that TLS provides is serve authentication um after you have that serve authentication done that's when you have a secure Channel you can use encryption and you can use your tamper detection controls like hmax and a correct TLS configuration is absolutely critical like no self-signed certificates if you have a self-signed certificate that's telling me that you're not authenticating the server you're really not um the the best analogy and I'm not a fan of analogies but the best analogy is just a little bit like say flying to South Korea and writing your own passport on the plane I don't think the South Korean border guards are going to let you into the country somehow just because you wrote your own certificate it's not your own passport it's the same with self-signed certificates please just don't use self-science certificates um people think oh you know are you self-scientific it's in Dev and then I use normal certificates in prod no just use real real certificates in in Dev as well even if that means setting up your own certificate Authority which is a whole nother discussion but please don't use self-science certificates um Azure SQL database and SQL Server prior to 2019 support TLS 1.2 and Below um Azure uh SQL Server 2022 also supports uh TLS 1.3 but you must configure it and this is one of the great examples of one of the benefits of using paths Solutions over I as so for example your platform as a service safer in Azure SQL database versus is say running a SQL Server inside of a virtual machine or on-prem for that matter you have to configure the the TLs whereas in the case of Pas platform as a service Azure SQL database and Azure SQL Mi we take care of handling the certificates for you so when we're looking at connecting to a SQL database we have to use a connection string right we're all very familiar with odbc you know connection strings there's two major settings you need to worry about the first one is in Crypt equals true and Trust server certificate if I had my way I would rename the encrypt option but it's been around for a long time so it's not going to happen because it's more than encryption in my opinion it should be like Channel protect right because that's precisely what it does it's encryption and tamper detection and then finally trust server certificate this is a really important one if you have trust server certificate equals true that means I actually don't care what I what I connect to there's no authentication going on especially whatever you present to me a server whatever the server presents to your application you're going to trust it and that basically means you do not know who you're who you're talking to you just don't trust service certificate equals equals false doesn't mean don't do any significant validation or anything like that it means I don't trust it by default so you must do the certificate validation right so chaining up to a trusted route things like date range checks um key validation like it is it a server authentication certificate things like has it been revoked there's all these other things that you need to worry about right so the recommended settings are in Crypt equals true and Trust server certificate equals false now the really good news and SQL Server 2022 is that we now have an option for even tighter TLS policies we're doing cryptical strict and essentially what that does is it disables trust server certificate altogether and it will do the correct certificate checks it also allows support for TLS 1.3 and essentially what

we've done in SQL Server 2022 and this actually quite a lot of work is we've broken apart um tabular data stream TDS which is a native SQL on The Wire protocol and TLS 1.3 we've actually broken them apart and so that you we can actually upgrade one over the other historically TDS and TLS were kind of intermingled but that has now been has now been broken apart so this is fantastic if you're using SQL Server 2022 you can actually now use TLS 1.3 so long as you're running on an operating system that supports TRS 1.3 as well

which is primarily Windows Server 2022 Prime primarily okay that's serve authentication out of the way so basically TLS and do TLS correctly for clients authentication you really have a bunch of options but the big one that we really uh you know are big fans of and the SQL team is really pushing heavily is to use Azure active directory this is always the preferred way of doing clients authentication and the reason for this is because Azure active directory is handling the credential and not the application and not SQL Server if you use SQL authentication there is a password verifier not the password the password verifier stored in SQL which means that if SQL is compromised and the attacking has enough privilege and can get that data they have something that will help them try to derive the password I'm not saying they'll be successful and it is actually a lot of work but it's still there right but in the case of azure Azure active directory the credential is not there period it is not there and so if the attacker does actually compromise the system the attacker doesn't get the credentials of the users this is really important when you're looking at zero trust especially for assume breach because assume breach assumes that you've completely compromised the environment does the attacker get something critical like the password or a password verifier and the answer is no because it's not there for non-user accounts so for example I don't know what's a good example say a virtual machine or an Azure function app um you can actually use a managed identity and a managed identity again is a an identity that's handled by by Azure active directory and so when the VM starts up as an example and tries to make a connection to SQL Server it will actually connect as the managed identity on the VM and again the credential is not there I was working with a healthcare organization it was a long time ago and they had a specialized application running inside of a VM and they had to store the SQL credentials in the VM the problem with that is if this the VM is compromised the attacker now has the credentials to get into the SQL database so we ended up making a change so that rather than doing that we actually made the connection as the identity on the VM instead and again if there's a compromise there's no credential there because it's all handled by by Azure active directory again absolutely fantastic when you're thinking about xero trust and assumed breach here's another example if you turn on Azure SQL auditing you've really got a couple of options if you're using a storage account to store the audit logs but how do you make sure that the SQL instance can actually write to that storage account you have two options the first one is to use a managed identity which means that Azure SQL database will have its own identity and then you can restrict access to that particular SQL instance to that storage account the other option is to use a storage access key a SAS you know SAS key or a SAS token the problem with that when I'm reviewing a threat model one of the first questions I ask is where is that storage access key stored because you need to protect it well people say well it's stored in key Vault well how do you protect that well we you know we have in our back control on it well how do you make sure how do you identify the SQL instance that's connecting and it's like turtles all the way down right so managed identities the reason why you you would use a managed identity here is because that way SQL is not storing a credential somewhere and this is always always the best way of protecting things like this is to use managed identities let's have a quick look at our environment so far and kind of what it looks like you'll see on the left hand side we have the clients authentication using Azure active directory perfect all communication absolutely everywhere is using TLS perfect that's giving us serve authentication and it's giving us Channel protections as well in the middle there we've got SQL itself when you if you connect to SQL Server that's authenticated using TLS and it's using a managed identity when it acts as a client writing out to the storage account for its audit logs this looks like a really fantastic example and we're using a restricted rbac policy on the storage account to restrict access just to that particular SQL instance this looks really really good so far now let's look at authorization and again you've got to really understand here you've got control plane authorization and they've got data plane authorization and they're different so the control plane this is all Azure right so if you're deploying an Azure SQL database or SQL MI you will assign people the appropriate roles to do SQL Administration so for example SQL database administrator contributor there can create new instances of of database instances but they can't look at data they can't manipulate security policies they can't add users to the database right then you've got SQL security manager which is kind of the opposite they can do security policies and so on but they can't spin up sound new database instance and they certainly can't look at the data so this this is two examples of different roles control plane roles that's uh are opposite ends of the spectrum right you don't want to Grant someone both these roles at the same time and so these are examples of of control plane a couple of control plane roles um SQL itself also has roles right it also has permissions as well and it also has control plane roles and data plane roles it's kind of weird in that way but that's just the legacy of of on-prem Right Where You could have control plane roles and data plane roles I'm not going to go into all the details here but there's you know you can go and look at um you know learn.microsoft.com and there's plenty of documentation around the various server roles and database level roles my colleague um one of my colleagues has written a uh a blog post and trying to break some of these permissions up his name is Andreas Walter I would definitely urge you to go and look at that where he tries to take out some of these roles and make them much smaller much more granular so that way you could go out someone who's a very very small role as opposed to like a big role that gives people a whole bunch of access to a whole bunch of things and that's our ongoing work as well so so hat tip to Andres Andreas there next week come on to one of my favorite topics to go with all the other favorite topics and that is cryptographic controls at rest and we have a whole bunch we have our we have at our disposal here um I really want to stress something that to me is really important and that is that you may have heard this whole notion that security is only as strong as the weakest link it's actually not true cryptographic controls are in a massively beneficial last line of defense like when everything else has been absolutely blown away and destroyed and the attacker is in the environment a well-designed and correctly designed cryptographic control can actually mitigate those threats so for example let's say you blow the firewall away you get some weak are back policies blah blah blah blah blah right in the attack against the data but it's encrypted and they can't get the key okay I mean not great but at least it's not plain text right when the attacker has all the data has all the social security numbers you know everyone's prescription information or Healthcare information uh much better that the data is encrypted and that's often why security mitigations are often referred to as compensating controls are there to compensate for other weaknesses in the system and cryptographic controls in my humble opinion are actually one of the best sort of compensating controls because they can really really help out you know if a whole system has been utterly destroyed so the first of the controls I think everyone kind of knows this by now is transparent data encryption it's been around for a long time the nice thing about it is it's really easy to use there are zero code changes required it just works remember I said very early on it's important to understand what the control actually mitigates in this example TD is designed to disclose sorry to mitigate the disclosure of sensitive data from a stolen data volume right so someone walks into a Data Center steals the volume and walks out or if you've got on-prem and someone walks in and you've got a server under the desk and gets hold of the hard drive and walks out of the you know the environment that they can't decrypt the data still useful incredibly useful but probably the most important part is actually backups because backups are actually encrypted using the same key hierarchy and backups are completely away from the server it's not uncommon to say store a backup every once in a while on-prem right or in a storage account or something but the point is that it's away from the SQL instance itself and transparent data encryption is really useful in this environment the root key there's a whole key hierarchy which I'm not going to go into really should be holding key Vault that includes SQL server in a VM and then you'll provide Access Control using say managed identity to access the the root key and then decrypt you know the hierarchy of keys that are then used to provide the encryption and decryption of the data at rest there's a newer technology called Always encrypted It's relatively new in the overall scheme of things and in od 31 I actually go into this in much more detail so when you think about always encrypted you've really got this this this sort of spectrum right so on one end you've got you know databases which are designed to be queried I mean they're designed to insert and delete and update and so on but you know they're really designed to be queried as well then at the other end you've got encryption which is designed to do the exact opposite right it's designed to stop you looking at the data so we've got this real problem here we've got these products that are designed to be queried and these these features that are designed to stop you from querying what's interesting here is it always encrypted is kind of in the middle it actually this is a really important Point here always I'm going to read it verbatim because I'm very very specific I've always been very specific about my words always encrypted allows you to perform some queries some queries over encrypted data encrypted data without decrypting it at the server without decrypting it at the server stop and think about that for a minute what it means is the data is encrypted at the server and it's not decrypted at the server which means that the plain text is not in the server's memory at all in the main process memory is not and the way it works is basically by doing queries over site for text is essentially what it does there are two types of always encrypted there's always always encrypted with secure enclaves and always encrypted without secure enclaves Cosmos DB also supports always encrypted and it's the non-enclave version what's really important here is in both versions whether it's the Enclave or the non-enclay version the encryption and decryption keys are not in the server process memory they're not there at all all the encryption and decryption like for the most part is done at the client in Secure enclaves there's a there's a variance that which I'll talk about in a minute but essentially the full encryption and decryption is done at the client so the ciphertext going back to the clients then the client driver decrypts the data and again I go into this in more detail much more detail in od31 um if the serum is compromised there's no plain text there there's like if you've got a column encrypted with with always encrypted the plain text isn't there and there's no key there it's not there so again this is really important when we're talking about assumed reach and in the you know we thought talking about you know zero trust incredibly important because the data is just not there so there's nothing for the attacker to actually benefit from at all now with the enclaves there's two versions sorry about that um so there's two versions the first one is sgx which stands for software guard extensions which is an uh a special set of instructions and functionality on some Intel CPUs and there's also VBS enclaves which stands for virtualization based security enclaves so in the case of VBS enclaves that uses the uh the Azure hypervisor so the same technology that we're using in Windows and in Azure for that matter to isolate one VM from another it's the same technology but what we're doing instead of actually isolating part of the query engine is being isolated in its own little host somewhere off to the side somewhere so even if you compromise like the SQL Server it actually can't get to the other to the memory region because it doesn't exist because the hypervisor is actually limiting access to that to that enclave and in the case of sgx the actual isolation is all the way down to the CPU so the sgx does provide stronger isolation and stricter isolation but it does require specific Hardware under the hood so when you're deploying an Azure SQL database you would need to configure it what's cool about VBS is it has much more compute options and much more region options as well compared to sgx and it's frankly a lot easier to configure than sgx enclaves now with always encrypted it will require code changes it's just as simple as that if you've got an existing system and you want to use always encrypted you will need to change the code that being said if you're building a new application you should really consider using always encrypted for those sensitive columns it's much easier to design a system from the get-go understanding how always always encrypted works again remember the SQL Server process does not have the keys they're not there now in the case of The Enclave versions the key is actually in The Enclave it's pulled out of key Vault and so on and so forth but an unwrapped in The Enclave but the uh the actual encryption and decryption is done in The Enclave as well so it needs to query it can decrypt the data in The Enclave but it still Returns the ciphertext back to the customer so that's the only time it's actually exposed again but it's not exposed to the attackers it's not in the same process as a SQL Server so going back to our environment again you see we've got all the stuff that we had before but in the top right hand corner we've added always encrypted um and so we can have always encrypted columns so for example you know Healthcare information Social Security numbers anything that's considered either private healthcare information or personally identifiable information you can always you know can encrypt with always encrypted there's also SQL arba you know permissions and roles that you can apply from an authorization perspective and also row level security if you really wanted to and I haven't talked about row level security in any at all that's a whole nother discussion unto itself other than that nothing else nothing else changes always encrypted for the most part is about disclosure of data right it's reducing or you know limiting the the potential impact for disclosed data but it doesn't really address sort of bulk data integrity and that's where um other Technologies come into play that are available in in SQL so as I mentioned you know if you've got always encrypted so I talked about I'll talk about this in a bit more detail in the early 31 but you can actually take a cell in always encrypted and actually paste it into another cell the Integrity detection is actually at the cell level it's not at the it's not at the row level um that's it that's that can be important for some people if a user has enough access they may be able to do that maybe let's pull off that attack Maybe so well what about sort of bulk tampering in general like changing transactions right there's a demo that we gave like a World Cup um scenario where you're booking uh where you're betting on on on a team winning winning so for example you may have say the Netherlands versus Belgium right and you say Okay Belgium's gonna win two to one well what happens if the Netherlands actually wins 2-1 and you have a malicious Insider where you phone your malicious Insider up and say hey change my bet from Belgium winning to the Netherlands winning and so The militias Insider changes it and you you end up earning ten thousand dollars and you splits at 50 50 with the you know the person on the inside that's actually a real threat scenario it really is I mean not I'm not just you know betting on World Cup but just in general so in this case The militias Insider has full access to everything so how do you mitigate them so we have a technology built into Azure SQL database and Azure SQL sorry SQL Server 2020 SQL Server 2022 and it's also now in preview in SQL Mi and that is a technology called Ledger uh this Builds on top of temporal temporal tables and essentially what it does is on a Time by time basis on a short period of time it takes the data and it hashes that data and it uses sha-256 as the hash and then stores uh those hashes into a protected uh storage account this is this next part is critically important so the actual data structure is called a miracle tree doesn't matter what it is just a hierarchy of shot of hashes that's all it is and the reason why we do that is just for performance but it's critically important that you store that Merkel Tree on temp in a tamper-proof service and you really have two options um storage account with the immutability bit flipped so it's immutable storage or Azure confidential Ledger you can use either and they have essentially uh they are tamper-proof right there's not like you know it's hard to change it's like you can't do it um this is much better than on our back control right so you have an r-back control an inside admin can probably override that our back control make the changes in this example they if they do do it then the Merkel trees are invalid and we can actually show that you know hey this change was made um and we can see that you know there was tampering made to the actual transaction so I really can't stress this enough you've got to store the um the resulting data structures which are all automatic from SQL server or as SQL database and actually store them either in mutable storage or in Azure confidential Ledger so now let's go back to our scenario again and you see here it's still the same we've still got always encrypted up there and everything else that we have we've added two options uh all three things down the bottom the first is the actual um Ledger itself which is just a set of special tables and we can put a hardback policy on there and and use immutability as well right so confidential Ledger or a storage account with with the immutability bit flipped and there's also Auditors you can have a separate role that just does auditing to make sure that hey you know the audit logs look good and that's a special role that's also built into the product as well so I want to sort of segue really quickly into threat modeling and you'll see why I'm doing this in a moment so at Microsoft we're big Believers in threat modeling um I review threat models at least probably three or four minimum a week and what we're looking at is looking at the design of a system and the way we look at it through this lens through a thing called stride which is essentially what can attack an attacker do so for example I have a process a SQL Server process it's subject to spoofing like how do you know it's actually the correct SQL Server process and not something else and that's spoofing and then you mitigate spoofing with certain techniques then the tampering being about to change data repudiation is reneging on a transaction which is actually The Ledger scenario a lot of repudiation threats start start life as tampering threats information disclosure denial of service and elevation of privilege what's interesting about all of these is they're mitigated with these broad techniques so for example spoofing you mitigate with authentication and there's a whole bunch of different technologies that you can choose Believe It or Not we've actually talked about some of these Technologies in fact if I look at the diagram that we brought up before and I sort of overlay The Stride categories you'll see like for example when this user when a user connects to SQL Server how do we know it's a valid user that's a spoofing thread and you mitigate remember you mitigate it with authentication in this case we're going to use aad perfect when the server connects so when the client connects to the SQL Server how does it know it's actually the valid SQL Server that's a spoofing thread and we mitigate that with TLS fantastic that's a great mitigation data on the wire is subject to tampering and information disclosure we actually mitigate that with TLS so we've got a two for there right we actually mitigated two sets of vulnerabilities or two two types of threat with one technology which is TLS in this case if you look down the bottom you'll see that we've got the um The Ledger storage and you'll see that we've got things like tampering and repudiation um the T and the r they're mitigated by using by using Ledger and you can see all the way through this you've got various mitigations that come into play to mitigate those specific threats which are the stride categories one thing that's not in here is denial of service and you may say okay I'm going to just rely on the built-in Azure mitigations around the Nyla service but you can also opt-in for DDOS protections as well if you you know if you want but right now there's nothing in here that talks directly to sort of network level DDOS so this is believe it or not this is actually the start of a threat model where we're looking at the system from a sort of design perspective and making sure that we have all the appropriate mitigations in place so I hope that makes a bit of sense so some things for you to look at as you're designing systems I really want to stress here that the big Focus From the Azure security team the Azure database security team is always encrypted Ledger and Azure active directory for clients authentication it could argue TLS as well for Server authentication but that's kind of just there um and mainly because you have so many options for clients authentication that's why we really want to push um aad authentication because that way there's no credential being stored really important you understand some of these core security principles right you know the spoofing well how do you mitigate that with authentication what's the correct technology to choose and do understand how specific Technologies actually mitigate specific threats and is it the correct the correct technology to use in that specific scenario learn thread modeling if uh if it's something that's you know something unaware of you really should learn it learn the basics of it and go to microsoft.com sdl secure which stands for security development lifecycle there's lots of documentation up there about threat modeling big big fan of thread modeling and then finally you know apply these security skills to your own designs right so as you're designing something think about okay I've got a process here how to authenticate that I use TLS fantastic correct answer I got the data at rest you know what's the rbac policy is it that are encrypted you know if so where are the keys stored these are sort of questions that you're going to be asking yourself as you move through you know design of an environment and frankly you know someone's got to push this as well but you know go ahead and pick up a copy of the book design developing Security Solutions because you go through all of this in gory detail including threat modeling including you know cryptographic controls there's a big section uh chapter 13 on on database controls so with that I'm done I hope you found this this useful and thanks so much for tuning in

2023-05-27 17:35

Show Video

Other news