Azure-docs: Unable to Install SqlIaasAgent on Windows Server 2019 with SQL Server 2017

[mjcarrabine]

How do I resolve the issue described below or what steps should I be taking to install the SqlIaasAgent extension on Windows Server 2019 with Sql Server 2017?

I have a virtual machine using the Microsoft Windows Server 2019 Datacenter image on which I have installed SQL Server 2017 (using the SQLServerDSC SqlSetup resource).

When I try to deploy the SqlIaasExtension using an ARM template, the deployment fails with status message:

SQL Server IaaS Agent: Could not get instancePath registry key 'hklm\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';Automated Patching: Automated Patching enabled: False, Windows Update state: ScheduledInstallation, VM is up to date in applying important updates.;Automatic Telemetry: Performance Collector State: Running

Detailed Status;

[
    {
        "code": "ComponentStatus/SQL Server IaaS Agent/failed/56061",
        "level": "Error",
        "displayStatus": "Provisioning failed",
        "message": "Could not get instancePath registry key 'hklm\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'"
    },
    {
        "code": "ComponentStatus/Automated Patching/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"Enable\":false,\"DayOfWeek\":\"Sunday\",\"MaintenanceWindowStartingHour\":2,\"MaintenanceWindowDuration\":60,\"PatchCategory\":\"WindowsMandatoryUpdates\"}"
    },
    {
        "code": "ComponentStatus/Automated Patching Configuration Manager/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "Status Report: AutomatedPatchingEnabled: False, WeeklyPatchingSchedule: ScheduleDayOfWeek: Sunday,MaintenanceStartHour: 2,MaintenanceDurationInMinutes: 60,UpdatesDownloadHour: 22, PatchCategory: WindowsMandatoryUpdates, SchedulerWaitTimeInSeconds: 60, MonitorWaitTimeInSeconds: 60, RebootWaitTimeInSeconds: 120"
    },
    {
        "code": "ComponentStatus/Automated Patching Status Monitor/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "Status Report: WindowsUpdateStatusDetectTime: 1/1/0001 12:00:00 AM, WindowsUpdateConflictDetected: False, UpdatesInstalledAutomatically: False, VirtualMachineOutOfDate: False; Details: Windows Update is set to install updates automatically."
    },
    {
        "code": "ComponentStatus/Automated Patching Scheduler/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "Status Report: Status: UpdateSchedulerDisabled, LastMaintenanceWindowStartTimeInLocalTime: Not Set, LastMaintenanceWindowEndTimeInLocalTime: Not Set, TotalUpdatesDownloaded: 0, TotalUpdatesScanned: 0, TotalUpdatesInstalled: 0, TotalReboots: 0, ExceededMaintenanceWindow: False, TotalDownloadErrors: 0, TotalScanErrors: 0, TotalInstallErrors: 0, TotalRebootErrors: 0, TotalSystemRestoreErrors: 0, PercentUpdatesInstalled: 0, "
    },
    {
        "code": "ComponentStatus/Automated Backup/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"Enable\":false,\"EnableEncryption\":false,\"RetentionPeriod\":0,\"StorageAccountUrl\":\"\",\"BackupScheduleType\":\"SYSTEM\",\"FullBackupFrequency\":\"NULL\",\"FullBackupStartTime\":\"0\",\"FullBackupWindowHours\":\"-P10675199DT2H48M5.4775808S\",\"LogBackupFrequency\":\"-P10675199DT2H48M5.4775808S\"}"
    },
    {
        "code": "ComponentStatus/Managed Backup Health Status/succeeded/57002",
        "level": "Warning",
        "displayStatus": "Provisioning succeeded",
        "message": "The number of databases with managed backup enabled: 0, the number of databases with managed backup disabled: 3, the number of databases with encrypted backup: 0, the number of databases with unencrypted backup: 3, the number of successful backups: 0, the number of failed backups: 0"
    },
    {
        "code": "ComponentStatus/Automated Backup Configuration Status/succeeded/57001",
        "level": "Warning",
        "displayStatus": "Provisioning succeeded",
        "message": "Enabled:False, EncryptionEnabled:False, RetentionPeriod:0, FullBackupFrequency:NULL, FullBackupStartTime:0, FullBackupWindowHours:-P10675199DT2H48M5.4775808S, LogBackupFrequency -P10675199DT2H48M5.4775808S, BackupSystemDbs False, BackupScheduleType SYSTEM, IsDBParameterValid True"
    },
    {
        "code": "ComponentStatus/Automatic Telemetry/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"Region\":\"eastus\"}"
    },
    {
        "code": "ComponentStatus/Key Vault Credential/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"Enable\":false,\"CredentialName\":null}"
    },
    {
        "code": "ComponentStatus/Last Configuration Update/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "AKV Integration is not configured."
    },
    {
        "code": "ComponentStatus/EKM Configuration/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "EKM is not configured on the SQL Server instance."
    },
    {
        "code": "ComponentStatus/Credential Creation/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "Credentials are not detected in SQL Server."
    },
    {
        "code": "ComponentStatus/Server Configuration Management/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"SQLConnectivityUpdateSettings\":{\"ConnectivityType\":\"Private\",\"Port\":\"1433\"},\"SQLWorkloadTypeUpdateSettings\":{\"SQLWorkloadType\":\"GENERAL\"},\"SQLStorageUpdateSettings\":{\"DiskConfigurationType\":\"NEW\",\"DiskCount\":\"1\",\"NumberOfColumns\":\"1\",\"StartingDeviceID\":\"2\"},\"AdditionalFeaturesServerConfigurations\":{\"BackupPermissionsForAzureBackupSvc\":null,\"IsRServicesEnabled\":false}}"
    },
    {
        "code": "ComponentStatus/SQL Configuration/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"Storage\":{\"Name\":\"StorageReport\",\"Status\":0,\"VolumesList\":[{\"DriveLetter\":\"D\",\"TotalSizeInMiB\":16382,\"FreeSizeInMiB\":14408,\"SqlServerLogSizeInMiB\":0,\"SqlServerDataSizeInMiB\":0,\"OtherDataSizeInMiB\":1974,\"FileSystemLabel\":\"Temporary Storage\"},{\"DriveLetter\":\"\",\"TotalSizeInMiB\":499,\"FreeSizeInMiB\":465,\"SqlServerLogSizeInMiB\":0,\"SqlServerDataSizeInMiB\":0,\"OtherDataSizeInMiB\":34,\"FileSystemLabel\":\"System Reserved\"},{\"DriveLetter\":\"C\",\"TotalSizeInMiB\":129545,\"FreeSizeInMiB\":108228,\"SqlServerLogSizeInMiB\":46,\"SqlServerDataSizeInMiB\":45,\"OtherDataSizeInMiB\":21226,\"FileSystemLabel\":\"Windows\"}],\"SqlWorkload\":\"NOTSET\",\"SqlVirtualDisk\":{\"FriendlyName\":\"\",\"Interleave\":0,\"NumberOfColumns\":0},\"NextLun\":2},\"Connectivity\":{\"Name\":\"SqlConnectivityReport\",\"Status\":0,\"Port\":1433,\"SqlFirewallEnabled\":false,\"SqlConnectivityType\":\"NotSet\",\"SqlAuthenticationEnabled\":false},\"AutoPatching\":{\"Name\":\"AutoPatchingReport\",\"Status\":0,\"Enable\":false,\"DayOfWeek\":\"Sunday\",\"MaintenanceWindowStartingHour\":2,\"MaintenanceWindowDuration\":60,\"PatchCategory\":\"WindowsMandatoryUpdates\"},\"AutoBackup\":{\"Name\":\"AutoBackupReport\",\"Status\":0,\"Enable\":false,\"EnableEncryption\":false,\"RetentionPeriod\":-1,\"StorageAccountUrl\":\"NOTSET\",\"BackupSystemDbs\":false,\"BackupScheduleType\":null,\"IsDBParameterValid\":true},\"AzureKeyVault\":{\"Name\":\"AzureKeyVaultReport\",\"Status\":0,\"Enable\":false,\"CredentialsList\":[]},\"AutoTelemetryReport\":{\"Name\":\"AutoBackupReport\",\"Status\":0,\"PerformanceCollectorStatus\":\"Running\",\"Location\":\"eastus\"},\"AdditionalFeaturesReport\":{\"Name\":\"AdditionalFeaturesReport\",\"Status\":0,\"RServicesEnabled\":false,\"BackupPermissionsToAzureBackupSvc\":false}}"
    },
    {
        "code": "ComponentStatus/Last Configuration Update/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"Name\":\"Unknown\",\"Status\":1,\"UpdateSqlAuthSettingsSuccess\":true,\"UpdateSqlConnectivitySettingsSuccess\":true,\"UpdateSqlStorageSettingsSuccess\":true,\"UpdateAdditionalFeaturesSuccess\":true,\"ErrorMessage\":\"\"}"
    },
    {
        "code": "ComponentStatus/Additional Features Status/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "{\"Name\":\"AdditionalFeaturesReport\",\"Status\":0,\"RServicesEnabled\":false,\"BackupPermissionsToAzureBackupSvc\":false}"
    },
    {
        "code": "ComponentStatus/Customer Support Logs/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded"
    },
    {
        "code": "ComponentStatus/Customer Support Logs Plugin Status/succeeded",
        "level": "Info",
        "displayStatus": "Provisioning succeeded",
        "message": "The Plugin is currently enabled"
    }
]

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 0bc65042-b759-8d79-dae9-1f519eb8c061
• Version Independent ID: 1ef8390e-c73d-1ae1-a144-13d226de7e06
• Content: Create a SQL Server Windows VM in the portal
• Content Source: articles/virtual-machines/windows/sql/quickstart-sql-vm-create-portal.md
• Service: virtual-machines-sql
• GitHub Login: @MashaMSFT
• Microsoft Alias: mathoma

[jakaruna-MSFT]

@mjcarrabine Thanks for posting here. Please let me know the document which you followed if you are following one

[mjcarrabine]

Hello @jakaruna-MSFT , there is no single document I am following, but it would be great if that existed. It seems that if you are using one of Microsoft's provided Sql Server templates, the SqlaasAgent extension just works, but there is not much documentation on how to use it otherwise. Unfortunately, there is not currently a Microsoft provided image with SQL Server 2017 on Windows Server 2019.

I was hoping you could help answer:

• Can the SqlIaasAgent Extension be successfully provisioned on a virtual machine with SQL Server 2017 + Windows Server 2019?
  
  
  
  • If so, what are the proper steps to deploy the SqlIaasAgent on a virtual machine with SQL Server 2017 + Windows Server 2019 using an ARM template for deployment and DSC for configuration?
  
  
  • If not, what is the recommended way to monitor this Sql Server virtual machine in Azure?
  
  

I have tried a lot of things, but this is the simplest process I tried before opening this issue:

1. Using an ARM template to deploy a Windows Server 2019 Datacenter Virtual Machine.
2. Using DSC to install SQL Server 2017 Standard. This works, I am able to log into SQL Server. (I am using the en_sql_server_2017_standard_x64_dvd_11294407 image that I downloaded from MSDN, expanded, and uploaded to an Azure File Share)

    File SQLServer2017Source {
            Ensure = "Present"
            Type = "Directory"
            Recurse = $true
            SourcePath = "\\$($Node.FileShareStorageAccountName).file.core.windows.net\applications\MS SQL Server 2017\SqlSetup_x64_ENU"
            DestinationPath = "C:\IRM\MS SQL Server 2017\SqlSetup_x64_ENU"
            Credential = [pscredential]::new("Azure\$($Node.FileShareStorageAccountName)", (ConvertTo-SecureString -force -AsPlainText $Node.FileSharePassword))
            DependsOn = "[File]IRMFolder"
        }

        SqlSetup SqlSetup{
            InstanceName = "MSSQLSERVER"
            Features = "SQLENGINE" # This is a comma separated string WITHOUT SPACES, not an array
            SourcePath = "C:\IRM\MS SQL Server 2017\SqlSetup_x64_ENU"
            SQLSysAdminAccounts = @("Administrators")
            DependsOn = "[File]SQLServer2017Source"
        }

1. Using an ARM Template to try to deploy the SqlIaasAgent Extension. This results in the SQL Configuration pane being added to the main menu for the Virtual Machine in the Azure portal, but the extension is showing as "Provisioning failed" with the messages in my original post.

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.0",
  "parameters": {
    "virtualMachineName": {
      "value": "DEV-SQL05-02"
    },
    "sqlConnectivityType": {
      "value": "Private"
    },
    "sqlPortNumber": {
      "value": 1433
    },
    "sqlStorageDisksCount": {
      "value": 1
    },
    "sqlStorageDisksSize": {
      "value": 1023
    },
    "sqlStorageDisksStorageAccountType": {
      "value": "Premium_LRS"
    },
    "sqlStorageWorkloadType": {
      "value": "GENERAL"
    },
    "sqlStorageDisksConfigurationType": {
      "value": "NEW"
    },
    "sqlStorageStartingDeviceId": {
      "value": 2
    },
    "sqlAutopatchingDayOfWeek": {
      "value": "Sunday"
    },
    "sqlAutopatchingStartHour": {
      "value": "2"
    },
    "sqlAutopatchingWindowDuration": {
      "value": "60"
    },
    "sqlAuthenticationLogin": {
      "value": "irmadmin"
    },
    "sqlAuthenticationPassword": {
      "reference": {
        "keyVault": {
          "id": "/subscriptions/71aa3acb-7954-487a-95dd-c0ffa3b2475f/resourceGroups/IRM-DEV-05-02/providers/Microsoft.KeyVault/vaults/IRM-DEV-05-02-KeyVault"
        },
        "secretName": "DEV-SQL05-02-sql-irmadmin-password"
      }
    },
    "rServicesEnabled": {
      "value": "false"
    }
  }
}

