Fixing the SQL / MySQL RP update procedure on Azure Stack Hub 1910

Anyone who has been running and operating Azure Stack Hub for a while is hopefully aware that there are a number of activities that need to take place to keep it up to date and supported. The most obvious one is updating the core infrastructure using the integrated update process. It’s made easier in that the main dashboard on the admin portal for your stamp informs you of the update status - if it’s up to date or if there’s an update to apply. When it comes to additional Resource Providers, you have to do the work your self. This post details how to fix the update process for the SQL and MySQL RP’s for systems running version 1910.

I’m making the assumption that you have already deployed the RP’s previously, and have access to the pfx file that contains the sqladapter certificate for your stamp.

Version 1910 of Azure Stack Hub requires version 1.1.47.0 of the SQL & MySQL Resource Providers.

The first thing you will need to do is download the respective RP install binaries:

• SQL RP can be found here: https://aka.ms/azurestacksqlrp11470

• MySQL RP can be found here: https://aka.ms/azurestackmysqlrp11470

Extract the contents of the downloaded file(s) on a system that has access to the PEP and also has the compatible Azure Stack Hub PowerShell modules installed for Azure Satck Hub 1910: https://docs.microsoft.com/en-us/azure-stack/operator/azure-stack-powershell-install?view=azs-1910

Create a directory in the location where you extracted the RP files called cert.  You will need to copy the pfx file containing the certificate for the SQL adapter into this directory. This step is the same for both SQL and MySQL.

View fullsize

We need to modify the common.psm1 file in the Prerequisites folder, similar to an earlier blog post on deploying the RP (based on 1.1.33.0)

View fullsize

Taking a look at the file shows us the prerequisites for the function:

View fullsize

It expects the Azure RM module version to be 2.3.0 and that the installation path for the modules to be \SqlMyslPsh.
If following the official instructions for deploying the Azure Stack PowerShell module compatible with 1910, this will never work, so we need to modify the Test-AzureStackPowerShell function.

Change the $AzPshInstallFolder on line 82 from "SqlMySqlPsh" to "WindowsPowerShell\Modules"

Change the $AzureRmVersion on line 84 from "2.3.0" to "5.8.3"

View fullsize

We need some further changes to the function to ensure that the correct path for the module is evaluated:

Locate line 118/119 in the module.

View fullsize

We need to modify the path so it reflects that of the loaded module.  Change the "AzureRM" to AzureRM.Profile", as highlighted in the images below.

View fullsize

N.B. - The instructions for the fix are also applicable for new SQL & MySQL Resource Provider deployments, as they use the common.psm1 module too.

Save the file and using the update script example tailored to your environment, the update should now work.

You should see status messages like below:

View fullsize

View fullsize

View fullsize

Overall, it takes approximately 50 minutes to run through the update.

Deploying the SQL RP with the latest Azure Stack PowerShell module

This is a quick post about how you can use the latest Azure Stack PowerShell module to deploy the SQL resource provider to Azure Stack (1908) , rather than having to use an older version that the RP documentation says you must do (which can be messy, having to remove / install various modules).

If you follow the pre-requisites, you require PowerShell for Azure Stack. Following the link in the pre-reqs gives you the instructions to install the latest version (1.7.2 at time of writing). I followed the instructions for version 1904 and later and that was great.

Further into the SQL RP document, there is a helper script to help you with the deployment. The script states that the modules required are for specific versions, which are outdated for 1908. This actually conflicts with the pre-requirement, as the link is to install version 1.7.2 of the AzureStack Module. I don’t particularly want o remove/install different version of Modules to my system as it is time consuming and unnecessary, IMO. Anyway, I decided to carry on as it may have been an oversight.

View fullsize

Next, I had download version 1.1.33.0 of the SQL RP

View fullsize

I ran the self-extracting exe file and from the extracted directory, I ran from and elevated PowerShell session the DeploySQLProvider.ps1 script

View fullsize

Even though I had followed all the pre-reqs in the article, an exception was thrown as seen below:

View fullsize

As you can see, it is complaining about the Azure Stack PowerShell module. I know that version 2.5.0 and ArmProfile 2019-03-01-hybrid is supported with Azure Stack 1908, so I took a look at the script to find out what was throwing this error.

I did a search for ‘Checking for Azure Stack Powershell Module …’ and it took me to the following :

