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

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.

You don’t in Always Encrypted, so it’s now gone more widely, both for free. In 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. You’ve seen how we can just temporarily turn it off when connecting. TDE doesn’t Always Encrypt encrypts at the column. At level, 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 and off the encryption key method. Both of them use customer managed keys. However, TDE also uses service managed keys.

So you just let as your SQL Database do with it. 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 setting equals enabled. So this is a quick summary of the difference between Always Encrypted and TDE.

5. 30. implement Dynamic Data Masking

In this video, we’re going to look how to configure server and database level firewall rules. So why do we need firewall rules? Well, if we don’t, either everybody will be able to contact your database. That’s a security risk, or nobody would be able to, and that’s a usability risk. By default, all connections to the server and database are rejected. SQL Database communicates off report 1433. So let’s have a look and see where these firewalls are. So I’m going to go into my Azure SQL Database server, so not the database itself as server, and I’m going to scroll down into Firewalls and virtual networks. So let’s have a look at the options. First of all, you can deny public network access, so that could be useful.

If you just want Azure to be able to connect to it, then you can say how secure you want the TLS to be. That’s transport layer security. We’ve already talked about the connection policy in a previous video, and you can allow Azure Services and resources to connect to this server. If not, you can switch it to no and then create a reserved IP address. A classic deployment, it’s called for the resource it needs to connect and then allow access through the IP address. A public IP address would be needed. Therefore, for each resource that’s more secure. Morphidly, it depends how secure you need. Now I was talking about server firewall rules and database level firewall rules. Well, what’s the difference? Well, serverlevel firewall rules offer users and apps to have access to all of the databases in that server.

This is also the case for Azure SQL Database, which doesn’t have a server that you can manage apart from the things that you can see here. So if server level file rules are for all databases, database file rules are for an individual or app, and database rules are checked before server level rules. So let’s set up a new rule. So I’m going to call this my new Firewall rule, and I’m going to choose some random IP addresses. So I’m going to start at 86, 132, one, or make it 1034, going all the way to 35. So I’m going to say that and you can see updating firewall rules. Done. Now it used to be that you could set up a zero rule, so going from zero to zero.

But as you will see, it’s the same as turning on allow Azure Services and Resources to access this setting, so you no longer do that through the portal. Now obviously not everybody will be able to do this. You will need SQL Server Contributor or SQL Security Manager role, or of course the owner of the results that contains the Azure SQL Server will be able to do this as well. It applies to all databases on the server on Azure SQL Database, whether single or pooled databases.

This doesn’t apply to Azure SQL managed instance. Now let’s go to SSMS. So if you want to check the current server level IP firewall rules, internet Protocol addresses, you just use this select Star from Sys firewall rules. So here we can see our allow all Windows as your IPS. So from zero to zero and we have my new firewall rule and you can see the create dates as well. However, that’s not very useful, that’s actually not when it was created, but when it was last modified, it seems, because I didn’t create them all in the same millisecond. Now, if you want to create a new firewall rule in TSQL, then you can use Spall rule. So it contains three different arguments and you will need to be in the Master database for this. So we have a name, a start IP address and an end IP address.

Now, not it is very straight the capital N that you can see at the beginning here, that changes the name into an Nchar or NVAR char as opposed to just being a varchar. If I was to run it without the capital N at the front, you’ll see, not valid. So we need that capital N. Quite a lot of storage procedures are fine with you not including it, and then it’s going to be implicitly converted. This isn’t one of them. So here you can see that this new one has been created. And if I go back into my Firewalls and Virtual Network on Microsoft Azure Portal, you’ll see when I refresh the page, that here is my rule that I’ve just created. Now to delete it, it’s the store procedure SP delete firewall rule and you just need the name. So I just want to show you how we recreated it.

There it is also in the system firewall rules. So if I delete it and refresh this, then my firewall rule is now gone. I should point out there are other ways of being able to do this as well. You can use PowerShell CLI, that’s the command line interface, or the Rest API if you want to do programming. Right, so what I’m going to do now is look at database firewall rules. So I’m here in Firewalls and Virtual network of the server. So now let’s go to our SQL database. So I go down to security and I go down to Firewalls and oh, it’s not there. You cannot set database firewall rules using the portal. It can only be done as we have previously done it.

So TSQL statements and PowerShell CLI and rest API. You will need the Control database permission at the database level and you will need to set up a server level firewall rule first. So let’s have a look at this database underscore Firewall underscore rules. So this is for this one particular database. In fact, I’m going to change it from Master to DP 300. There we go. So now I can set up a firewall rule exactly the same format. And if we now have a look at the database firewall rules. That’s fine and I can delete it in exactly the same way. So SP set database firewall rule and SP delete database firewall rule. You’ll notice that the server ones don’t have spat Server.