{
  "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "virtualMachineName": {
      "type": "string"
    },
    "sqlConnectivityType": {
      "type": "string"
    },
    "sqlPortNumber": {
      "type": "int"
    },
    "sqlStorageDisksCount": {
      "type": "int"
    },
    "sqlStorageDisksSize": {
      "type": "int"
    },
    "sqlStorageDisksStorageAccountType": {
      "type": "string",
      "allowedValues": [ "Premium_LRS", "Standard_LRS" ],
      "metadata": {
        "description": "Determines SQL Data Disk Storage Type and caching"
      }
    },
    "sqlStorageWorkloadType": {
      "type": "string",
      "metadata": {
        "description": "SQL Server Virtual Machine Workload Type: GENERAL - general work load; DW - datawarehouse work load; OLTP - Transactional processing work load"
      },
      "allowedValues": [
        "GENERAL",
        "OLTP",
        "DW"
      ]
    },
    "sqlStorageDisksConfigurationType": {
      "type": "string"
    },
    "sqlStorageStartingDeviceId": {
      "type": "int"
    },
    "sqlAutopatchingDayOfWeek": {
      "type": "string"
    },
    "sqlAutopatchingStartHour": {
      "type": "string"
    },
    "sqlAutopatchingWindowDuration": {
      "type": "string"
    },
    "sqlAuthenticationLogin": {
      "type": "string"
    },
    "sqlAuthenticationPassword": {
      "type": "securestring"
    },
    "rServicesEnabled": {
      "type": "string"
    }
  },
  "variables": {
  },
  "resources": [
    {
      "apiVersion": "2015-06-15",
      "type": "Microsoft.Compute/virtualMachines/extensions",
      "name": "[concat(parameters('virtualMachineName'), '/SqlIaasExtension')]",
      "location": "[resourceGroup().location]",
      "dependsOn": [
      ],
      "properties": {
        "type": "SqlIaaSAgent",
        "publisher": "Microsoft.SqlServer.Management",
        "typeHandlerVersion": "1.2",
        "autoUpgradeMinorVersion": "true",
        "settings": {
          "AutoTelemetrySettings": {
            "Region": "[resourceGroup().location]"
          },
          "AutoPatchingSettings": {
            "PatchCategory": "WindowsMandatoryUpdates",
            "Enable": true,
            "DayOfWeek": "[parameters('sqlAutopatchingDayOfWeek')]",
            "MaintenanceWindowStartingHour": "[parameters('sqlAutopatchingStartHour')]",
            "MaintenanceWindowDuration": "[parameters('sqlAutopatchingWindowDuration')]"
          },
          "KeyVaultCredentialSettings": {
            "Enable": false,
            "CredentialName": ""
          },
          "ServerConfigurationsManagementSettings": {
            "SQLConnectivityUpdateSettings": {
              "ConnectivityType": "[parameters('sqlConnectivityType')]",
              "Port": "[parameters('sqlPortNumber')]"
            },
            "SQLWorkloadTypeUpdateSettings": {
              "SQLWorkloadType": "[parameters('sqlStorageWorkloadType')]"
            },
            "SQLStorageUpdateSettings": {
              "DiskCount": "[parameters('sqlStorageDisksCount')]",
              "NumberOfColumns": "[parameters('sqlStorageDisksCount')]",
              "StartingDeviceID": "[parameters('sqlStorageStartingDeviceId')]",
              "DiskConfigurationType": "[parameters('sqlStorageDisksConfigurationType')]"
            },
            "AdditionalFeaturesServerConfigurations": {
              "IsRServicesEnabled": "[parameters('rServicesEnabled')]"
            }
          }
        },
        "protectedSettings": {
          "SQLAuthUpdateUserName": "[parameters('sqlAuthenticationLogin')]",
          "SQLAuthUpdatePassword": "[parameters('sqlAuthenticationPassword')]"
        }
      }
    }
  ],
  "outputs": {
  }
}

[jakaruna-MSFT]

@mjcarrabine Thanks for the information. I will check and update you

[MashaMSFT]

Hi @mjcarrabine , thank you for contacting Microsoft! Can you please try using PowerShell to install the SQL IaaS agent in lightweight mode?

Instructions can be found here: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-register-with-resource-provider

Once it's installed, please try to upgrade to full (this will cause SQL Server service to restart):
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-server-agent-extension?tabs=azure-portal#change-management-modes

Please let us know if these steps work for you?
Thanks!
Masha from SQL Docs team

[jakaruna-MSFT]

@mjcarrabine Did you got the chance to look at the @MashaMSFT 's comment

[mimckitt]

@mjcarrabine we will go ahead and close this out. If the links provided do not help just let us know and we can reopen and continue the discussion.

[mjcarrabine]

Thank you @MashaMSFT , @jakaruna-MSFT and @MicahMcKittrick-MSFT, I was able to install the SqlIaasAgent on Windows Server 2019 with SQL Server 2017 using PowerShell at the links provided by @MashaMSFT. I was also able to install it by going directly to Full mode.

Is it possible to install the latest SqlIaasExtension using an ARM Template like other extensions?

• If not, can the documentation be updated to include a line that says "The ARM template method does not work with the latest version of the SqlIaasExtension." The documentation does a good job explaining the new way, but it does not indicate that the way that used to work is no longer supported. It would have saved me many hours.
• Also, if not, why not? ARM templates are a very powerful tool provided by Azure. All of my deployments are currently using ARM templates followed by Azure DSC for configuration. Adding this PowerShell step in the middle is disruptive to automating deployment of SQL Server VMs.

