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.

Connecting Azure SQL Database using Azure Active Directory – Password / Azure Active Directory – Universal with MFA

-> I am planning to create a series of blogs on Azure Active Directory and how SQL server can be connected using Azure AD.

Other Blogs in this Series,
-> Integrate Azure Active Directory with Active Directory in Azure Virtual Machine
-> Create a login in Azure Virtual Machine Active Directory and Sync it to Azure Active Directory
-> Login to Azure Virtual machine using Azure Active Directory credentials

-> In this blog we will connect an Azure SQL Database using “Azure Active Directory – Password” / “Azure Active Directory – Universal with MFA”.

-> Creating an Azure SQL Database,

Connecting to Azure SQL Database using “Azure Active Directory – Password”

TITLE: Connect to Server
Cannot connect to jbswiki.database.windows.net.


ADDITIONAL INFORMATION:
One or more errors occurred. (mscorlib)
One or more errors occurred. (mscorlib)

AADSTS50055: The password is expired.
Trace ID: bb63ac8d-f575-4470-863a-3bbd8e04c900
Correlation ID: f6f8ac22-4c23-404e-8f94-9835cf279550
Timestamp: 2021-03-08 13:27:21Z (System.Data)

BUTTONS:
OK

-> Open a “New InPrivate Window” from edge or a “New Incognito window” from chrome and navigate to http://portal.azure.com/.

-> Once the password are set. We will move past that error.

TITLE: Connect to Server
Cannot connect to jbswiki.database.windows.net.


ADDITIONAL INFORMATION:
One or more errors occurred. (mscorlib)
One or more errors occurred. (mscorlib)

AADSTS900021: Requested tenant identifier ‘00000000-0000-0000-0000-000000000000’ is not valid. Tenant identifiers may not be an empty GUID.
Trace ID: 9da84098-11f2-4465-862a-27e2d6cdb200
Correlation ID: df8a8735-bfe0-46aa-98eb-095d49cbe2fd
Timestamp: 2021-03-08 09:01:18Z (System.Data)

BUTTONS:

OK

-> Open SQL Database JBSWIKI from Azure portal and then click on “Server name : jbswiki.database.windows.net”,

-> Click on “Active Directory admin”,

-> Click on “Set admin”,

-> Select the required login,

-> Click on “Save”,

-> Once after the above change we should be able to login without any issues.

-> There are scenarios where you might get below error,

TITLE: Connect to Server
Cannot connect to jbswiki.database.windows.net.

ADDITIONAL INFORMATION:
One or more errors occurred. (mscorlib)
One or more errors occurred. (mscorlib)

AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access ‘022907d3-0f1b-48f7-badc-1ba6abab6d66’.
Trace ID: 722d3a34-5969-499f-b3f8-c08366ca0700
Correlation ID: f8e79ae2-315e-4415-a2a4-693460fa4072
Timestamp: 2021-03-08 09:50:49Z (System.Data)

BUTTONS:
OK

-> In this case we should use “Azure Active Directory – Universal with MFA” instead of “Azure Active Directory – Password”,

-> We have added user as administrators for Azure SQL database. We can use below approach to add users using TSQL for non-administrator users,

use [jbswiki]
go
CREATE USER [Karthik@jvivek2k1yahoo.onmicrosoft.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo];
go
ALTER ROLE db_datareader ADD MEMBER [Karthik@jvivek2k1yahoo.onmicrosoft.com] 

-> We cannot see security folder anymore. Lets try a simple select and check if it works.

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.

Create a login in Azure Virtual Machine Active Directory and Sync it to Azure Active Directory

-> I am planning to create a series of blogs on Azure Active Directory and how SQL server can be connected using Azure AD.

Other Blogs in this Series,
-> Integrate Azure Active Directory with Active Directory in Azure Virtual Machine
-> Connecting Azure SQL Database using Azure Active Directory - Password / Azure Active Directory - Universal with MFA
-> Login to Azure Virtual machine using Azure Active Directory credentials

-> In this blog we will create a login in Azure Virtual Machine Active Directory and Sync it to Azure Active Directory.

-> On Azure Virtual machine where Active Directory is configured. Click on Start -> Run -> dsa.msc.

-> Select appropriate options for password. I have selected below options as this is just a test setup,

-> Login to Portal.azure.com and open Azure Active Directory. Click on users and you will see below,

-> Lets synchronize the newly created login to Azure Active Directory.

-> On desktop where Azure AD is installed. Open Azure AD Connect,

-> Click on Configure and select “Customize synchronization configuration”,

-> Provide a login that has global administrator privilege and click on Next,

-> Login to Portal.azure.com and open Azure Active Directory. Click on users and you will see below,

-> Login is visible now.

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.