Introduction and Overview
Track Data Changes
The course is part of this learning path
In the Information age data is the new currency and like anything valuable, it needs to be protected. Azure SQL and its environment provide a range of mechanisms for protecting your data from a multitude of hazards. The potential threats range from bad actors trying to steal information to unintentional human error corrupting your data. To cover all eventualities Azure provides pre-emptive protection in the form of network security, several types of data encryption, data classification, and vulnerability assessment services. After the fact protection is available in the form of built-in data change tracking. This course shows not only tells you about what protection is available for your database but also how to implement it.
If you have any feedback relating to this course, feel free to contact us at firstname.lastname@example.org.
- Learn what security components are available within Azure SQL
- Understand how these elements work together to provide a secure environment
- Learn how to implement infrastructure security
- Learn how to secure your data from external and internal hazards
- Learn how to implement data change tracking
- Anyone who wants to learn how to implement secure Azure SQL databases
- Those preparing for Microsoft’s DP-300 exam
To get the most out of this course, you have should a general understanding of the fundamentals of Microsoft Azure. Experience using databases — especially SQL Server — would also be beneficial.
The GitHub repository for this course can be found here: https://github.com/cloudacademy/azure-sql-data-security-dp-300
Managing network access via a firewall pertains to Azure SQL and SQL Server, either on-premise or running on a VM, and not to Azure SQL managed instance.
Looking first at Azure SQL, there are two levels or types of firewall rules. There are server-level rules and database level rules. When a connection request comes in from the Internet or another resource within Azure, it is the database-level firewall rules that are checked first to see if the connection’s IP address is within the allowed address range. If it is the connection is permitted, if it isn’t, then the server-level firewall rules are checked. If the connecting IP address is within the server-level firewall allowed IP range, then the connection is made, else it fails. As this architecture suggests, and in line with the principle of least privilege access, it is recommended to set the database-level firewall rules where client connections only need access to a particular database and not all databases on the server. Generally speaking, you would only use server firewall rules for server and database admin purposes or for connections that need access to all databases on the server. For both database firewalls and server firewalls, you can set up to a maximum of 128 rules on each. Let’s go into the Azure portal and see how we can configure the firewall rules from there.
I’ll go into the SalesData database. At the top, we can see set server firewall. Click on that to go through to the firewall settings page. You can see I’ve got one client IP address set up as the Home rule, which is why I can access the database from SQL management studio. Here I have Allow Azure service and resources to access this server. This is a blanket rule that essentially sets up a firewall rule behind-the-scenes that does what it says; allows all the Azure services and resources to connect to the database server and not just the database. So here’s the thing we went into this database, not the database server. But all rules that apply here are actually server-level rules. If I go out of here and over to the server and down to security, then firewalls and virtual networks, it’s the same deal. In fact, it’s the same page even if you go into the firewall rules through the database. In the portal, you are dealing with server firewall rules. You cannot set database firewall rules through the portal. Use SQL commands to set database firewall rules. I’ll connect to the dbmonitor server via SQL management studio and query the firewall rules table. Here we have the rule that I’ve set up at the server level, which is my IP address, and here we have the rule allowing all Azure resources and services to access this server. We can look at the database firewall rules similarly. And funnily enough, or not, we don’t have any because I only set the rules through the portal, so only server-level rules. Apart from interrogating the rules, you can also set rules. For the server level rules, you have the stored procedure
sp_set_ firewall_ rule, and sp_delete_ firewall_rule. It is the same for database-level rules. What I’ll do is replace my server rule with a database rule. I want to create the new rule before I delete the old rule, or I will lose access to the server and database.
When you set IP address rules, we’re talking in terms of ranges rather than a specific IP address, even if you are only allowing one IP address access. With that new rule in place, I can delete the server firewall rule, which needs to happen in the context of the master database. Going back to the portal, we can see the rule has disappeared. We can also eliminate this rule. So, the allow access to all Azure services and resources is not defined by the name. It’s defined by the fact that the start and end IP addresses are all zero. Having deleted that rule, we can see that we no longer allow access to Azure services and resources. I don’t want that, so I’ll create that server firewall rule, and after refreshing the page, we can see that it is turned back on. Even though we set a database level rule, we cannot see it within the firewall settings in the portal.
While you can use Windows firewall rules to allow or restrict access to an SQL Server instance, as you can with any other Windows resource, this would be a last line of defense and applicable only to IP addresses within your secure network.
There are several ways you can connect to an Azure SQL managed instance. An SQL managed instance must reside within a virtual network. If you do not have an existing virtual network, you can create one at the time of the managed instance deployment. Any virtual machines or resources that reside within the virtual network can connect to the managed instance with the appropriate connection string, even if they are on different subnets. It is possible to connect to an SQL managed instance from within another virtual network. The managed instance uses a private IP address within its own virtual network, so the initial connection must be to the managed instance’s network. What’s nice about the Vnet to Vnet connection is that the virtual networks don’t have to be on the same subscription. There are a couple of options for connecting multiple Azure virtual networks to each other. You can use Azure Vnet peering or a Vnet to Vnet VPN gateway. Of these two methods, Azure Vnet peering is preferred because it has little latency as it uses the Microsoft backbone network.
There are also a couple of options for connecting to an Azure SQL managed instance from outside the Azure network, that is from on-premise. You can create a site to site VPN connection that will allow you access to the managed instance’s virtual network, or you can use an Azure ExpressRoute connection. Azure ExpressRoute allows you to connect an on-premise network with Microsoft services like Azure and MS 365 using a registered 3rd party provider. ExpressRoute providers could be likened to a type of special Internet service provider that has direct connectivity to the Microsoft network. This means the ExpressRoute connection does not go over the public Internet and consequently offers more reliable and consistent connections with higher speeds and lower latency.
Let’s look at a couple of ways you can connect to an SQL managed instance. Firstly, I want to show you connecting via SQL management studio from an on-premise computer using a public endpoint. In the portal, I’ll go to my managed instance, and under security click on networking. Within networking, I’ll enable the public endpoint and click save, not forgetting to copy the host address and making note of port 3342. Next, I’ll go into virtual network subnet and then into subnets to find the name of the network security group. Now that I know that I can go into my network security group and create a new inbound security rule. I want to create a rule that has a higher priority than deny all inbound which is 4096. Click add to bring up the inbound security rule blade. I’ll enter 1300 as the priority, change the protocol to TCP, give it a name, and set the destination port range to 3342. Then click add. I said earlier that you don’t configure access to an SQL managed instance with firewall rules, and some of you might be thinking that these inbound security rules look a lot like firewall rules, which they do. However, technically speaking these rules aren’t on the managed instance but on the virtual network that the managed instance resides in. With the public endpoint enabled, and a rule on the virtual network to allow traffic on the right port we can now go to SQL management studio and connect to the managed instance. The connection string is the managed instance URL modified with the word public and a comma and the port number 3342 appended to it.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.