Using Azure Bicep to Deploy SQL MI into an Existing Virtual Network

Recently, I was working with a customer who wanted to deploy SQL Managed Instance (MI) into an existing virtual network with Infrastructure as Code (IaC). Having some experience with Azure Resource Manager (ARM) templates, I decided to fulfill their request by developing it with the latest release of Azure Bicep. Even though I have experience with ARM development, I find ARM is more of a machine language which makes it difficult for some of us humans to process. 🙂

I would highly encourage anyone getting started with IaC to use Bicep over native ARM template development. In fact, even those ARM gurus should consider it too!

My Top 3 Bicep Features

Before jumping into the SQL Managed Instance deployment, I'd like to highlight a few features I appreciate about Bicep.

Got Module?

One of the really great features of Bicep that you'll see in this deployment includes the use of modules. From MS Docs:

Bicep module is just a Bicep file that is deployed from another Bicep file. You can encapsulate complex details of the resource declaration in a module, which improves readability of files that use the module. Bicep modules are converted into a single Azure Resource Manager template with nested templates for deployment.

RobertLightner_0-1638800553642.png

Example 1: Bicep module syntax for calling storageaccount.bicep

Ternary operator ?:

For this deployment, you may run notice an unusual syntax ?: used throughout many of the Bicep template files. This is known as a ternary operator and is the basic syntax for conditional expressions. In other programming languages, it is commonly referred to as the conditional operator, inline if (iff), or ternary if. For Bicep, the syntax takes this form variable: condition ? expr1 : expr2, where variable could also be a resource or output. I think of it as if(condition), then expression1, else expression2.

RobertLightner_1-1638800553659.png

Example 2: Using both the empty() and string comparison to determine which value passes through to the module for sqlmi-subnet.bicep.

Referencing Existing Resources

One of many things that was challenging for me with ARM templates was referencing existing resources. Bicep makes this super easy by using the existing keyword. I used this method for referencing an existing Virtual Network and Subnet for SQL Managed Instance deployment.

RobertLightner_2-1638800553665.png

Example 3: Referencing an existing Virtual Network in a different resource group.

Project Background

There are situations where AppDevOps teams will need to deploy resources into an existing virtual network infrastructure. This deployment covers such a situation and assumes the virtual network/subnet already exists.

There are three requirements before SQL MI can be deployed to a subnet:

  • Network Security Group (NSG)
  • User Defined Route Table (UDR)
  • Subnet Delegation: Microsoft.SQL/ManagedInstance

Whether all or some of these requirements are already assigned to the subnet, the person deploying this template will need to have, at a minimum, SQL Managed Instance Contributor rights before it can deploy successfully.

In addition to collaborating with the existing networking infrastructure, this deployment has an option for Customer Managed Key (BYOK). In the parameters file, the deployment can either rely on Service Managed or Customer Managed key for transparent data (TDE).

SQL Managed Instance Deployment Features

  • [optional] Customer-Managed Key for Transparent Data Encryption support
    • Key Vault integration
      • Only SQL MI managed identity has access to Key Vault
      • Only Azure Trusted Services are allowed to connect to Key Vault
  • Enables Azure Vulnerability Assessment reports
    • Specify which email addresses receives an email
  • Secure Account to store Azure reports
    • Only specified client IP/CIDR address space (see parameters file), SQL MI subnet, and Azure Trusted Services can connect
  • Create databases by specifying their names as an array parameter in main.parameters.json

Prerequisites

