DP-300 Microsoft Azure Database – Implement security for data at rest and in transit

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, on the move. So we’re going to start with transparent Data encryption or TDE, TDE encrypts and then Denry 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’m in the Azure SQL database, but the server part of it. So not the database itself, but the server behind the database. And we’ve got limited viewability of this.

Now, if I go down to security and transparent data encryption. So the way that this works is 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 by 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 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’s not ones which are created through restore or database copying. Now, when we look at Azure SQL managed instance, it’s sent at the instance level and is inherited to 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, 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 up 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 so this changes between a service managed or a customer managed key vault. We’ve got Add, Hyphen, AZ, SQL Server key vault key. So that adds a key vault key to an SQL Server.

And then for the database we’ve got Set Hyphen AZ SQL database. Transparent data encryption. So this modifies the TDE property for a particular database. And you can can also use Rest API if you are programming. So this is transparent data encryption. So it encrypts data at the page level when in Rest. It is encrypted when written and is deenrypted when you read it.

2. 29, 33. implement object-level encryption and Always Encrypted

Now let’s say that you have got a table which people need to have access to, but it’s also got sensitive data. What can you do? Well, you can encrypt it using always encrypted. So this is available in all flavors of SQL, so as your SQL database, the managed instance and on virtual machines. So we have a table here, the sales address table table. And let’s say that the city and the address line one was sensitive. So what I’m going to do is I’m going to go to this table in SSMS, right, and click on it and go to encrypt columns. So we start with a splash screen so you can see 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 one 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 to the same pattern. Deterministic is therefore perhaps a bit more predictable than randomized. Why it doesn’t come to the same pattern each time. So why don’t I use randomized each time? Well, let’s say I was using randomized for the city and I had a join for instance. So I was joining the sales OT city, let’s say. 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 column. But of course that’s incredibly wasteful when you’ve got to join because I only want those particular cities which match the join. So deterministic allows for equality, joins group by indexes and distinct.

So distinct means to summarize it up just like group by but you can’t actually do the aggregations. Randomized prevents this. So for this example, I am going to have address line one as randomized and I’m going to have the encryption for city to be deterministic. Now, you’ll note that there is an exclamation here. So the collation, the way that the computer sorts will change, it needs to be in a bin two 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 set, it figure to store or perhaps more 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 key vaults stores secrets, so let’s click create. So I will choose a particular resource group, 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 key vault. But trouble is that’s already being used. So I’ll call it key vault DP 300. Is that okay? Looks it selects 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 modeled by keys, or that’s what HSM stands for. Now, the pricing of these two tiers is actually the same until unless you use the hardware security model, the HSM. So I’m just going to stick to standard. So then we can see number of days 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, the access policies. And it’s this bit which is 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 which are important are Create, Get, and List.

However, there are more in the cryptographic operations. You also need UnwrapKey, wrap, key verify, and sign. So it’s important that these are also checked. These are the ones which are necessary to create the column master key. If we also want to be able to encrypt and decrypt the data, we need those checked as well. But I’ll be coming back to this in the next video. I should say. By the way, the price, the prices are actually quite reasonable. I think it’s something like $0. 03 for 10,000 transactions. So you can see from this page that’s actually very reasonable for what is 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 important key permissions that you need. So just go through the rest. So public endpoint all network. So I’ll be able to access it. And then I will create just running a 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 our 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 next we are running the settings. So while encryption decryption is 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 do 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 city to 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 have a look at the reason for the error is because address line one, which I use, 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 randomized. You can’t be used in Equality joins group 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 again, address line one and city. 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 it does give you the randomization that you chose encryption, either randomized 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. Address line one is now this zero, x and so forth. The city is also encrypted, but because it was encrypted deterministically, you’ll notice that these first two, which both said both, have been encrypted in the same manner. So the 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 one 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 on Connect, I’ll go to options and always encrypt a tab.

So I’m going to check the enable. Always encrypted. Now in earlier versions you had to go into the additional connection parameters and type in column encryption setting equals enabled. 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. So I’ll connect and there we are connected. 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 finally 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 selected and I’m going to skip the authorized application and then very importantly click Save. So what I’m going to do now is exit SSMS because it might not check that things have changed, open it back up again. So log in again as Jane and have always encrypted enabled, write the query again. And here we do now have address line one and city. So next city Buffal. I’m going to say where the city’s? Buffalo because I want to want to retrieve those execute and it’s saying oh, there’s a problem and it’s something to do with encryption. What do I need to do? Well, I need to create a parameter. So I need to say declare my city has and I need to ensure I have got the exact one that I need nvarchar 30. So the parameter needs to be cryptid as well so that it’s looking for the encryption.

So I declare my city as an invocal 30 equals Buffal. And so now I’m looking for the parameter my city and it’s still saying okay, something’s still wrong, encryption scheme mismatch. What do I do now? There’s one more step we need to do. Going to city query options advanced and at the bottom we have got enabled parameterization for Always Encrypted. You may already have this checked already. We did this earlier on, but I’ve unchecked it 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 declare 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 getting too complicated territory. So just know that for always encrypted you really need to enable prioritization for it. But if you’re not using always encrypted then make sure that’s D checked because you may run into problems later on. Now, in terms of database permissions, you have got alter any column master key and alter any column encryption key. These are needed to create or delete these keys.

You’ve also got view any column master and viewer 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, Jane is the security administrator and the database administrator, then you can use SSMS like I’ve done. However, if this should be different people, so the security administrator does the keys but doesn’t necessarily have access to the database, and then the database administrator looks at the metadata about the key. 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 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 new Hyphen cell sign certificate. They’d import the module for SQL server. They would create the column master key using new Hyphen SQL certificate store column master key settings. They would generate a column encryption key using new Hyphen SQL column encryption key encrypted values. They would share the location of this with the DBA and that they would read the key data back to verify. So that would be the role of the security administrator.

So this in Power shell 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 create a SQL column master key settings with new Hyphen SQL column master key settings. Create a column master key metadata in the database with new Hyphen SQL column master key and create a column encryption key and encrypt it with 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 them 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 run. 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.

• Category: Uncategorized