Security for Azure SQL Database and Managed Instance

Enable Microsoft Entra Database Authentication

Microsoft Entra ID (formerly Azure Active Directory) provides a central way to manage who can access Azure SQL Database and Azure SQL Managed Instance. Instead of creating separate SQL logins for each user, organizations can connect their existing identity system to the database, which means users sign in with the same credentials they use for email and other Microsoft services. When a user authenticates through Entra ID, the database receives a token that proves their identity, and the database engine then checks what permissions that user has been granted. This approach reduces the number of passwords teams must manage and makes it easier to revoke access when someone leaves the organization, because the change happens in one central place rather than in every database. Database administrators grant permissions to Entra ID users or groups the same way they grant permissions to SQL logins, using standard SQL commands like GRANT and REVOKE.

Enable Database Auditing

Database auditing tracks what actions users perform inside the database, which helps organizations detect suspicious activity, meet compliance requirements, and investigate security incidents. When auditing is enabled, Azure SQL Database records events such as who logged in, what queries they ran, and whether they succeeded or failed in their attempts to access data. The audit logs are stored in Azure Storage, Log Analytics, or Event Hubs, depending on how the organization wants to analyze them. Administrators can review these logs manually or set up automated alerts that trigger when certain patterns appear, such as repeated failed login attempts or access to sensitive tables outside business hours. Auditing adds some overhead to database operations, so teams typically configure it to capture the events that matter most for their security and compliance needs rather than logging everything.

Plan and Implement Dynamic Data Masking

Dynamic data masking hides sensitive information in query results so that users who should not see certain data never receive it in the first place. For example, a customer service representative might need to see a customer's name and account number but should not see their full social security number or credit card details; dynamic masking replaces those sensitive fields with masked values like "XXX-XX-1234" while leaving the actual data untouched in the database. Administrators define masking rules at the column level, specifying which fields to mask and what pattern to use, such as showing only the last four digits of a phone number. The masking happens automatically when the query runs, so applications do not need to be modified to support it. This control works alongside other security measures like authentication and encryption, adding a layer of protection for data that is already stored in the database.

Implement Transparent Data Encryption

Transparent Data Encryption (TDE) protects data at rest by encrypting the entire database, including backups and transaction logs, without requiring changes to applications that use the database. When data is written to disk, TDE encrypts it using a symmetric key, and when authorized users read it, TDE decrypts it automatically; this process happens transparently to anyone querying the database. The encryption key itself is protected by either a service-managed key that Azure handles automatically or a customer-managed key stored in Azure Key Vault, giving organizations control over key rotation and access. TDE defends against scenarios where someone gains physical access to the storage media or steals backup files, because without the encryption key the data appears as unreadable gibberish. Enabling TDE does not significantly impact performance because the encryption and decryption operations are optimized at the storage layer.

Recommend When to Use Azure SQL Database Always Encrypted

Always Encrypted goes beyond TDE by protecting sensitive data even while it is in use during query processing, meaning the data is encrypted in memory and in transit, and only the application that holds the encryption key can decrypt it. This makes Always Encrypted ideal for scenarios where strict data isolation is required, such as storing credit card numbers, social security numbers, or health records, because even database administrators and cloud operators cannot view the plaintext values. The encryption keys are stored outside the database, typically in Azure Key Vault or a Windows certificate store on the application server, and the database engine never receives the keys themselves. However, Always Encrypted limits which operations can be performed on encrypted columns, because the database cannot compute or compare encrypted values; for example, you cannot filter or sort by an encrypted column without first decrypting it on the client side. Organizations choose Always Encrypted when the sensitivity of the data outweighs the operational complexity it introduces, while using TDE for broader protection across all data in the database.