[MashaMSFT]

Hi @mjcarrabine , thank you for this feedback! Let me see what I can find out for you regarding this.

Thanks!
Masha from the SQL Docs team

[MashaMSFT]

Hi @mjcarrabine , thanks again for contacting Microsoft! I just wanted to provide a quick update and let you know that we're looking into creating an ARM template :) I'll keep you posted on the progress, and let you know what comes of our investigation.

Thanks!
Masha from the SQL Docs team

[clumsym76]

We have the same issue with a migrated VM (recovered from data disk), registration was successful, but we are not able configure the extension via

`
$SettingsString = '{"AutoPatchingSettings":{"PatchCategory":"WindowsMandatoryUpdates","Enable":true,"DayOfWeek":"Sunday","MaintenanceWindowStartingHour":"2","MaintenanceWindowDuration":"60"},"KeyVaultCredentialSettings":{"Enable":false},"ServerConfigurationsManagementSettings":{"SQLConnectivityUpdateSettings":{"ConnectivityType":"PRIVATE","Port":"1433"},"SQLWorkloadTypeUpdateSettings":{"SQLWorkloadType":1},"SQLStorageUpdateSettingsV2":{"DiskConfigurationType":"NEW","SQLDataSettings":{"DefaultFilePath":"F:\Data","LUNs":[0,1,2]},"SQLLogSettings":{"DefaultFilePath":"F:\Log","LUNs":[0,1,2]},"SQLTempDbSettings":{"DefaultFilePath":"F:\TempDb","LUNs":[0,1,2]}},"AdditionalFeaturesServerConfigurations":{"IsRServicesEnabled":false}},"SqlManagement":{"IsEnabled":true},"DeploymentTokenSettings":{"DeploymentToken":1648357018}}';

$ProtectedSettingsString = '{"SQLAuthUpdateUserName": "admin", "SQLAuthUpdatePassword": "password"}';

Set-AzVMExtension -ResourceGroupName "rg.name" -VMName "vm.name" -Name "SqlIaasExtension" -SettingString $SettingsString -ProtectedSettingString $ProtectedSettingsString -Location "location.name" -Publisher "Microsoft.SqlServer.Management" -Type "SqlIaaSAgent"
`

