SQL resource provider maintenance operations

The SQL resource provider runs on a locked down virtual machine (VM). To enable maintenance operations, you need to update the VM's security. To do this using the principle of Least Privilege, use PowerShell Just Enough Administration (JEA) endpoint DBAdapterMaintenance. The resource provider installation package includes a script for this action.

Update the VM Windows Defender definitions

These instructions only apply to SQL RP V1 running on Azure Stack Hub Integrated Systems.

To update the Windows Defender definitions:

1. Download the Windows Defender definitions update from Security intelligence updates for Windows Defender.

   On the definitions update page, scroll down to "Manually download the update". Download the "Windows Defender Antivirus for Windows 10 and Windows 8.1" 64-bit file.

   You can also use this direct link to download/run the fpam-fe.exe file.

2. Create a PowerShell session to the SQL resource provider adapter VM's maintenance endpoint.

3. Copy the definitions update file to the VM using the maintenance endpoint session.

4. On the maintenance PowerShell session, run the Update-DBAdapterWindowsDefenderDefinitions command.

5. After you install the definitions, we recommend you delete the definitions update file by using the Remove-ItemOnUserDrive command.

PowerShell script example for updating definitions

You can edit and run the following script to update the Defender definitions. Replace values in the script with values from your environment.

# Set credentials for local admin on the resource provider VM.
$vmLocalAdminPass = ConvertTo-SecureString '<local admin user password>' -AsPlainText -Force
$vmLocalAdminUser = "<local admin user name>"
$vmLocalAdminCreds = New-Object System.Management.Automation.PSCredential `
    ($vmLocalAdminUser, $vmLocalAdminPass)

# Provide the public IP address for the adapter VM.
$databaseRPMachine  = "<RP VM IP address>"
$localPathToDefenderUpdate = "C:\DefenderUpdates\mpam-fe.exe"

# Download the Windows Defender update definitions file from https://www.microsoft.com/wdsi/definitions.
Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?LinkID=121721&arch=x64' `
    -Outfile $localPathToDefenderUpdate

# Create a session to the maintenance endpoint.
$session = New-PSSession -ComputerName $databaseRPMachine `
    -Credential $vmLocalAdminCreds -ConfigurationName DBAdapterMaintenance `
    -SessionOption (New-PSSessionOption -Culture en-US -UICulture en-US)
# Copy the defender update file to the adapter VM.
Copy-Item -ToSession $session -Path $localPathToDefenderUpdate `
     -Destination "User:\"
# Install the update definitions.
Invoke-Command -Session $session -ScriptBlock `
    {Update-AzSDBAdapterWindowsDefenderDefinition -DefinitionsUpdatePackageFile "User:\mpam-fe.exe"}
# Cleanup the definitions package file and session.
Invoke-Command -Session $session -ScriptBlock `
    {Remove-AzSItemOnUserDrive -ItemPath "User:\mpam-fe.exe"}
$session | Remove-PSSession

Configure Azure Diagnostics extension for SQL resource provider

These instructions only apply to SQL RP V1 running on Azure Stack Hub Integrated Systems.

Azure Diagnostics extension is installed on the SQL resource provider adapter VM by default. The following steps show how to customize the extension for gathering the SQL resource provider operational event logs and IIS logs for troubleshooting and auditing purpose.

1. Sign in to the Azure Stack Hub administrator portal.

2. Select Virtual machines from the pane on the left, search for the SQL resource provider adapter VM and select the VM.

3. In Diagnostics settings of the VM, go to the Logs tab and choose Custom to customize event logs being collected.

4. Add Microsoft-AzureStack-DatabaseAdapter/Operational!* to collect SQL resource provider operational event logs.

5. To enable the collection of IIS logs, check IIS logs and Failed request logs.

6. Finally select Save to save all the Diagnostics settings.

Once the event logs and IIS logs collection are configured for SQL resource provider, the logs can be found in a system storage account named sqladapterdiagaccount.

To learn more about Azure Diagnostics extension, please see What is Azure Diagnostics extension.

These instructions only apply to SQL RP V1 running on Azure Stack Hub Integrated Systems.

When using the SQL and MySQL resource providers with Azure Stack Hub integrated systems, the Azure Stack Hub operator is responsible for rotating the following resource provider infrastructure secrets to ensure that they don't expire:

• External SSL certificate provided during deployment.
• The resource provider VM local admin account password provided during deployment.
• Resource provider diagnostic user (dbadapterdiag) password.
• (version >= 1.1.47.0) Key Vault certificate generated during deployment.

PowerShell examples for rotating secrets

Change all the secrets at the same time.

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -DiagnosticsUserPassword $passwd `
    -DependencyFilesLocalPath $certPath `
    -DefaultSSLCertificatePassword $certPasswd  `
    -VMLocalCredential $localCreds `
    -KeyVaultPfxPassword $keyvaultCertPasswd

Change the diagnostic user password.

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -DiagnosticsUserPassword  $passwd

Change the VM local admin account password.

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -VMLocalCredential $localCreds

Rotate the SSL certificate

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -DependencyFilesLocalPath $certPath `
    -DefaultSSLCertificatePassword $certPasswd

