Securing your data ecosystem in Azure SQL Managed Instance
Published Jun 21 2023 05:48 AM 4,260 Views

When you run SQL Servers on-premises, you own the entire burden of securing your data. As you modernize applications and migrate to Azure SQL Managed Instance, you delegate some of the responsibility for security to Microsoft. In doing so, you gain additional benefits over and above dedicating time to staying on top of ports, patches, and protection. Microsoft expands security beyond threat protection into a holistic approach dedicated to securing your data.


SQL Managed Instance approaches security as layers of protection. There are so many scenarios in which your data can be compromised, and external threats aren’t the sole concern. What if you have a disgruntled bad actor on staff who is intent on stealing data? If that person has the password for your on-premises SQL Server, data theft becomes a severe threat.


When you migrate to SQL Managed Instance, Microsoft assumes responsibility for the first layer of security: the physical security of the data center and hardware. Following that layer, Microsoft secures five more layers: network security, followed by cluster security, then access management, threat protection, information protection, and finally, in the center of that vault, your data. Each layer specializes in one domain for a combined defense-in-depth approach such as access rules and predictive protection.


Using the data-exfiltration example, let’s look at how this layered perimeter approach prevents anyone – even an inside ‘trusted’ employee – from misusing your data.


A diagram of security layers protecting data in Azure SQL Managed Instance. From outside to inside: physical security, network security, cluster security, access management, threat protection, information protection, and customer data.A diagram of security layers protecting data in Azure SQL Managed Instance. From outside to inside: physical security, network security, cluster security, access management, threat protection, information protection, and customer data.

Physical Security

Azure deployments of Azure SQL Managed Instance benefit from the physical security of Azure data centers. Physical security begins around the facility’s perimeter, which is equipped to deflect and deter intrusions. Physical security extends to strictly controlled entrance on a needs-to-access basis, biometrics identity validation, and time- and space-limited authorization to access a specific set of resources. At device end-of-life, best practices and guidance for safe media sanitization are followed depending on the type of media to dispose of any data, documenting each individual data-bearing device’s lifecycle throughout Azure’s data centers with a virtual paper trail. You can read more about these practices and the compliance frameworks Azure datacenters adhere to in Azure documentation on physical security.


Within the facilities, along the rows of racks, inside the boxes with blinking lights, Azure SQL Managed Instance runs on Intel® silicon. The 3rd Gen Intel shared vision of security built for multitenancy. Thanks to this partnership, Azure was the first major public cloud provider to deliver confidential computing, and its hardware continues to rely on Intel’s foundational security technologies.


If, however, your particular situation calls for complete and uncompromising control over all data locations and flows, consider using Azure SQL Managed Instance in an Azure Arc-enabled deployment instead. This deployment mode obviously relies on your own physical security measures; but, there are still particular guidelines on securing Azure Arc-enabled servers which may help you along the way.


Network Security

SQL Managed Instance natively integrates with your virtual network. Because SQL Managed Instance aims to be 100 percent compatible with SQL Server, it needs to provide dedicated, single-tenant features, such as cross-database transactions, common language runtime (CLR), and access to system databases. All are necessary SQL functionality, but points of potential security vulnerability. Like with an on-premises SQL Server, you need a strong security perimeter to safeguard your data in a SQL Managed Instance. VNet integration provides you with control over the traffic going in and out of your network. With VNet integration, you can configure SQL Managed Instance for secure private connectivity much like you would do for a virtual machine.


In this way, SQL Managed Instance provides network isolation from other tenants and workflows on the Azure platform. Connectivity to your on-premises environment can be achieved using Azure ExpressRoute or VPN gateway. In a default deployment, the SQL endpoint is exposed only via an IP address private to its virtual network, allowing you to configure it further for safe connectivity from other Azure or hybrid networks.  


To more closely control inbound and outbound flows, Network Security Groups filter network traffic between Azure resources in an Azure virtual network. A network security group contains security rules that allow or deny inbound network traffic to, or outbound network traffic from, several types of Azure resources. For each rule, you can specify source and destination, port(s), and protocol.


Additionally, route tables permit you to direct outgoing traffic down a specific path. Beyond the obvious drawing of routes to suit a particular network topology, you can also use route tables to direct outbound traffic to a network virtual appliance – a device that acts as an additional security measure, such as a firewall.


SQL Managed Instance is also compatible with with Azure Virtual Network Manager (in preview), which is a management service to group, deploy, and manage virtual networks, across regions and subscriptions. Azure Virtual Network Manager also provides the ability to manage virtual network connectivity and network security rules for network security groups. You can create a managed instance in virtual networks created through the Azure Resource Manager deployment model, and then move the instance to another subnet inside the same VNet or across VNets. This compatibility allows you to define network groups to logically segment (zone) your architecture and define connectivity configurations to establish mesh or hub-and-spoke technologies. You can also define organization-wide security administration rules to specify inbound/outbound rules at scale.


Azure Firewall is another security solution offered by Microsoft and can be used alongside network security groups to provide additional layers of security for virtual networks. It is a managed, cloud-based network security service that protects your Azure Virtual Network resources. The firewall has built-in high availability and unrestricted cloud scalability to help you create, enforce, and log application and network connectivity policies across subscriptions and virtual networks. It offers both network-level and application-level filtering. Threat intelligence-based filtering comes directly from Microsoft Cyber Security and enables real-time alerts so you can deny known malicious traffic.


