The database ‘jbswiki’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

-> Application fails with below error when trying to connect to Azure SQL database,

Msg 40544, Level 17, State 2, Line 10
The database ‘jbswiki’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

-> Executing below query on “jbswiki” database to check the used and free space on the database,

if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%' 
SELECT [name], fileid, filename, [size]/128.0 AS 'Total Size in MB', 
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', 
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' 
FROM sysfiles 
else
SELECT @@servername as 'ServerName',db_name() as DBName,[name], file_id, physical_name, [size]/128 AS 
'Total Size in MB', 
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', 
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' 
FROM sys.database_files
go

-> From the screenshot above, it is clear that the database “jbswiki” is full.

-> Lets try a simple insert statement and check the behaviour,

-> Login to Azure portal and select the database jbswiki. On “Overview” tab you will see that the database is full,

-> Click on “Compute + storage” under Settings. Change the “Data Max Size” to an appropriate value. In my case I have changed from 100 to 500 MB. Click Apply,

-> Application connections to database jbswiki started working fine after above change.

-> Below is the view of “Overview” tab after the change,

-> Lets try an insert and check if it is working,

->It worked fine this time.

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.

Connect to SQL Server with PowerShell and Azure key Vault

-> Requirement will be to query an Azure SQL Database via PowerShell and display the results. We will not list the SQL Server Instance, Username and password as clear text in PowerShell script, instead we use the secret from Azure Key Vault to achieve this.

Create an Azure SQL Database

-> Complete tabs “Networking”, “Security”, “Additional settings” and “Tags” as appropriate.

Create an Azure Key Vault

-> Complete tabs “Access policy”, “Networking” and “Tags” as appropriate.

-> Get into Azure Key Vault “sqlcon-key-vault-eastus”. Click on “Secrets” and then “Generate/Import”,

-> Value as mentioned in above screenshot is as below,

Server=tcp:jbdw.database.windows.net,1433;Initial Catalog=prod-jbsqldb01;Persist Security Info=False;User ID=<username>;Password=<Password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

-> Click Create.

Get the required Data using PowerShell

##Script to display database names available## 
## Version 1.0 
##Author : Vivek Janakiraman## 
##cls ##Clearing Screen 

Connect-AzAccount

##Key in the correct Subscription below##
Set-AzContext -Subscription "00000000-0000-0000-0000-000000000000"

$keyvault = "sqlcon-key-vault-eastus" ##Provide the Azure key Vault name##
$ConnectionString = "prod-jbsqldb01-connection-string" ##Provide the the name of the secret created within Azure key Vault##


$Vault = get-azkeyvault -VaultName $keyvault
$Secret = get-azkeyvaultsecret -Name $ConnectionString -VaultName $keyvault
$SecretValue = $Secret.SecretValue 
$ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecretValue))

$tsqlcommand = "select name from sys.databases"; 

#SQL Connection - connection to SQL server 
$sqlConnection = new-object System.Data.SqlClient.SqlConnection; 
$sqlConnection.ConnectionString = $connectionString; 

#SQL Command - set up the SQL call 
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand; 
$sqlCommand.Connection = $sqlConnection; 
$sqlCommand.CommandText = $tsqlcommand; 

#SQL Adapter - get the results using the SQL Command 
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand 
$dataSet = new-object System.Data.Dataset 
$recordCount = $sqlAdapter.Fill($dataSet) 

#Close SQL Connection 
$sqlConnection.Close(); 

#Get single table from dataset 
$data = $dataSet.Tables[0] 
$data

-> Below is the output,

-> There is a possibility of below error while executing the script,

Exception calling "SecureStringToBSTR" with "1" argument(s): "Value cannot be null.
Parameter name: s"
At C:\temp\Connection_Template_Working.ps1:16 char:1
+ $ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToSt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException
 
Exception setting "ConnectionString": "Format of the initialization string does not conform to specification starting at index 0."
At C:\temp\Connection_Template_Working.ps1:22 char:1
+ $sqlConnection.ConnectionString = $connectionString;
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting
 
Exception calling "Fill" with "1" argument(s): "The ConnectionString property has not been initialized."
At C:\temp\Connection_Template_Working.ps1:33 char:1
+ $recordCount = $sqlAdapter.Fill($dataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

Exception calling “SecureStringToBSTR” with “1” argument(s): “Value cannot be null.

-> This error occurs when parameter $ConnectionString is wrong. It should be same as what is seen in below screenshot in Azure key Vault,

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.

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.