Rotate the Key Vault certificate

.\SecretRotationSQLProvider.ps1 `
    -Privilegedendpoint $Privilegedendpoint `
    -CloudAdminCredential $cloudCreds `
    -AzCredential $adminCreds `
    -KeyVaultPfxPassword $keyvaultCertPasswd

SecretRotationSQLProvider.ps1 parameters

These instructions only apply to SQL RP V2 running on Azure Stack Hub Integrated Systems.

Like the Azure Stack Hub infrastructure, value-add resource providers use both internal and external secrets. As an operator, you're responsible for:

• Providing updated external secrets, such as a new TLS certificate used to secure resource provider endpoints.
• Managing resource provider secret rotation on a regular basis.

When secrets are nearing expiration, the following alerts are generated in the administrator portal. Completing secret rotation will resolve these alerts:

• Pending internal certificate expiration
• Pending external certificate expiration

Prerequisites

In preparation for the rotation process:

1. If you haven't already, Install PowerShell Az module for Azure Stack Hub before continuing. Version 2.0.2-preview or later is required for Azure Stack Hub secret rotation. For more information, see Migrate from AzureRM to Azure PowerShell Az in Azure Stack Hub.

2. Install Azs.Deployment.Admin 1.0.0 modules: PowerShell Gallery | Azs.Deployment.Admin 1.0.0

Install-Module -Name Azs.Deployment.Admin

3. If external certificate is nearing expiration, review Azure Stack Hub public key infrastructure (PKI) certificate requirements for important prerequisite information before acquiring/renewing your X509 certificate, including details on the required PFX format. Also review the requirements specified in the Optional PaaS certificates section, for your specific value-add resource provider.

Prepare a new TLS certificate for external certificate rotation

Next, create or renew your TLS certificate for securing the value-add resource provider endpoints:

1. Complete the steps in Generate certificate signing requests (CSRs) for certificate renewal for your resource provider. Here you use the Azure Stack Hub Readiness Checker tool to create the CSR. Be sure to run the correct cmdlet for your resource provider, in the step "Generate certificate requests for other Azure Stack Hub services". For example New-AzsDbAdapterCertificateSigningRequest is used for SQL and MySQL RPs. When finished, you submit the generated .REQ file to your Certificate Authority (CA) for the new certificate.

2. Once you've received your certificate file from the CA, complete the steps in Prepare certificates for deployment or rotation. You use the Readiness Checker tool again, to process the file returned from the CA.

3. Finally, complete the steps in Validate Azure Stack Hub PKI certificates. You use the Readiness Checker tool once more, to perform validation tests on your new certificate.

Rotate the internal certificate

Open an elevated PowerShell console and complete the following steps to rotate the resource provider's external secrets:

1. Sign in to your Azure Stack Hub environment using your operator credentials. See Connect to Azure Stack Hub with PowerShell for PowerShell sign-in script. Be sure to use the PowerShell Az cmdlets (instead of AzureRM), and replace all placeholder values, such as endpoint URLs and directory tenant name.

2. Determine the product-id of the resource provider. Run the Get-AzsProductDeployment cmdlet to retrieve a list of the latest resource provider deployments. The returned "value" collection contains an element for each deployed resource provider. Find the resource provider of interest and make note of the values for these properties:

   • "name" - contains the resource provider product ID in the second segment of the value.

   For example, the SQL RP deployment may have a product ID of "microsoft.sqlrp".

3. Run the Invoke-AzsProductRotateSecretsAction cmdlet to rotate the internal certificate:

   Invoke-AzsProductRotateSecretsAction -ProductId $productId
   

Rotate the external certificate

You need to first make note of the values for the following parameters.

Open an elevated PowerShell console and complete the following steps:

1. Sign in to your Azure Stack Hub environment using your operator credentials. See Connect to Azure Stack Hub with PowerShell for PowerShell sign-in script. Be sure to use the PowerShell Az cmdlets (instead of AzureRM), and replace all placeholder values, such as endpoint URLs and directory tenant name.

2. Get the product-id parameter value. Run the Get-AzsProductDeployment cmdlet to retrieve a list of the latest resource provider deployments. The returned "value" collection contains an element for each deployed resource provider. Find the resource provider of interest and make note of the values for these properties:

   • "name" - contains the resource provider product ID in the second segment of the value.
   • "properties"."deployment"."version" - contains the currently deployed version number.