Private Link is Azure technology that makes SQL Managed Instance available in a virtual network of your choice. A network administrator can establish a private endpoint to SQL Managed Instance in their app’s virtual network, while the SQL administrator chooses to accept or reject the endpoint before it becomes active. Think of this private link as a dedicated network cable from that VNet all the way to your managed instance. Private endpoints establish secure, isolated, one-way connectivity between a service and multiple virtual networks without exposing your service's entire network infrastructure.


In addition to making SQL Managed Instance available to another virtual network or service, private endpoints can also be used to "bring in" a particular Azure resource into our network. For example, you can create a private endpoint to your Azure Storage account in the same virtual network where SQL Managed Instance resides.


Access Management

Azure SQL Managed Instance supports three mechanisms of access management: SQL authentication, Azure Active Directory authentication, and Windows authentication for Active Directory. With all three, applying principle of least privilege when you manage permissions to database users lies at the core of efforts to minimize the risk of and damage caused by unauthorized access.

SQL authentication authenticates a user when they connect to Azure SQL Database or SQL Managed Instance via a 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 enables users to connect using username and password.


Azure AD Authentication is a mechanism of connecting to SQL Managed Instance 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. When using Azure AD server principals, refer to the handy “how-to” document on establishing security in SQL MI using AAD server principals.


Windows Authentication for Active Directory is an additional single sign-on (SSO) authentication option for Azure AD users that can be used alongside Azure AD Authentication to provide an additional layer of security for identity and access management. Kerberos authentication for Azure AD enables Windows Authentication access to SQL Managed Instance. You can move your existing services to the cloud while maintaining a seamless user experience. Windows Authentication works with legacy drivers that might not have built-in Azure AD support. It works on both domain-joined and Azure AD-joined machines. It works in double-hop scenarios (i.e., IIS impersonation), and offers support for multi-factor authentication (MFA) and Conditional Access.


In addition to these secure authentication options, managed identities are commonly used to authenticate SQL Managed Instance to other resources. Managed identities provide an automatically managed identity in Azure AD for applications to use when connecting to resources that support Azure AD authentication, eliminating the need for developers to manage those credentials. Azure AD supports two types of managed identities: system-assigned managed identity (SMI) and user-assigned managed identity (UMI). For more information, see Managed identity types. An SMI is automatically assigned to SQL Managed Instance when it's created. When you're using Azure AD authentication with SQL Database, you must assign an SMI when Azure service principals are used to create Azure AD users in SQL Database. In the past, only an SMI could be assigned to the SQL Managed Instance or SQL Database server identity, but now a User-defined Managed Identity (UMI) can also be assigned to SQL Managed Instance as the instance or server identity.


Threat Protection

Microsoft Defender for SQL is a plan within Microsoft Defender for Cloud that includes functionality for surfacing and mitigating potential database vulnerabilities, and detecting anomalous activities that could indicate a threat to your database. It provides a single, go-to location for enabling and managing these capabilities. It also provides a set of advanced security capabilities, including SQL Vulnerability Assessment and Advanced Threat Protection. Vulnerability Assessment can discover, track, and help you remediate potential database vulnerabilities. It provides visibility into your security state, and it includes actionable steps to resolve security issues and enhance your database fortifications. Advanced Threat Protection detects anomalous activities indicating unusual and potentially harmful attempts to access or exploit your database. It continuously monitors your database for suspicious activities and provides immediate security alerts on potential vulnerabilities, injection attacks, and anomalous database access patterns. Advanced Threat Protection alerts provide details of suspicious activities and recommend action on how to investigate and mitigate the threat.


Another way to keep track of potential risks is with Azure SQL Auditing, which tracks database events and writes them to an audit log in your Azure storage account. Auditing helps maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations. It enables and facilitates adherence to compliance standards, although it doesn't guarantee compliance on its own. For more information, see the Microsoft Azure Trust Center where you can find the most current list of SQL Managed Instance compliance certifications.


Information Protection

Transport Layer Security (encryption-in-transit) secures customer data by encrypting data in motion. A TLS/SSL server certificate is automatically generated for each SQL Managed Instance, while you can control the minimum version of Transport Layer Security (TLS) protocol required for all incoming connections. As a best practice, configure your applications to only establish encrypted connections and validate that the server certificate is trusted, which makes them more resilient to man-in-the-middle type attacks.


When it comes to data at rest, transparent data encryption (encryption-at-rest) protects that data 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 Advanced Encryption Standard (AES) 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, including Bring Your Own Key (BYOK) in Azure Key Vault.


While someone with system administrator access to your SQL Managed Instance might be able to download a backup file, the backup file is useless with transparent data encryption. When they restore the backup to their server, the data will be unreadable.


Rest assured. Microsoft invests heavily in cybersecurity research and development and is architecting the underlying security platform in a forward-looking manner in the November 2022 Feature wave, which is in testing on developer subscriptions.


Learn more. Join us for a live webinar on June 22 at 10am PT/5pm UTC where we will cover 5 different ways to improve data security on Azure SQL Managed Instance:

Version history
Last update:
‎Jun 21 2023 07:23 AM
Updated by: