Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics
This article outlines the basics of securing the data tier of an application using Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The security strategy described follows the layered defense-in-depth approach as shown in the picture below, and moves from the outside in:
Microsoft Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics provide a relational database service for cloud and enterprise applications. To help protect customer data, firewalls prevent network access to the server until access is explicitly granted based on IP address or Azure Virtual network traffic origin.
IP firewall rules
IP firewall rules grant access to databases based on the originating IP address of each request. For more information, see Overview of Azure SQL Database and Azure Synapse Analytics firewall rules.
Virtual network firewall rules
Virtual network service endpoints extend your virtual network connectivity over the Azure backbone and enable Azure SQL Database to identify the virtual network subnet that traffic originates from. To allow traffic to reach Azure SQL Database, use the SQL service tags to allow outbound traffic through Network Security Groups.
Virtual network rules enable Azure SQL Database to only accept communications that are sent from selected subnets inside a virtual network.
Controlling access with firewall rules does not apply to SQL Managed Instance. For more information about the networking configuration needed, see Connecting to a managed instance
Managing databases and servers within Azure is controlled by your portal user account's role assignments. For more information on this article, see Azure role-based access control in the Azure portal.
Authentication is the process of proving the user is who they claim to be. Azure SQL Database and SQL Managed Instance support SQL authentication and Azure AD authentication. SQL Managed instance additionally supports Windows Authentication for Azure AD principals.
SQL authentication refers to the authentication of a user when connecting to Azure SQL Database or Azure SQL Managed Instance using username and password. A server admin login with a username and password must be specified when the server is being created. Using these credentials, a server admin can authenticate to any database on that server or instance as the database owner. After that, additional SQL logins and users can be created by the server admin, which enable users to connect using username and password.
Azure Active Directory authentication:
Azure Active Directory authentication is a mechanism of connecting to Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics by using identities in Azure Active Directory (Azure AD). Azure AD authentication allows administrators to centrally manage the identities and permissions of database users along with other Azure services in one central location. This includes the minimization of password storage and enables centralized password rotation policies.
A server admin called the Active Directory administrator must be created to use Azure AD authentication with SQL Database. For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication. Azure AD authentication supports both managed and federated accounts. The federated accounts support Windows users and groups for a customer domain federated with Azure AD.
Additional Azure AD authentication options available are Active Directory Universal Authentication for SQL Server Management Studio connections including multi-factor authentication and Conditional Access.(Video) Azure SQL Managed Instance
Windows Authentication for Azure AD Principals:
Kerberos authentication for Azure AD Principals enables Windows Authentication for Azure SQL Managed Instance. Windows Authentication for managed instances empowers customers to move existing services to the cloud while maintaining a seamless user experience and provides the basis for infrastructure modernization.
To enable Windows Authentication for Azure Active Directory (Azure AD) principals, you will turn your Azure AD tenant into an independent Kerberos realm and create an incoming trust in the customer domain. Learn how Windows Authentication for Azure SQL Managed Instance is implemented with Azure Active Directory and Kerberos.
Managing databases and servers within Azure is controlled by your portal user account's role assignments. For more information on this article, see Azure role-based access control in Azure portal. Controlling access with firewall rules does not apply to SQL Managed Instance. Please see the following article on connecting to a managed instance for more information about the networking configuration needed.
Authorization refers to controlling access on resources and commands within a database. This is done by assigning permissions to a user within a database in Azure SQL Database or Azure SQL Managed Instance. Permissions are ideally managed by adding user accounts to database roles and assigning database-level permissions to those roles. Alternatively an individual user can also be granted certain object-level permissions. For more information, see Logins and users
As a best practice, create custom roles when needed. Add users to the role with the least privileges required to do their job function. Do not assign permissions directly to users. The server admin account is a member of the built-in db_owner role, which has extensive permissions and should only be granted to few users with administrative duties. To further limit the scope of what a user can do, the EXECUTE AS can be used to specify the execution context of the called module. Following these best practices is also a fundamental step towards Separation of Duties.
Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (for example, group membership or execution context). Row-Level Security can also be used to implement custom Label-based security concepts. For more information, see Row-Level security.
SQL Database and SQL Managed Instance secure customer data by providing auditing and threat detection capabilities.
SQL auditing in Azure Monitor logs and Event Hubs
SQL Database and SQL Managed Instance auditing tracks database activities and helps maintain compliance with security standards by recording database events to an audit log in a customer-owned Azure storage account. Auditing allows users to monitor ongoing database activities, as well as analyze and investigate historical activity to identify potential threats or suspected abuse and security violations. For more information, see Get started with SQL Database Auditing.
Advanced Threat Protection
Advanced Threat Protection is analyzing your logs to detect unusual behavior and potentially harmful attempts to access or exploit databases. Alerts are created for suspicious activities such as SQL injection, potential data infiltration, and brute force attacks or for anomalies in access patterns to catch privilege escalations and breached credentials use. Alerts are viewed from the Microsoft Defender for Cloud, where the details of the suspicious activities are provided and recommendations for further investigation given along with actions to mitigate the threat. Advanced Threat Protection can be enabled per server for an additional fee. For more information, see Get started with SQL Database Advanced Threat Protection.
Information protection and encryption
Transport Layer Security (Encryption-in-transit)
SQL Database, SQL Managed Instance, and Azure Synapse Analytics secure customer data by encrypting data in motion with Transport Layer Security (TLS).
SQL Database, SQL Managed Instance, and Azure Synapse Analytics enforce encryption (SSL/TLS) at all times for all connections. This ensures all data is encrypted "in transit" between the client and server irrespective of the setting of Encrypt or TrustServerCertificate in the connection string.
As a best practice, recommend that in the connection string used by the application, you specify an encrypted connection and not trust the server certificate. This forces your application to verify the server certificate and thus prevents your application from being vulnerable to man in the middle type attacks.
For example when using the ADO.NET driver this is accomplished via Encrypt=True and TrustServerCertificate=False. If you obtain your connection string from the Azure portal, it will have the correct settings.
Note that some non-Microsoft drivers may not use TLS by default or rely on an older version of TLS (<1.2) in order to function. In this case the server still allows you to connect to your database. However, we recommend that you evaluate the security risks of allowing such drivers and application to connect to SQL Database, especially if you store sensitive data.
For further information about TLS and connectivity, see TLS considerations
Transparent Data Encryption (Encryption-at-rest)
Transparent data encryption (TDE) for SQL Database, SQL Managed Instance, and Azure Synapse Analytics adds a layer of security to help protect data at rest from unauthorized or offline access to raw files or backups. Common scenarios include data center theft or unsecured disposal of hardware or media such as disk drives and backup tapes. TDE encrypts the entire database using an AES encryption algorithm, which doesn't require application developers to make any changes to existing applications.
In Azure, all newly created databases are encrypted by default and the database encryption key is protected by a built-in server certificate. Certificate maintenance and rotation are managed by the service and require no input from the user. Customers who prefer to take control of the encryption keys can manage the keys in Azure Key Vault.
Key management with Azure Key Vault
Bring Your Own Key (BYOK) support for Transparent Data Encryption (TDE) allows customers to take ownership of key management and rotation using Azure Key Vault, Azure's cloud-based external key management system. If the database's access to the key vault is revoked, a database cannot be decrypted and read into memory. Azure Key Vault provides a central key management platform, leverages tightly monitored hardware security modules (HSMs), and enables separation of duties between management of keys and data to help meet security compliance requirements.
Always Encrypted (Encryption-in-use)
Always Encrypted is a feature designed to protect sensitive data stored in specific database columns from access (for example, credit card numbers, national/regional identification numbers, or data on a need to know basis). This includes database administrators or other privileged users who are authorized to access the database to perform management tasks, but have no business need to access the particular data in the encrypted columns. The data is always encrypted, which means the encrypted data is decrypted only for processing by client applications with access to the encryption key. The encryption key is never exposed to SQL Database or SQL Managed Instance and can be stored either in the Windows Certificate Store or in Azure Key Vault.
Dynamic data masking
Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking automatically discovers potentially sensitive data in Azure SQL Database and SQL Managed Instance and provides actionable recommendations to mask these fields, with minimal impact to the application layer. It works by obfuscating the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. For more information, see Get started with SQL Database and SQL Managed Instance dynamic data masking.
Vulnerability assessment is an easy to configure service that can discover, track, and help remediate potential database vulnerabilities with the goal to proactively improve overall database security. Vulnerability assessment (VA) is part of the Microsoft Defender for SQL offering, which is a unified package for advanced SQL security capabilities. Vulnerability assessment can be accessed and managed via the central Microsoft Defender for SQL portal.
Data discovery and classification
Data discovery and classification (currently in preview) provides basic capabilities built into Azure SQL Database and SQL Managed Instance for discovering, classifying and labeling the sensitive data in your databases. Discovering and classifying your utmost sensitive data (business/financial, healthcare, personal data, etc.) can play a pivotal role in your organizational Information protection stature. It can serve as infrastructure for:
- Various security scenarios, such as monitoring (auditing) and alerting on anomalous access to sensitive data.
- Controlling access to, and hardening the security of, databases containing highly sensitive data.
- Helping meet data privacy standards and regulatory compliance requirements.
For more information, see Get started with data discovery and classification.
In addition to the above features and functionality that can help your application meet various security requirements, Azure SQL Database also participates in regular audits, and has been certified against a number of compliance standards. For more information, see the Microsoft Azure Trust Center where you can find the most current list of SQL Database compliance certifications.
- For a discussion of the use of logins, user accounts, database roles, and permissions in SQL Database and SQL Managed Instance, see Manage logins and user accounts.
- For a discussion of database auditing, see auditing.
- For a discussion of threat detection, see threat detection.
What is the security in Azure SQL Managed Instance? ›
The data is always encrypted, which means the encrypted data is decrypted only for processing by client applications with access to the encryption key. The encryption key is never exposed to SQL Database or SQL Managed Instance and can be stored either in the Windows Certificate Store or in Azure Key Vault.What's the difference between Azure SQL Database and Azure SQL managed instance? ›
The most significant difference from SQL Database and SQL Managed Instance is that SQL Server on Azure Virtual Machines allows full control over the database engine.What is the security of Azure SQL Database? ›
Azure SQL Database secures data by allowing you to: Limit access using firewall rules. Use authentication mechanisms that require identity. Use authorization with role-based memberships and permissions.What are the benefits of Azure SQL managed instance over Azure SQL Database? ›
SQL Managed Instance provides additional security isolation from other tenants on the Azure platform. Security isolation includes: Native virtual network implementation and connectivity to your on-premises environment using Azure ExpressRoute or VPN Gateway.Which four of the following detection types are available to Azure SQL Database Protection? ›
- SQL Injection.
- SQL Injection vulnerability.
- Unusual usage pattern.
- Login from new location.
- Run Routine Security Audits. ...
- Have a Strong Password Policy. ...
- Deploy and Test SQL Server Updates. ...
- Use a Firewall. ...
- Use Encryption. ...
- Avoid Installing Non-Essential Software. ...
- Use a SQL Monitoring Tool. ...
- Use a Data Access Controller.
Up to 280, unless the instance storage size or Azure Premium Disk storage allocation space limit has been reached. 32,767 files per database, unless the instance storage size limit has been reached. Maximum size of each data file is 8 TB. Use at least two data files for databases larger than 8 TB.How many databases can you have in Azure SQL Managed Instance? ›
The limit of 100 databases per SQL Managed Instance is a hard limit that cannot be changed.What is the difference between Azure SQL Managed Instance and Cosmos DB? ›
Azure SQL is based on SQL Server engine, you can easily migrate applications and continue to use the tools, languages, and resources that you're familiar with. Azure Cosmos DB is used for web, mobile, gaming, and IoT application that needs to handle massive amounts of data, reads, and writes at a global scale.What type of encryption does Azure SQL Database use? ›
In Azure, the default setting for TDE is that the DEK is protected by a built-in server certificate. The built-in server certificate is unique for each server and the encryption algorithm used is AES 256.
Is Azure SQL DB encrypted at rest? ›
Azure SQL Database currently supports encryption at rest for Microsoft-managed service side and client-side encryption scenarios. Support for server encryption is currently provided through the SQL feature called Transparent Data Encryption.Is Azure SQL Database protected by firewall? ›
Azure SQL Database creates a firewall at the server level for single and pooled databases. This firewall blocks connections from IP addresses that do not have permission. To connect to an Azure SQL database from an IP address outside of Azure, you need to create a firewall rule.What is general purpose in Azure SQL Database Managed instance? ›
The General Purpose service tier is the default service tier in Azure SQL Database designed for most of generic workloads. If you need a fully managed database engine with a default SLA and storage latency between 5 and 10 ms, the General Purpose tier is the option for you.What is the benefit of Azure managed instance? ›
- Manage and scale up to thousands of Linux and Windows VMs.
- Build and scale with managed Kubernetes.
- Azure Spring Apps. ...
- Execute event-driven serverless code functions with an end-to-end development experience.
The database is the set of files where application data (the reason for a database) and meta data is stored. An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database.How do I threat detection in Azure SQL Database? ›
Launch the Azure Portal at https://portal.azure.com. Navigate to the configuration blade of the SQL Database you want to monitor. In the Settings blade, select Auditing & Threat Detection. In the Auditing & Threat Detection configuration blade turn ON auditing, which will display the Threat detection settings.Which three security features match the SQL Server security level? ›
There are three security features that match the database level security: authentication, authorization, and encryption.What are five key steps that help to ensure database security? ›
- Deploy physical database security. ...
- Separate database servers. ...
- Set up an HTTPS proxy server. ...
- Avoid using default network ports. ...
- Use real-time database monitoring. ...
- Use database and web application firewalls.
Database security is based on three important constructs—confidentiality, integrity, and availability. The goal of database security is to protect your critical and confidential data from unauthorized access.What are the security features in SQL Server? ›
Transparent Data Encryption (TDE) is supported to work with all other security capabilities in SQL Server. Transparent Data Encryption (TDE) provides real-time I/O encryption and decryption of the data and log files. TDE encryption leverages a database encryption key (DEK) is stored in the user database.
Which feature is not supported by Azure SQL Managed instance? ›
|Feature||Azure SQL Database||Azure SQL Managed Instance|
|Cross-database transactions||No||Yes, within the instance. See Linked server differences for cross-instance queries.|
|Database mail - DbMail||No||Yes|
Procedures and Triggers are not supported in the Azure SQL Managed Instance.What are the advantages of managed instance? ›
Managed instance groups maintain high availability of your applications by proactively keeping your instances available. A MIG automatically repairs failed instances by recreating them. You might also want to repair instances when an application freezes, crashes, or runs out of memory.What is the maximum database size in Azure SQL Managed instance? ›
On Azure SQL Managed Instance you can store up to 16 TB of data as stated here. You can also decide to leave Azure SQL (PaaS) and go back to Azure SQL Server VMs (IaaS).What is the compatibility level of Azure SQL Database Managed instance? ›
|Product||Database Engine version||Default compatibility level designation|
|SQL Server 2022 (16.x)||16||160|
|SQL Server 2019 (15.x)||15||150|
|SQL Server 2017 (14.x)||14||140|
|Azure SQL Database||12||150|
You can create multiple pools on a server, but you can't add databases from different servers into the same pool.What are the disadvantages of Azure Cosmos DB? ›
Performance: Azure Cosmos DB can be slow when it comes to large amounts of data and complex queries, as it is optimized for high availability and consistency over raw performance. Cost: Azure Cosmos DB can be expensive, especially for high-performance and high-throughput use cases.How do I migrate Azure SQL managed instance to Azure SQL Database? ›
Connect to your source SQL Server instance. Click the Migrate to Azure SQL button, in the Azure SQL Migration wizard in Azure Data Studio. Select databases for assessment, then click on next. Select your Azure SQL target, in this case, Azure SQL Managed Instance.Why is Cosmos DB better than SQL? ›
In contrast to SQL Server which provides strong ACID guarantees by default, Cosmos DB allows you to choose between multiple consistency levels, each providing its guarantees. The most extreme of these, eventual consistency, only guarantees that all replicas will converge at some point in the future.What is the difference between Azure SQL Always encrypted and TDE? ›
TDE works with SQL Server 2008 and above as well as Azure SQL Database, but requires SQL Server Enterprise Edition. Always Encrypted works with all editions of SQL Server 2016 (13. x) SP1 and above, plus Azure SQL Database. Both TDE and Always Encrypted are free in Azure SQL Database.
Which key provides strongest encryption in SQL Server? ›
Back Up the Service Master Key - SQL Server
The service master key is the root of the encryption hierarchy.
- Use Universal Authentication in SSMS. ...
- Use Interactive Authentication supported in SQL Server Data Tools (SSDT). ...
- Use other SQL tools supporting Multi-Factor Authentication.
TDE stores the entire database in an encrypted format. Data at Rest Encryption prevents those with physical access to the database or a backup copy mounting it on another SQL service instance.Is Azure SQL Database case sensitive? ›
As indicated in https://azure.microsoft.com/en-us/blog/working-with-collations-in-sql-azure/, Azure sql server uses a default collation called "SQL_Latin1_General_CP1_CI_AS". That is, the western latin alphabet, case insensitive, but accent sensitive. If your own database was case sensitive, you are now in trouble.How do you tell if a SQL DB is encrypted? ›
If you query sys. dm_database_encryption_keys, the encryption state column will tell you whether database is encrypted or not. If you query sys. dm_database_encryption_keys, the encryption state column will tell you whether database is encrypted or not.What are the security benefits of Azure SQL? ›
Azure SQL Database firewall
To help protect customer data, Azure SQL Database includes a firewall functionality, which by default prevents all access to SQL Database, as shown below. The gateway firewall can limit addresses, which allows customers granular control to specify ranges of acceptable IP addresses.
Two authentication flows are available to implement Windows Authentication for Azure AD principals on Azure SQL Managed Instance: the incoming trust-based flow supports AD joined clients running Windows server 2012 or higher, and the modern interactive flow supports Azure AD joined clients running Windows 10 21H1 or ...What type of authentication does Azure use for SQL Server? ›
You can now connect to SQL Server using the following authentication methods using Azure AD identities: Azure Active Directory Password. Azure Active Directory Integrated. Azure Active Directory Universal with Multi-Factor Authentication.What is difference between Azure SQL Database and SQL Managed Instance? ›
SQL Managed Instance provides support for instance-scoped features enabling easy migration of existing applications, as well as sharing resources among databases. Whereas, SQL Server on Azure VMs provide DBAs with an experience most similar to the on-premises environment they're familiar with.What are the two types of authentication supported by Azure SQL Database and SQL Managed Instance? ›
Authentication. Authentication is the process of proving the user is who they claim to be. Azure SQL Database and SQL Managed Instance support SQL authentication and Azure AD authentication. SQL Managed instance additionally supports Windows Authentication for Azure AD principals.
What can you use to provide protection for Azure SQL managed instance? ›
- Limit access using firewall rules.
- Use authentication mechanisms that require identity.
- Use authorization with role-based memberships and permissions.
- Enable security features.
III. For Managed Instance, you can create linked servers to do a cross database queries. An Azure relational database service.What are the 2 types of SQL instance? ›
Instance of SQL Server
If we install 'n' times, then 'n' instances will be created. There are two types of instances in SQL Server a) Default b) Named. Only one default instance will be supported in one Server. Multiple named instances will be supported in one Server.
SQL Server provides four system databases including master , msdb , model , and tempdb . The master system database stores system-level information of the SQL server instance.How secure is Azure managed identity? ›
Managed identities enable Azure resources to communicate with services that support Azure AD authentication. No one, including the Global Administrator, has access to the credentials, which can't be accidentally leaked by being included in code.Which encryption security is available in SQL Azure? ›
Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest.Does SQL managed instance support Windows Authentication? ›
There are two phases to set up Windows Authentication for Azure SQL Managed Instance using Azure Active Directory (Azure AD) and Kerberos. One-time infrastructure setup. Synchronize Active Directory (AD) and Azure AD, if this hasn't already been done. Enable the modern interactive authentication flow, when available.Are Azure managed disks encrypted? ›
Most Azure managed disks are encrypted with Azure Storage encryption, which uses server-side encryption (SSE) to protect your data and to help you meet your organizational security and compliance commitments.What is managed identity in Azure SQL Database? ›
Managed Service Identities are automatically managed by Azure and enable you to authenticate to services that support Azure AD authentication, without needing to insert credentials into your code. You learn how to: Grant your VM access to Azure SQL Database. Enable Azure AD authentication.How does Azure handle security? ›
With Azure Storage, you can secure data using: Transport-level encryption, such as HTTPS when you transfer data into or out of Azure Storage. Wire encryption, such as SMB 3.0 encryption for Azure File shares.
Is Azure SQL database encrypted at rest? ›
Azure SQL Database currently supports encryption at rest for Microsoft-managed service side and client-side encryption scenarios. Support for server encryption is currently provided through the SQL feature called Transparent Data Encryption.What is the always encrypted feature for Azure SQL databases? ›
Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national/regional identification numbers (for example, U.S. social security numbers), stored in Azure SQL Database, Azure SQL Managed Instance, and SQL Server databases.What features are supported by Azure SQL Managed Instance? ›
|Feature||Azure SQL Database||Azure SQL Managed Instance|
|Cross-database transactions||No||Yes, within the instance. See Linked server differences for cross-instance queries.|
|Database mail - DbMail||No||Yes|
As per the document from Microsoft at https://learn.microsoft.com/en-us/learn/modules/azure-database-fundamentals/azure-sql-managed-instance, the Azure SQL database and Azure SQL Managed Instance are PaaS.What are the two types of authentication in SQL Server? ›
SQL Server supports two authentication modes, Windows authentication mode and mixed mode.Which authentication is more secure in SQL Server? ›
Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication.What is the difference between SQL Server SQL authentication and Windows Authentication? ›
If you work in an Active Directory environment, Windows authentication is recommended to use. If you work in a non-Active Directory environment, you can utilize SQL Server authentication for database connections. Windows authentication does provide more security and flexibility for managing logins in SQL Server.