Securing Data at Rest and in Transit

Let's start with the fundamental split: 'data at rest' is any information stored permanently on a disk, like the records inside a SQL Server database. 'Data in transit' is that same information when it's being sent over a network, for example, when your application queries the database and the results travel back as network packets.

For data at rest in Azure SQL Database or Azure SQL Managed Instance, the primary tool is Transparent Data Encryption (TDE). TDE automatically encrypts the data before it is written to the disk and decrypts it when it is read back into memory. The word 'transparent' is critical: your application and users do not notice it is happening. They connect as normal, run queries as normal, and the database engine handles the encryption behind the scenes. TDE uses a symmetric key called the Database Encryption Key (DEK), which itself is protected by a certificate or an asymmetric key stored in the master database of the server. In Azure, you can either let the platform manage this key (service-managed key) or take control yourself using a customer-managed key stored in Azure Key Vault. Azure Key Vault is a secure, cloud-based safe for secrets: passwords, certificates, and encryption keys. Using a customer-managed key means you can rotate (change) the key on your own schedule and even revoke access to the database if needed, effectively destroying the data by removing the key.

There is also a feature called Always Encrypted, which is different from TDE. Always Encrypted encrypts data in the client application *before* it ever reaches the database. The database never sees the unencrypted values. This means even a database administrator with full access to the server cannot read those encrypted columns. The encryption keys for Always Encrypted are stored in the client's key store or in Azure Key Vault. The exam will test you on when to use TDE versus Always Encrypted: TDE protects the whole database at rest on disk, while Always Encrypted protects specific sensitive columns from being seen by the database itself.

Now for data in transit. When a client application talks to an Azure SQL Database, the connection should always use Transport Layer Security (TLS). TLS is a protocol that encrypts the data as it leaves the client and decrypts it only when it arrives at the server. It also verifies the server's identity using certificates, so you know you're talking to the real database, not an impostor. Azure forces TLS 1.2 or higher for all connections; older versions like TLS 1.0 and 1.1 are deprecated. In the connection string of your application, you must set 'Encrypt=True' and 'TrustServerCertificate=False' to enforce TLS and validate the certificate. If you set TrustServerCertificate to True, you are telling the client to accept any certificate, which defeats the security because a man-in-the-middle attacker could present their own certificate and intercept your data.

Beyond TLS, there is Azure Virtual Network service endpoints and private endpoints. A service endpoint extends your virtual network to the SQL Database service over the Azure backbone network, keeping traffic off the public internet. A private endpoint gives your database a private IP address inside your virtual network, so traffic never leaves Microsoft's secure network. This is the gold standard for data in transit because it eliminates exposure to the public internet entirely.

The exam also expects you to know about firewall rules. Azure SQL has a server-level firewall and a database-level firewall. By default, all public access is blocked. You must explicitly allowlist client IP addresses or ranges. There is also the option to 'Allow Azure Services and resources to access this server', which is a broad setting that permits traffic from all Azure data centres. The exam frequently warns against leaving this on in production because it is too permissive.

Finally, let's tie it all together with key management. The keys that protect both data at rest (TDE keys) and data in transit (TLS certificates) should be rotated periodically. Azure Key Vault can automate key rotation. You should also enable auditing and monitoring to detect unauthorised access attempts. SQL Server provides 'SQL Audit' to log access to sensitive data, and Azure SQL Database has 'Advanced Data Security' which includes vulnerability assessment and threat detection.

Consider a medium-sized e-commerce company running an online store. Their Azure SQL Database holds customer names, addresses, and payment card details (though payment data should ideally be tokenized, this example assumes a misconfiguration). The company hires a new database administrator (you, for this scenario) to replace someone who left abruptly. You discover several serious security gaps.

First, you check data at rest. The old DBA never enabled TDE. That means if someone steals the physical disk backups or gains access to the underlying storage files, they can read all customer data in plain text. Your first action is to enable TDE on the database. In Azure, this is a toggle in the portal or a single ALTER DATABASE statement. You also decide to use a customer-managed key in Azure Key Vault, so you can rotate the key quarterly. You create a new key in Key Vault, grant the SQL Server managed identity access to it, and configure the database to use that key.

Next, data in transit. You examine the application's connection strings. They look like this:

You immediately spot the error: TrustServerCertificate is set to True. This means the application will accept any certificate the server presents, including a fake one from an attacker. You change it to False. You also notice the application is connecting over the public internet. The company has an Azure Virtual Network. You create a private endpoint for the SQL Database, which gives it a private IP address. Then you update the connection string to use that private IP or the private DNS zone name. Now traffic from the application servers stays entirely inside Microsoft's network, never touching the public internet.

Third, firewall rules. The old DBA had left 'Allow Azure Services and resources to access this server' enabled. This is a wide-open door. You disable it and instead add specific IP ranges for the company's application servers and your own jump box for administration. You also set up a database-level firewall for the most sensitive databases so that even if someone gains access to the server admin account, they still need to be on the correct IP to connect.

Finally, you set up auditing. You enable SQL Audit to log all SELECT statements on the table containing credit card numbers (though ideally that column would be encrypted with Always Encrypted or tokenized). You also enable Advanced Data Security to receive alerts when someone tries to brute-force the admin password or performs an unusual query pattern.

That is what a real-world DBA does daily: triaging misconfigurations, hardening connections, managing keys, and always thinking about how data looks when it's stored versus when it moves.