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
You can choose to encrypt particular columns in a table. This feature is available within Azure SQL, managed instances, and SQL Server and can be implemented through SQL Server management studio. I’m going to show you how to implement column encryption within an SQL Server database using a key stored in an Azure key vault. First off, I will create the new key called column encrypt key, next and this is important, we need to create an access policy. This policy needs to be associated with the identity that will be accessing the key vault from the database server, whether that is Azure SQL, a managed instance or in this case SQL Server. The access permissions must include get, list, unwrap key, wrap key, verify, and sign. Once the access policy has been created and saved, we can head over to SQL Server management studio. Within the database that contains the columns, we want to encrypt, expand security then always encrypted keys and right-click on column master keys to create a new column master key. I’ll give the key a name and then select Azure key vault as my key store.
At this point sign in with the identity that has the key permissions that we just previously set. Select the key vault if you have more than one and then select the key you want to use. I’ll just script that key to a new query window so you can have a look. Next, we need to create a new column encryption key, by selecting new column encryption key from column encryption keys, just below. This is pretty simple, give your column encryption key a name and then select your master key, which in this case is the one we’ve just created. If I script that key here is what it looks like. Now, let’s encrypt a column by right-clicking on the database, select tasks followed by encrypt columns. As you can see the column encryption wizard allows you to encrypt columns from multiple tables at one time. At the upper right, I will select the key I want to use the encryption; you can automatically create key at this point if you wish. Next, I will choose the encryption type of deterministic and click next. At this point, you could generate a PowerShell script to run at a later date, but I’ll just encrypt the column right now. Now that’s done let’s do a select star from the table containing the encrypted column. There we have the license data encrypted, but what’s interesting is that that column is a nvarchar 10, and clearly in the result set that is a lot more than 10 characters. As I said before, you would go through exactly the same process if you are doing this with an Azure SQL database.
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.