With very few exceptions, data stored on the Azure platform is encrypted at rest by default. This means that if a hard drive that contains your data is stolen or the server where your data is stored is unlawfully accessed, the data cannot be deciphered. Keep in mind that when you give a group, individual, or a service principal access to the data, the unencrypted data is available to them. From an Azure Synapse Analytics dedicated SQL pool perspective, the means for encrypting server‐side data files, data backups, and log files at rest rely on the Transparent Data Encryption (TDE) feature. As shown in Figure 8.34, TDE can be enabled on the Transparent Data Encryption blade in the Azure portal.

FIGURE 8.34 Encrypt data at rest, TDE, dedicated SQL pool.

Encryption of data in motion is achieved by using the strongest possible version of TLS when accessing your data using the HTTP protocol. When HTTPS is utilized, a secure connection is made between the data consumer and the server. When the data is transferred through the network, it is encrypted using the strongest cypher that both the client and server support. If you want to make the network transmission even more secure, you might consider isolating the network communication channel using a virtual private network (VPN). This can be achieved by implementing the Azure VPN Gateway product or an Azure ExpressRoute. There are three common VPN models: point‐to‐point, point‐to‐site, and site‐to‐site. A point‐to‐point connection is restricted to a single connection between two machines, whereas a point‐to‐site connection means that a single machine has access to all the machines within a given site. A site in this context means an on‐premises network. A site‐to‐site model would allow all machines contained in two networks to connect with each other. An ExpressRoute connection is a dedicated connection from Azure to your on‐premises datacenter. It is configured with the help of an Internet service provider and is costly; however, it is the most secure means for transferring data, as the transmission does not traverse the Internet at all.

An additional concept, encryption‐in‐use, was explained earlier in the “Design Data Encryption for Data at Rest and in Transit” section. Recall that encryption‐in‐use is enabled using a product called Always Encrypted. As mentioned previously, TDE is used to encrypt data on the server‐side; however, there may be a need to encrypt data client‐side within an application. Always Encrypted is useful for encrypting data client‐side, directly within an application, before storing it on the Azure platform. Encrypting data client‐side ensures that individuals with highly privileged server‐side credentials cannot view or decrypt the data without being specifically granted permission. This gives you complete control over your data, while allowing a third party, like Microsoft, to perform the database administration responsibilities.

Implement Row‐Level and Column‐Level Security

Row‐level security (RLS) is implemented at the information protection level of the layered security model (refer to Figure 8.1). Row‐level security restricts access to rows in a database table and is realized using the CREATE SECURITY POLICY command and predicates. Figure 8.35 illustrates row‐level security.

FIGURE 8.35 Row‐level security

Using the EXECUTE AS statement prior to the SELECT statement, as follows, results in the result set being filtered based on the user permissions:

 EXECUTE AS USER = ‘BrainwaveBrainjammer1’

 SELECT * FROM BRAINWAVES

Column‐level security restricts access to specific columns on a database table, as illustrated in Figure 8.36.

To get hands‐on experience implementing column‐level security, complete Exercise 8.7.

FIGURE 8.36 Column‐level security