Standard

Posted by

Posted on

March 18, 2021

Posted under

Azure

Comments

Leave a comment

SQL SERVER IAAS AGENT EXTENSION – “Security” tab not populated correctly

-> IaaS Agent extension in full mode was enabled on one of our IaaS SQL Server. Some tabs were exhibiting different behaviour.

-> Check out this article to understand what is IaaS Agent extensions and how you can enable it.

Security Tab

-> SQL server instance on JBSAG1 has “server authentication” set to “SQL Server and Windows Authentication mode”. So the details displayed above is not right, also the port number displayed in above screenshot “-1” doesn’t seem right.

High Availability (Preview)

-> “High Availability (Preview) is disabled.

Backups

-> Click “Apply”.

-> It failed with below error,

Deployment to resource group 'JBSAON_SYNC' failed.
Additional details from the underlying API that might be helpful: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/DeployOperations for usage details.
{
  "message": "Deploy failed with deploymentStatusCode (Failure)",
  "name": "Error",
  "stack": "Error: Deploy failed with deploymentStatusCode (Failure)\n    at new t (https://portal.azure.com/Content/Dynamic/j1Od2RZvsmhl.js:82:895)\n    at Q.nextTick.nt.requirejs.t.createError (https://portal.azure.com/Content/Dynamic/j1Od2RZvsmhl.js:76:6150)\n    at Object.i.createError (https://portal.azure.com/Content/Dynamic/j1Od2RZvsmhl.js:69:2649)\n    at Object.nt [as QReject] (https://portal.azure.com/Content/Dynamic/V8SLU-3Vpv3Z.js:13:1307)\n    at u (https://portal.azure.com/Content/Dynamic/VB2Dp4SgtH0Y.js:186:133)",
  "type": "MsPortalFx.Errors.Error",
  "baseTypes": [
    "MsPortalFx.Errors.Error"
  ],
  "data": {
    "subscriptionId": "““",
    "deploymentName": "DeploySqlVmAb-20210318142749",
    "resourceGroupName": "JBSAON_SYNC",
    "resourceGroupLocation": "eastus",
    "resourceProviders": [
      "Microsoft.Compute"
    ],
    "templateJson": "{}",
    "parameters": {},
    "deploymentMode": 3,
    "skipPreflight": false,
    "validateTemplate": true,
    "telemetryId": "““",
    "submitDeployment": true,
    "getAllOperations": true,
    "pollForUpdates": true,
    "deploymentStatusCode": -1,
    "deploymentRequest": {
      "tags": {}
    },
    "validationResult": {
      "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Resources/deployments/DeploySqlVmAb-20210318142749",
      "name": "DeploySqlVmAb-20210318142749",
      "type": "Microsoft.Resources/deployments",
      "tags": {}
    },
    "deploymentUri": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Resources/deployments/DeploySqlVmAb-20210318142749?api-version=2020-06-01",
    "templateLinkUri": "",
    "templateLink": {},
    "deployment": {
      "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Resources/deployments/DeploySqlVmAb-20210318142749",
      "name": "DeploySqlVmAb-20210318142749",
      "type": "Microsoft.Resources/deployments",
      "tags": {},
      "properties": {
        "templateHash": "17360793790949814697",
        "parameters": {
          "location": {
            "type": "String",
            "value": "eastus"
          },
          "virtualMachineName": {
            "type": "String",
            "value": "JBSAG1"
          },
          "sqlAutobackupEnabled": {
            "type": "Bool",
            "value": true
          },
          "sqlAutobackupRetentionPeriod": {
            "type": "String",
            "value": "30"
          },
          "sqlAutobackupStorageAccountName": {
            "type": "String",
            "value": "iaassqlbackup"
          },
          "sqlAutobackupStorageAccountType": {
            "type": "String",
            "value": "Standard_LRS"
          },
          "sqlAutobackupEncryptionEnabled": {
            "type": "Bool",
            "value": false
          },
          "backupSystemDbs": {
            "type": "Bool",
            "value": true
          },
          "backupScheduleType": {
            "type": "String",
            "value": "Automated"
          }
        },
        "mode": "Incremental",
        "debugSetting": {
          "detailLevel": "None"
        },
        "provisioningState": "Failed",
        "timestamp": "2021-03-18T09:00:10.4034586Z",
        "duration": "PT2M14.6040746S",
        "correlationId": "7d3c6711-a22e-49e1-9971-5007a4189129",
        "providers": [
          {
            "namespace": "Microsoft.SqlVirtualMachine",
            "resourceTypes": [
              {
                "resourceType": "SqlVirtualMachines",
                "locations": [
                  "eastus"
                ]
              }
            ]
          }
        ],
        "dependencies": [
          {
            "dependsOn": [
              {
                "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Storage/storageAccounts/iaassqlbackup",
                "resourceType": "Microsoft.Storage/storageAccounts",
                "resourceName": "iaassqlbackup",
                "apiVersion": "2018-07-01"
              },
              {
                "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Storage/storageAccounts/iaassqlbackup",
                "resourceType": "Microsoft.Storage/storageAccounts",
                "resourceName": "iaassqlbackup",
                "actionName": "listKeys",
                "apiVersion": "2018-07-01"
              }
            ],
            "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.SqlVirtualMachine/SqlVirtualMachines/JBSAG1",
            "resourceType": "Microsoft.SqlVirtualMachine/SqlVirtualMachines",
            "resourceName": "JBSAG1"
          }
        ],
        "error": {
          "code": "DeploymentFailed",
          "message": "At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/DeployOperations for usage details.",
          "details": [
            {
              "code": "Ext_AutomatedBackup_SqlIaaSQueryAccountMissing",
              "message": "Login failed for user 'NT Service\\SqlIaaSExtensionQuery'. Please ensure that the login is added as sysadmin to target SQL instance."
            }
          ]
        },
        "validationLevel": "Template"
      }
    },
    "tenant": {
      "name": "Default Directory",
      "id": "97cfb497-567c-4aa9-95bf-ea7b729e8b55",
      "objectId": "6fc536d1-4b6d-4d72-8060-304e575acd72"
    },
    "requestTimestamp": "2021-03-18T08:57:57.9293802Z",
    "operations": [
      {
        "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Resources/deployments/DeploySqlVmAb-20210318142749/operations/BDFBDF9AB2B33D05",
        "operationId": "BDFBDF9AB2B33D05",
        "properties": {
          "provisioningOperation": "Create",
          "provisioningState": "Failed",
          "timestamp": "2021-03-18T09:00:10.2889607Z",
          "duration": "PT2M9.1221836S",
          "trackingId": "d2a1170d-8bd1-4854-aa72-9aece5b67332",
          "serviceRequestId": "f05ecfeb-ffe0-4f96-aa3d-bba18e527aa7",
          "statusCode": "Conflict",
          "statusMessage": {
            "status": "Failed",
            "error": {
              "code": "Ext_AutomatedBackup_SqlIaaSQueryAccountMissing",
              "message": "Login failed for user 'NT Service\\SqlIaaSExtensionQuery'. Please ensure that the login is added as sysadmin to target SQL instance."
            }
          },
          "targetResource": {
            "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.SqlVirtualMachine/SqlVirtualMachines/JBSAG1",
            "resourceType": "Microsoft.SqlVirtualMachine/SqlVirtualMachines",
            "resourceName": "JBSAG1"
          }
        }
      },
      {
        "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Resources/deployments/DeploySqlVmAb-20210318142749/operations/17AB34698944F1D5",
        "operationId": "17AB34698944F1D5",
        "properties": {
          "provisioningOperation": "Action",
          "provisioningState": "Succeeded",
          "timestamp": "2021-03-18T08:58:01.1255356Z",
          "duration": "PT0.9902122S",
          "trackingId": "9ffcadfb-25a3-4234-90fe-cfd609018ee9",
          "serviceRequestId": "1627a242-36c1-42a1-9278-bce04e4d7e9b",
          "statusCode": "OK",
          "targetResource": {
            "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Storage/storageAccounts/iaassqlbackup",
            "resourceType": "Microsoft.Storage/storageAccounts",
            "resourceName": "iaassqlbackup",
            "actionName": "listKeys",
            "apiVersion": "2018-07-01"
          }
        }
      },
      {
        "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Resources/deployments/DeploySqlVmAb-20210318142749/operations/AE9D10DFE9F64701",
        "operationId": "AE9D10DFE9F64701",
        "properties": {
          "provisioningOperation": "Read",
          "provisioningState": "Succeeded",
          "timestamp": "2021-03-18T08:58:01.1096949Z",
          "duration": "PT0.9743715S",
          "trackingId": "2bfceb0c-547d-4881-88b4-aa273dbeaeae",
          "statusCode": "OK",
          "targetResource": {
            "id": "/subscriptions/”“/resourceGroups/JBSAON_SYNC/providers/Microsoft.Storage/storageAccounts/iaassqlbackup",
            "resourceType": "Microsoft.Storage/storageAccounts",
            "resourceName": "iaassqlbackup",
            "apiVersion": "2018-07-01"
          }
        }
      }
    ]
  },
  "extension": "SqlAzureExtension",
  "errorLevel": 2,
  "timestamp": 6382639.050000002,
  "innerErrors": []
}

Below is the error we can see,

“error”: {
“code”: “Ext_AutomatedBackup_SqlIaaSQueryAccountMissing”,
“message”: “Login failed for user ‘NT Service\\SqlIaaSExtensionQuery’. Please ensure that the login is added as sysadmin to target SQL instance.”
}

-> It seems like the backup is failing because of login failure for account NT Service\SqlIaaSExtensionQuery.

-> Adding login NT Service\SqlIaaSExtensionQuery as a sysadmin as pointed out in above error message,

USE [master]
GO

CREATE LOGIN [NT SERVICE\SqlIaaSExtensionQuery] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\SqlIaaSExtensionQuery]
GO

-> Setting up backups after above change,

-> It completed fine.

-> Even the Security tab shows the correct details now and also “High Availability (Preview) is enabled.

-> So the root cause of this issue is missing sysadmin account “NT SERVICE\SqlIaaSExtensionQuery”. Account NT SERVICE\SqlIaaSExtensionQuery will be added automatically as a sysadmin when IaaS Agent extension is enabled. In our case this account was not added due to some underlying issue or was removed by someone by mistake or as part of an audit.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply