Securing modern data platforms. If you store data in a lake but only lock the front gate while the back fence is wide open, your organisation will get robbed. For the SC-100 exam, you need to know that SQL Database, Cosmos DB, and Azure Synapse are three very different animals that need three different security cages.
Jump to a section
A simple way to picture Securing Modern Data Platforms: SQL, Cosmos DB, and Azure Synapse
You are the Head Chef of a busy restaurant with three very different kitchens. The first kitchen is the Formal Dining Kitchen (SQL Database). It is a traditional, structured kitchen where recipes are fixed, ingredients are stored in neatly labelled jars, and every dish follows strict rules. You control exactly who enters, who touches the knives, and who sees the secret family recipe book.
The second kitchen is the Buffet Kitchen (Cosmos DB). It is a global, flexible operation. Ingredients arrive from all over the world at different speeds. The menu changes constantly, and customers from different countries need their food instantly. You use a digital ordering system that automatically replicates recipes to satellite kitchens in London, Tokyo, and New York. Your job is to ensure that even if a chef in London is sick, the kitchen in New York can still serve the same dish.
The third kitchen is the Warehouse Kitchen (Azure Synapse). This kitchen does not cook meals for customers. Instead, it analyses mountains of old order receipts, supplier invoices, and customer feedback forms to predict next month's menu. It is a massive data analysis centre. You must protect this historical data because it contains the restaurant's secret pricing strategies and customer buying habits.
As the Head Chef, you are the Cybersecurity Architect. You must lock the pantry doors, issue ID badges to only the right chefs, encrypt the secret recipes, and set up cameras to watch for thieves. You need one security playbook for the Formal Kitchen, a different one for the Buffet Kitchen, and a third for the Warehouse Kitchen. If you treat them all the same, either the Formal Kitchen will become too slow, or the Buffet Kitchen will leave the back door open.
A data platform is where an organisation stores its most valuable information: customer records, financial transactions, sensor readings, and employee data. In the old days, you stored everything in one giant database on one server. Now, organisations use different platforms for different jobs because each platform has special strengths. You need to secure each one differently.
Let us start with Azure SQL Database. Think of this as a highly structured spreadsheet on steroids. Every piece of data lives in a table with rows and columns. You must know the exact shape of your data before you store it. SQL stands for Structured Query Language, a language used to talk to relational databases.
To secure Azure SQL Database, you use three main layers:
Firewall rules: You decide which IP addresses are allowed to connect. If a hacker tries to connect from a strange IP, the firewall blocks them.
Microsoft Entra ID authentication: Users cannot just log in with a username and password. They must authenticate using their work identity from Microsoft Entra ID (the cloud directory service).
Always Encrypted: This technology encrypts your data inside the database while it is being processed. Even the database administrator cannot read the credit card number if they do not have the key.
Now let us look at Azure Cosmos DB. This is a NoSQL database. NoSQL means it does not use tables and rows. Instead, it stores data as documents, graphs, or key-value pairs. It is designed for global scale. You can have a database in Virginia and a copy in Sydney, and both can be updated at the same time. This is called multi-region writes.
Securing Cosmos DB is different because it is exposed to the internet by default. You must:
Use private endpoints to connect Cosmos DB to your virtual network so that traffic never goes over the public internet.
Use resource tokens to give users temporary, limited access to specific documents.
Enable encryption at rest and in transit. Encryption at rest means the data is scrambled when stored on disk. Encryption in transit means the data is scrambled while travelling across the network.
Finally, Azure Synapse Analytics. This is a data warehouse and big data analytics service. It is designed to analyse petabytes of data. Petabytes are thousands of terabytes. A data warehouse is a central repository of historical data from many sources.
The main security concerns for Synapse are:
Column-level security: You can hide salary columns from non-managers.
Row-level security: You can make a salesperson in Europe only see European customer rows.
Dynamic data masking: You can show the last four digits of a credit card number but hide the rest.
Why do these three platforms need different security approaches? Because they serve different purposes. SQL Database is for transactional systems like banking. Cosmos DB is for globally distributed apps like social media feeds. Synapse is for data analysis and reporting. If you lock them all down in the same way, you will either break the app or leave a security gap.
Identify the data platform
First, read the scenario and determine whether you are dealing with Azure SQL Database, Cosmos DB, or Azure Synapse. Each platform has different security features. For example, if the scenario mentions globally distributed data with low latency, you are likely dealing with Cosmos DB.
Determine the data sensitivity level
Not all data needs the same level of protection. Use Microsoft Purview to classify data as public, internal, confidential, or highly confidential. Credit card numbers are highly confidential. Product names are internal. This classification drives your choice of encryption and access control.
Configure network-level security
Choose the best network access method. For SQL Database and Cosmos DB, prefer private endpoints. For Synapse, you may need to use firewall rules in combination with workspace managed virtual network. The goal is to minimise exposure to the public internet.
Set up authentication and authorisation
Use Microsoft Entra ID for authentication whenever possible. Avoid SQL authentication or access keys. For fine-grained access control, use Azure RBAC roles at the resource level, and for Cosmos DB, use resource tokens for per-document permissions. For Synapse, configure column-level security and row-level security.
Enable data protection features
Turn on encryption at rest (default for all three platforms). Enable encryption in transit by enforcing TLS 1.2. For sensitive columns, enable Always Encrypted for SQL Database or client-side encryption for Cosmos DB. For Synapse, apply dynamic data masking to hide parts of sensitive columns.
Implement monitoring and auditing
Enable diagnostic settings to send logs to a Log Analytics workspace. Use Azure SQL Auditing to log all database queries. For Cosmos DB, enable diagnostic logs for control-plane and data-plane operations. For Synapse, use SQL auditing and integration with Microsoft Sentinel for threat detection.
Manage encryption keys
Use Azure Key Vault to store encryption keys. Use customer-managed keys (CMK) if you need to control key rotation and access. For Always Encrypted, the column master key is stored in Azure Key Vault. Regularly rotate keys and use key versioning to maintain forward secrecy.
You are the cybersecurity architect for a global retail company called ShopFast. ShopFast sells clothes online. They use Azure SQL Database to manage their inventory. When a customer buys a shirt, the inventory count must go down instantly and accurately. They use Cosmos DB to power their product recommendation engine. When a customer in France looks at shoes, the recommendation engine must work in milliseconds using data from all customers worldwide. They use Azure Synapse to run quarterly reports on sales trends.
Your first task is to secure the Azure SQL Database. You configure a firewall rule that only allows traffic from the IP addresses of ShopFast's application servers. You enable Microsoft Entra ID authentication so that only employees with valid work accounts can access the database. You turn on Auditing, which logs every query that runs against the database. If a malicious insider tries to dump the customer table, you will have a record.
Next, you secure Cosmos DB. You create a virtual network and deploy a private endpoint. Now all traffic between the app servers and Cosmos DB stays inside Microsoft's backbone network, never the public internet. You set up resource tokens so that the recommendation engine can only read product data, not customer payment details. You enable multi-region writes and use conflict resolution policies to handle simultaneous updates from Paris and New York.
Finally, you secure Azure Synapse. You configure column-level security to hide the compensation information from the finance team. You set up dynamic data masking on the credit card columns. You use Azure Purview, now called Microsoft Purview, to scan the data and classify sensitive columns automatically. You set up a data masking policy that shows only the last four digits of a credit card number to everyone except the payment team.
At the end, you run a security assessment. You discover that the Cosmos DB account has a public IP address exposed. You immediately lock it down with a private endpoint. You also find that the SQL Database audit logs are not being sent to a central Log Analytics workspace. You configure that. Now you have a secure, audited data estate.
SC-100 tests your ability to choose the right security control for the right data platform. They love to give you a scenario with a specific business requirement and ask you to pick the correct feature.
Exact concepts you must memorise:
The difference between data at rest, data in transit, and data in use. Data at rest encryption is handled by Azure Storage Service Encryption. Data in transit uses TLS 1.2 or higher. Data in use encryption is handled by Always Encrypted for SQL Database or client-side encryption for Cosmos DB.
The difference between authentication and authorisation. Authentication is verifying who you are. Authorisation is verifying what you are allowed to do. Microsoft Entra ID handles authentication. Roles and permissions handle authorisation.
The difference between firewall rules, virtual network service endpoints, and private endpoints. Firewall rules are IP-based. Service endpoints extend your virtual network to the PaaS service. Private endpoints give the PaaS service a private IP inside your virtual network. Private endpoints are the most secure option.
Common traps SC-100 sets:
They give you a scenario where a company needs to prevent a database administrator from seeing sensitive data. Many test takers pick Transparent Data Encryption (TDE). TDE encrypts data at rest, but the admin can still query it. The correct answer is Always Encrypted, because it encrypts the data in use and the admin does not have the key.
They give you a scenario about Cosmos DB and say 'enable firewall rules'. The trap is that Cosmos DB is globally distributed, so IP-based firewalls are hard to manage. The better answer is private endpoints.
They ask about securing a Synapse workspace. Many pick row-level security for hiding salary data from a junior analyst. Row-level security hides rows, not columns. For hiding a column, you need column-level security or dynamic data masking.
Exam topics to list:
Always Encrypted vs Transparent Data Encryption vs Dynamic Data Masking
Azure RBAC roles for data platforms
Microsoft Entra ID authentication vs SQL authentication
Private endpoints vs service endpoints vs firewall rules
Managed identity for accessing Azure Key Vault
Customer-managed keys vs platform-managed keys
Cosmos DB resource tokens vs primary keys
Synapse column-level security vs row-level security
Data classification and labelling using Microsoft Purview
Auditing and threat detection for SQL and Cosmos DB
Always Encrypted is the only way to prevent database administrators from reading sensitive data in Azure SQL Database because it encrypts the data in use and stores the keys outside the database.
Azure Cosmos DB should be secured with private endpoints, not just firewall rules, to remove it from the public internet entirely.
Azure Synapse Analytics uses column-level security to hide entire columns and dynamic data masking to obfuscate parts of a column, which row-level security cannot do.
The three pillars of data security in Azure are authentication (who you are), authorisation (what you can do), and encryption (protecting the data itself).
Managed identities in Azure eliminate the need to store passwords or keys in application code, reducing the risk of credential leaks.
For the SC-100 exam, always choose the most restrictive, least privileged option: private endpoints over service endpoints, managed identity over access keys, and Always Encrypted over TDE for sensitive columns.
Data classification using Microsoft Purview is the first step in data security because you cannot protect what you do not know exists.
These come up on the exam all the time. Here's how to tell them apart.
Always Encrypted
Encrypts data in use (in memory) so admins cannot read it
Requires storing the column master key in Azure Key Vault
Prevents database administrators from seeing sensitive columns
Transparent Data Encryption (TDE)
Encrypts data at rest (on disk) only
No key management needed; Azure manages it by default
Database administrators can still query the data normally
Azure SQL Database
Structured, relational data with fixed schema
Uses SQL queries and stored procedures
Best for transactional applications (OLTP)
Azure Cosmos DB
Flexible schema (NoSQL) for documents, graphs, key-values
Uses SQL API or NoSQL APIs (MongoDB, Cassandra, Gremlin)
Best for globally distributed applications with low latency
Firewall Rules
Allows traffic based on source IP address
Service still has a public endpoint exposed
Hard to manage if IP addresses change frequently
Private Endpoint
Removes the public endpoint entirely
Service gets a private IP inside your virtual network
All traffic stays on the Microsoft backbone
Row-level Security (RLS)
Restricts access to entire rows of data
Useful for multi-tenant apps where Tenant A cannot see Tenant B's data
Implemented with security policies and filter predicates
Column-level Security (CLS)
Restricts access to specific columns in a table
Useful for hiding salary or credit card columns from some users
Implemented with GRANT statements on specific columns
Mistake
Transparent Data Encryption (TDE) is enough to prevent database administrators from seeing credit card numbers.
Correct
TDE encrypts data at rest, but the database admin can still query the data because TDE is transparent to the application. To prevent the admin from seeing the data, you need Always Encrypted, which encrypts the data in use and gives the admin no access to the keys.
People think 'encryption' is a single magic bullet. They do not realise there are different layers of encryption (at rest, in transit, in use) and that TDE only covers one layer.
Mistake
Azure Cosmos DB is insecure because it exposes a public endpoint by default, so you should always use firewall rules.
Correct
Cosmos DB is designed with security in mind, but the recommended approach is to use private endpoints to remove it from the public internet entirely. Firewall rules are a second-best option because they still expose the service to the internet, just with IP filtering.
Beginners are comfortable with firewalls from their home Wi-Fi experience and assume the same logic applies to cloud services. They do not understand the concept of a private endpoint.
Mistake
Azure SQL Database and Azure Synapse Analytics are essentially the same service, so they have the same security features.
Correct
Both use the SQL Server engine, but Synapse has unique features like column-level security, row-level security, and dynamic data masking that are not built into Azure SQL Database in the same way. Synapse also uses a different architecture (massively parallel processing) that requires different security considerations.
The names sound similar, so beginners assume they are the same. The exam tests your ability to distinguish between them.
Mistake
For Cosmos DB, the best way to secure it is to disable the public endpoint and rely on the shared access signature (SAS) tokens.
Correct
Private endpoints are more secure than SAS tokens because they eliminate exposure to the public internet entirely. SAS tokens, while useful, can still be leaked and misused. Private endpoints plus managed identity is the gold standard.
SAS tokens are easy to understand (like a temporary password), so beginners gravitate toward them. They forget that a token that is stolen is just as dangerous as a password that is stolen.
Mistake
Once you enable encryption at rest on a data platform, you never need to think about encryption again.
Correct
Encryption at rest is one layer. You also need encryption in transit (TLS) and, for highly sensitive data, encryption in use (Always Encrypted or client-side encryption). You also need to manage the keys, rotate them regularly, and decide who can access the keys.
The word 'encryption' feels like a checkbox. Beginners do not realise it is a continuous process that involves key management, rotation, and access control.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Transparent Data Encryption (TDE) encrypts the data on the disk so that if someone steals the hard drive, they cannot read it. Always Encrypted encrypts the data in memory while the database is running, so even a database administrator cannot read the encrypted columns without the encryption key.
Yes, but Azure Cosmos DB encrypts data at rest by default. You do not need to do anything extra for that layer. For extra protection, you can use client-side encryption to encrypt sensitive fields before they are sent to the database.
A private endpoint gives a cloud service a private IP address inside your virtual network. Traffic stays on the Microsoft backbone and never traverses the public internet. It is more secure than a firewall rule because the service is effectively invisible from the internet.
Column-level security lets you restrict access to specific columns in a table. For example, you can allow a manager to see the 'salary' column while hiding it from a regular team member. It is implemented using the GRANT statement on specific columns.
Use a combination of private endpoints (one per region) and a multi-region write configuration. Do not open the public endpoint. Use managed identities for authentication and resource tokens for fine-grained access.
Dynamic data masking hides sensitive data in query results on the fly. For example, you can configure it to show only the last four digits of a credit card number (like 'XXXX-XXXX-XXXX-1234') to non-privileged users. The full number is still stored in the database.
You've finished Securing Modern Data Platforms: SQL, Cosmos DB, and Azure Synapse. Continue through the SC-100 study guide to build a complete picture of the exam.
Done with this chapter?