Chapter 5. Implement Secure Data Platform Environment in Azure

Security is a critical topic, and is often cited as one of the reasons for keeping SQL Servers on-premise. It’s interesting to reflect that many of the security reasons given for not migrating to Azure, are equally applicable to on-premise…

The chapter opens with a look at the on-premise SQL Server authentication methods (i.e. Windows Authentication and SQL Server Authentication), before looking at Azure SQL Server authentication (i.e. SQL Server Authentication and Azure Active Directory [AAD]). Much of this will be familiar to on-premise DBAs. Azure Active Directory is similar to Windows Active Directory authentication. AAD is preferred since it allows Multi-Factor Authentication (MFA). There’s a useful walkthrough on how to set up AAD via the Azure Portal.

Next, we look at security principals (i.e. something/someone that wants access). This involves securables (objects to access), principals (who is accessing), and permissions (what they can do). Like on-premise Windows Active Directory, with AAD you assign a SQL Server role to an AAD group.   

There’s a useful reminder of the fixed server-level roles and their capabilities. It is also possible to create additional server roles (e.g. combine several of the fixed server roles). Similarly, details of the fixed database roles are described. Useful diagrams of fixed server and fixed database permissions are included.

There’s an overview of the commands and syntax to configure database authorization (i.e. grant, revoke, deny), and configuring database and object-level permissions.

The principal of least privilege is discussed, where you assign the principal the least permissions possible to do their work. There’s often a delicate balancing act between this security granularity and the amount of administration wok required, which often leads to permissions being assigned at a higher level than is necessary. 

The following section provides details of the many different security features that will help you in protecting your SQL Server’s data. Useful examples are provided throughout. Many of these features with be familiar to on-premise DBAs. 

Data-at-rest security is provided by Transparent Data Encryption (TDE), this protects against restoring a backup to an unsecure server, a helpful walkthrough provides guidance on how to implement TDE. 

Next, there’s a natural progression to looking at protecting data as it moves from the database to the client, using Always Encrypted, implementation details are provided. Similarly, there’s a useful section on implementing Dynamic Data Masking to mask sensitive data (e.g. credit card #), for certain users/groups.

Firewalls are a mainstay of access over the internet. There’s a walkthrough on how to set up both server-level and database-level firewall rules. 

Another useful security feature is row-level security. Here, access to data is controlled at the row-level, so certain groups/users only see the rows of data that they have access to. 

Increasing awareness of data privacy has seen a move to identify, classify, and report on sensitive data (and to obfuscate this data on non-prod systems). SQL Server provides an in-built feature to do this via its Data Discovery and Classification feature.

Additional security is provided by Advanced Threat Protection (ATP). This monitors connections and queries, detects abnormal and harmful activities, and sends alerts etc. It can report on:  

• vulnerability to SQL injection

• potential SQL injection attacks

• access from unusual locations/principals/data centers

• harmful applications

• brute force attacks.  

ATP is especially useful when combined with auditing, and details on configuring server and database audits are given. 

Change Data Tracking (CDC) allows changes to be tracked (e.g. can load changes to a Data Warehouse). CDC captures the inserts, updates, and deletes on specified table (via trans log). Configuration details are provided (i.e. to enable the database and table), but no example is given.

You can perform a vulnerability assessment that monitors your Azure databases, using various rules, and assesses your systems for security vulnerabilities. Even better, it provides action steps to resolve problems.

This was a long chapter, both in depth and range of topics. It illustrates the importance of security to Microsoft. Azure provides a great many different security features to protect your data, but it’s up to you to implement them appropriately. 

Awareness of the vulnerability assessment feature is especially useful, since it will indicate the common security problems your current system exhibit. I didn’t find any mention of the private link security feature, which seemed odd, since it’s popular in Azure Databases.

Chapter 6. Monitor and Optimize Azure Data Platform Resources

The chapter opens with a look at using Azure Portal to view data storage usage, including used space, allocated space, and maximum storage size. It’s also possible to view various charts for numerous metrics (e.g. compute usage), over different time periods.

Next, there’s a look at Alert rules, these are rules that send an alert if a given condition is met. There’s a step-by-step walkthrough on setting this up, you specify scope, condition, and action (e.g. current Azure Database, if DTU usage over 90%, then send email). 

There’s a very useful section on setting up diagnostics. Diagnostics can stream to an Azure Log Analytics workspace, Event hubs, or an Azure storage account. Various conditions can be monitored, including: basic metrics (e.g. CPU usage), tempdb usage, errors, waits, timeouts, blocking, and deadlocks. There are less options for Azure Managed Instance. There’s a useful examples of monitoring various metrics and analyzing the output using the Kusto Query Language (KQL) – a WHERE-driven method of querying.

The chapter ends with a look at Query Performance Insights. This gives DBAs insights based on data stored in Query Store, much of this is automated via reports (e.g. top 5 longest running queries). At the individual query level, there are recommendations relating to: create index, drop index, and parameterized queries. After seeing how useful Query Performance Insights is, it would be nice to have it available on on-premise SQL Server too.

This is a very useful and interesting chapter. You’ll often need to investigate slow queries or problems with Azure databases, and this chapter provides you with direction and instructions.

The chapter’s opening paragraph says the chapter is about Azure Monitor, however, this chapter does not cover Azure Monitor – the next chapter is about Azure Monitor specifically. The first sentence under the Diagnostics setting section was awkward to read (or I didn’t understand it).  Another sentence says ”…long-tuning queries…” instead of “…long-running queries..”.