Security interviews have a different texture than performance or architecture interviews. Nobody’s asking you to be clever. They’re asking you to be thorough, and to actually understand why a control exists, because half the wrong answers in security sound perfectly reasonable until you think about the specific threat they’re supposed to stop.
This list is split into Beginner (1–17), Intermediate (18–35), and Advanced (36–50), written the way you’d actually talk through these in a room – not textbook definitions, but the reasoning a DBA who’s actually had to defend a design decision carries around in their head.
One honest note before you start: in real interviews, “it depends on the threat model” is often the correct answer to a security question, more than it is in performance or architecture interviews. The good answers below explain what it depends on — that’s what you’re being tested on, not a single memorized rule.
Beginner Level
1. What is Transparent Data Encryption (TDE), and is it on by default in Azure SQL? TDE encrypts the database files at rest — the actual data and log files on disk — so if someone somehow got hold of the physical storage, they’d see encrypted pages, not readable data. It’s enabled by default on every new Azure SQL database, and you don’t have to do anything to turn it on.
2. What’s the difference between encryption at rest and encryption in transit? Encryption at rest (TDE) protects data sitting on disk. Encryption in transit protects data while it’s moving across the network between your client and the database — Azure SQL enforces this via TLS on every connection by default, so the two work together to cover different points where data could be exposed.
3. Does Azure SQL require encrypted connections? Yes — by default, Azure SQL Database requires Encrypt=True (TLS) on incoming connections, and rejects unencrypted connection attempts. This is different from a lot of on-prem SQL Server setups, where encryption is often optional and frequently just never turned on.
4. What is a firewall rule, and how does it act as a security control? It’s an IP allowlist at the server or database level — by default, nothing can reach your Azure SQL server at all, and firewall rules explicitly permit specific IPs or ranges to connect. It’s your first layer of defense: even a leaked password is useless to an attacker connecting from an IP that was never allowed in.
5. What’s the difference between SQL Authentication and Azure Active Directory (Microsoft Entra ID) authentication? SQL Authentication is a username and password stored and managed inside the database engine itself. Microsoft Entra ID authentication uses your organization’s centralized identity provider, supporting multi-factor authentication, conditional access policies, and centralized password rotation/revocation — which is why it’s the generally recommended approach for production environments.
6. Why is Azure AD/Entra ID authentication considered more secure than SQL Authentication? Because it centralizes identity — when someone leaves the company, disabling their Entra ID account instantly cuts off database access too, instead of someone having to remember to also drop a separate SQL login. It also supports MFA and conditional access (like blocking logins from unexpected countries), which plain SQL Authentication has no concept of at all.
7. What is Always Encrypted, and what problem does it solve? It encrypts specific sensitive columns (like SSNs or credit card numbers) client-side, so the data stays encrypted even in memory and in transit — meaning even someone with full admin rights on the database server can’t see the plaintext values. It’s aimed specifically at protecting sensitive data from insider threats and DBA-level access, not just external attackers.
8. What’s the difference between Always Encrypted and Transparent Data Encryption? TDE protects the whole database at rest, and the engine can freely read plaintext data once it’s loaded — a DBA querying the table sees real values. Always Encrypted protects specific columns end-to-end, and the engine itself never sees the plaintext at all — a DBA running a plain SELECT on an Always Encrypted column just sees encrypted bytes.
9. What is Row-Level Security (RLS)? A feature that lets you restrict which rows a user can see or modify, enforced automatically by the engine based on a security predicate — commonly used in multi-tenant applications so each tenant’s queries automatically only return their own data, without every single query needing an explicit WHERE TenantId = ... clause written correctly by every developer.
10. What is Dynamic Data Masking (DDM)? A feature that obscures sensitive data in query results for non-privileged users — showing something like XXX-XX-1234 instead of a full SSN — without changing the actual stored data. It’s a presentation-layer control, not real encryption, so it’s meant to reduce casual/accidental exposure, not stop a determined attacker with direct database access.
11. Is Dynamic Data Masking a substitute for proper access control or encryption? No, and this trips people up — DDM only affects what’s shown in query results to users without the UNMASK permission; it doesn’t stop someone with sufficient privileges (or a clever workaround query) from inferring or extracting the real data. Treat it as a UX/reduce-accidental-exposure control layered on top of real access control, not a replacement for it.
12. What is auditing in Azure SQL, and what does it capture? Auditing tracks database events — logins, permission changes, query execution, schema changes — and writes them to a target like a Storage Account, Log Analytics workspace, or Event Hub. It’s what you’d point to as evidence during a compliance audit, or dig through after an incident to reconstruct exactly what happened and who did it.
13. What is Microsoft Defender for SQL, and what does it add on top of basic security features? It’s a paid add-on that provides vulnerability assessment (scanning for misconfigurations and risky permissions) and Advanced Threat Protection (real-time detection of things like SQL injection attempts or anomalous login patterns). It goes beyond passive controls like TDE or firewall rules into active threat detection.
14. What is Vulnerability Assessment in Azure SQL? A scanning tool (part of Defender for SQL) that checks your database against a set of security best-practice rules — excessive permissions, missing auditing, unencrypted sensitive columns, outdated configurations — and gives you a prioritized list of findings with remediation guidance, rather than you having to manually audit every setting yourself.
15. What’s the principle of least privilege, and how do you apply it in Azure SQL? Giving every user or application only the exact permissions they need to do their job, nothing more. In practice: avoiding blanket db_owner grants for application service accounts, using specific GRANT statements or custom database roles scoped to exactly the tables/procedures needed, and regularly reviewing who actually has elevated access versus who was just given it once and never revisited.
16. What is a Managed Identity, and why is it more secure than storing credentials in application code? A Managed Identity is an Azure AD identity automatically managed for an Azure resource (like an App Service or Function App), letting it authenticate to Azure SQL without any password or connection string secret ever being stored in code or config. It removes an entire class of risk — credentials leaking through a code repository, a config file, or a compromised CI/CD pipeline.
17. What is Azure Key Vault, and how does it relate to Azure SQL security? Key Vault is a managed service for storing secrets, keys, and certificates securely. In an Azure SQL context, it’s commonly used to store the customer-managed key for TDE, or to hold connection secrets/credentials that applications retrieve at runtime instead of hardcoding them — centralizing secret management and access auditing in one place.
Intermediate Level
18. How does customer-managed key TDE differ from service-managed key TDE, and when would you choose it? Service-managed keys are the default — Microsoft generates and rotates the encryption key automatically, and you never touch it. Customer-managed keys let you supply and control the key yourself via Azure Key Vault, giving you the ability to revoke access to the key (effectively making the database inaccessible) independently of the database itself — often a compliance requirement in regulated industries where the organization needs demonstrable control over its own encryption keys.
19. Walk through how Always Encrypted actually protects data end-to-end, including where the keys live. Data is encrypted client-side, using a column encryption key, before it ever leaves the application — the database only ever stores and processes ciphertext. The column encryption key is itself encrypted by a column master key, which lives outside the database entirely, typically in Azure Key Vault or a local certificate store, and the database never has access to the plaintext key at all. This means a compromised database — even with full admin access — cannot decrypt Always Encrypted columns without also compromising the separate key store.
20. What’s the difference between Always Encrypted and Always Encrypted with secure enclaves? Standard Always Encrypted severely limits what you can do with an encrypted column server-side — no range comparisons, no pattern matching, because the engine truly never sees plaintext. Secure enclaves allow richer operations (range queries, in-place encryption changes) by decrypting data inside a hardware- or software-attested, isolated enclave on the server, without exposing plaintext to the broader SQL engine process, OS, or a DBA’s query window — trading some architectural complexity for much more usable encrypted columns.
21. How do you implement Row-Level Security for a multi-tenant application, in practice? You write an inline table-valued function that checks a tenant identifier — often set via EXEC sp_set_session_context at connection time by the application, using an authenticated value it controls — against a TenantId column on the protected table, then apply it as a FILTER predicate (and optionally a BLOCK predicate for writes) using CREATE SECURITY POLICY. The critical point: this enforces isolation at the engine level, so even a bug in an application developer’s WHERE clause can’t leak cross-tenant data, because the database itself is silently filtering every query regardless of what the application asked for.
22. What’s the difference between a FILTER predicate and a BLOCK predicate in Row-Level Security? FILTER predicates silently restrict which rows are returned by SELECT, UPDATE, and DELETE — the user just doesn’t see rows they’re not allowed to see, without an error. BLOCK predicates explicitly prevent write operations (INSERT, UPDATE, DELETE) that would violate the policy, raising an error instead of silently filtering — useful when you specifically want to stop someone from writing data into or out of a row they shouldn’t touch, rather than just hiding it from reads.
23. How would you audit and detect excessive or unused permissions in an Azure SQL database? Query sys.database_permissions joined with sys.database_principals to see the full grant landscape, cross-reference against actual query activity in Query Store or extended events to see who’s genuinely using their access versus who was granted something once and never used it, and pay particular attention to any principal with db_owner, CONTROL, or broad ALTER ANY permissions — those are the ones worth justifying individually rather than accepting as “probably fine.”
24. What is SQL injection, and what Azure SQL-specific tools help detect or prevent it? SQL injection is when untrusted input is concatenated directly into a query string, letting an attacker inject their own SQL logic. The real fix is always parameterized queries/stored procedures at the application layer — no database-side control fully substitutes for that. But Microsoft Defender for SQL’s Advanced Threat Protection specifically detects SQL injection attempts and successful exploits in real time, which is a valuable detection layer even on an application you didn’t write or can’t immediately patch.
25. How does Microsoft Defender for SQL’s Advanced Threat Protection detect anomalous login behavior? It builds a behavioral baseline of normal access patterns — typical login locations, times, and client applications for each principal — and flags deviations, like a login from a geography that account has never connected from, or an unusual volume of data being extracted in a short window. It’s genuinely useful specifically because it doesn’t rely on a static rule someone has to write and maintain; it adapts as normal patterns shift.
26. What’s the difference between database-level and server-level firewall rules? Server-level rules apply to every database under that logical server. Database-level rules apply only to a specific database and, importantly, are stored within that database — meaning they travel with it during geo-replication or copy operations, which server-level rules don’t. Database-level rules are the better choice when different databases on the same server genuinely need different access policies.
27. What is a Private Endpoint, and how does it improve Azure SQL security compared to public endpoint access? A Private Endpoint gives your Azure SQL server a private IP address inside your own Virtual Network, so traffic between your application and the database never traverses the public internet at all — even Azure’s own backbone routing stays within the private network path. It eliminates an entire category of exposure that firewall rules only mitigate (a public endpoint with firewall rules is still a public endpoint; a private endpoint isn’t reachable from the public internet in the first place).
28. What’s the difference between a Private Endpoint and a Virtual Network (VNet) service endpoint for Azure SQL? A VNet service endpoint extends your VNet’s identity to the Azure SQL public endpoint, letting you restrict access to specific subnets — but the traffic still technically goes over Azure’s backbone to a public IP, and the database still has a public endpoint that exists (just restricted). A Private Endpoint actually assigns the database a private IP inside your VNet, removing the public endpoint exposure entirely — generally the stronger and now more commonly recommended option.
29. How would you design least-privilege access for an application’s database connection string? Avoid using a highly privileged account for routine application connections — create a dedicated login/user mapped to a custom database role granted only EXECUTE on the specific stored procedures the application actually calls, or SELECT/INSERT/UPDATE on exactly the tables it touches, rather than blanket db_datareader/db_datawriter, let alone db_owner. Pair this with a Managed Identity where possible so there’s no credential to leak in the first place.
30. What is Data Discovery & Classification in Azure SQL, and why does it matter for security work? It’s a built-in feature that scans your database schema and suggests classification labels (like “Confidential — SSN” or “Confidential – Financial”) for columns likely to contain sensitive data, based on column names and patterns. It matters because you genuinely can’t secure what you haven’t identified — it’s often the first step before applying Dynamic Data Masking, Always Encrypted, or RLS deliberately rather than guessing which columns actually need protection.
31. How does Azure SQL auditing interact with compliance requirements like GDPR, HIPAA, or SOC 2? Auditing provides the evidence trail these frameworks require — who accessed what data, when, and what changed — typically retained in a Log Analytics workspace or Storage Account with a retention period matching the compliance requirement (which can be years, not the platform’s short default). It doesn’t make you compliant by itself, but it’s usually a required, non-negotiable piece of the evidence a compliance audit will actually ask to see.
32. What’s the difference between server admin, Azure AD admin, and a database-level db_owner? The server admin (SQL auth) is the top-level administrative account created at server provisioning, with full control over every database on that server. The Azure AD admin is the equivalent top-level identity, but tied to an Entra ID user or group instead of a SQL login, and it’s the recommended primary administrative path since it supports MFA and centralized identity management. db_owner is scoped to a single database and doesn’t carry server-level administrative rights — a meaningful distinction when reasoning about blast radius if any one of these accounts is compromised.
33. How would you rotate credentials for an application connecting to Azure SQL with minimal downtime? If using SQL Authentication, create the new login/password first, update the application’s connection string or Key Vault secret reference, deploy the change, confirm successful connections under the new credential, then disable (not immediately drop) the old login as a rollback safety net before removing it entirely a bit later. If using Managed Identity, there’s no credential to rotate at all — which is exactly why it’s the preferred pattern where the workload supports it.
34. What’s the security risk of using the “Allow Azure services and resources to access this server” firewall option, and when is it acceptable? That setting allows any Azure resource in any subscription, not just your own, to attempt a connection — it’s broader than most people realize the first time they see it. It’s generally acceptable for a low-sensitivity dev/test environment or when paired with strong authentication (Entra ID, not weak SQL passwords) as a secondary control, but for production systems handling sensitive data, Private Endpoints or specific VNet rules are the more defensible design.
35. How do you secure backups and geo-replicated copies of an Azure SQL database? Backups inherit TDE encryption automatically, so they’re encrypted at rest by default without extra configuration. For geo-replication and failover groups, the secondary database is also encrypted and subject to the same access controls, but it’s worth explicitly verifying that firewall rules, Always Encrypted key access, and RLS policies are consistently applied on the secondary too — since a secondary that’s slightly less locked down than the primary is a real, commonly overlooked gap.
Advanced Level
36. Design a comprehensive security architecture for a new, highly regulated multi-tenant SaaS application on Azure SQL. Walk through your layers. I’d start with network isolation via Private Endpoint, no public endpoint at all. Authentication via Microsoft Entra ID exclusively, with Managed Identity for the application tier so no credential exists to leak. Row-Level Security enforced at the engine level for tenant isolation, backed by a session-context value set from an authenticated claim, not a client-supplied parameter. Always Encrypted (with secure enclaves if the workload needs range queries on protected columns) for genuinely sensitive fields like payment or health data. TDE with customer-managed keys in Key Vault if the compliance framework requires demonstrable key control. Defender for SQL enabled for vulnerability assessment and threat detection, auditing routed to a long-retention Log Analytics workspace, and Data Discovery & Classification run regularly as data model changes over time, not just once at launch.
37. How would you architect Always Encrypted with secure enclaves to support both strong security and rich querying, and what are the residual risks? I’d enable enclave computations at the database level, use an enclave-enabled column master key configuration, and design the application to leverage the enclave for range queries and in-place encryption changes rather than pulling data client-side for those operations. The residual risk worth being honest about: enclave attestation depends on the underlying hardware/software attestation service being trustworthy and correctly configured — a misconfigured or bypassed attestation step undermines the entire security guarantee, so I’d treat attestation configuration as itself a security-critical piece to review, not just “turn on enclaves and done.”
38. Explain the security implications of Automatic Tuning’s CREATE INDEX/DROP INDEX actions in a regulated environment, and how you’d govern it. Schema changes happening automatically, without a change ticket or peer review, is a real concern in an environment with strict change-control requirements — even though the changes themselves are performance-motivated, not security-motivated, an unreviewed schema change is still an audit finding waiting to happen. I’d run Automatic Tuning in “recommend only” mode for regulated production databases, routing recommendations into the normal change-approval workflow instead of letting them apply unattended, accepting slower response to performance regressions in exchange for full auditability.
39. How would you detect and respond to a suspected data exfiltration attempt on Azure SQL Database? Defender for SQL’s Advanced Threat Protection is the first automated layer — it specifically flags unusual data extraction volume and anomalous access patterns. I’d cross-reference that alert against auditing logs for the specific queries and principal involved, check Azure AD sign-in logs for that identity around the same window (was it a legitimate credential used from an unusual location, or a genuinely compromised session), and have a pre-defined incident response runbook ready — disable the credential immediately, preserve the audit trail for forensic review, and only then investigate root cause, in that order, because containment shouldn’t wait on full root-cause analysis.
40. What are the security trade-offs of granting VIEW SERVER STATE or other broad diagnostic permissions to a monitoring service account? VIEW SERVER STATE and similar diagnostic permissions can expose query text, execution plans, and session-level details across the entire server, not just the databases that specific monitoring tool is supposed to watch — which can leak sensitive data indirectly (a query’s literal parameter values showing up in captured plan XML, for instance) to a service account that only needed aggregate performance metrics. I’d scope monitoring permissions as narrowly as the tool actually requires, review exactly what that tool captures and where it stores it, and treat the monitoring pipeline itself as part of the security surface, not an exception to it.
41. How would you design a key rotation strategy for customer-managed TDE keys stored in Azure Key Vault, without causing a database outage? Azure SQL supports automatic key rotation when configured to auto-rotate to the latest key version in Key Vault, so the practical design is ensuring the new key version is created and available in Key Vault before the old version’s expiration, with monitoring/alerting on key expiration dates so rotation never becomes an emergency. I’d also maintain at least one prior key version accessible (not immediately purged) for a defined grace period, specifically to avoid a scenario where a delayed rotation or a Key Vault access issue makes the database briefly inaccessible.
42. Explain how you’d secure a hybrid environment where on-prem applications need to connect to Azure SQL Database, without exposing a public endpoint. I’d use a Private Endpoint combined with either a Site-to-Site VPN or ExpressRoute connecting the on-prem network to the Azure VNet hosting that private endpoint, plus private DNS zone integration so on-prem name resolution correctly resolves the database’s private IP instead of falling back to the public FQDN. Authentication would still go through Entra ID (via a hybrid identity setup like Entra Connect if the on-prem environment uses Active Directory), so the network path and the identity layer are both fully private, not just one or the other.
43. What’s your approach to threat-modeling a new Azure SQL-based application before it goes to production? I’d walk through the data flow end-to-end — where sensitive data enters, where it’s stored, who and what can access it at each hop — and explicitly ask, for each hop, what happens if that specific component is compromised: a leaked application credential, a compromised Key Vault, an over-permissioned service account, a misconfigured firewall rule. I’d specifically avoid assuming any single control is sufficient on its own — the value of defense-in-depth is that a failure in one layer (like a leaked credential) shouldn’t by itself expose the sensitive data, because Always Encrypted or RLS is still standing behind it.
44. How does Row-Level Security interact with Always Encrypted, and are there any gotchas when using both together? They operate at different layers and generally compose fine — RLS controls which rows are visible, Always Encrypted controls whether the column’s content is readable — but a gotcha worth knowing: the RLS predicate function itself cannot directly compare against an Always Encrypted column’s plaintext value inside the predicate logic, since the engine never has plaintext for that column to evaluate against. If your tenant-isolation logic needs to key off a column that’s also Always Encrypted, you generally need a separate, non-encrypted identifier column for the RLS predicate to filter on, rather than trying to filter directly on the encrypted column.
45. Describe how you’d audit for privilege escalation risk in a large Azure SQL environment with many database roles and nested role memberships. I’d programmatically walk sys.database_role_members recursively to flatten nested role memberships into an actual effective-permission map per principal, rather than trusting the role names alone — a role named “ReadOnlyReports” that’s nested inside a role with db_owner is a real, easy-to-miss escalation path. I’d specifically flag any chain that terminates in db_owner, CONTROL SERVER, or similar broad grants, and treat those chains as requiring explicit justification, not just an assumption that the role name accurately describes its actual effective access.
46. What are the security considerations specific to Azure SQL Managed Instance versus Azure SQL Database, given Managed Instance’s broader SQL Server feature surface? Managed Instance’s support for cross-database queries, linked servers, and SQL Agent reintroduces some classic on-prem attack surface that Azure SQL Database’s more restricted PaaS model simply doesn’t have — a linked server, for instance, can be a lateral movement path if not carefully governed. I’d apply the same least-privilege discipline to these Managed-Instance-specific features as I would on-prem, rather than assuming the “managed” part of Managed Instance means these traditional risks don’t apply — the engine surface area is closer to full SQL Server, and so is the corresponding risk surface.
47. How would you approach data residency and sovereignty requirements using Azure SQL’s security and geo-replication features? I’d first confirm the primary region satisfies the residency requirement, then carefully evaluate whether geo-replication or failover groups to a secondary region are even permitted under that specific compliance requirement — some data residency mandates prohibit replication outside a specific geography entirely, which would rule out cross-region DR and push toward zone-redundant configuration within the compliant region instead. This is a case where a security/compliance constraint genuinely overrides what would otherwise be a best-practice DR recommendation, and it’s worth explicitly confirming with the compliance team rather than assuming standard DR guidance applies unmodified.
48. Explain the security value of Query Store data from a forensic/incident-response perspective, and its limitations. After an incident, Query Store can show you the actual query text and execution history around the time in question — useful for confirming what a compromised or malicious account actually executed, and when. Its limitation is retention (it’s bounded by the configured Query Store retention window, not indefinite) and that it captures query shape and performance data, not full audit-grade detail like the specific literal parameter values passed at each execution — for genuine forensic-grade evidence, dedicated auditing routed to long-retention storage is the tool built for that job, and Query Store is a useful supplementary signal, not a substitute for it.
49. How would you design a security review process for Automatic Tuning, Advisor recommendations, and other “automated” changes in an environment with strict audit requirements? I’d treat every automated recommendation-and-apply feature the same way — require it to run in recommend-only mode for anything touching a regulated production database, route the recommendations into the existing change-management workflow so they get the same review and approval trail a manually proposed change would, and separately audit (via Activity Log) any instance where an automated feature applied a change without going through that gate, treating an unexpected automated change as an incident worth investigating in its own right, not just a performance curiosity.
50. Walk through a real (or realistic) security incident you’ve handled or would expect to handle on Azure SQL, end to end. This is deliberately open-ended, because interviewers want your process, not a memorized script. A solid structure to demonstrate: (1) detect — via Defender for SQL alert, an anomalous audit log pattern, or an external report, (2) contain immediately — disable the compromised credential or restrict network access before doing anything else, prioritizing stopping ongoing harm over understanding root cause, (3) preserve evidence — ensure audit logs and any relevant Query Store/diagnostic data aren’t lost or rotated out before you’ve captured them, (4) investigate root cause using the preserved evidence — how did the credential get compromised, what was actually accessed, (5) remediate the underlying gap, not just the symptom — if it was an overly broad firewall rule or an unrotated credential, fix that class of problem, not just this one instance, and (6) document a postmortem with concrete follow-up actions. Interviewers consistently favor candidates who describe containment happening before full investigation — panicking straight into root-cause analysis while the breach is still active is a common and telling mistake.
A Closing Thought
Notice how many of these advanced answers come back to the same idea: no single control is enough on its own, and the goal is layers that each cover what the others miss — network isolation doesn’t replace encryption, encryption doesn’t replace least privilege, and automated detection doesn’t replace a tested incident response process. That’s not a coincidence — it’s genuinely how security work holds up under real pressure, and it’s exactly what a good interviewer is listening for underneath the specific feature names.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
