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
Connecting to an SQL managed instance via a public endpoint is useful, but not the most secure method and definitely not how you want to connect other Azure resources to your database server. Connecting from within the same virtual network as the managed instance doesn’t require any special setup using the private endpoint. Let’s see how we can join 2 virtual networks that are from different subscriptions and tenants. I’ve created a new VM with the associated virtual network under a different subscription and login, just going with the default settings. There is one important restriction to note for this configuration. It currently only works when the virtual networks are in the same region. In a nutshell, the process of peering the two networks together involves telling each network about the other one with their resource IDs and assigning the network contributor role to a guest user from the other subscription.
Now that my second virtual network is been created, I’ll go into access control and create a new role assignment giving the network contributor role to the user from the other subscription. With the role assignment saved, I can add the peering. Click on peering then add peering and give the peering a name. Select the Resource Manager deployment model and check I know my resource Id. I’ll go back to the managed instance subscription and grab the resource ID from that virtual network. Back in the Vnettovnet network, I’ll paste in the resource ID from the other virtual network. Next, select the directory from the drop-down list and click authenticate, followed by OK. To complete the peering, it’s a case of rinse and repeat with the other virtual network. Go into access control and assign the network contributor role to a guest user from the other subscription. Again, go into peerings and click add. Click resource manager as the deployment mode, check I know my resource ID, and paste in the resource ID from the other network. As before, choose the directory from the drop-down list and click authenticate. If I put my two browser windows next to each other, we can see the network peerings referencing the other networks. To make use of the virtual network peering let's connect to the SQL managed instance from the VM in the other subscription. I’ll RDP into the VM and open up SQL Server management studio. This time I can connect with the private endpoint connection string which I’ll grab from the SQL managed instance portal. The private endpoint is connecting through port 1433, the typical SQL Server TCP port. I’ll paste the connection string into the server name and use SQL Server authentication to log in. There we go, connecting to an SQL Managed instance across 2 virtual networks using Azure VNet peering.
Another scenario that often crops up is integrating on-premise data sources with cloud-based applications or functionality. One of the ways to implement this connectivity is the Azure data Gateway. This is a piece of software that is installed in an on-premise computer and allows you to integrate with Azure services using your Azure credentials, as in an active directory login. Once you have installed and registered the gateway software on a local machine then you can register the gateway with an Azure service like Logic Apps or Power BI and integrate your on-premise data source. Let’s go through the process of registering an on-premise SQL Server database as a gateway data source with Power BI, and then doing the same through the Azure portal.
To set up the gateway the first thing we need to do is download and install the gateway software. You can download it from Microsoft.com, but it just so happens that there is a download link next to the settings menu within Power BI, so I’ll just download that now. There are two versions of the gateway software, the standard or enterprise version or the personal version. I’ll accept the terms and conditions and hit install. This does take a little while. I’m going to register the gateway using an Azure active directory user, as opposed to a Microsoft.com login. I’ll give the gateway a name and enter and confirm a recovery key. As you can see the gateway is region bound, and in this case bound to the Australia East region. Under diagnostics, we can test connectivity. Now that the gateway has been installed and registered, we can set up the other end of the connection within power Bi. Based on my credentials, I’m using the same ones as I did to register the gateway, power Bi can see the gateway. Next, I’ll set up an SQL Server data source to use the gateway. I’ll give it a meaningful name like local SQL Server and select the data source type from the list. The server is my local machine's name, and sales data is one of the databases on the computer. As you can see when using windows authentication, I need to supply the username as domain, which is my local machine, and the username. And there we have it, a successful connection. You can also register the on-premise data Gateway within Azure, by creating an on-premise data Gateway resource. I’ll give the resource a name and create a new resource group. You can see with the location set as East US there are no gateway installations discovered. When I change the location to Australia East, matching that of the installed gateway, then my power Bi gateway installation appears. Next, I’ll create it and we can go in and have a look at my on-premise data resource and see that the status is configured.
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.