Implement a secure environment

Authentication

Microsoft Entra ID setup

• SQL Database: set an Entra admin on the logical server (a user or, better, a group). Then create contained users with CREATE USER [name] FROM EXTERNAL PROVIDER. The Entra admin is the only one who can create the first Entra users, and the connection to run it must itself use Entra authentication.
• Managed Instance: set an Entra admin and give the instance’s identity the Directory Readers permission (or use a user-assigned managed identity with the needed Graph permissions) so it can resolve names. Create Entra logins (server-level) with CREATE LOGIN [name] FROM EXTERNAL PROVIDER, then users.
• SQL Server on Azure VM or on-premises (2022+): Entra authentication needs the server Arc-enabled (or an Azure VM with the SQL IaaS extension), an Entra application and a certificate or managed-identity-backed key vault configuration. Set the Entra admin in the portal.
• Entra modes: Password, Integrated (federated domain), Universal with MFA, Service principal, Managed identity, Default. For apps running in Azure, use managed identity so there is no secret.
• Entra-only authentication disables SQL logins on the server. It’s the answer for “force all access through Entra”.
• A database user created from an Entra group lets you grant permissions once and manage membership in Entra.

Authentication for SQL on Azure VMs and Managed Instance

• VMs: mixed mode or Windows-only authentication set at install or in the portal; AD-domain-joined VMs use Kerberos/Windows authentication, with an Azure AD Domain Services or hybrid AD. Entra authentication as above.
• Managed Instance: SQL authentication and Entra authentication only. No Windows authentication directly, but Windows Authentication for Entra principals (Kerberos through incoming trust-based flow or modern interactive flow) lets domain-joined clients use Kerberos with Entra identities.

Security principals and permissions

