1. 28. implement Transparent Data Encryption (TDE)
Hello. And in this section, we’re going to look at how we can implement security for data at rest and in transit. In other words, constantly on the move. So we’re going to start with transparent data encryption, or TDE. TDE encrypts and then decrypts data at the page level at rest. So in other words, if you write data, it’s encrypted when written, and when you read data, it’s decrypted. Now, make sure you don’t confuse this with TLS (transparent layer security), which encrypts data when it’s in transit. So here I am in the Azure SQL Database, but in the server part of it. So not the database itself but the server behind the database.
And we’ve got limited view ability of this. Now, if I take the security and transparent data encryption route, So the way that this works is that it uses a database encryption key called a DEK, and in this case, it’s a symmetric one. What this means is that only a single key is needed to encrypt and decrypt the data. Now, you can have a service-managed key or you can bring your own key, OK? So, having a look at the service managed key, it is protected by the TDE protector using a service managed certificate, or it’s protected using an asymmetric key, which could be stored in the Azure key vault. So for an Azure SQL database, it is set at the server level, and new databases then inherit it. So new databases are encrypted by default. Now, I should point out that those are not ones that are created through restore or database copying. Now, when we look at an Azure SQL managed instance, it’s sent at the instance level and is inherited by all encrypted databases. And it can’t be used to encrypt system databases.
So you can see here at the server level of this database, we can say what sort of transparent data encryption we’re going to be using, and then when we go to the database level, so transparent data encryption, we can turn it on or off. Now, you should note that we can’t go down to this in the managed instance; you can only do this in the Azure SQL database. Now involve we can use TSQL. So we can just say “alter database,” “name of database,” and “set encryption on or off.” And it does take a bit of a while. So this is the way that you can use it in a Dura SQL-managed instance. However, you can’t switch the TDE protector to a key in the key vault in TSQL. Now, there are other ways of doing this. You can use PowerShell. So PowerShell is what we get here when we go to the cloud shell. So there are three commands I want to point out: set Hyphen AZ SQL Server transparent data encryption protector, which changes between a service-managed and a customer-managed key vault. We’ve got Add, Hyphen, AZ, and SQL Server key vault keys. So that adds a key vault key to an SQL Server. And then there’s the database. In the SQL database, enter the hyphen AZ. transparent data encryption. So this modifies the TDE property for a particular database. If you are programming, you can also use the REST API. So this is transparent data encryption. When at rest, it encrypts data at the page level. It is encrypted when written and deencrypted when you read it.
2. 29, 33. implement object-level encryption and Always Encrypted
Now let’s say that you have a table that people need access to but that also contains sensitive data. What can you do? Well, you can encrypt it using Always Encrypted. So this is available in all flavours of SQL, such as your SQL database, the managed instance, and on virtual machines. So we have a table here with the sales address table table. And let’s say that the city and the address line 1 were sensitive. So, in SSMS, I’m going to go to this table, right-click on it, and select encrypt columns. So we start with a splash screen, which you can see is designed to protect sensitive information such as credit card numbers. So now you have to choose which columns are sensitive. So we’re going to say the address, line 1, and the city. so I’ll just check them. And then there’s an encryption type, and the type is either deterministic or randomized. What does this mean? Well, deterministic means that every time you encrypt it, it will come out with the same pattern. As a result, deterministic is possibly more predictable than randomized. why it doesn’t come to the same pattern each time. So why don’t I use randomization each time? Well, let’s say I was using randomization for the city and I had a join, for instance. So, let’s say, I was joining the salesott city. Well, how could I find out where a city was called Bubble? The only way I could do that would be to unencrypt all of the columns. But of course that’s incredibly wasteful when you’ve got a join, because I only want those particular cities that match the join.
So deterministic allows for equality, joins groups by indexes, and is distinct. So distinct means to summarise it in the same way that group by does, but you can’t actually do aggregations. Randomization prevents this. So for this example, I am going to address line one as randomized, and I’m going to have the encryption for city be deterministic. Now, you’ll note that there is an exclamation point here. So the collation, the way that the computer sorts will change, needs to be a bin two, a binary two collation. So in this case, Latin one, general bin two. So I’ll go next. Where do we want the key, the column master key, to be saved? So we can either choose the Windows setup for forgetting to store or, perhaps better for an online thing, the Azure Key Vault. That means we need to have an Azure Key Vault. So let’s go into the portal, and let’s go into Keyvolt. So I’m going to create a new key vault. So keys store secrets. So let’s click “create.” So I will choose a particular resource group, and I’ll create a new one. So this is the Key Vault resource group. So enter the name. So you can see the name contains alphanumeric characters and dashes. So I’ll call it a key vault. But the trouble is, that’s already being used. So I’ll call it Key Vault DP 300. Is that okay? It appears to select the region. Well, I’m going to store it close to the database. So United Kingdom South, and then the pricing tier.
So, standard or premium? Premium allows for hardware security modelled by keys, or that’s what HSM stands for. Now, the pricing of these two tiers is actually the same, unless you use the hardware security model, or HSM. So I’m just going to stick to the standard. So then we can see the number of days left to retain deleted vaults and pure protection. In other words, is there a mandatory retention period for deleted vaults? So I’m going to say “enable.” Next are the access policies. And it’s this bit that’s really critical for storing the secrets for Always Encrypted. So we have these different key permissions. So get, list, update, create, import, delete, recover, back up, and restore. So the ones for this particular thing that are important are Create, Get, and List. There are, however, more in cryptographic operations. UnwrapKey, wrap, key verify, and sign are also required. So it’s important that these are also checked. These are the ones that are necessary to create the column master key. If we also want to be able to encrypt and decrypt the data, we need to check those as well. But I’ll be coming back to this in the next video. I should say. The prices, by the way, are quite reasonable. I think it’s something like $0.05 for 10,000 transactions. So you can see from this page that it’s actually very reasonable for what it’s going to be used for. So you can see, you only start paying when you have these hardware security modules. Start paying in terms of dollars. So these are the key permissions that you need.
So just go through the rest. As a result, all public endpoints are networked. So I’ll be able to access it. And then I will create a simple validation, making sure that everything checks out. That’s fine. And click “create.” So now it’s deploying. So I’ll just pause the video until it’s finished. So that just took about 30 seconds, and the deployment was complete. So going back to “Always encrypted,” I’m going to sign in to Microsoft Azure. Sign there. So it’s now saying I can use whatever subscription. And here’s the list of the Azure key vaults I can use. So that’s great. So we’re going to run the settings now. So while encryption and decryption are in progress, write operations should not be performed. Potential for data loss So really, do be careful. And also, I’m running a very low skew. That’s the particular model that I’m using. And it says to be careful. So again, I’m going to now proceed to finish. So there’s a summary of what I’m going to do. So I’m changing the city in my deterministic address line. want to be randomized, but in addition, we’re also creating a new master key. and a new encryption key as well. So click “finish,” sign into my account, and it’s now encrypting. So, as you can see, it’s taking a bit of a while, so I’ll pause the video until it’s finished. Now, this resulted in an error, and let’s take a look at the reason for the error: address line 1, which I used for randomized encryption, is not available as a key column in a constraint index or statistics. And it’s being used in a non-clustered index. So what I’m going to do is go into my index list, and I’m going to drop this particular index. So remember what I said about randomization. You can’t be used in equality joins groups by indexes and distinct.
Well, this is an example of the fact that you can’t use it in an index. So let’s go back into this and encrypt the columns again. So go through these screens. So, address line one and city once more. No additional configuration is necessary because I’m using existing keys. I’ll proceed now and again; I’ll pause the video and wait for the encryption operations to be finished. And now, this time, the encryption operations have been successful. So you can see the report here if you are interested. So the advantage of this is that it gives you the randomization that you’ve chosen in your encryption, either randomised or deterministic. So let’s click “close” and let’s run this again. Will I be able to get the information? Now, notice what’s happened. Line one of the address is now this zero, x, and so on. The city is also encrypted, but because it was encrypted deterministically, you will notice that these first two, which both say bottle, have been encrypted in the same manner. So deterministic encryption works. So this is how we can implement “Always encrypted.” In the next video, we’ll have a few nuances about how we can use always-encrypted.
3. 29, 33. More about Always Encrypted
So in the previous video, we implemented “always encrypted” for the city and “address” columns of the address table. But how can we actually see what’s actually there? Well, I can do this if I exit SSMS, and then I’ll open it up again. Now I’ll login as Jane Gain. But, before I click Connect, I always go to options and encrypt a tab. So I’ll go check the enable.
Always encrypted. Now in earlier versions, you had to go into the additional connection parameters and type “enabled” in the column encryption setting. But you don’t have to do that with the current versions of SSMS. That’s just there in case you have to use an older version. The term “electronic commerce” refers to the sale of electronic goods. So I will go into a new query, make sure I’m in the right database, and select Star from the address table. So we should have failed back, and fine, we can move on. Except when I sign in again, we’ll find that’s not the case; there is an error, and here’s the error: fail to decrypt a column encryption key by using the Azure Key Vault. So what does that mean? Well, let’s go back to the portal, and if I go into access policies, you can see that we have an access policy for Philip Burton, but I’m currently logged in as Jane, so that’s not going to work. So what I need is to add a new access policy. So I’m going to add all of these key permissions, and I’m going to select a principal.
So that would be Jane, so she’s selected, and I’m going to skip the authorized application and then, very importantly, click Save. So what’s I going to do now? is exiting SSMS because it might not check that things have changed, open it back up again. So log in again as Jane and, with Always Encrypted enabled, write the query again. And now we have address line one and city. So the next city is Buffalo. I’m going to say where the city is. Buffalo because I want to retrieve those executed files and it’s saying, “Oh, there’s a problem and it has something to do with encryption.” What do I need to do? Well, I need to create a parameter. So I need to declare my city as and I need to ensure I have the exact one that I need, varchar 30. So the parameter needs to be encrypted as well, so that it’s looking for the encryption. So I declare my city as an in vocal 30 equals Buffal, and now I’m looking for the parameter my city, and it’s still saying something’s wrong, an encryption scheme mismatch. What do I do now? There’s one more step we need to take. Going to “City Query Options,” we have advanced options, and at the bottom we have enabled parameterization for Always Encrypted. You may already have this checked already.
We did this earlier on, but I’ve unchecked editing so you can see what happens. So click OK. And now when I run, you can see that there is a squiggly blue underline on this declaration of this parameter. So it’s now saying that it will be converted into something else, which will allow this to run. So you can imagine that we’re starting to get into complicated territory. So just know that for always-encrypted files, you really need to enable prioritization for them. But if you’re not using Always Encrypted, then make sure that’s checked because you may run into problems later on. Now, in terms of database permissions, you have got to alter any column master key and alter any column encryption key. These are needed to create or delete these keys. You can also define any column master and any color encryption key definition. So this is needed to access or read the metadata of these keys, to manage them, or to query encrypted columns. And if you want to grant somebody any of this, then you use something like “grant view any column master key definition to” and the name of the user. Now, there’s one final thing that I want to talk about. We’ve always encrypted. The way that we’re doing this here in SSMS means that we’re not having any role separation. So what is “role separation”? Well, there are two people involved in this.
There is the security administrator. So the security administrator generates column encryption keys and column master keys. So that person needs access to the keys and the key store, but that person doesn’t need access to the database. The database administrator, or DBA, manages the metadata about the keys in the database, but they do not need access to the keys or the key store. Now the question is, should the security administrator and the database administrator be different people if you’re happy with them being the same person? So in other words, if Jane is the security administrator and the database administrator, then you can use SSMS like I’ve done. If this is done by different people, the security administrator handles the keys but does not necessarily have access to the database, and the database administrator examines the key’s metadata. If they need to be different people, then you have to use PowerShell. If they’re the same person, you can still use PowerShell, but you can also use SSMS and go to incorrect columns. Now, using PowerShell means learning a lot of the language that you would need. And here’s an example of what’s needed for the security administrator: They would create a column master key using a new Hyphen cell sign certificate. They’d import the module for SQL Server.
They would create the column master key using the new Hyphen SQL certificate store column master key settings. They would generate a column encryption key using the new Hyphen SQL column encryption key to decrypt the encrypted values. They would share the location of this with the DBA, and they would read the key data back to verify. So that would be the role of the security administrator. So this in PowerShell can be kept separate from the DBA, which is getting the location of the column master key. Importing the SQL Server module, connecting to your database with Get Hyphen SQL Database, and creating SQL column master key settings with new HyphenSQL column master key settings Create a column master key metadata in the database with a new Hyphen SQL column master key, and create a column encryption key and encrypt it with a new Minus SQL column encryption key. So hopefully you can see from that that the two roles can be separate. And if you need it separate, then the important thing is that you can only use PowerShell if you don’t need it separate, if you don’t need role separation. If you’re happy for the security administrator and the database administrator to be the same person, then you can do this in SSMS. So this is how you implement Always Encrypted: Now, before I go on to the next video, I’m going to go into “encrypt columns again,” go to “column selection,” and I’m going to change these to a normal encryption. I’m going to get rid of the encryption by just saying plain text and just running. That is going to be decrypted. And the reason I’m doing that is because it does take a fair bit of compute power to do these select queries. So I’m going to remove Always Encrypted before I go into the next video.
4. Always Encrypted versus Transparent Data Encryption
In this video, we’re going to compare and contrast. Always encrypted and transpound data encryption, or TDE. What’s the difference between them? Well, TDE is an older technology, so you can see it was first around in SQL Server 2008, whereas Always Encrypted started in 2016. Now, you do require the Enterprise edition or Azure SQL database to use TDE. Because you do not have Always Encrypted, it is now more widely available, both for free. in an Azure SQL database. Both protect data at rest. However, TDE does not protect data in use. So, for instance, when it’s being transported, you use Transport Layer Security. However, Always Encrypted is encrypted all the way to your client, and the data is encrypted and decrypted on the client side.
For the TDE, it’s encrypted or decrypted on the server, so therefore it is already decrypted by the time it goes on the move. Always Encrypted protects data from SQL administrators and other admins, or at least it can do that. You’ve seen how we can just temporarily turn it off when connecting. The column is not always encrypted by TDE. At level 1, TDE encrypts the entire database. TDE is transparent to the application. In other words, no additional programming is required. Always Encrypted, however, is only partially transparent to the application. You do have encryption options, whereas TDE is just on or off the encryption key method. Both of them use customer-managed keys. However, TDE also uses service-managed keys. So you just leave it to your SQL database. And Always Encrypted protects the keys in use, and the driver is required. You could see we couldn’t get any information from that particular table or from those columns when we didn’t say column encryption settings were enabled. So this is a quick summary of the difference between Always Encrypted and TDE.
5. 30. implement Dynamic Data Masking
In the previous few videos, we had a look at tables that were always encrypted, which meant that certain columns would be out of bounds for certain people. But what if it was only a sorcerer, for example? Maybe I want people to be able to see the first few characters but not the rest of them. Maybe I’ve got a credit card where I want certain people to be able to see the last four digits, and this can be done using dynamic data masking. So let’s go into the portal, and we’ll go into our database. So not the server, but the database itself. and we scroll down to dynamic data masking. This is under the securities part. So this encrypts the column. This is called “column level encryption” (CLE).And here you can see that the computer’s already said, right, we’ve got all of these columns. You might want to consider masking them. Consider masking your first name, last name, and email address. “Actually, we have the GDPR, and we have the California Consumer Privacy Act,” you might say to some of these. We’ve got other things.
Maybe we should be protecting these things. So you can see that there are a certain number of columns that the computer has gone through; okay, maybe these should be protected. And if that’s the case, you can just click “Add mask.” But I’m going to add a cluster mask. I’m going to go into the address and say, “Okay, you can see the first few characters.” So the house number, for instance, So I’m going to click on “Add mask.” So here we can select the schema, the table, and the column. And then we can select a masking field format. So let’s have a look at these. First of all, we’ve got the default value. So this would be 0 for numeric datatypes, 1 January 1900 for data and time datatypes, and XXXX for string data types. And you can use fewer XS. We have the credit card values if there are fewer than four characters. So you can expose the last four digits of the credit card with a constant string prefix, then send an email. So this exposes the first letter, replaces everything else with a constant prefix, and then a number. So this would be a random number range. So a random number between two boundaries, followed by a custom string with a prefix, padding, and suffix. So this shows the first X digits, the last Y digits, and something in the middle. So let’s take a look at what I’ve got. Let’s say I want to have the first five digits exposed. So I’m going to say, “Give me five digits and then a custom padding string.” So I’ll put “street address,” one word, and then how many characters at the end do I want to expose? Well, I’m going to say zero. So I’m going to add this, and I’m going to add additional columns as well. just from here.
So let’s go to the city and add a mask. and you can see there is a default value there. Now you can also say certain people should be excluded from the masking; in other words, they should always be able to see it. But notice this bit: administrators are always excluded. So Jane, my administrator, will always be able to see these columns. So I’m going to click save, and it’s saving, as you can see very quickly. So now let’s go back into SQL Server Management Studio and just have a look. And you can see that Jane is unaffected as an administrator; she can see everything. So next, I’m going to connect as Susan. So just type in a password, and Susan has access to the address. So I’m going to create a new query for Susan. You can see Susan say this down here and execute. And now you can see the address, which is 8713 Street, which is the city’s ZIP code. Now you can do this in SQL if you wish. So change the table, the table’s name, the mast before that, the column, and the column’s name. So I’m going to choose the state and province. So here I can do default, for example; for other alternatives, I could do partial, so that’s the custom one that we’ve seen previously; I can do email; or I could do random things like that. So let’s execute that. Let’s go back to Susan. And now we can see the state and province are also hidden from view. So that’s basically all there is. Let’s just go back to the portal. So you can see that you can have some users excluded for masking.
So I could type in, say, Susan at FILECATS on Microsoft.com, and if I wanted to put in multiple people, I could put in a semicolon. So let’s save that, go back into Susan, and now she can see everything. I’m also going to say grant unmask two and enter Susan’s email address or username, which should work as well. Similarly, if I want to unmask, that is, reapply the mask, I am revoking unmask. And so, having done that, we’re back to the masked version. And you can also do that with rolls as well. You can also use PowerShell for this. We’re talking about the Get and Set Azqldatabase data masking policy and the Get and Set Azqldatabase data masking rule. So this is dynamic data masking, where people have access to the data in terms of having permissions to select, but you might not want them to be able to select everything. So we can use dynamic data masking to hide some of it. And don’t forget the various things we can be using. We can use a default value, a credit card value (last four digits), email the first letter number, that’s a random number range, and a custom string. How many letters at the beginning and the end, or how many characters you want to show. And then there is your padding, which is a custom string in the middle.
6. 32. configure server and database-level firewall rules
So I’m going to navigate to my Azure SQL Database Server, not the database itself, and scroll down to firewalls and networks. So, let’s do a loop. To begin with, you have public network access, which is advantageous. You can specify how secure you want Azure to be if you just want it to be accessible, such as via the Internet. We discussed the connection policy in a previous video, and you can allow Azure Services and resources to connect to this server. His server. Set swine and t to no instead, and then create a readdress. Readdress. It is called “deployment” because it must connect and then allow access to an address. Addictive language is required. Has to be required.
As a result, respond risers act’s “m,” “timid all depends on how much security you require. I was now discussing firewall rules and databases. All rules apply to all levels. Well, different server-level firewall rules provide users with access to all Antabuse servers; this is also true for databases, which, aside from the items listed here, do not have a server to manage them. them. If server-level file rules apply to individual databases, database file rules are checked first. uses.
So let’s make a new rule. rule. I’ll call it the “firewall rule” and the “wall rule,” and I’ll pick some addresses. So I’m going to start at 1, or make it 1034, going all the way to 35. So I’ll go, and you’ll go, and you’ll be able to see the updated fireworks display. Previously, you could set a zero rule that went from zero to zero. However, as you can see, it is now enabling “allocating Ser Resources resource settings,” so you no longer need to go through that portal.
Ultimately, not everyone will agree. To do this, you will need Of course, the results of SQL Server Contributor or SQL Security Manager do not include Azure SQL Databases, which will be able to dwell on them as well. It applies to all databases on L Database, whether single databases or databases with multiple databases. This is not true for Azure SQ instances. Sims imply now uses the Sys firewall rules select star to display the current server-level IP firewall rules (Internet Protocol addresses). You can see that we have enabled all Windows to act as your IPS. So we have my new firewall rule from zero to zero, and you can see the creation dates as well.
However, that’s not very useful; that’s not when it was created, but when it was last modified, it appears, because you create them all in milliseconds. You can now use the rule pall to create a firewall rule in TSQL. As a As a result, there are three arguments: For this, argument must master this. For this. So far, we’ve got a starting IP address, an IP address, and an address. Now, it is not very obvious that the capital N at the beginning of the name changes it into an Nchar or NVAR char as opposed to simply being a varchar.
It would be in just type lid if I ran it without the capital N at the start. noamevalid. As a result, many storage parents don’t mind if you don’t inland them, and it will be converted, converted, converted, and converted. inverted. Throne anthem. As can be seen, a new made. was made. And then back to fire virtual. Virtual You’ll recall that I mentioned creating a network on a network on the Microsoft Azure Portal he etc. created. Now to delete it, it’s the store rdoururule, the wall rule, wall rule and you just name. the name. So I just want to show you how we did it. It. It is also in the system rules. So if I delete it and then refresh this, my firewall rule is no longer active. I should mention that there are other options, such as using PowerShell, which is the computer interface, or the Rest API if you are programming. ramming. Right, so what I’m going to do now is look at database rules. So here I am, inside the server’s firewalls and virtual network. So let’s go to the database now. So I go down to Secure go and disable the firewalls, only to discover that there isn’t one. The portal does not support firewall rules.
It can only be done as we have previously done servers orotund servers and There’s al the Rest, the Rest, and the Rest API, as well as TSQL, elements, and PCLI. Rest API. You must have the Control database installed at the root level, and first firewall the root level level fire. So let’s work out the firewall rules for this database. So this is only for this database. Actually, I’m going to change “get it from here” to “here300GGoH.” re300ggo. So now I can use the firewall rule exactly as written. And if we look at the database of theme, adhere. That’s fine; delete it exactly as written. As a result, before dechevalizing the database firewall rule, SP creates it. These are the hoseatingerver, eat server, and database leave rules. ll rules. So in the portal, you can do this for servers or If we go into firewalls and virtual networks, you can’t do this for databases. You will have to use another way, such as TSQL. Server-level firewall rules offer users and apps access to all the databases there. Database firewall rules provide information about an individual or application.