View fullsize

It looked like the Test-AzureStackPowershell function was the candidate. The function wasn’t defined within the script, so it must have come from some other module. I ran the following commands to get the information I needed:

get-command Test-AzureStackPowerShell

get-module Common | ft Path

You can see the output here; the source of the function was the key to find where it resided:

View fullsize

Now I went and took a look at the Common.psm1 module.

I did a search for a distinct part of the error message that’s thrown - ‘Use ArmProfile 2018-03-01-hybrid’ and it took me to this:

View fullsize

I could see in the elseif statement that it was looking for a minor version equal to 3. Given that I’m running 2.5.0, that was never going to work. So to try and resolve the error, I changed the ‘-eq’ to ‘-ge’, so that it would work for any minor version greater or equal to 3 and above.

View fullsize

I removed the currently loaded Common module:

Remove-Module Common

Then I ran the DeploySQLProvider.ps1 script again:

View fullsize

Bingo! Fixed it. As you can see, it evaluates version 2.5.0 as supported and carries on until the deployment completes successfully…

So it turns out a simple change in the Common module has made life a lot more straightforward.

Using Lets Encrypt certificates with Azure Stack

Anyone who has deployed an Azure Stack integrated system will know that one of the crucial items to get right from the outset are the PKI certificates that will be used for external services, such as the portal, blob storage, ARM API’s. For production environments, Microsoft recommend having separate certificates for each of the endpoints, some of which require wildcard SSL certs. These tend to be more expensive if purchasing from a third party.

Of course, you could use an Enterprise CA based within your environment, but I’ve seen too many issues where intermediate CA’s are used to sign the SSL certificates, due to the public key not existing in the Microsoft or various Linux distro’s Trusted CA publishers store.

Side note: When A VM is provisioned on Azure Stack, only the root CA is imported by the WA agent. The intermediate CA public cert needs to be manually imported.

With all that in mind, I was lucky enough to work with a client who had the idea to cut costs, but to use a CA that is trusted universally; that being Lets Encrypt. As the strapline states on their website, ‘Lets Encrypt is a free, automated and open Certificate Authority’ .

Free? I like how much that costs, there must be a catch? Well, yes and no. The certs have a 90 day lifespan, so you’ll have to ensure you rotate the certificates before they expire, but this can be automated, so not a big deal. The documentation for expiration alerts say that you’ll be alerted when they’re within 30 days, but I’ve found you get warnings within 90 days, so be aware that you don’t just ignore them and they do eventually expire - make a note in your calendar!

View fullsize

So what do we need for this to implement this solution?

Here are the pre-reqs:

• An Azure subscription

• An Azure DNS Zone for your domain

• A Service Principal within your Azure AD tenant

• Azure PowerShell modules. If using the AZ modules, the Enable-AzureRmAlias should be set. N.B. This does not work with the Azure Stack PowerShell modules, as the AzureRM.Dns modules currently included do not support the creation of CAA records - we need this capability!

• Azure Stack Readiness checker PowerShell Module.

• Posh-ACME PowerShell module https://github.com/rmbolger/Posh-ACME

• Oh, and my Azure Stack Lets Encrypt PoSh module :)

For anyone wondering how to setup Azure DNS zones for use with Azure Stack, please see my blog post here.

Assuming you’ve installed / imported / configured / downloaded all of the pre-reqs, extract the contents of the zip file downloaded for the Azure Stack Lets Encrypt Module and then:

1. Run the new-DNSTxtRole.ps1 script that is included within the Azure Stack Lets Encrypt zip file. It will prompt you for your credentials to connect to your Azure Subscription and then create a new called ‘DNS Zone Contributor’ within your subscription that you can use to assign least privileges to the Service Principal on your Resource Group that contains your DNS Zone. It restricts rights to creating TXT records so that Lets Encrypt can use them to verify that you are in fact the owner of the domain.

Here’s what the script contains:

# Use this to create a role to assign to service principal used by Posh-Acme

$profile = Connect-AzureRMAccount