• Logins (server-level) and users (database-level). In SQL Database, prefer contained database users (no login) so the database is portable. MI and VMs use logins mapped to users.
• Fixed server roles (sysadmin on VMs and MI; SQL Database uses ##MS_ServerStateReader## style server roles and loginmanager/dbmanager database roles in master) and fixed database roles (db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_accessadmin, db_backupoperator, db_denydatareader, db_denydatawriter).
• Custom roles: create a role, grant needed permissions, add members. Prefer role-based grants to per-user grants.
• Securables form a hierarchy: server, database, schema, object. Schema-level grants (GRANT SELECT ON SCHEMA::Sales TO role) are a clean way to enforce least privilege.

T-SQL basics

CREATE USER [app-identity] FROM EXTERNAL PROVIDER;
CREATE ROLE report_reader;
GRANT SELECT ON SCHEMA::Reporting TO report_reader;
ALTER ROLE report_reader ADD MEMBER [app-identity];
DENY UPDATE ON dbo.Salaries TO report_reader;
REVOKE SELECT ON dbo.Customers FROM report_reader;

• GRANT gives, DENY blocks and overrides grants (except for sysadmin/db_owner/CONTROL ownership chains), REVOKE removes a prior grant or deny.
• EXECUTE AS and module signing let a procedure do work the caller can’t, and ownership chaining skips permission checks on objects with the same owner. Use stored procedures as the access layer for least privilege.
• CONTROL is high, equivalent to ownership of the securable. Avoid when SELECT/INSERT/UPDATE/DELETE/EXECUTE is enough.
• Inspect with sys.database_principals, sys.database_role_members, sys.database_permissions, fn_my_permissions.

Graphical tools

In SSMS: Security folder to create users, roles, set database properties > Permissions and Securables. In the Azure portal, you configure the Entra admin and firewall, but object permissions are in SSMS (or T-SQL). Azure Data Studio was retired, so use SSMS or Visual Studio Code with the MSSQL extension.

Troubleshooting authentication and authorization

Encryption at rest and in transit

Transparent data encryption (TDE)

TDE encrypts data files, log files and backups at rest with a database encryption key (DEK) protected by a TDE protector. It doesn’t hide data from anyone who is connected.

• With a customer-managed key, the server needs an identity with get, wrapKey, unwrapKey on the key (or Key Vault RBAC role). If the key becomes inaccessible, the database goes inaccessible after a short time. Enable soft delete and purge protection on the vault.
• Key rotation: update the protector in Key Vault, rotate automatically with the auto-rotation feature.
• SQL Server on VM: create a master key in master, a certificate, a database encryption key, then ALTER DATABASE ... SET ENCRYPTION ON. Back up the certificate and private key, or a restore elsewhere will fail. The SQL Server Connector for Azure Key Vault (or EKM) supports a Key Vault key.
• Geo-replicas and failover groups must use the same protector key available on both servers.
• Hyperscale and MI support TDE; TDE for tempdb is encrypted when any database on the instance is encrypted.

Object-level (cell/column) encryption

T-SQL encryption functions let the database itself hold the keys: ENCRYPTBYPASSPHRASE, ENCRYPTBYKEY / DECRYPTBYKEY with symmetric keys, protected by certificates or asymmetric keys, under a database master key. The engine can decrypt, so DBAs with enough rights can read the data. Use for specific columns when you need encryption inside the engine and indexing on ciphertext isn’t needed. Encrypted columns use varbinary and lose range or equality search unless you store a hash.

Always Encrypted

Always Encrypted encrypts data in the client driver; the engine never sees plaintext or the keys. It protects against DBAs, cloud operators and anyone with server access.

• Column master key (CMK) lives in Azure Key Vault, Windows certificate store or HSM. Column encryption key (CEK) is stored encrypted by the CMK in the database.
• Deterministic encryption: same plaintext gives same ciphertext, so equality comparison, joins, grouping and indexes work. Weaker (patterns leak). Randomized: stronger, supports no operations on ciphertext (without enclaves).
• Client requires Column Encryption Setting=Enabled in the connection string and a supported driver. Queries need parameterised values for encrypted columns.
• Limitations: no LIKE, range or sort on ciphertext without enclaves; can’t be combined with some features (temporal, some replication).

Always Encrypted with secure enclaves

An enclave is a protected region inside the engine where data can be decrypted and computed on, without exposing plaintext to the host or DBA. Enables in-place encryption (change keys or encrypt existing columns with T-SQL, no data round trip), and rich computations on randomized-encrypted columns: =, <>, >, <, LIKE, BETWEEN, IN, and sort. Requires enclave-enabled keys, a driver with enclave support, and attestation:

• SQL Database: VBS enclaves (Virtualization-based security) or Intel SGX in DC-series hardware (SGX with Microsoft Azure Attestation; VBS uses none, or attestation configured).
• SQL Server 2019+: VBS enclaves, with Host Guardian Service attestation or no attestation.
• Pick this when the question needs pattern matching or range queries on encrypted data while hiding it from admins.

Encryption in transit

• All Azure SQL connections use TLS (minimum TLS 1.2 recommended). Set minimum TLS version on the server. Use Encrypt=True and TrustServerCertificate=False in connection strings. On SQL Server VMs, install a trusted certificate and enable Force Encryption or strict (TDS 8.0).
• Connection policy: Proxy (via gateway, port 1433) vs Redirect (client connects directly to the node after the handshake, needs port range 11000-11999) vs Default (Redirect for connections from inside Azure, Proxy from outside).

Firewall rules and network isolation

Firewall rules

Rules are evaluated database-level first, then server-level. Deny public network access blocks the public endpoint entirely. Max 128 server rules and 128 database rules. Managed Instance has no firewall rules: it uses NSGs and route tables on its subnet and its public endpoint (port 3342) is off by default.

Private link and service endpoints

Pick private endpoint for “access from on-premises over private IP”, “no public exposure”, “most secure”. Pick service endpoint for a cheap VNet-to-service lock without DNS changes. For MI, the instance already lives in your VNet, and you can add private endpoints for cross-VNet access.

Compliance controls for sensitive data

Data discovery and classification

Data discovery and classification scans columns, suggests labels (Sensitivity labels from Microsoft Purview or built-in information types) and information types, stores them as column metadata (sys.sensitivity_classifications), appears in audit logs (data_sensitivity_information), and gives a report. Strategy: discover, review recommendations, classify, combine with auditing, masking and Always Encrypted for the controls. It doesn’t itself prevent access. Defender for SQL vulnerability assessment and Purview extend classification.

Auditing

Auditing tracks events to an audit log: Azure Storage account, Log Analytics workspace, Event Hubs. Set at server level (applies to all databases) or database level; server auditing is the typical answer. Defaults capture successful and failed logins and batch completed events. Audit logs go to Log Analytics for query and alerts, Event Hubs for SIEM, storage for retention (.xel files).

• On SQL Server (VM), you create a SERVER AUDIT, SERVER AUDIT SPECIFICATION and DATABASE AUDIT SPECIFICATION and send to a file, the Windows application/security log, or URL.
• Managed Instance supports server audit to Blob storage (CREATE SERVER AUDIT ... TO URL), plus Log Analytics and Event Hubs through diagnostic settings.
• Microsoft Defender for SQL adds threat detection (SQL injection, anomalous access) and vulnerability assessment.

Change tracking and change data capture

The objective says “change data tracking”. Know both:

ALTER DATABASE ... SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON); sys.sp_cdc_enable_db then sys.sp_cdc_enable_table.

Dynamic data masking (DDM)

DDM obscures query results for non-privileged users without changing stored data. Functions: default, email, random (numbers), partial (custom string). Configured by rule on a column: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'). The UNMASK permission (granular at column, table, schema, database level) lets users see real values. Users with db_owner, CONTROL and administrators see unmasked data. DDM isn’t encryption or a security boundary against a determined user: inference queries (WHERE Salary > 50000) can reveal values. Choose it for “show limited info to support staff” and pair with permissions.

Ledger

Ledger provides tamper evidence with cryptographic hashing (blockchain-like chain of block hashes), so you can prove data was not altered.

Database digests (hashes of the latest block) are stored outside the database’s trust boundary: Azure Confidential Ledger or immutable Azure Blob storage; automatic digest management on SQL Database. sp_verify_database_ledger checks integrity against the digests. Choose ledger for “prove no one, including DBAs, tampered with records” (financial, audit). It doesn’t hide or encrypt data.

Row-level security (RLS)

RLS limits which rows a user can read or modify, enforced in the engine by a security policy with an inline table-valued predicate function.

• Filter predicate: silently filters rows for SELECT, UPDATE, DELETE.
• Block predicate: stops violating writes (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE).
• Use SESSION_CONTEXT or USER_NAME()/IS_MEMBER() in the predicate for tenant or user mapping. Applications using a shared login set sp_set_session_context.
• CREATE SECURITY POLICY Policy ADD FILTER PREDICATE dbo.fn_tenant(TenantId) ON dbo.Orders WITH (STATE = ON);

Which control for which threat