We got the following exception:

Set-AzVMExtension : Long running operation failed with status 'Failed'. Additional Info:'VM has reported a failure when processing extension 'SqlIaasExtension'. Error message: "SQL Server IaaS Agent: ||Update failed, Plugin: Automated Patching, ErrorCode: NotRetryableUnexpectedError, Object reference not set to an instance of an object.||,||Update failed, Plugin: Server Configuration Management, ErrorCode: NotRetryableUnexpectedError, Failed while checking if sql login 'admin' exists. Sql exception number: 18456.Login failed for user 'DOMAIN\HOSTNAME$'.;Login failed for user 'DOMAIN\HOSTNAME$'.;The creator of this fault did not specify a Reason.||;Resource Provider Plugin: Rp plugin is enabled.;Automated Patching: Automated Patching enabled: False, Windows Update state: ScheduledInstallation, VM is out of date in applying some of the important updates.;SQL Configuration: {"Name":"ConfigurationUpdateStatus","Status":2,"UpdateSqlAuthSettingsSuccess":false,"UpdateSqlConnectivitySettingsSuccess":false,"UpdateSqlStorageSettingsSuccess":false,"UpdateAdditionalFeaturesSuccess":false,"ErrorMessage":""}".' ErrorCode: VMExtensionProvisioningError ErrorMessage: VM has reported a failure when processing extension 'SqlIaasExtension'. Error message: "SQL Server IaaS Agent: ||Update failed, Plugin: Automated Patching, ErrorCode: NotRetryableUnexpectedError, Object reference not set to an instance of an object.||,||Update failed, Plugin: Server Configuration Management, ErrorCode: NotRetryableUnexpectedError, Failed while checking if sql login 'admin' exists. Sql exception number: 18456.Login failed for user 'DOMAIN\HOSTNAME$'.;Login failed for user 'DOMAIN\HOSTNAME$'.;The creator of this fault did not specify a Reason.||;Resource Provider Plugin: Rp plugin is enabled.;Automated Patching: Automated Patching enabled: False, Windows Update state: ScheduledInstallation, VM is out of date in applying some of the important updates.;SQL Configuration: {"Name":"ConfigurationUpdateStatus","Status":2,"UpdateSqlAuthSettingsSuccess":false,"UpdateSqlConnectivitySettingsSuccess":false,"UpdateSqlStorageSettingsSuccess":false,"UpdateAdditionalFeaturesSuccess":false,"ErrorMessage":""}". ErrorTarget: StartTime: 10/6/19 10:41:04 AM EndTime: 10/6/19 10:41:04 AM OperationID: 67a011e5xxxxxxxxx Status: Failed

