Advanced Data Solutions : 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.

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.

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.

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.

Posted at https://sl.advdat.com/3or20nk

Monday, December 6, 2021

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