For example, the SQL RP deployment may have a product ID of "microsoft.sqlrp", and version "2.0.0.2".

3. Build the resource provider's package ID, by concatenating the resource provider product ID and version. For example, using the values derived in the previous step, the SQL RP package ID is microsoft.sqlrp.2.0.0.2.

4. Using the package ID derived in the previous step, run Get-AzsProductSecret -PackageId to retrieve the list of secret types being used by the resource provider. In the returned value collection, find the element containing a value of "Certificate" for the "properties"."secretKind" property. This element contains properties for the RP's certificate secret. Make note of the name assigned to this certificate secret, which is identified by the last segment of the "name" property, just above "properties".

For example, the secrets collection returned for the SQL RP contains a "Certificate" secret named SSLCert.

5. Use the Set-AzsProductSecret cmdlet to import your new certificate to Key Vault, which will be used by the rotation process. Replace the variable placeholder values accordingly before running the script.

   $productId = '<product-id>'
   $packageId = $productId + '.' + '<installed-version>'
   $certSecretName = '<cert-secret-name>' 
   $pfxFilePath = '<cert-pfx-file-path>'
   $pfxPassword = ConvertTo-SecureString '<pfx-password>' -AsPlainText -Force   
   Set-AzsProductSecret -PackageId $packageId -SecretName $certSecretName -PfxFileName $pfxFilePath -PfxPassword $pfxPassword -Force
   

6. Finally, use the Invoke-AzsProductRotateSecretsAction cmdlet to rotate the secrets:

   Invoke-AzsProductRotateSecretsAction -ProductId $productId
   

Monitor the secret rotation progress

You can monitor secret rotation progress in either the PowerShell console, or in the administrator portal by selecting the resource provider in the Marketplace service:

Azure Stack Hub has multiple ways to collect, save, and send diagnostic logs to Azure Support. Starting from version 1.1.93, SQL Resource Provider supports the standard way of collecting logs from you Azure Stack Hub environment. For more information, see Diagnostic log collection.

Starting from version 1.1.93, SQL Resource Provider supports the standard way of collecting logs from your Azure Stack Hub environment. If you are using an older version, it is recommended to update your SQL Resource Provider to the latest version.

To collect logs from the locked down VM, use the PowerShell Just Enough Administration (JEA) endpoint DBAdapterDiagnostics. This endpoint provides the following commands:

• Get-AzsDBAdapterLog. This command creates a zip package of the resource provider diagnostics logs and saves the file on the session's user drive. You can run this command without any parameters and the last four hours of logs are collected.
• Remove-AzsDBAdapterLog. This command removes existing log packages on the resource provider VM.

Endpoint requirements and process

When a resource provider is installed or updated, the dbadapterdiag user account is created. You'll use this account to collect diagnostic logs.

To use the DBAdapterDiagnostics commands, create a remote PowerShell session to the resource provider VM and run the Get-AzsDBAdapterLog command.

You set the time span for log collection by using the FromDate and ToDate parameters. If you don't specify one or both of these parameters, the following defaults are used:

• FromDate is four hours before the current time.
• ToDate is the current time.

PowerShell script example for collecting logs

The following script shows how to collect diagnostic logs from the resource provider VM.

# Create a new diagnostics endpoint session.
$databaseRPMachineIP = '<RP VM IP address>'
$diagnosticsUserName = 'dbadapterdiag'
$diagnosticsUserPassword = '<Enter Diagnostic password>'

$diagCreds = New-Object System.Management.Automation.PSCredential `
        ($diagnosticsUserName, (ConvertTo-SecureString -String $diagnosticsUserPassword -AsPlainText -Force))
$session = New-PSSession -ComputerName $databaseRPMachineIP -Credential $diagCreds `
        -ConfigurationName DBAdapterDiagnostics `
        -SessionOption (New-PSSessionOption -Culture en-US -UICulture en-US)

# Sample that captures logs from the previous hour.
$fromDate = (Get-Date).AddHours(-1)
$dateNow = Get-Date
$sb = {param($d1,$d2) Get-AzSDBAdapterLog -FromDate $d1 -ToDate $d2}
$logs = Invoke-Command -Session $session -ScriptBlock $sb -ArgumentList $fromDate,$dateNow

# Copy the logs to the user drive.
$sourcePath = "User:\{0}" -f $logs
$destinationPackage = Join-Path -Path (Convert-Path '.') -ChildPath $logs
Copy-Item -FromSession $session -Path $sourcePath -Destination $destinationPackage

# Clean up the logs.
$cleanup = Invoke-Command -Session $session -ScriptBlock {Remove-AzsDBAdapterLog}
# Close the session.
$session | Remove-PSSession