Before deploying, please read through these prerequisites to avoid any unexpected issues.

  • Clone or download (ZIP) my repo (https://github.com/DaFitRobsta/ARM-Templates.git) to a local folder on your computer
    • The source code is in the SQLMI folder
  • Bicep – Read through the Bicep tutorial to setup your environment.
  • An existing Virtual Network with an empty subnet (/27 or larger)
  • RBAC roles needed to deploy Bicep template(s), any combination of the following:
    • Owner
    • User Access Administrator
      • Granting SQL MI Managed Identity access to the account for storing Azure Defender Vulnerability Assessment reports
    • Contributor (Not needed if Owner is already assigned)
      • Deployment of all Azure resources:
        • Network Security Group (NSG)
        • Route Table (UDR)
        • SQL Managed Instance
        • Storage Account
        • Key Vault
    • SQL Managed Instance Contributor or Network Contributor (Not needed if Owner or Contributor roles are assigned)
      • Updating virtual network/subnet delegation to SQLMI
      • Creating NSG and/or UDR if one doesn't already exist on subnet
  • Azure AD roles needed for Azure AD :
    • The SQL MI Managed Identity will need the Directory Readers role in order to enable Azure AD integration. Currently this operation isn't provided in the deployment.

Tell me about this Parameters File

SQL MI has so many parameters, and while I don't expose every single one, this deployment comes very close to it. Before deploying this template, clone this repo

Review the following parameters before deploying:

Parameters Values (with description)
sqlManagedInstanceName Name of SQL Managed Instance
sqlManagedInstanceSkuEdition GeneralPurpose (default), BusinessCritical
sqlManagedInstanceSkuName GP_Gen5 (default), BC_Gen5
vnetName Name of the SQL MI Virtual Network
vnetResourceGroupName SQL MI Virtual Network Resource Group Name
managedInstanceSubnetName SQL MI Subnet name
sqlManagedInstanceAdminLogin SQL MI Admin name
sqlManagedInstanceStorageSizeInGB Minimum value: 32
Maximum value: 8192. Increments of 32 GB allowed only
sqlManagedInstanceStorageAccountType LRS (default), ZRS, GRS
sqlManagedInstancevCores 4, 8, 16, 24, 32, 40, 64, 80
sqlManagedInstanceLicenseType BasePrice (BYOL), LicenceIncluded
sqlManagedInstanceProxyOverride Proxy, Redirect, Default
sqlManagedInstanceTimeZoneId Id of the time zone. Allowed values are time zones supported by Windows. List of Ids can also be obtained by executing [System.TimeZoneInfo]::GetSystemTimeZones() in PowerShell.
sqlmiKeyVaultSkuName Standard (default), Premium
sqlmiTDECustomerManagedKey true = Customer Managed Key

false (default) = Service Managed Key

sqlManagedInstanceEnableAADAuthentication true, false (default)

Enable Azure AD ?

sqlManagedInstanceAdministratorAADLogin AAD Login name of the server administrator
sqlManagedInstanceAdministratorAADSID SID (object ID) of the server administrator
sqlManagedInstanceAdministratorAADTenantID Tenant ID of the administrator
dbRetentionDays Specify how long you want to keep your point-in-time backups. Default value is 7 days
sqlManagedInstanceAADonlyAuthentication true, false (default)

Set Azure Only

sqlmiVulnerabilityAssessmentRecurringScans true (default), false

Enable or disable Azure (ASC) SQL Vulnerability Assessment Scans

sqlmiVulnerabilityAssessmentRecurringScansEmailSubAdmins true (default), false

Email reports to admins

sqlmiVulnerabilityAssessmentRecurringScansEmails Array of email addresses to receive ASC reports
clientIPcidr Client IP address (CIDR format) to allow access to storage account vulnerability reports

(example: 13.168.10.0/24)

sqlMIDatabaseNames Array of database names

PowerShell Deployment

The steps outlined assumes the deployment is occurring from a workstation configured with Bicep and PowerShell. Other deployment options include Azure CloudShell via CLI or PowerShell which are not covered in this article. Update the parameters file before deploying.

Example 1: Deploy to Azure Commercial

PS C:reposARMSQLMI> .deployBicep.ps1

Example 2: Deploy to Azure Government with a specific parameters file

PS C:reposARMSQLMI> .deployBicep.ps1 -AzureEnvironment AzureUSGovernment -TemplateParameterFile .main.parameters.gov.json

Example 3: Deploy to Azure Government with a specific parameters file and Azure AD tenant. The use of the TenantId would be in situations where you are a guest user in the tenant the subscription is associated with.

PS C:reposARMSQLMI> .deployBicep.ps1 -AzureEnvironment AzureUSGovernment -TemplateParameterFile .main.parameters.gov.json -TenantId “xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxx”

Important

  • Deployment of the first SQL MI in the subnet might take up to six hours, while subsequent deployments take up to 1.5 hours. This is because a virtual cluster that hosts the instances needs time to deploy or resize the virtual cluster. For more details visit Overview of Azure SQL Managed Instance management operations
  • Each virtual cluster is associated with a subnet and deployed together with first instance creation. In the same way, a virtual cluster is automatically removed together with last instance deletion leaving the subnet empty and ready for removal.
  • The SQL MI Admin Password is automatically generated and not saved anywhere. You'll need to manually update it to log into SQL MI with the admin account.

Known Issues

  • Azure AD Authentication Integration
    • This deployment supports setting Azure AD Authentication and Azure AD Authentication Only, if the person running this deployment is assigned the Global Admin or Privileged Role Administrator role.
    • For all other roles, a Global Admin or Privileged Role Administrator can create an Azure AD group and assign the Directory Readers permission to the group. Read Directory Readers role in Azure Active Directory for Azure SQL for more information.
    • An alternate solution is for a Global Admin or Privileged Role Administrator run the script (grantSqlMiAadReaderRole.ps1) from your local cloned repo to grant your SQL Managed Instance Azure AD read permission.

Conclusion

If you made it to this point, thank you for reading and hopefully you have a better understanding of Azure Bicep and some of the techniques I used for deploying Azure SQL Managed Instance into an existing networking environment.

For other articles I've posted, please find them here: https://aka.ms/RobertLightner.

References

Disclaimer

The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

 

This article was originally published by Microsoft's Azure Blog. You can find the original article here.