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

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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 encryption (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 Defender Vulnerability Assessment reports
    • Specify which email addresses receives an email
  • Secure Storage Account to store Azure Defender 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 storage 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 Authentication:
    • 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 and update the SQLMI/main.parameters.json with your desired settings. For every unique SQL MI deployment, I highly recommend having a corresponding parameters file (i.e. main.<name of instance>.parameters.json, main.sqlmi01-dev.parameters.json, main.sqlmi03-prd.parameters.json). This will allow you to make changes to a specific deployment without impacting how the others were deployed.

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 Authentication?

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 Active Directory Only Authentication

sqlmiVulnerabilityAssessmentRecurringScans

true (default), false

Enable or disable Azure Security Center (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:\repos\ARM\SQLMI> .\deployBicep.ps1

 

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

PS C:\repos\ARM\SQLMI> .\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:\repos\ARM\SQLMI> .\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.

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.