[MashaMSFT]

Hi @clumsym76 , I'm sorry to hear you're running into issues and thanks for bringing this to our attention! Can you please provide steps for what you're trying to do?

Did you register in lightweight and are you trying to upgrade to full? If the SQL IaaS extension is in an errored state, have you tried uninstalling the SQL IaaS extension entirely and installing it directly to full?

Thanks!
Masha from the SQL Docs team

[MashaMSFT]

@clumsym76 , I'm also seeing a few differences between the installation command on the docs, and the installation command you're using:

From the docs:
Set-AzVMExtension -ResourceGroupName "<ResourceGroupName>"
-Location "" -VMName "" -Name "SqlIaasExtension" -Publisher "Microsoft.SqlServer.Management"
-ExtensionType "SqlIaaSAgent" -TypeHandlerVersion "2.0";
`

From your example:

Set-AzVMExtension -ResourceGroupName "rg.name" -VMName "vm.name" -Name "SqlIaasExtension" -SettingString $SettingsString -ProtectedSettingString $ProtectedSettingsString -Location "location.name" -Publisher "Microsoft.SqlServer.Management" -Type "SqlIaaSAgent"

Can you try using the command from the docs explicitly to verify if there's any difference in behavior?

Thanks!
Masha from the SQL Docs team

[kgopi05]

Hi,
i have installed the SQLIaaSAgent manually and imported into Terraform got below values in the settings.

i would like know what is this DeploymentToken value and how to get this value ? There is no documentation about this value . Any help is much appreciated.

settings = jsonencode(
{
DeploymentTokenSettings = {
DeploymentToken = 9234173137
}
SqlManagement = {
IsEnabled = false
}
}
)

[mjcarrabine]

Hi @mjcarrabine , thanks again for contacting Microsoft! I just wanted to provide a quick update and let you know that we're looking into creating an ARM template :) I'll keep you posted on the progress, and let you know what comes of our investigation.

Thanks!
Masha from the SQL Docs team

Any progress on creating an ARM template?

The current lack of automation for managing SQL Virtual Machines is putting a big dent in our ability to automate deployments.

[pjvander]

Just wanted to piggyback onto this issue, and state that I am also receiving "Provisioning Failed" messages when trying to deploy the SQLIaaSAgent via a Terraform "azurerm_virtual_machine_extension":

```
resource "azurerm_virtual_machine_extension" "dsc-extension-mssql-iaas-agentVm0" {
name = "Microsoft.SqlServer.Management"
location = var.location
resource_group_name = azurerm_resource_group.sqlvmrg.name
virtual_machine_name = azurerm_virtual_machine.sqlvm0.name
publisher = "Microsoft.SqlServer.Management"
type = "SqlIaaSAgent"
type_handler_version = "2.0"
#count = "var.vm_count_to_create"
depends_on = [azurerm_virtual_machine.sqlvm0,
azurerm_automation_account.dsc-automation-account,
azurerm_virtual_machine_extension.join-domainVm1]
tags = {
environment = var.env
billing_code = var.tags_billing_code
}
settings = < {
"configurationArguments": {
"Properties": {
"SqlServerLicenseType": "PAYG",
"sqlManagement": "LightWeight"
}
}
}
SETTINGS_JSON
}

