SQL 资源提供程序维护操作SQL resource provider maintenance operations

SQL 资源提供程序在锁定的虚拟机 (VM) 上运行。The SQL resource provider runs on a locked down virtual machine (VM). 若要启用维护操作,需要更新 VM 的安全性。To enable maintenance operations, you need to update the VM's security. 若要使用“最低特权”原则执行此操作,请使用 PowerShell Just Enough Administration (JEA) 终结点 DBAdapterMaintenanceTo do this using the principal of Least Privilege, use PowerShell Just Enough Administration (JEA) endpoint DBAdapterMaintenance. 资源提供程序安装包包含此操作的脚本。The resource provider installation package includes a script for this action.

修补和更新Patching and updating

不能将 SQL 资源提供程序作为 Azure Stack Hub 的一部分进行维护,因为它是一个加载项组件。The SQL resource provider isn't serviced as part of Azure Stack Hub because it's an add-on component. Microsoft 会根据需要为 SQL 资源提供程序提供更新。Microsoft provides updates to the SQL resource provider as necessary. 发布更新的 SQL 适配器后,会提供一个脚本来应用更新。When an updated SQL adapter is released, a script is provided to apply the update. 此脚本创建新的资源提供程序 VM,并将旧提供程序 VM 的状态迁移到新 VM。This script creates a new resource provider VM, migrating the state of the old provider VM to the new VM. 有关详细信息,请参阅更新 SQL 资源提供程序For more information, see Update the SQL resource provider.

提供程序 VMProvider VM

由于资源提供程序在用户 VM 上运行,因此需要应用已发布的所需修补和升级。 Because the resource provider runs on a user VM, you need to apply the required patches and updates when they're released. 使用修补升级周期提供的 Windows 更新包将更新应用到 VM。Use the Windows update packages that are provided as part of the patch-and-update cycle to apply updates to the VM.

更新 SQL 凭据Updating SQL credentials

你需要负责在 SQL 服务器上创建和维护 sysadmin 帐户。You're responsible for creating and maintaining sysadmin accounts on your SQL servers. 资源提供程序需要拥有这些特权的帐户才能代表用户管理数据库,但无需访问用户的数据。The resource provider needs an account with these privileges to manage databases for users, but it doesn't need access to the users' data. 如果需要更新 SQL 服务器上的 sysadmin 密码,可以使用资源提供程序的管理员界面来更改存储的密码。If you need to update the sysadmin passwords on your SQL servers, you can use the resource provider's administrator interface to change a stored password. 这些密码将存储在 Azure Stack Hub 实例上的 Key Vault 中。These passwords are stored in a Key Vault on your Azure Stack Hub instance.

若要修改设置,请选择“浏览”>“管理资源”>“SQL 宿主服务器”>“SQL 登录”并选择用户名。 To modify the settings, select Browse > ADMINISTRATIVE RESOURCES > SQL Hosting Servers > SQL Logins and select a user name. 必须先在 SQL 实例上(必要时还需要在任何副本上)进行更改。在“设置”下,选择“密码”。 The change must be made on the SQL instance first (and any replicas, if necessary.) Under Settings, select Password.

更新 SQL 管理员密码

机密轮换Secrets rotation

这些说明仅适用于 Azure Stack Hub 集成系统。These instructions only apply to Azure Stack Hub Integrated Systems.

在 Azure Stack Hub 集成系统中使用 SQL 和 MySQL 资源提供程序时,Azure Stack Hub 操作员负责轮换以下资源提供程序基础结构机密以确保它们不会过期: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:

  • 部署期间提供的外部 SSL 证书。External SSL certificate provided during deployment.
  • 部署期间提供的资源提供程序 VM 本地管理员帐户密码。The resource provider VM local admin account password provided during deployment.
  • 资源提供程序诊断用户 (dbadapterdiag) 密码。Resource provider diagnostic user (dbadapterdiag) password.
  • (版本 >= 1.1.47.0)在部署过程中生成的 Key Vault 证书。(version >= 1.1.47.0) Key Vault certificate generated during deployment.

用于轮换机密的 PowerShell 示例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

更改 VM 本地管理员帐户密码。Change the VM local admin account password.

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

更改 SSL 证书密码。Change the SSL certificate password.

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

更改 Key Vault 证书密码。Change the Key Vault certificate password.

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