$roleDef = Get-AzureRmRoleDefinition -Name "DNS Zone Contributor"
$roleDef.Id = $null
$roleDef.Name = "DNS TXT Contributor"
$roleDef.Description = "Manage DNS TXT records only."
$roleDef.Actions.RemoveRange(0,$roleDef.Actions.Count)
$roleDef.Actions.Add("Microsoft.Network/dnsZones/TXT/*")
$roleDef.Actions.Add("Microsoft.Network/dnsZones/read")
$roleDef.Actions.Add("Microsoft.Authorization/*/read")
$roleDef.Actions.Add("Microsoft.Insights/alertRules/*")
$roleDef.Actions.Add("Microsoft.ResourceHealth/availabilityStatuses/read")
$roleDef.Actions.Add("Microsoft.Resources/deployments/read")
$roleDef.Actions.Add("Microsoft.Resources/subscriptions/resourceGroups/read")
$roleDef.AssignableScopes.Clear()
$roleDef.AssignableScopes.Add("/subscriptions/$($profile.Context.Subscription.Id)")

$role = New-AzureRmRoleDefinition $roleDef
$role

2. Add your Service Principal to your DNS Zone as a ‘DNS Zone Contributor’. I named my Service Principal LetsEncryptAzureStack in this example.

View fullsize

From the portal, find your DNS Zone resource, and select Access Control (IAM). From the blade that opens, select +Add.

View fullsize

1. Select the DNS Zone Contributor as for the Role

2. In the Select field, type in the name of your Service Principal. Select it, then press Save
   

3 Now we need to create some CAA records within the domain. This is required by Lets Encrypt in order to create the certificates. I created a function within the module to handle this. From an elevated PowerShell session navigate to the folder you extracted the module to and run:

Import-Module .\AzSLetsEncrypt.psm1

Once imported, ensure you’ve connected to your Azure Subscription within PowerShell and run the following:

#Connect to Azure
Connect-AzureRmAccount
New-AzsDnsCaaRecords -ResourceGroup <ResourceGroup> -Region <Azure Stack Region Name> -FQDN <FQDN for the DNZ Zone> -PaaS

Enter the Resource Group that hosts your DNS Zone, The name of your Azure Stack region and the FQDN for the domain. Optionally, if you specify the PaaS switch, it will create the CAA records for the PaaS endpoints too. If everything is in place, you should hopefully see something that looks like this in your Azure DNS Zone: This step only has to be run once, unless you delete the CAA records.

View fullsize

4 Now we’ve got all of that in place, we can create our certificates! Assuming you’ve still got the module imported, you can use the New-AzsPkiLECertificates function to create your certs. Rather than explain all the options, I’ve created a wrapper script :

$DebugPreference = 'continue'

$Region = '<Azure Stack Region Name>'
$FQDN = '<FQDN>'
$DNSResourceGroup = '<DNS Zone Resource Group>'

$Params = @{
 RegionName = $Region
 FQDN = $FQDN
 ServicePrincipal = '<Service Principal GUID>'
 ServicePrincipalSecret = '<Service Principal Secret>'
 pfxPass = 'P@ssword!'
 SubscriptionId = '<Subscription ID>'
 TenantId = '<Tenant ID>'
 CertPath = 'c:\azsCerts'
}

$scriptpath = $PSScriptRoot
cd $scriptpath
import-module .\AzSLetsEncrypt.psm1


#New-AzsDnsCaaRecords -ResourceGroup $DNSResourceGroup -Region $Region -FQDN $FQDN -PaaS
New-AzsPkiLECertificates @Params -Force -paas

The function will create the core certificates and if you select the PaaS switch, the optional App service and SQL RP certs. It will take a while to run as, as there is an element of waiting for the TXT records that are created for validation to replicate.

View fullsize

Once all the certificates have been created, they are validated to ensure they are compatible for use with Azure Stack using the Microsoft.AzureStack.ReadinessChecker module.:

View fullsize

Once the script has completed, you should have a folder structure that looks like this:

View fullsize

I’ve used these certificates in two installations now and they’ve worked with no problems.

As the Posh-Acme module is used to generate the certs, the data is stored here:

%userprofile%\AppData\Local\Posh-ACME

 If you need to recreate the certs before the allowed renewal period within the Posh-ACME module, or have some issues, you can delete the sub folders in this location and re-run the script.

The folder structure that’s generated should match the requirements for the Azure Stack install routine / certificate rotation. Check out the documentation here on how to rotate the certificates if they’re coming towards their expiration date.

I hope this is of some use and allows you to save some money!