[MashaMSFT]

Hi @mjcarrabine , thanks for reaching out once more! My apologies for the delay; we're still looking into this.

@pjvander , thanks for letting us know! Let me look into this; but just so you know you may get faster assistance opening up a support ticket, as this forum is really meant for issues with the documentation, and not with the product.

Thanks!
Masha from the SQL Docs team

[gavinwoolley]

Hi, i would like to share my findings. I have managed to make this work successfully using Terraform, Azure and SQL 2017 with Server 2019 image.
This is the link to the Terraform module for configuring SQL https://github.com/gavinwoolley/TerraformWindowsVmInfrastructure/blob/master/Azure/modules/sql-vm/4-configure-sql.tf

You can see what the entire solution does here : https://github.com/gavinwoolley/TerraformWindowsVmInfrastructure

[a30000931]

@gavinwoolley that repo seems to be private

[gavinwoolley]

@gavinwoolley that repo seems to be private

Try again now, should be public.

[a30000931]

@gavinwoolley thanks!

@kgopi05 i also get this issue when re-running the TF code with the extension that azure adds a:
DeploymentTokenSettings = {
- DeploymentToken = "9982303"
}

so TF wants to of course remove it - and the numbe changes each time

thoughts?

[MashaMSFT]

Hi @mjcarrabine , thank you for your patience as we continue looking into this for you. Just to make sure I understand your intent correctly, you're trying to automate the deployment of multiple machines, and you want all of these machines registered with the SQL VM resource provider in full management mode, is that correct?