SecretRotationSQLProvider.ps1 参数SecretRotationSQLProvider.ps1 parameters

参数Parameter 说明Description 注释Comment
AzureEnvironmentAzureEnvironment 用于部署 Azure Stack Hub 的服务管理员帐户的 Azure 环境。The Azure environment of the service admin account used for deploying Azure Stack Hub. 仅对于 Azure AD 部署是必需的。Required only for Azure AD deployments. 受支持的环境名称是 AzureChinaCloudSupported environment name is AzureChinaCloud. 可选Optional
AzCredentialAzCredential Azure Stack Hub 服务管理员帐户凭据。Azure Stack Hub service admin account credential. 必需Mandatory
CloudAdminCredentialCloudAdminCredential Azure Stack Hub 云管理域帐户凭据。Azure Stack Hub cloud admin domain account credential. 必需Mandatory
PrivilegedEndpointPrivilegedEndpoint 用于访问 Get-AzureStackStampInformation 的特权终结点。Privileged Endpoint to access Get-AzureStackStampInformation. 必需Mandatory
DiagnosticsUserPasswordDiagnosticsUserPassword 诊断用户帐户密码。Diagnostics user account password. 可选Optional
VMLocalCredentialVMLocalCredential MySQLAdapter VM 上的本地管理员帐户。Local admin account on the MySQLAdapter VM. 可选Optional
DefaultSSLCertificatePasswordDefaultSSLCertificatePassword 默认 SSL 证书 (*.pfx) 密码。Default SSL certificate (*.pfx) password. 可选Optional
DependencyFilesLocalPathDependencyFilesLocalPath 依赖项文件本地路径。Dependency files local path. 可选Optional
KeyVaultPfxPasswordKeyVaultPfxPassword 用于为数据库适配器生成 Key Vault 证书的密码。The password used for generating the Key Vault certificate for database adapter. 可选Optional

已知问题Known issues

问题Issue:
机密轮换日志。Secrets rotation logs. 如果机密轮换自定义脚本在运行时失败,则不会自动收集机密轮换的日志。The logs for secrets rotation aren't automatically collected if the secret rotation custom script fails when it's run.

解决方法Workaround:
使用 Get-AzsDBAdapterLogs cmdlet 收集所有资源提供程序日志,包括 C:\Logs 中保存的 AzureStack.DatabaseAdapter.SecretRotation.ps1_*.log。Use the Get-AzsDBAdapterLogs cmdlet to collect all resource provider logs, including AzureStack.DatabaseAdapter.SecretRotation.ps1_*.log, saved in C:\Logs.

更新 VM 操作系统Update the VM operating system

使用以下方法之一更新 VM 操作系统。Use one of the following methods to update the VM operating system.

  • 使用当前进行了修补的 VM 映像安装最新的资源提供程序包。Install the latest resource provider package using a currently patched VM image.
  • 在安装或更新资源提供程序期间安装 Windows 更新包。Install a Windows Update package during the installation of, or update to, the resource provider.

更新 VM Windows Defender 定义Update the VM Windows Defender definitions

更新 Windows Defender 定义:To update the Windows Defender definitions:

  1. Windows Defender 的安全智能更新下载 Windows Defender 定义更新。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". 下载“适用于 Windows 10 和 Windows 8.1 的 Windows Defender Antivirus”64 位文件。Download the "Windows Defender Antivirus for Windows 10 and Windows 8.1" 64-bit file.

    也可使用此直接链接下载/运行 fpam-fe.exe 文件。You can also use this direct link to download/run the fpam-fe.exe file.

  2. 与 SQL 资源提供程序适配器 VM 的维护终结点建立 PowerShell 会话。Create a PowerShell session to the SQL resource provider adapter VM's maintenance endpoint.

  3. 使用维护终结点会话将定义更新文件复制到 VM。Copy the definitions update file to the VM using the maintenance endpoint session.

  4. 在维护 PowerShell 会话中,运行 Update-DBAdapterWindowsDefenderDefinitions 命令。On the maintenance PowerShell session, run the Update-DBAdapterWindowsDefenderDefinitions command.

  5. 安装定义之后,我们建议使用 Remove-ItemOnUserDrive 命令删除定义更新文件。After you install the definitions, we recommend you delete the definitions update file by using the Remove-ItemOnUserDrive command.