It’s just spat firewall. So this is how you can create server and database level firewall rules. So in the portal, you can do this for servers or SQL Server database Server if we go into firewalls and virtual networks however, 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 to have access to all the databases there. Database Firewall rules offer an individual or app.

6. 32. configure server and database-level firewall rules

In this video, we’re going to look how to configure server and database level firewall rules. So why do we need firewall rules? Well, if we don’t, either everybody will be able to contact your database. That’s a security risk, or nobody would be able to, and that’s a usability risk. By default, all connections to the server and database are rejected. SQL Database communicates off report 1433. So let’s have a look and see where these firewalls are. So I’m going to go into my Azure SQL Database server, so not the database itself as server, and I’m going to scroll down into Firewalls and virtual networks. So let’s have a look at the options. First of all, you can deny public network access, so that could be useful.

If you just want Azure to be able to connect to it, then you can say how secure you want the TLS to be. That’s transport layer security. We’ve already talked about the connection policy in a previous video, and you can allow Azure Services and resources to connect to this server. If not, you can switch it to no and then create a reserved IP address. A classic deployment, it’s called for the resource it needs to connect and then allow access through the IP address. A public IP address would be needed. Therefore, for each resource that’s more secure. Morphidly, it depends how secure you need. Now I was talking about server firewall rules and database level firewall rules. Well, what’s the difference? Well, serverlevel firewall rules offer users and apps to have access to all of the databases in that server.

This is also the case for Azure SQL Database, which doesn’t have a server that you can manage apart from the things that you can see here. So if server level file rules are for all databases, database file rules are for an individual or app, and database rules are checked before server level rules. So let’s set up a new rule. So I’m going to call this my new Firewall rule, and I’m going to choose some random IP addresses. So I’m going to start at 86, 132, one, or make it 1034, going all the way to 35. So I’m going to say that and you can see updating firewall rules. Done.

Now it used to be that you could set up a zero rule, so going from zero to zero. But as you will see, it’s the same as turning on allow Azure Services and Resources to access this setting, so you no longer do that through the portal. Now obviously not everybody will be able to do this. You will need SQL Server Contributor or SQL Security Manager role, or of course the owner of the results that contains the Azure SQL Server will be able to do this as well. It applies to all databases on the server on Azure SQL Database, whether single or pooled databases. This doesn’t apply to Azure SQL managed instance. Now let’s go to SSMS. So if you want to check the current server level IP firewall rules, internet Protocol addresses, you just use this select Star from Sys firewall rules. So here we can see our allow all Windows as your IPS. So from zero to zero and we have my new firewall rule and you can see the create dates as well. However, that’s not very useful, that’s actually not when it was created, but when it was last modified, it seems, because I didn’t create them all in the same millisecond.

Now, if you want to create a new firewall rule in TSQL, then you can use Spall rule. So it contains three different arguments and you will need to be in the Master database for this. So we have a name, a start IP address and an end IP address. Now, not it is very straight the capital N that you can see at the beginning here, that changes the name into an Nchar or NVAR char as opposed to just being a varchar. If I was to run it without the capital N at the front, you’ll see, not valid. So we need that capital N. Quite a lot of storage procedures are fine with you not including it, and then it’s going to be implicitly converted. This isn’t one of them. So here you can see that this new one has been created.

And if I go back into my Firewalls and Virtual Network on Microsoft Azure Portal, you’ll see when I refresh the page, that here is my rule that I’ve just created. Now to delete it, it’s the store procedure SP delete firewall rule and you just need the name. So I just want to show you how we recreated it. There it is also in the system firewall rules. So if I delete it and refresh this, then my firewall rule is now gone. I should point out there are other ways of being able to do this as well. You can use PowerShell CLI, that’s the command line interface, or the Rest API if you want to do programming. Right, so what I’m going to do now is look at database firewall rules. So I’m here in Firewalls and Virtual network of the server. So now let’s go to our SQL database. So I go down to security and I go down to Firewalls and oh, it’s not there. You cannot set database firewall rules using the portal.

It can only be done as we have previously done it. So TSQL statements and PowerShell CLI and rest API. You will need the Control database permission at the database level and you will need to set up a server level firewall rule first. So let’s have a look at this database underscore Firewall underscore rules. So this is for this one particular database. In fact, I’m going to change it from Master to DP 300. There we go. So now I can set up a firewall rule exactly the same format. And if we now have a look at the database firewall rules. That’s fine and I can delete it in exactly the same way.

So SP set database firewall rule and SP delete database firewall rule. You’ll notice that the server ones don’t have spat Server. It’s just spat firewall. So this is how you can create server and database level firewall rules. So in the portal, you can do this for servers or SQL Server database Server if we go into firewalls and virtual networks however, 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 to have access to all the databases there. Database Firewall rules offer an individual or app.

• Category: Uncategorized