A database that holds customer records, financial transactions, or anything subject to a compliance regime is the asset an attacker wants most, and Azure SQL security is the discipline that decides whether reaching that asset takes one stolen password or a coordinated breach of several independent controls. The gap between a database that ships with defaults and one that has been hardened is not a single setting. It is the difference between an attack surface that any leaked connection string can cross and one where authentication, network path, encryption, monitoring, and authorization each have to fail before data leaves the building.
The common failure is to treat the database as secure because it sits inside Azure. The platform encrypts storage by default, presents a certificate on the connection, and asks for a username and password, so the surface looks covered. It is not. A SQL login with a password that ends up in a configuration file, a server firewall opened to a broad address range so a developer could connect from home, and the assumption that encryption at rest also protects data while it is being queried, together describe most of the real incidents that reach a database in Azure. None of those gaps shows up as an error. The database works, queries return, and the exposure stays invisible until someone finds it.
This guide treats Azure SQL security as five surfaces that must be closed together rather than a checklist of independent toggles. Authentication decides who can prove identity to the server. The network path decides who can even reach the endpoint to attempt a connection. Encryption decides what an attacker recovers if they reach the storage, the backups, or the query traffic. Auditing and threat detection decide whether you find out an attack happened. Authorization decides how much damage a single compromised identity can do once it is inside. The argument running through every section is the entra-auth-and-network rule: the two changes that move the security posture the furthest are switching to Microsoft Entra authentication and removing public network exposure, because together they close the credential surface that SQL logins leave open and the reachability surface that an open firewall leaves open. Everything else in this guide is defense in depth layered behind those two moves.
What “secure” means for an Azure SQL database
Securing Azure SQL is not a property you switch on. It is a posture assembled from controls that sit at different layers, and the value of the posture comes from how independent those layers are. If a single failure can carry an attacker from the public internet to readable rows, the design has one control doing all the work. If each layer has to be defeated separately, a leaked secret or a misconfigured rule becomes an incident the other layers contain rather than a breach.
Hold a mental model of the database as a target reached through a sequence of gates. The outermost gate is reachability: can a packet from a given source even arrive at the server endpoint. The next gate is authentication: can the connection prove an identity the server accepts. Inside that, authorization decides what that identity may read or change. Underneath all of it, encryption governs what the bytes look like at rest, in transit, and for the most sensitive columns, in use. Wrapped around the whole thing, auditing and detection record who did what and raise an alarm when the pattern looks like an attack. A hardened database closes every gate. A default database leaves the outer two wide and trusts the password alone.
The reason this framing matters is that engineers tend to fix the gate they can see. A login fails, so they widen the firewall. A connection string is awkward to manage, so they store the password in app configuration. Each local fix is reasonable in isolation and each one quietly removes a layer. The discipline is to treat the layers as a system: never trade away the network gate to fix an auth problem, never trade away strong identity to make a connection string simpler, and never assume that one strong layer excuses a weak one. The sections that follow take each surface in turn, explain the control that closes it, show the configuration that realizes the control, and name the breach that the common misconfiguration enables.
Why is the database not already secure inside Azure?
The platform provides a secure floor, not a secure posture. Storage is encrypted at rest, connections require TLS, and the server demands credentials. None of that controls who can reach the endpoint, whether identities are managed centrally, whether sensitive columns are protected from a privileged reader, or whether anyone is watching. Those decisions are yours, and the defaults leave them open.
A useful way to feel the gap is to enumerate what an attacker has to obtain at each layer of a default database versus a hardened one. Against a default Azure SQL server with a SQL administrator login and a firewall rule that allows a wide range, the attacker needs one thing: the password. If it appears in a committed configuration file, a leaked backup of an application server, a screenshot in a support ticket, or a phished developer’s notes, the path from that secret to a full table scan has no further obstacle, because the firewall already admits the source range and the SQL login authenticates without any second factor or central revocation. The credential is the whole defense, and credentials are the control that leaks most often.
Against a hardened database the same stolen password buys nothing. There is no SQL login to authenticate, because the server enforces Microsoft Entra-only authentication, so the credential format the attacker holds is not even accepted. There is no public endpoint to connect to, because access arrives through a private endpoint inside the virtual network, so a connection from the open internet never reaches the listener. If the attacker somehow obtains a token for a real Entra identity, that identity is scoped to the least privilege it needs, so a compromised application account can read its own tables and nothing more, and the audit log records the access for detection to flag. Five gates, five separate things to defeat. That is the posture this guide builds.
How authentication to Azure SQL actually works
Authentication is the gate that decides whether a connection can prove an identity the server will accept, and it is the surface where the largest single improvement is available. Azure SQL supports two authentication models that can coexist or, in the hardened configuration, one that excludes the other. Understanding the difference is the foundation for every other identity decision.
The first model is SQL authentication. The server stores logins and password hashes in the master database, and a connection presents a username and password that the server validates locally. This is the model most engineers meet first because it mirrors a self-managed SQL Server and because the provisioning experience asks for an administrator login and password up front. SQL authentication is self-contained, which is precisely its weakness. The credential lives in the database, it is validated by the database, and it has to be distributed to every client that connects. There is no central directory to disable it from, no built-in second factor, and no relationship to the organization’s identity lifecycle. When an employee leaves, a SQL login keeps working until someone remembers to drop it. When a password leaks, it stays valid until someone rotates it. The credential is a long-lived shared secret, and long-lived shared secrets are the most common root of database compromise.
The second model is Microsoft Entra authentication. Instead of validating a local password, the server trusts tokens issued by Microsoft Entra ID, the organization’s identity platform. A user, a group, a managed identity, or a service principal authenticates to Entra, receives a token scoped to the database resource, and presents that token to the server, which validates it against Entra. The identities are managed where every other identity in the organization is managed, which means central disable, conditional access policies, multifactor authentication, and the same offboarding process that revokes email access also revokes database access. For a managed identity, there is no secret at all: the platform issues and rotates the credential, and the application never holds a password to leak.
Should I use SQL authentication or Entra authentication?
Use Microsoft Entra authentication wherever the connecting identity can hold one, which is nearly always: users connect as themselves or through groups, and applications connect with a managed identity. Reserve SQL authentication for the narrow cases where no Entra identity is available, and plan to retire those. Entra authentication removes the long-lived password and ties access to central identity governance.
The decisive property of Entra authentication is centralization. A SQL login is an island. An Entra identity is a node in a directory that already enforces the organization’s security policy. When a security team adds a conditional access rule that blocks sign-in from outside trusted locations or requires a compliant device, that rule applies to the database connection without any change to the database, because the database trusts Entra and Entra applies the policy. When an account is compromised and disabled, the database connection stops working immediately, because the token issuance the connection depends on no longer happens. None of that is reachable for a SQL login, which the database validates entirely on its own with no awareness of the wider security posture.
For applications, the strongest form of Entra authentication is the managed identity. A managed identity is an Entra service principal that Azure creates and manages for a resource such as an App Service, a virtual machine, a container app, or a function. The platform provisions the identity, rotates its credentials automatically, and exposes a token endpoint the application calls to obtain a database token at runtime. The application code holds no secret. There is no connection string password, no certificate to renew, and nothing to rotate, because the platform owns the credential lifecycle. An application that connects to Azure SQL with a managed identity has removed the single most leaked artifact in database security, the stored password, from its design entirely. The companion article on choosing between a managed identity and a service principal works through when each fits, and the pattern for Azure SQL is to default to a managed identity for any workload running on an Azure compute resource.
What does Entra-only authentication do?
Entra-only authentication is a server-level setting that disables SQL authentication entirely, so the server accepts only Microsoft Entra tokens and rejects every username-and-password login including the SQL administrator. It removes the local credential surface completely. After it is enabled, a leaked SQL password is worthless because no SQL login can authenticate, regardless of whether the login still exists.
The reason Entra-only authentication is the high-value move is that it changes the security model from “Entra is available and recommended” to “SQL credentials are impossible.” As long as SQL authentication remains enabled, every SQL login is a live credential surface, and the security of the database depends on no one ever leaking one. That is a property you cannot verify and cannot enforce, because any administrator can create a SQL login at any time and any application team can decide a password is simpler than configuring a managed identity. Entra-only authentication closes the option. The setting lives on the logical server and applies to every database on it.
# Set the Microsoft Entra admin on the logical server, then enforce Entra-only auth.
az sql server ad-admin create \
--resource-group rg-data-prod \
--server-name sql-orders-prod \
--display-name "SQL Admins" \
--object-id "00000000-0000-0000-0000-000000000000"
# Enforce Entra-only authentication (disables all SQL logins, including the SQL admin).
az sql server ad-only-auth enable \
--resource-group rg-data-prod \
--name sql-orders-prod
Before enabling Entra-only authentication, confirm that every application connecting to the server has been migrated to an Entra identity, because the change takes effect immediately and any connection still presenting a SQL login will start failing. The safe sequence is to set the Entra administrator, migrate each application to a managed identity, grant the corresponding database users and roles, verify the new connections in a lower environment, and only then flip the server to Entra-only. The auth-failure path for SQL logins is the subject of the companion guide on the Azure SQL login failed error 18456, which is worth reading alongside this section, because several of the 18456 sub-states change meaning once Entra-only authentication is in force and a 18456 against an Entra-only server almost always means a token or mapping problem rather than a wrong password.
How do I grant database access to an Entra identity?
After authentication establishes who a connection is, the database still has to know that identity and what it may do. Create a contained database user mapped to the Entra principal inside the specific database, then add it to the roles that grant exactly the access it needs. The user lives in the database; the identity lives in Entra.
For a managed identity belonging to an application, the user is created from the external provider using the identity’s display name, and the grant is scoped to the smallest role that covers the workload. An application that reads and writes its own tables needs membership in the data reader and data writer roles, not in db_owner, and certainly not in the server administrator role. The grant is part of the database schema, so it belongs in the same source control and deployment pipeline that manages the rest of the schema, which keeps the authorization model reviewable and reproducible rather than something an administrator clicked once and no one can reconstruct.
-- Run while connected as the Entra admin, in the target database.
CREATE USER [app-orders-api] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-orders-api];
ALTER ROLE db_datawriter ADD MEMBER [app-orders-api];
-- For a managed identity, the principal name matches the resource's identity display name.
-- For a group, create the user from the group so membership governs access centrally.
CREATE USER [SQL Read Only Analysts] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [SQL Read Only Analysts];
Mapping the user to an Entra group rather than to individuals is the pattern that scales, because membership is then governed in one place and the database never needs to change when a person joins or leaves the team. Adding an analyst to the read-only group grants database access; removing them revokes it; the database holds one user that represents the group and never accumulates a long tail of individual logins that no one maintains. This is the same principle that makes Entra authentication valuable in the first place, applied to authorization: keep the membership decision in the directory, and let the database trust it.
Encryption at rest: what Transparent Data Encryption covers
Encryption answers a different question from authentication. Authentication controls who can connect; encryption controls what an attacker recovers if they bypass the connection entirely and reach the storage, the backups, or the wire. Azure SQL has two encryption features that protect different things, and the most expensive security mistake in this area is assuming one of them does the job of the other.
Transparent Data Encryption, usually written TDE, encrypts the database at rest. The data files, the log files, and the backups are encrypted on disk, and the encryption and decryption happen transparently in the storage layer as pages move between disk and memory. An attacker who obtains the physical storage, a stolen backup file, or the underlying disk recovers ciphertext, not readable rows. TDE is enabled by default on databases created in Azure SQL, which is one of the secure-floor properties the platform provides, and it protects against the theft-of-media class of threat: a lost backup, a copied disk, a decommissioned drive that was not wiped.
The word that does the work in the name is “transparent.” TDE is invisible to the application and to anyone with a valid connection. A query that authenticates and authorizes successfully reads decrypted data, because the storage layer decrypts pages as it serves them into the buffer pool. That is the entire point of TDE and also the boundary of what it protects. It defends the data where it sits on disk. It does nothing to defend the data from someone who reaches it through a legitimate query path, because to that path the data is, by design, decrypted.
Does TDE protect data while it is being queried?
No. TDE encrypts data at rest in files, logs, and backups, and decrypts it transparently as pages are read into memory to serve a query. Any connection that authenticates and is authorized sees plaintext rows. TDE defends against stolen storage and backups, not against a compromised credential, a SQL injection flaw, or an over-privileged user reading the data through a normal query.
This is the single most common encryption misconception in Azure SQL, and it leads to real exposure because it produces false confidence. A team enables encryption, sees that the database is encrypted, and concludes that sensitive data is protected from unauthorized reading. It is protected from a stolen backup. It is not protected from a leaked connection string, an application bug that returns more rows than intended, an internal user with more access than they should have, or an attacker who has already authenticated. For those threats the relevant control is not at-rest encryption at all. It is authentication, authorization, network isolation, and for the most sensitive columns, encryption that keeps the data ciphertext even on the server.
By default TDE uses a service-managed key, where Azure owns and rotates the key hierarchy and the engineering team does nothing. For organizations with a compliance requirement to control the key themselves, TDE supports a customer-managed key stored in Azure Key Vault, often described as bring-your-own-key. With a customer-managed key, the database’s encryption key is protected by a key the organization holds in its own vault, and revoking access to that key renders the database unreadable, which gives the organization a hard control over the data’s availability. The trade-off is operational: the key now has a lifecycle the team owns, and losing access to it or deleting it makes the database unrecoverable, so customer-managed keys require the same soft-delete and purge-protection discipline on the vault that the companion material on Key Vault hardening describes. Choose a customer-managed key when a regulation or a contractual control requires the organization to hold the key; otherwise the service-managed key removes an operational risk without weakening the at-rest protection.
Always Encrypted: protecting sensitive columns end to end
Always Encrypted addresses the threat that TDE cannot: a privileged reader on the server side seeing plaintext. It encrypts specific columns, the ones holding the sensitive values such as national identifiers, payment card numbers, or health data, and it keeps those columns encrypted not only at rest but in memory and on the wire, so that the database engine itself never sees the plaintext. The encryption and decryption happen in the client driver, using keys the database server cannot access. To a database administrator, a server-side attacker, or anyone querying the column directly on the server, the values are ciphertext.
This is a fundamentally different protection model from TDE, and the difference is where the decryption boundary sits. With TDE, the boundary is the storage layer: below it the data is encrypted, above it the engine and every query see plaintext. With Always Encrypted, the boundary moves all the way out to the client application: the engine stores and returns ciphertext, and only the authorized client driver, holding the column encryption key, can turn it back into readable values. The server processes data it cannot read. That property is what lets an organization separate the people who manage the database from the people who may see the sensitive data, which is the control that compliance regimes around payment and health data frequently require.
Always Encrypted uses a two-tier key hierarchy. A column encryption key encrypts the data in the column. A column master key, stored outside the database in a key store the client can reach such as Azure Key Vault or a certificate store, encrypts the column encryption key. The database stores only the encrypted column encryption key and metadata describing where the master key lives; it never holds the master key and so can never decrypt the column on its own. A client that needs plaintext retrieves the encrypted column encryption key from the database, fetches the column master key from the key store it has permission to access, decrypts the column encryption key, and then decrypts or encrypts the column values locally. The separation of the data, the column encryption key, and the column master key across the server and the client is the structural reason the server cannot read the protected columns.
What does Always Encrypted protect that TDE does not?
Always Encrypted protects sensitive columns from anyone with server-side access, including database administrators and a server-side attacker, because decryption happens only in the authorized client driver using keys the server cannot reach. TDE protects data at rest from stolen storage but leaves it plaintext to any authenticated query. Always Encrypted keeps the chosen columns ciphertext on the server, in memory, and in transit.
The trade-off Always Encrypted carries is on querying. Because the server holds ciphertext, it cannot perform arbitrary operations on encrypted columns. With deterministic encryption, where the same plaintext always produces the same ciphertext, the server can still do equality comparisons and joins on the column, which supports lookups by an encrypted value. With randomized encryption, where the same plaintext produces different ciphertext each time, the server can do nothing with the column except store and return it, which is stronger against inference attacks but rules out searching or grouping on it. The choice between deterministic and randomized encryption for each column is a security-versus-functionality decision: deterministic where the application needs to look the value up, randomized where it only ever needs to store and retrieve it. There is an enclave-based variant that extends the range of supported operations by performing them inside a secure enclave on the server, and whether it is available and how it is provisioned is a detail to verify against the current Azure SQL documentation before designing around it, because the supported operations and the hardware requirements have evolved.
Reserve Always Encrypted for the columns that genuinely warrant it, not the whole schema. Encrypting every column with a randomized key would make the database almost unqueryable and would impose the client-side key management overhead on data that does not need it. The discipline is to classify the data, identify the columns whose exposure would constitute the breach you are defending against, and apply Always Encrypted to exactly those. The other columns are protected by the layered posture, authentication, network isolation, least privilege, and TDE, while the most sensitive columns get the additional guarantee that even a fully compromised server does not yield their plaintext. The companion deep dive on Azure SQL Database internals is worth reading for how the engine handles storage and querying, because it makes the cost of randomized encryption on a column concrete: the operations the engine would normally optimize against that column are simply unavailable when it holds ciphertext it cannot interpret.
Network access: the firewall, private endpoints, and removing public exposure
Network access is the outermost gate, the one that decides whether a connection attempt can even reach the server before authentication is considered. A database that is unreachable from the public internet has removed an entire class of attack regardless of how strong its credentials are, because the attacker never gets to present them. The network configuration is therefore not a secondary control behind authentication; together with authentication it is one of the two highest-value moves in the entra-auth-and-network rule.
Azure SQL presents a public endpoint by default, gated by a server-level firewall that denies all connections until a rule allows them. The firewall rules are address-range allowlists: a rule names a start and end IP address, and a connection from a source inside that range passes the firewall and proceeds to authentication. There is also a setting that allows Azure services and resources to reach the server, which opens it to traffic originating from within Azure. The firewall is a real control, but it is a coarse one, and the way it tends to be misused is the source of a great deal of exposure. A developer cannot connect from home, so someone adds a rule covering a wide range, or worse, a rule from 0.0.0.0 to 255.255.255.255 that allows the entire internet, intending to narrow it later. The rule outlives the intention. The “allow Azure services” toggle, meanwhile, admits traffic from any Azure tenant, not only your own, which is far broader than most teams assume when they enable it.
The control that genuinely closes the network gate is the private endpoint. A private endpoint projects the Azure SQL server into your virtual network as a private IP address, so the database is reachable over the private network from your resources and over an expressroute or VPN from on-premises, and the public endpoint can then be disabled entirely. With public network access turned off and a private endpoint in place, a connection from the open internet does not reach a firewall that denies it; it reaches nothing, because there is no public listener to connect to. The attack surface shrinks from “the whole internet, gated by an allowlist” to “the private network, period.” This is the difference between a lock on a door facing the street and a door that does not face the street at all.
Is the server firewall enough, or do I need a private endpoint?
For production data, prefer a private endpoint with public network access disabled. The firewall is an allowlist on a public endpoint, which means the database is still internet-facing and one over-broad rule away from exposure. A private endpoint removes the public listener entirely, so internet-origin connections have nothing to reach. The firewall suits development convenience; the private endpoint suits a data store that should never be publicly reachable.
The full network lockdown for a production database has three parts working together. First, disable public network access on the logical server, which turns off the public endpoint so no firewall rule can re-expose it. Second, create a private endpoint in the virtual network the application runs in, which gives authorized resources a private path to the database. Third, integrate the private endpoint with private DNS so that the server’s name resolves to the private IP from inside the network, which is the step teams most often miss and which produces confusing connectivity failures when it is wrong, because the name resolves to the now-disabled public endpoint instead of the private one. The companion walkthrough on how to set up private endpoints end to end covers the DNS integration in detail, and that DNS step is the difference between a private endpoint that works and one that exists but is never used because resolution still points at the public address.
# Disable the public endpoint so no firewall rule can re-expose the server.
az sql server update \
--resource-group rg-data-prod \
--name sql-orders-prod \
--enable-public-network false
# Create the private endpoint into the application's subnet.
az network private-endpoint create \
--resource-group rg-data-prod \
--name pe-sql-orders \
--vnet-name vnet-app-prod \
--subnet snet-data \
--private-connection-resource-id "/subscriptions/<sub-id>/resourceGroups/rg-data-prod/providers/Microsoft.Sql/servers/sql-orders-prod" \
--group-id sqlServer \
--connection-name sql-orders-link
After disabling public access, the firewall rules become irrelevant for internet traffic, because there is no public endpoint for them to gate, which is the desired state: the network decision is now binary rather than a maintained allowlist that drifts over time. Where the firewall still matters is in environments that keep the public endpoint for a deliberate reason, such as a development server, and there the rule is to keep the allowed ranges as narrow as the access genuinely requires and to never use the all-addresses rule or the broad “allow Azure services” setting for anything holding real data. Network segmentation and controlled access are part of the broader model the companion piece on Azure Zero Trust architecture lays out, where the database’s private endpoint is one expression of the assume-breach principle: even a compromised resource inside the network reaches only the databases its subnet and identity permit, not every database because they all share a public endpoint.
Auditing and threat detection: knowing when something is wrong
The controls covered so far prevent unauthorized access. Auditing and threat detection address the layer that the others cannot: knowing that an attack happened, whether it succeeded, and what it touched. A database can have strong authentication, tight network access, and careful encryption and still be breached through a path no one anticipated, and without a record of access the breach is invisible and the response is blind. Auditing produces the record; threat detection reads the record and the live activity for patterns that look like an attack.
Azure SQL auditing records database events to a destination you choose, typically a storage account, a Log Analytics workspace, or an event hub for streaming to a wider monitoring system. The audit captures the events that matter for security: successful and failed logins, the queries that executed, schema and permission changes, and access to the data. The choice of destination shapes what you can do with the record. A storage account is the cheapest durable archive and suits a long retention requirement. A Log Analytics workspace makes the audit queryable with the same query language used across the rest of the monitoring estate, which is what lets a security team correlate database access with sign-in logs and network events rather than reading the audit in isolation. For most production databases, sending the audit to a Log Analytics workspace is the configuration that turns the record into something a team will actually investigate, because it lands where the rest of the security signal already is.
Microsoft Defender for SQL is the threat-detection layer. It is a paid plan that monitors the database for anomalous activity and known attack patterns and raises alerts when it sees them. The patterns it detects include the signatures of SQL injection, access from an unfamiliar location or principal, a sudden change in the volume or shape of queries, and attempts to exfiltrate data. It also runs a vulnerability assessment that scans the database configuration against a baseline and flags misconfigurations such as excessive permissions, missing auditing, or weak settings, which turns the abstract idea of a hardened posture into a concrete report of where this specific database falls short. Whether a given detection or assessment rule is included and how it is priced is worth verifying against the current Microsoft pricing and documentation before relying on it, because the plan’s coverage has expanded over time.
How do auditing and threat detection work together?
Auditing is the durable record of what happened: logins, queries, schema and permission changes, and data access, written to storage or a Log Analytics workspace. Threat detection, through Microsoft Defender for SQL, watches live activity and the record for attack patterns such as SQL injection and anomalous access, and raises alerts. Auditing answers “what occurred”; detection answers “is this an attack right now.”
The two are complementary and neither replaces the other. Auditing without detection gives you a record no one reads until after an incident is already known, which makes it a forensic tool rather than a defensive one. Detection without auditing gives you an alert with no surrounding context to investigate, because the record of what else the principal did is missing. Together they form the monitoring layer: detection raises the alarm, and the audit log provides the trail to scope the incident, identify what was accessed, and confirm whether the alert was a true positive. Configure both, send the audit somewhere a security team already works, and route Defender alerts into the same incident process that handles the rest of the organization’s security alerts so that a database alert is triaged rather than sitting unread in a portal blade.
The configuration discipline that makes this layer effective is to enable auditing at the server level so that every database on the server inherits it, rather than enabling it per database and discovering later that a newly created database was never covered. Server-level auditing applies to current and future databases automatically, which closes the gap where a database created after the security review escapes the policy. The same logic applies to Defender for SQL: enabling it at the subscription level through Microsoft Defender for Cloud covers the SQL resources in the subscription, so a new server does not silently launch without protection. Defaults that apply going forward are stronger than configurations applied once to the resources that happened to exist at the time, because the threat is as much the resource created next quarter as the one secured today.
Least privilege: database roles and row-level security
Authorization is the gate that limits the damage a compromised identity can do once it is past authentication. If every identity that touches the database has broad rights, then any single compromise is a full breach, because the attacker inherits whatever the compromised identity could do. Least privilege is the practice of granting each identity exactly the access its function requires and no more, so that a compromise is contained to that function’s scope rather than escalating to the whole database.
The most common authorization failure in Azure SQL is the over-privileged application account. An application connects with an identity that is a member of db_owner, or even the server administrator, because that was the fastest way to make the connection work during development and no one narrowed it afterward. An application that only reads and writes its own tables does not need to create or drop objects, change permissions, or read other applications’ data, yet db_owner grants all of that. When the application’s identity is compromised, through a leaked token, a vulnerability in the application, or a SQL injection flaw, the attacker inherits db_owner and the breach is total. Granting the application membership in only the data reader and data writer roles, or better, in a custom role scoped to the specific tables and operations it uses, means a compromise of that identity yields only what the application itself could do, which is a contained incident rather than a database-wide breach.
The built-in database roles cover the common cases. The data reader role grants read access to all tables. The data writer role grants insert, update, and delete. For an application that needs more precision, a custom role granting permissions on named schemas or tables draws the boundary exactly where the application’s function ends, which is the strongest form of least privilege because it expresses the access in terms of what the application does rather than a broad category. The discipline is to start from no access and grant up to what the function requires, rather than starting from broad access and trying to narrow it down, because narrowing down is something teams intend to do and rarely complete, while granting up forces each permission to be justified.
How do I apply least privilege without breaking the application?
Start from the application’s actual data operations, the tables it reads and writes and the procedures it calls, and grant a role scoped to exactly those rather than a broad role like db_owner. Test in a lower environment where the narrow grant either covers every operation or surfaces the missing permission as a clear error. Add only the permission the error names. The result is a role that fits the application and contains any compromise of its identity.
Row-level security adds a finer boundary within a table. Where a table holds rows belonging to different tenants, customers, or regions, and different identities should see only their own rows, row-level security enforces that filter in the database itself rather than relying on every query to include the right WHERE clause. A security policy attaches a predicate function to the table, and the engine applies the predicate automatically to every query, so a query that forgets the tenant filter still returns only the rows the connecting identity is permitted to see. This moves the access boundary from the application code, where a single missed filter is a data leak, into the database, where the boundary is enforced uniformly regardless of how the query was written.
-- Row-level security: a predicate that limits rows to the connecting tenant.
CREATE SCHEMA sec;
GO
CREATE FUNCTION sec.fn_tenant_predicate(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS allowed
WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);
GO
CREATE SECURITY POLICY sec.TenantFilter
ADD FILTER PREDICATE sec.fn_tenant_predicate(TenantId) ON dbo.Orders
WITH (STATE = ON);
Row-level security is most valuable in multitenant designs where a single database serves many customers and the isolation between them has to be guaranteed, because it converts tenant isolation from an application invariant that every query must honor into a database invariant the engine enforces. It does not replace the coarser controls; an identity still authenticates, still connects over the private network, and still belongs to a least-privilege role. Row-level security sits inside all of that as the finest boundary, ensuring that even a correctly authorized identity with read access to a table sees only the rows its context permits. The combination of a least-privilege role for what an identity may do and row-level security for which rows it may see is the concrete form of least privilege applied to a shared table.
The InsightCrunch Azure SQL security checklist
The five surfaces come together as a checklist that orders the work by impact and names the rationale at each step, so that a team hardening a database knows not only what to do but why the step closes a real exposure. This is the findable artifact of this guide: the InsightCrunch Azure SQL security checklist, a single reference that maps each control to the threat it addresses and the breach its absence enables.
| Step | Control | What it closes | The breach if skipped |
|---|---|---|---|
| 1 | Set a Microsoft Entra admin and migrate applications to managed identities | The credential surface; no stored password to leak | A leaked SQL connection string is a full credential, valid until manually rotated |
| 2 | Enable Microsoft Entra-only authentication on the server | The option of SQL logins existing at all | Any administrator can create a SQL login later, reopening the credential surface |
| 3 | Disable public network access and connect through a private endpoint | The reachability surface; the public listener is gone | One over-broad firewall rule re-exposes the database to the internet |
| 4 | Integrate the private endpoint with private DNS | Name resolution pointing at the dead public endpoint | Connections fail or silently resolve to the public address |
| 5 | Confirm TDE is enabled, with a customer-managed key where compliance requires | Data readable on stolen storage or backups | A copied backup or disk yields plaintext rows |
| 6 | Apply Always Encrypted to the most sensitive columns | Server-side readers seeing plaintext for those columns | A compromised server or a database administrator reads regulated data |
| 7 | Enable server-level auditing to a Log Analytics workspace | The absence of a record of access | A breach is invisible and unscopable after the fact |
| 8 | Enable Microsoft Defender for SQL at the subscription | Undetected attack patterns and misconfigurations | SQL injection and anomalous access go unalerted |
| 9 | Grant each identity a least-privilege role, never db_owner | The blast radius of a single compromise | A compromised application identity becomes a database-wide breach |
| 10 | Apply row-level security on shared multitenant tables | Cross-tenant row visibility | One missed WHERE clause leaks another tenant’s rows |
The checklist is ordered deliberately. Steps one through four are the entra-auth-and-network rule, the two highest-value moves, expanded into their concrete sub-steps, and they come first because they close the surfaces that the most common incidents cross. Steps five and six are the encryption layer, ordered so that the at-rest default is confirmed before the more involved column-level protection is designed. Steps seven and eight are detection, which has to exist before the others can be trusted to hold, because a control you cannot verify in operation is a control you are guessing about. Steps nine and ten are authorization, the containment layer that limits what any compromise can reach. A team that works the checklist top to bottom closes the largest exposures first and arrives at a database where every gate has to fail independently for data to leave.
The entra-auth-and-network rule
If a team can make only two changes to a default Azure SQL database, the two with the largest effect are switching to Microsoft Entra authentication and removing public network exposure. This is the entra-auth-and-network rule, and it is the organizing claim of this guide because it identifies the decisive moves rather than treating every control as equally urgent.
The rule holds because of where the most common incidents actually originate. Database breaches in Azure overwhelmingly cross one of two surfaces: a leaked credential, or a reachable public endpoint. Entra authentication, taken to its conclusion with Entra-only enforcement and managed identities for applications, closes the credential surface by removing the stored password entirely and tying every remaining identity to central governance that can disable it instantly. Disabling public network access and connecting through a private endpoint closes the reachability surface by removing the public listener, so an attacker on the open internet has nothing to connect to regardless of what credential they hold. Those two surfaces account for the path most real incidents take, which is why closing them first moves the posture further than any other pair of changes.
The rule also explains why the other controls, valuable as they are, are defense in depth rather than the primary line. TDE protects against stolen storage, which is a real but less common path than a leaked credential. Always Encrypted protects against a server-side reader, which matters intensely for regulated columns but is a narrower threat than the wide-open credential and network surfaces. Auditing and detection tell you when something went wrong, which is essential but is a response capability rather than a prevention. Least privilege contains a compromise, which is the layer that matters once the first two surfaces are already closed. The rule is not that the other controls do not matter. It is that authentication and network access are the surfaces an attacker reaches first and most often, so they are where the first and most determined hardening effort belongs.
The common misconfigurations and the breaches they enable
Each surface has a characteristic misconfiguration, a pattern that recurs across real databases, and naming the breach each one enables is what turns the abstract advice into a concrete reason to close it. The patterns below are the ones engineers report most, described as a recurring case with the hardening step that resolves it.
The first pattern is the SQL login where Entra-only authentication would be safer. An application or a person connects with a SQL login and a password, the password ends up in a configuration file or a secret store that is itself exposed, and the leaked password is a complete, long-lived credential with no central revocation. The hardening step is to migrate the identity to Entra, default applications to managed identities so there is no password at all, and enforce Entra-only authentication so a SQL login cannot be created to reopen the surface. The breach this closes is the most common one: a credential leaks and authenticates straight through to the data.
The second pattern is the firewall opened to a broad range instead of a private endpoint. A connectivity problem is solved by widening the firewall, the wide rule outlives its reason, and the database is reachable from far more of the internet than anyone intends, sometimes from all of it. The hardening step is to disable public network access entirely and connect through a private endpoint, which removes the public listener so that no firewall rule can re-expose the database. The breach this closes is an internet-origin connection reaching the authentication gate at all, which is a gate that should never have been reachable from the public internet for a production data store.
The third pattern is treating TDE as protection for data in use. A team enables encryption, confirms the database is encrypted, and concludes the sensitive data is protected, when TDE only protects the data at rest and leaves it plaintext to any authorized query. The hardening step is to recognize that the threat of a privileged reader or a compromised credential is addressed by authentication, network isolation, least privilege, and for the most sensitive columns Always Encrypted, not by at-rest encryption. The breach this misconception enables is silent, because the false confidence means the team never applies the control that would actually protect the data from the threat they think they have already handled.
The fourth pattern is the over-privileged application account, the identity that is a member of db_owner or the server administrator because that was expedient during development. The hardening step is to grant the application a least-privilege role scoped to exactly the tables and operations it uses, so that a compromise of its identity yields only the application’s own access. The breach this closes is the escalation from a single compromised identity to a database-wide breach, which is the difference between an incident contained to one application’s data and one that exposes everything in the database.
How to verify the posture rather than assume it
A security configuration that is set once and never checked is a configuration that drifts. New databases launch without the policy, a firewall rule gets added during an incident and never removed, an application is granted db_owner to unblock a deploy, and the posture the team believes they have diverges from the posture the database actually has. Verification is the practice of confirming the live state matches the intended state, and it is what separates a database that is secure from one that was secured at some point in the past.
The authentication posture is verified by confirming that Entra-only authentication is enabled on the server and that no SQL logins remain that could authenticate if it were ever disabled. A query against the database’s principals shows which users exist and how they map, and an inventory of the server’s Entra-only setting confirms the credential surface is closed. The check to automate is not “is there an Entra admin” but “is Entra-only enforcement on,” because the admin can be set while SQL logins still work, which leaves the surface open.
# Verify Entra-only authentication is enforced on the server.
az sql server ad-only-auth get \
--resource-group rg-data-prod \
--name sql-orders-prod
# Verify public network access is disabled.
az sql server show \
--resource-group rg-data-prod \
--name sql-orders-prod \
--query "publicNetworkAccess"
The network posture is verified by confirming that public network access reports as disabled and that a private endpoint exists and is connected, and crucially by testing resolution from inside the network to confirm the server name resolves to the private IP rather than the public one. A private endpoint that exists but whose DNS still points at the public address is a private endpoint no connection uses, so the resolution test is the one that proves the network gate is actually closed rather than merely configured. The encryption posture is verified by confirming TDE reports as enabled and, where Always Encrypted is in use, that the protected columns report as encrypted with the expected key, which a query against the column metadata confirms.
The authorization posture is the one that drifts most quietly, because a permission granted to unblock a deploy leaves no error and no alarm. Verify it by enumerating role membership and looking specifically for application identities in db_owner or the server administrator role, which are the memberships that should almost never exist for an application. Microsoft Defender for SQL’s vulnerability assessment automates much of this, scanning the configuration against a baseline and reporting excessive permissions, missing auditing, and weak settings as findings, which turns the verification from a manual query into a recurring report. Treat the assessment findings as the drift detector for the whole posture: a finding that auditing is off, or that an identity has excessive permissions, is the early signal that the live state has diverged from the intended one, caught before it becomes the path an incident takes.
How to make the posture auditable and repeatable
A posture configured by hand on one database is a posture that exists only on that database and only until someone changes it. Making it auditable and repeatable means expressing it as code, deploying it through the same pipeline that manages the rest of the infrastructure, and capturing the access model in source control where it is reviewable. This is what turns Azure SQL security from a set of clicks one engineer made into a property of the system that any engineer can read, reproduce, and verify against.
The server and database configuration, the Entra admin, the Entra-only enforcement, the disabled public access, the private endpoint, the auditing destination, and the Defender plan, all belong in an infrastructure-as-code definition such as Bicep or Terraform, so that a new database launches already hardened rather than launching open and being secured afterward if anyone remembers. Defining the secure configuration as the template means the secure state is the default for every database the team creates, which is the only reliable way to close the gap where a resource created next quarter escapes the policy applied to the resources that existed during the last review. The companion library on VaultBook is the place to run these definitions against a live environment: you can run the hands-on Azure labs and command library on VaultBook to enforce Entra-only authentication, provision a private endpoint, and apply the encryption and auditing configuration as tested, repeatable code rather than portal steps you would have to remember and redo for the next database.
The database-level access model, the contained users mapped to Entra groups, the role memberships, the custom roles, and the row-level security policies, belongs in the database schema definition under source control alongside the tables it protects. Treating the grants as schema means a change to who can read a table is a reviewable pull request rather than an ad hoc grant an administrator ran and no one recorded, and it means the access model can be reconstructed exactly in a new environment by deploying the schema. An access model in source control is also an auditable one: the history shows who changed a permission and when, which is the record a security review or a compliance audit asks for and which a database configured by hand cannot produce.
The auditing destination closes the loop on repeatability. With the audit landing in a Log Analytics workspace, the queries that verify the posture, that check for unexpected role memberships, failed Entra-only logins, or connections from outside the expected network, can run on a schedule and alert on a finding, so the verification described in the previous section becomes continuous rather than a periodic manual exercise. The combination of the configuration as infrastructure code, the access model as versioned schema, and the audit as queryable monitoring is what makes the posture durable: it is defined where it can be reviewed, deployed where it applies to every database, and watched where divergence shows up as an alert rather than as an incident.
How an Entra token reaches the database at runtime
Understanding the runtime flow behind Microsoft Entra authentication removes the mystery that often leads teams back to SQL logins out of a sense that tokens are harder to reason about. The flow is mechanical, and once it is visible, the managed-identity pattern stops looking like extra complexity and starts looking like the removal of the password the application would otherwise have to hold.
When an application running on an Azure compute resource connects with a managed identity, the sequence is the same each time. The application’s database driver detects that it should authenticate with an Entra token and asks the platform’s local identity endpoint for one scoped to the database resource. The platform, which owns the managed identity’s credential, returns a token without the application ever seeing or storing a secret. The driver presents that token to the logical server as it opens the connection. The server validates the token against Entra, confirms it represents an identity that maps to a database user, and admits the connection at the access level that user’s roles define. The application code involved in all of this is a connection string that names the server and database and declares the authentication mode; there is no password field, because there is no password.
The property that matters for security is that the token is short-lived and the credential behind it is rotated by the platform. A stolen token expires on its own, unlike a SQL password that stays valid until someone rotates it, and the managed identity’s underlying credential is never exposed to the application to be stolen in the first place. This is why the managed-identity pattern is the strongest authentication posture available: it removes the long-lived secret entirely and replaces it with a short-lived token the application requests on demand and never persists. The companion comparison of a managed identity against a service principal works through the cases where a workload runs outside Azure and cannot hold a managed identity, where a service principal with a certificate credential is the next-best option, but for anything running on Azure compute the managed identity is the default that makes the connection both secretless and centrally governed.
What happens to the token if the identity is disabled?
The connection stops working at the next token request. Because the application requests a fresh token regularly rather than holding a long-lived credential, disabling the Entra identity, whether through offboarding, a security response, or a conditional access policy, cuts off database access as soon as the current token expires and a new one is refused. A SQL login has no equivalent: it keeps authenticating until an administrator drops it.
This near-immediate revocation is the operational payoff of central identity governance applied to the database. A security team responding to a compromised account disables the account in Entra, and the database connections that account or its applications depend on stop within the lifetime of an already-issued token, with no database-side action required. The same mechanism enforces conditional access: if a policy decides the sign-in context is no longer acceptable, the token request fails and the connection cannot be re-established. None of this is reachable for SQL authentication, which the database validates entirely on its own with no link to the directory, which is the structural reason the entra-auth-and-network rule places Entra authentication among the two highest-value moves. The revocation story alone justifies the migration for any organization that has ever had to scramble to find and drop SQL logins after an account compromise.
Securing backups, replicas, and the data that leaves the primary
The controls covered so far protect the primary database, but data in Azure SQL also lives in backups and, for many deployments, in geo-replicated secondaries and read replicas. Each of those is a copy of the data, and a copy is an attack surface, so the posture has to account for where the data goes, not only where it sits. A database hardened on the primary and careless about its replicas has simply moved the exposure rather than closed it.
Backups inherit the primary’s encryption. Because TDE encrypts the data, log, and backup files, the automated backups Azure SQL takes are encrypted at rest with the same key hierarchy, which means a backup obtained by an attacker yields ciphertext. Where the primary uses a customer-managed key, the backups depend on that key as well, which is the property that lets revoking the key render both the live database and its backups unreadable, a deliberate hard control for organizations that need the ability to make data unrecoverable on demand. The corresponding risk is that the same dependency makes losing access to the key a path to losing the backups, so the key’s soft-delete and purge-protection settings protect the recoverability of every copy, not only the live database. Long-term retention backups, where they are configured, carry the same encryption and the same key dependency, so the retention policy and the key policy have to be reasoned about together.
Geo-replication and failover groups extend the data to another region, and the security posture has to extend with it. A geo-secondary is a continuously updated copy of the primary, and it is a real database with its own endpoint, so it needs the same network lockdown as the primary: its own private endpoint, its own disabled public access, and its own DNS integration in the secondary region’s network. The failure mode teams hit is hardening the primary thoroughly and leaving the secondary reachable, so that the data is protected in one region and exposed in the other, and a failover then promotes the exposed copy to primary. Authentication carries over because Entra is a global directory and the secondary trusts the same tenant, but the network and firewall configuration is per-server, so the secondary server needs its own lockdown applied as deliberately as the primary’s. The discipline is to treat every replica and every region as a full instance of the posture rather than assuming the primary’s configuration follows the data, because most of it does not.
SQL injection and the application boundary
Microsoft Defender for SQL detects SQL injection attempts, which is a reminder that one of the most direct paths to a database’s data does not go through a stolen credential or an open firewall at all. It goes through the application that is supposed to be the legitimate front door. SQL injection occurs when an application builds a query by concatenating untrusted input into the query text, so that input crafted to break out of its intended position changes the meaning of the query and causes the database to execute the attacker’s intent under the application’s own authorized identity. The database sees a query from an authenticated, authorized connection and runs it, because from the engine’s point of view nothing is wrong.
This is why least privilege on the application’s identity matters so much for containing injection, and why it appears in the entra-auth-and-network rule’s defense-in-depth layers rather than as an afterthought. An injection flaw lets an attacker run queries as the application, so the damage is bounded by what the application’s identity is permitted to do. If the application connects as db_owner, an injection flaw becomes a path to read every table, alter the schema, and change permissions. If the application connects as a narrow role scoped to its own tables, the same flaw yields only those tables, which is a contained incident. The application bug is the same in both cases; the blast radius is set by the authorization decision, which is the part the database controls. Least privilege does not prevent injection, but it is the control that determines whether an injection flaw is a limited exposure or a full breach.
The prevention for injection itself lives in the application, in the use of parameterized queries that separate the query structure from the input values so that input is always treated as data and never as query text. That is an application-layer discipline outside the database’s direct control, which is precisely why the database-side controls of least privilege, auditing, and Defender detection exist as the layers that contain and detect what the application layer fails to prevent. Auditing records the queries an injection attack runs, which is the trail that scopes the incident afterward, and Defender’s detection raises the alarm when the query pattern looks like injection, which is the signal that turns a silent compromise into an investigated alert. The application boundary is one more gate, and like the others it is strongest when the gates behind it assume it can fail.
Dynamic data masking and the control it is not
A control that frequently gets mistaken for column encryption is dynamic data masking, and clearing up the confusion prevents a team from believing they have protected sensitive values when they have only hidden them from a casual viewer. Dynamic data masking obscures the values in designated columns in the query result, showing a masked form such as a partial credit card number or an entirely hidden field, to identities that do not hold the unmask permission. It is a presentation-layer control: the data is stored in plaintext, the engine holds and processes the real values, and the masking is applied to the result returned to an unprivileged reader.
The distinction from Always Encrypted is the one that matters for the threat model. Always Encrypted keeps the column ciphertext on the server, so even a fully privileged server-side reader recovers only encrypted bytes. Dynamic data masking keeps the column plaintext on the server and merely hides it in the result for some readers, so an identity that can run an arbitrary query, that holds the unmask permission, or that can reach the storage directly recovers the real values. Masking is a useful control against the over-the-shoulder and broad-read-access threats, a support engineer who needs to confirm a record without seeing the full card number, or an analyst querying a table who has no business reason to see identifiers, but it is not a defense against a determined attacker who has authenticated with sufficient privilege, and it is no defense at all against someone who reaches the storage beneath the engine.
The practical place for dynamic data masking is as a convenience layer that reduces unnecessary exposure of sensitive values to the many identities that legitimately query a table but do not need the sensitive columns in clear, layered on top of, never instead of, the real protections. A reporting role can read a customer table with identifiers masked, which limits casual exposure without blocking the role’s actual work, while the genuinely sensitive columns that must stay secret even from a server-side reader are protected by Always Encrypted, and the whole table sits behind the authentication, network, and least-privilege gates. Treating masking as a usability refinement rather than a security boundary keeps it in its proper role and avoids the trap of designing a data-protection strategy around a control that obscures rather than encrypts. The classification work that decides which columns need Always Encrypted and which only warrant masking is the same exercise either way: identify the values whose exposure would be the breach, protect those with encryption, and use masking to trim the routine over-exposure of the rest.
Every control in this guide carries an operational cost, and pretending otherwise is how security configurations get abandoned when they collide with delivery. Naming the cost of each move makes the hardening decisions deliberate rather than aspirational, and it is the difference between a posture a team adopts and one they roll back the first time it gets in the way.
Migrating to managed identities removes the stored password, but it requires that the application run on an Azure compute resource that can hold a managed identity and that the connection code use a driver and authentication mode that support tokens. For a workload already on Azure compute this is configuration; for one running elsewhere it forces a decision about a service principal with a certificate instead, which carries its own credential lifecycle. Entra-only enforcement is a one-way change in practice, because it breaks any connection still using a SQL login the instant it is applied, so its cost is the migration discipline of confirming every connection first. Disabling public access and moving to a private endpoint adds the network engineering of the endpoint and the private DNS integration, and it changes how developers connect, since they can no longer reach the database from a laptop on the public internet without a path into the network, which is a deliberate friction that some teams resolve with a bastion or a VPN.
Always Encrypted carries the heaviest functional cost, because it constrains what the server can do with the protected columns and pushes key management into the client, which is why it is reserved for the columns that genuinely warrant it rather than applied across the schema. Auditing and Defender for SQL carry a cost in storage and in the paid plan respectively, and the verification that prevents drift carries a cost in the engineering time to express the posture as code and watch it. None of these costs is a reason to skip the control, but each is a reason to apply the control where it earns its cost and to sequence the work so the highest-value, lowest-friction moves, the entra-auth-and-network rule, come first and the heavier ones follow where the data’s sensitivity justifies them. A posture that accounts for its own operational cost is one that survives contact with a delivery schedule, which is the only kind of posture that protects data over time.
The verdict
Azure SQL security is not a feature to enable but a posture to assemble, and the assembly has an order. The two changes that move it the furthest are Microsoft Entra authentication, taken to Entra-only enforcement with managed identities for applications, and the removal of public network exposure in favor of a private endpoint. Those two close the credential and reachability surfaces that the most common incidents cross, which is the entra-auth-and-network rule and the reason both belong at the top of the work. Behind them, TDE protects data at rest while Always Encrypted protects the most sensitive columns from a server-side reader, two encryption features that defend different things and must not be confused, because believing TDE protects data in use is the misconception that leaves regulated columns exposed to exactly the threat the team thinks they have handled. Auditing and Microsoft Defender for SQL provide the detection that makes the rest trustworthy, and least-privilege roles with row-level security contain whatever a single compromise reaches.
The discipline that ties it together is to treat the surfaces as a system of independent gates and never trade one away to fix a problem in another. Widening a firewall to solve a connectivity issue trades the network gate; storing a password to simplify a connection string trades the credential gate; granting db_owner to unblock a deploy trades the authorization gate. Each trade is locally reasonable and globally corrosive, because it collapses the layered posture back toward the single-control default where one failure is a breach. The InsightCrunch Azure SQL security checklist orders the controls by impact and names the breach each one closes, so a team can work it top to bottom and arrive at a database where authentication, network, encryption, monitoring, and authorization each have to fail on their own before data leaves. That is the difference between a database that is secure and one that merely works, and it is built one gate at a time, in the order the threats arrive.
Frequently asked questions
How do I secure authentication to Azure SQL?
Use Microsoft Entra authentication rather than SQL logins, and take it to its strongest form. Set a Microsoft Entra administrator on the logical server, migrate applications to managed identities so they hold no password at all, create contained database users mapped to Entra principals or groups, and then enable Entra-only authentication on the server so SQL logins cannot authenticate. Entra authentication ties database access to central identity governance, which means conditional access, multifactor authentication, and instant disable on offboarding all apply to the database without any database-side change. Entra-only enforcement is the decisive step, because as long as SQL authentication remains enabled, any administrator can create a SQL login and reopen the long-lived credential surface that leaks most often. The result is a database where a stolen password is worthless, because the credential format is not accepted and the identities that do work are governed where every other identity in the organization is governed.
What is Transparent Data Encryption and what does it cover?
Transparent Data Encryption, or TDE, encrypts the database at rest: the data files, the log files, and the backups are encrypted on disk, and the storage layer encrypts and decrypts pages transparently as they move between disk and memory. It is enabled by default on Azure SQL databases and it defends against the theft-of-media threat, a stolen backup, a copied disk, or a drive that was not wiped before disposal, all of which yield ciphertext rather than readable rows. What TDE does not cover is data in use. Any connection that authenticates and is authorized reads decrypted data, because the engine sees plaintext once pages are in memory. TDE therefore protects storage, not queries, and it is no defense against a leaked credential, a SQL injection flaw, or an over-privileged user reading data through a normal query path. By default it uses a service-managed key, with a customer-managed key in Key Vault available where a compliance regime requires the organization to hold the key itself.
What does Always Encrypted protect?
Always Encrypted protects specific sensitive columns from anyone with server-side access, including database administrators and a server-side attacker. It keeps the chosen columns encrypted at rest, in memory, and in transit, and decryption happens only in the authorized client driver using a column encryption key that the database server cannot access, so the engine stores and returns ciphertext it cannot read. This is the protection TDE cannot provide, because TDE leaves data plaintext to any authorized query while Always Encrypted keeps the protected columns ciphertext even to a fully compromised server. The trade-off is on querying: with deterministic encryption the server can still do equality lookups and joins, while randomized encryption allows only storage and retrieval but resists inference. Reserve Always Encrypted for the columns whose exposure would be the breach you are defending against, such as identifiers, payment data, or health records, rather than encrypting the whole schema, which would make the database almost unqueryable.
How do I lock down Azure SQL network access?
For a production database, disable public network access on the logical server and connect through a private endpoint in your virtual network, then integrate that endpoint with private DNS so the server name resolves to its private IP. With the public listener gone, an internet-origin connection has nothing to reach, which removes an entire class of attack before authentication is even considered. The server firewall is an allowlist on a public endpoint, which means the database stays internet-facing and one over-broad rule away from exposure, so it suits development convenience rather than a production data store. The DNS integration is the step teams most often miss: a private endpoint whose name still resolves to the disabled public address is one no connection uses, so test resolution from inside the network to confirm the gate is actually closed. Keep any remaining firewall rules as narrow as the access genuinely requires, and never use the all-addresses rule or the broad allow-Azure-services setting for real data.
How do auditing and threat detection work for Azure SQL?
Auditing writes a durable record of database events, successful and failed logins, the queries that ran, schema and permission changes, and data access, to a destination such as a storage account or a Log Analytics workspace. Sending it to a workspace makes it queryable alongside sign-in and network logs, which is what lets a security team correlate and investigate rather than read the audit in isolation. Microsoft Defender for SQL is the detection layer: it watches live activity for attack patterns such as SQL injection, access from an unfamiliar principal or location, and data exfiltration, and it runs a vulnerability assessment that flags misconfigurations like excessive permissions or missing auditing. The two work together, detection raises the alarm and the audit provides the trail to scope the incident. Enable auditing at the server level so every current and future database inherits it, and enable Defender at the subscription so a new server does not launch unprotected.
How do I apply least privilege in Azure SQL?
Grant each identity exactly the access its function requires and no more, starting from no access and granting up rather than starting broad and narrowing down. An application that reads and writes its own tables belongs in the data reader and data writer roles, or a custom role scoped to its specific tables and operations, never in db_owner or the server administrator role. The reason is blast radius: when an over-privileged identity is compromised, the attacker inherits everything it could do, so an application in db_owner becomes a database-wide breach, while an application in a narrow role yields only its own access. Test the narrow grant in a lower environment, where a missing permission surfaces as a clear error naming exactly what to add, so the role ends up fitting the application precisely. For shared multitenant tables, add row-level security so the engine filters rows to the connecting identity’s context automatically, which moves tenant isolation from an application invariant every query must honor into a database invariant the engine enforces.
Does enabling TDE mean my sensitive data is protected from everyone?
No, and assuming so is the most common encryption mistake in Azure SQL. TDE protects data at rest, so a stolen backup or disk yields ciphertext, but it leaves data plaintext to any connection that authenticates and is authorized, because the engine decrypts pages as it serves them into memory. A leaked connection string, a SQL injection flaw, an application bug that returns too many rows, or an internal user with excessive access all read plaintext through the normal query path, because to that path the data is decrypted by design. The protections for those threats are authentication, network isolation, least privilege, and detection, with Always Encrypted for the most sensitive columns, which keeps them ciphertext even on the server. TDE is a layer in the posture, not the whole posture, and the false confidence it can create is itself a risk, because a team that believes the data is fully protected never applies the controls that would actually defend it from the threats they have.
What is the difference between deterministic and randomized encryption in Always Encrypted?
Both keep a column encrypted, but they differ in what the server can do with the ciphertext and how much they resist inference. Deterministic encryption always produces the same ciphertext for the same plaintext, which lets the server perform equality comparisons and joins on the column, so the application can look a record up by an encrypted value. The cost is that repeated values produce repeated ciphertext, which can leak information about the distribution of the data to an observer who sees the column. Randomized encryption produces different ciphertext each time even for the same plaintext, which gives no information to an observer and is the stronger choice against inference, but it allows the server to do nothing with the column except store and return it, ruling out searching, grouping, or joining on it. Choose deterministic where the application genuinely needs to query the value and accept the distribution leakage, and randomized where the column is only ever stored and retrieved, which is the safer default for the most sensitive fields.
Why does my application get a login error after I enable Entra-only authentication?
Enabling Entra-only authentication disables every SQL login immediately, so any connection still presenting a username and password starts failing at once. If an application breaks right after the change, it is almost certainly still using a SQL login rather than an Entra identity, and the fix is to migrate it to a managed identity or another Entra principal and grant the corresponding database user before, not after, enforcing Entra-only. When the connection is already using an Entra identity and still fails, the cause is usually a missing database user for that identity or a token-acquisition problem rather than a wrong password, which is a different diagnosis from the classic SQL-login failure. The companion guide on the Azure SQL login failed error 18456 covers how the sub-states change meaning under Entra-only authentication. The safe migration sequence is to set the Entra admin, move each application to an Entra identity, grant its database user and roles, verify in a lower environment, and only then enable Entra-only on the server.
Should I use a customer-managed key for TDE?
Use a customer-managed key when a regulation or a contractual control requires your organization to hold and control the encryption key itself, and use the default service-managed key otherwise. With a customer-managed key stored in Azure Key Vault, the database’s encryption key is protected by a key you own, and revoking access to that key renders the database unreadable, which gives you a hard control over the data’s availability that some compliance regimes mandate. The cost is operational: the key now has a lifecycle your team owns, and losing access to it or deleting it makes the database unrecoverable, so the vault needs soft delete and purge protection and careful access control to avoid turning a security control into an outage. The service-managed key, by contrast, removes that operational risk entirely while providing the same at-rest protection, because Azure owns and rotates the key hierarchy. The decision is about who must control the key, not about the strength of the encryption, which is equivalent either way.
How is a private endpoint different from a firewall rule?
A firewall rule is an allowlist on a public endpoint: the database remains reachable from the internet, and a rule decides which source addresses pass through to authentication. A private endpoint removes the public endpoint entirely and projects the database into your virtual network as a private IP, so it is reachable only over the private network and from on-premises through a VPN or ExpressRoute. The security difference is categorical. With a firewall, the database is internet-facing and the security depends on the allowlist staying narrow, which it tends not to, because connectivity problems get solved by widening rules that then outlive their reason. With a private endpoint and public access disabled, an internet-origin connection reaches nothing, because there is no public listener, so no rule can re-expose the database. For production data the private endpoint is the right control, and the firewall is appropriate only for environments like development where public reachability is an accepted convenience.
Can I use Azure SQL auditing without Microsoft Defender for SQL?
Yes, and they serve different purposes, though the strongest posture uses both. Auditing alone gives you a durable record of database activity that you can query and retain, which is valuable for compliance and for forensic investigation after an incident is already known. What auditing alone does not give you is active detection: the record sits unread until someone has a reason to look at it, so by itself it is a forensic tool rather than a defensive one. Microsoft Defender for SQL adds the detection layer that watches activity for attack patterns and raises alerts in near real time, and it runs vulnerability assessments that flag misconfigurations before they become incidents. Defender without auditing, conversely, gives you alerts with limited context to investigate. The two complement each other, detection to raise the alarm and the audit trail to scope and confirm the incident, so for a production database holding sensitive data, enable both rather than choosing between them.
How do I keep the security posture from drifting over time?
Express the configuration as infrastructure-as-code and the access model as versioned database schema, then verify the live state against the intended state on a schedule. Defining the server and database hardening, the Entra-only enforcement, disabled public access, private endpoint, auditing, and Defender plan, in Bicep or Terraform means every new database launches already secure rather than launching open and being hardened later if anyone remembers. Putting the contained users, role memberships, and row-level security policies in source-controlled schema makes every access change a reviewable pull request with an audit history. For ongoing verification, run scheduled queries against the audit in a Log Analytics workspace to catch unexpected role memberships, failed Entra-only logins, or connections from outside the expected network, and treat Microsoft Defender for SQL’s vulnerability assessment findings as the drift detector for the whole posture. Drift is the normal failure mode, a rule added during an incident, a grant made to unblock a deploy, so continuous verification, not a one-time configuration, is what keeps the posture real.
What database role should an application connect as?
An application should connect as a managed identity mapped to a contained database user that belongs to the narrowest role covering its actual operations, which for most applications is the data reader and data writer roles or a custom role scoped to specific tables. It should never connect as db_owner, the server administrator, or any broad role, because those grant far more than reading and writing the application’s own data, and a compromise of the identity would inherit all of it. The test for whether a role is narrow enough is to deploy with it in a lower environment: if the application functions, the role covers its needs, and if a permission is missing the error names exactly what to add, so the role converges on a precise fit. Using a managed identity rather than a SQL login also removes the stored password from the application’s design, and mapping to an Entra group rather than an individual keeps membership governed centrally, so the application’s database access is both least-privilege and free of a long-lived secret.
Does row-level security replace application-side filtering?
Row-level security enforces a row filter in the database engine, so it is stronger than application-side filtering for the cases it covers, but it complements rather than wholly replaces the application’s own logic. The value of row-level security is that it converts tenant or owner isolation from an invariant every query must remember to honor into one the engine applies automatically, so a query that forgets its WHERE clause still returns only the permitted rows. That closes the data-leak risk of a single missed filter, which is a real and common failure in multitenant designs. It does not remove the need for the application to pass the correct context, such as the tenant identifier in the session, because the predicate evaluates against that context, and it does not replace the coarser controls of authentication, network isolation, and least-privilege roles that sit around it. Use row-level security as the finest boundary inside an already layered posture, ensuring that even a correctly authorized read sees only the rows the connecting identity’s context permits.
Is encryption in transit handled automatically for Azure SQL?
Connections to Azure SQL are encrypted in transit by default, and the service requires TLS for connections, so the data on the wire between a client and the database is protected without additional configuration. The setting worth checking is the minimum TLS version the server enforces, which should be set to the current recommended version so that clients cannot negotiate down to an older, weaker protocol, and the exact recommended minimum is worth verifying against the current Microsoft documentation because the baseline has risen over time. Beyond the transport, the connection should also validate the server certificate rather than trusting it blindly, which the modern drivers do by default and which protects against an interception attack that presents a false endpoint. In-transit encryption protects the data while it moves but, like TDE, leaves it plaintext to the authorized query at the endpoint, so it is one layer among several rather than a complete protection, and the most sensitive columns still warrant Always Encrypted, which keeps them ciphertext on the wire and at the server alike.
How does securing Azure SQL differ from securing on-premises SQL Server?
The principles carry over but the controls and their defaults differ, and assuming the on-premises mental model applies unchanged is a common source of gaps. On-premises, the network is often a trusted perimeter and authentication leans on Windows or SQL logins; in Azure the database has a public endpoint by default, so the network gate has to be deliberately closed with a private endpoint rather than assumed from a corporate perimeter. Identity moves from Active Directory to Microsoft Entra ID, which brings central governance, conditional access, and managed identities that have no on-premises equivalent and that remove the stored password entirely. Encryption at rest through TDE is on by default in Azure rather than something an administrator configures, and Always Encrypted and dynamic data masking are available as managed features. Detection through Microsoft Defender for SQL and auditing to a Log Analytics workspace replace the bespoke monitoring an on-premises deployment assembles. The shift is toward identity-centric, platform-managed controls, and the discipline is to close the network and credential surfaces that the cloud’s defaults leave more open than a private data center did.