I wanted to know if something like this would meet your needs? https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-bulk-register-with-resource-provider

Thanks!
Masha from the SQL Docs team

[aleksbor]

Hi @MashaMSFT. @mjcarrabine is not doing anything extraordinary and yet you suggest him a 1000-line workaround. Can we get help deploying a SQL IaaS extension using a template? I have a similar scenario. Any way to reopen this issue?

[MashaMSFT]

@aleksbor , can you please share the business justification behind this need? What would the template be doing that is not possible to do with the SQL VM resource provider currently?

Thanks!
Masha from the SQL Docs team

[aleksbor]

@MashaMSFT we side-load sql and did not see integration with Azure Portal (resource provider was enabled in a subscription). I was able to install SQLIaaS extension using powershell, but it is still in the "transitioning" state; at least now we can see SQL controls in Azure Portal. So, what is the difference between the two and why SQLIaaS extension exists if you have a resource provider?
I don't want to use powerhell (or reduce that dependency) when I deploy resources using templates.

[mjcarrabine]

Hi @mjcarrabine , thank you for your patience as we continue looking into this for you. Just to make sure I understand your intent correctly, you're trying to automate the deployment of multiple machines, and you want all of these machines registered with the SQL VM resource provider in full management mode, is that correct?

I wanted to know if something like this would meet your needs? https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-bulk-register-with-resource-provider

Thanks!
Masha from the SQL Docs team

@MashaMSFT, if the recommendation has changed in the last 6 months from using the SqlIaasAgent extension to registering the VM with the SQL VM resource provider, then I no longer have a need to install the extension.

However, we are using 1. ARM templates for deployment, and 2. Azure DSC for configuration. Any steps in the process that cannot be added to those processes reduces the reliability and repeatability of the process.

The Register a SQL Server virtual machine in Azure with the SQL VM resource provider document states in the first paragraph
| Registering with the resource provider creates the SQL virtual machine resource within your subscription

So if it creates a resource, would you please offer an Azure Resource Manager template to create that resource so I don't need to add step 3. run PowerShell. I can create that as its own ticket on that page if you want me to.

[yareyes]

@aleksbor let me try to answer your question:

Actually the resource provider and SqlIaaSExtension go hand in hand. Before the SQL VM resource provider was released, SqlIaaSExtension could be accessed on the "SQL Server Configuration" blade in Portal.

Now that the resource provider exists, it's in charge of installing the SQLIaaSExtension on customer's behalf, so there is no need for you to install the SqlIaaSExtension separately. The manageability has also been moved to a complete new resource, where you have now option to change licensing as well.

It's true that you also can only install the extension by running powershell command, but I wouldn't recommend this option, since the Resource Provider is giving you this functionality.

To get an ARM template, you can go to Portal once and create a Virtual Machine with any SQL Image. Before you deploy, download the Template. The ARM template should look like this:

This template should be able to be automated. If you notice, the parameters in this ARM Template are same parameters used to just create SqlIaaSExtension.

The only two configurations settings I will like to clarify are:

1. StorageConfigurationSettings = This plugin is assuming that there are disks attached to the Virtual machine. This won't create new disks itself. Please be aware of that when automating.

2. DeploymentTokenSettings = This is just a random number that gets generated when SQL VM Resource provider is sending the settings. This random number is just telling the extension that there are new settings that needed to be applied. You could also skip this setting and there should be no issues.

To find all the possible settings that can be passed, you should be able to find them here.

Please let me know if this helps you with your doubts.