用于更新定义的 PowerShell 脚本示例PowerShell script example for updating definitions

可以编辑并运行以下脚本来更新 Defender 定义。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
# 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

收集诊断日志Collect diagnostic logs

若要从锁定的 VM 收集日志,请使用 PowerShell Just Enough Administration (JEA) 终结点 DBAdapterDiagnosticsTo collect logs from the locked down VM, use the PowerShell Just Enough Administration (JEA) endpoint DBAdapterDiagnostics. 此终结点提供以下命令:This endpoint provides the following commands:

  • Get-AzsDBAdapterLogGet-AzsDBAdapterLog. 此命令创建资源提供程序诊断日志的 zip 包,并将文件保存在会话的用户驱动器上。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-AzsDBAdapterLogRemove-AzsDBAdapterLog. 此命令删除资源提供程序 VM 上的现有日志包。This command removes existing log packages on the resource provider VM.

终结点要求和过程Endpoint requirements and process

安装或更新资源提供程序时,将创建 dbadapterdiag 用户帐户。When a resource provider is installed or updated, the dbadapterdiag user account is created. 此帐户用于收集诊断日志。You'll use this account to collect diagnostic logs.

备注

dbadapterdiag 帐户密码与部署或更新提供程序期间在 VM 上创建的本地管理员所用的密码相同。The dbadapterdiag account password is the same as the password used for the local admin on the VM that's created during a provider deployment or update.

若要使用 DBAdapterDiagnostics 命令,请与资源提供程序 VM 建立远程 PowerShell 会话,然后运行 Get-AzsDBAdapterLog 命令。To use the DBAdapterDiagnostics commands, create a remote PowerShell session to the resource provider VM and run the Get-AzsDBAdapterLog command.

使用 FromDateToDate 参数设置日志收集的时间跨度。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 为当前时间之前的四个小时。FromDate is four hours before the current time.
  • ToDate 为目前时间。ToDate is the current time.

用于收集日志的 PowerShell 脚本示例PowerShell script example for collecting logs

以下脚本演示如何从资源提供程序 VM 收集诊断日志。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

# 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

为 SQL 资源提供程序配置 Azure 诊断扩展Configure Azure Diagnostics extension for SQL resource provider

默认情况下,在 SQL 资源提供程序适配器 VM 上安装 Azure 诊断扩展。Azure Diagnostics extension is installed on the SQL resource provider adapter VM by default. 以下步骤介绍如何为收集 SQL 资源提供程序操作事件日志和 IIS 日志自定义扩展,以便用于故障排除和审核。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. 登录到 Azure Stack Hub 管理员门户。Sign in to the Azure Stack Hub administrator portal.

  2. 从左侧窗格中选择“虚拟机”,搜索 SQL 资源提供程序适配器 VM,然后选择该 VM。Select Virtual machines from the pane on the left, search for the SQL resource provider adapter VM and select the VM.

  3. 在 VM 的“诊断设置”中,转到“日志”选项卡,然后选择“自定义”,以自定义要收集的事件日志。In Diagnostics settings of the VM, go to the Logs tab and choose Custom to customize event logs being collected. 转到诊断设置Go to diagnostics settings

  4. 添加 Microsoft-AzureStack-DatabaseAdapter/Operational!* 用于收集 SQL 资源提供程序操作事件日志。Add *Microsoft-AzureStack-DatabaseAdapter/Operational!* _ to collect SQL resource provider operational event logs. 添加事件日志Add event logs

  5. 若要启用 IIS 日志收集,请选中“IIS 日志”和“失败请求日志”。To enable the collection of IIS logs, check _ IIS logs* and Failed request logs. 添加 IIS 日志Add IIS logs

  6. 最后,选择“保存”以保存所有诊断设置。Finally select Save to save all the Diagnostics settings.

为 SQL 资源提供程序配置事件日志和 IIS 日志收集后,即可在名为 sqladapterdiagaccount 的系统存储帐户中找到日志。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.

若要详细了解 Azure 诊断扩展,请参阅什么是 Azure 诊断扩展To learn more about Azure Diagnostics extension, please see What is Azure Diagnostics extension.

后续步骤Next steps

添加 SQL Server 宿主服务器Add SQL Server hosting servers