Category Archives: Azure SQL Database

Standard

Posted by

Posted on

April 7, 2025

Posted under

Azure SQL Database

Comments

Leave a comment

Azure Database Watcher Series: Generating Sample Workload on SQL Targets in Azure Database Watcher

Welcome to the Azure Database Watcher Series! 👋

In our latest YouTube video titled “Azure Database Watcher Series: Generating Sample Workload on SQL Targets in Azure Database Watcher”, we demonstrate how to generate a realistic workload on an Azure SQL Managed Instance — a critical step before exploring metrics on the Azure Database Watcher dashboard.

To help you follow along and practice everything shown in the video, we’ve prepared a ZIP file containing all the necessary scripts. These scripts simulate a variety of workloads — including read, write, and even blocking scenarios — perfect for testing your monitoring setup in Azure.

📦 Download the Scripts

Click below to download the sample workload bundle:

🔗 Download JB_Database_Watcher_Sample_Workload.zip

The ZIP file includes the following SQL files:

  • Loadtest1.txt – Master script to create required objects for the workload.
  • Loadtest1_q1.txt – Script 1 that simulates workload on the Azure SQL managed instance.
  • Loadtest1_q2.txt – Script 2 that simulates workload on the Azure SQL managed instance.
  • Blocking.txt – Script to check object details.

💡 How to Use

  1. Deploy the scripts against an Azure SQL Managed Instance that is being monitored by Azure Database Watcher.
  2. Follow the exact steps as shown in the video.
  3. Generate workloads and wait for metrics to populate.
  4. Get ready for our next video, where we’ll explore the Database Watcher dashboard and deep-dive into the insights gathered.

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.

Standard

Posted by

Posted on

February 5, 2025

Posted under

Azure SQL Database

Comments

Leave a comment

Securely Scheduling Index Maintenance on Azure SQL Database Using Automation Account & Managed Identity

Index maintenance is a critical task for optimizing database performance, ensuring queries run efficiently, and keeping Azure SQL Database in top shape. Traditionally, scheduling index maintenance required storing credentials in scripts, posing security risks. However, in this blog, we’ll walk through a secure way to automate index maintenance using Azure Automation Account, Managed Identity, and PowerShell, ensuring no explicit credentials are stored. 🔒

Check out the video in you tube for a hand on experience.

✅ Why Automate Index Maintenance?

  • Enhances query performance by reducing fragmentation.
  • Ensures index optimization without manual intervention.
  • Uses Managed Identity for secure authentication, avoiding stored credentials.
  • Fully automated scheduling with Azure Automation Account.

🏗️ Prerequisites

Before we begin, ensure you have the following:

  • An Azure SQL Database instance.
  • An Azure Automation Account.
  • The Ola Hallengren IndexOptimize stored procedure installed on your database.
  • PowerShell Az module installed in the Automation Account.

🎯 Step 1: Create a Managed Identity for the Automation Account

To allow your Automation Account to securely authenticate against Azure SQL Database, assign it a System-Assigned Managed Identity:

  1. Navigate to Azure Portal → Automation Accounts.
  2. Select your Automation Account.
  3. Under Identity, enable System Assigned Managed Identity.
  4. Copy the Object ID; we’ll need it in the next step.

🔑 Step 2: Grant Database Access to the Managed Identity

Now, we need to create a login and database user in Azure SQL Database for the Managed Identity.

Run the following T-SQL script in your Azure SQL Database:

CREATE USER jbdbreindex FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER jbdbreindex;

This grants db_owner permissions to the Managed Identity, allowing it to execute maintenance tasks.

🖥️ Step 3: Create an Azure Automation Runbook

Next, let’s create a PowerShell Runbook that connects to Azure SQL Database using the Managed Identity and executes the IndexOptimize stored procedure.

  1. In Azure Portal, navigate to Automation Accounts → Runbooks.
  2. Click Create a Runbook.
  3. Provide a name (e.g., IndexMaintenanceRunbook), select PowerShell as the Runbook type.
  4. Paste the following PowerShell script:
Write-Output "Index Maintenance started"

# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection

# Connect using Managed Identity
Connect-AzAccount -Identity

# Get a Token
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net ).Token

# Initialize Connection String
$sqlConnection.ConnectionString = "Data Source=jbsserver.database.windows.net;Initial Catalog=jbdb;Connect Timeout=60"

# Set the Token for authentication
$sqlConnection.AccessToken = $token

# Open the connection
$sqlConnection.Open()
Write-Output "Azure SQL database connection opened"

# Define the SQL Command
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 0
$sqlCommand.Connection = $sqlConnection

Write-Output "Issuing command to run stored procedure"

# Execute IndexOptimize stored procedure
$sqlCommand.CommandText= "EXECUTE dbo.IndexOptimize
@Databases = 'jbdb',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 30,
@LogToTable='Y'"

$result = $sqlCommand.ExecuteNonQuery()
Write-Output "Stored procedure execution completed"

# Close connection
$sqlConnection.Close()
Write-Output "Run completed"
  1. Click Save and Publish the Runbook.

⏰ Step 4: Schedule the Runbook

Now, let’s schedule the Runbook to run automatically.

  1. Go to Automation Account → Runbooks.
  2. Select your Runbook (IndexMaintenanceRunbook).
  3. Click Schedules → Add a Schedule.
  4. Set the recurrence (e.g., Daily at Midnight).
  5. Link the schedule to the Runbook.

📊 Step 5: Monitor Runbook Execution

To verify successful execution:

  1. Go to Automation Account → Runbooks → Job History.
  2. Check if the Runbook ran successfully.
  3. Use SELECT * FROM dbo.CommandLog in SQL to view maintenance logs.

🎉 Conclusion

Congratulations! 🎊 You have successfully scheduled Index Maintenance on Azure SQL Database securely using Azure Automation, PowerShell, and Managed Identity. This approach ensures:

  • 🔒 No explicit credentials are stored in scripts.
  • 📈 Automated performance tuning for SQL Database.
  • 🛠️ Seamless execution with minimal manual intervention.

Standard

Posted by

Posted on

August 19, 2024

Posted under

Azure, Azure Managed Instance, Azure SQL Database, Azure Synapse Analytics

Comments

Leave a comment

Azure Series: Resolving RBAC Errors When Creating Keys in Azure Key Vault

Resolving the RBAC Error When Creating a Key in Azure Key Vault

Azure Key Vault is a powerful service for securely managing keys, secrets, and certificates. However, you might occasionally encounter errors while performing operations, such as creating a key. One common issue is the error message: “The operation is not allowed by RBAC. If role assignments were recently changed, please wait several minutes for role assignments to become effective.”

Error information

CODE
Forbidden

MESSAGE
The operation is not allowed by RBAC. If role assignments were recently changed, please wait several minutes for role assignments to become effective.

RAW ERROR
Caller is not authorized to perform action on resource. If role assignments, deny assignments or role definitions were changed recently, please observe propagation time. Caller: appid=3686488a-04fc-4d8a-b967-61f98ec41efe;oid=59347bed-6be5-4c44-be30-7cf210e473f7;iss=https://sts.windows.net/16b3c013-d300-468d-ac64-7eda0820b6d3/ Action: ‘Microsoft.KeyVault/vaults/keys/create/action’ Resource: ‘/subscriptions/ea72f050-0699-4b00-a43c-aba6cd2743df/resourcegroups/jbmysql/providers/microsoft.keyvault/vaults/jbmysqlkeyvault/keys/jbmysqlkey’ Assignment: (not found) DenyAssignmentId: null DecisionReason: null Vault: jbmysqlkeyvault;location=eastus

This blog will walk you through understanding this error and provide a step-by-step guide to resolve it.

Understanding the Error

The error message indicates that the operation you’re trying to perform (in this case, creating a key) is not permitted due to Role-Based Access Control (RBAC) settings. This issue typically arises because of one or more of the following reasons:

  • Insufficient Permissions: The user or service principal doesn’t have the required permissions to perform the operation.
  • Recent Role Assignments: Recent changes to role assignments might not have been propagated yet.
  • Incorrect Role or Scope: The assigned role might not have the necessary permissions, or it might be scoped incorrectly.

Scenario Demonstration

To illustrate the issue, let’s attempt to create a key in Azure Key Vault and reproduce the error:

Open Azure CLI or PowerShell.

Run the following command to create a key in your Key Vault:

    az keyvault key create --vault-name <YourKeyVaultName> --name <YourKeyName> --protection software

    Observe the Error Message:

    The operation is not allowed by RBAC. If role assignments were recently changed, please wait several minutes for role assignments to become effective.

    Steps to Resolve the Error

    1. Verify Role Assignments

    Objective: Ensure that the correct roles are assigned to the user or service principal.

    Azure Portal:

    1. Navigate to the Azure Portal.
    2. Go to your Key Vault.
    3. Select Access control (IAM).
    4. Review the role assignments to ensure that the user or service principal has the Key Vault Contributor or Key Vault Administrator role.

    Azure CLI:

    az role assignment list --scope /subscriptions/<subscription-id>/resourceGroups/<resource-group>/providers/Microsoft.KeyVault/vaults/<key-vault-name> --output table

    Azure PowerShell:

    Get-AzRoleAssignment -Scope /subscriptions/<subscription-id>/resourceGroups/<resource-group>/providers/Microsoft.KeyVault/vaults/<key-vault-name>

    2. Update Role Assignments

    Objective: Add or update the necessary role assignments.

    Azure Portal:

    1. Go to your Key Vault in the Azure Portal.
    2. Navigate to Access control (IAM).
    3. Click Add role assignment.
    4. Assign the Key Vault Contributor role to the user or service principal.

    Azure CLI:

    az role assignment create --role "Key Vault Contributor" --assignee <UserOrServicePrincipal> --scope /subscriptions/<subscription-id>/resourceGroups/<resource-group>/providers/Microsoft.KeyVault/vaults/<key-vault-name>

    Azure PowerShell:

    New-AzRoleAssignment -RoleDefinitionName "Key Vault Contributor" -ServicePrincipalName <UserOrServicePrincipal> -Scope /subscriptions/<subscription-id>/resourceGroups/<resource-group>/providers/Microsoft.KeyVault/vaults/<key-vault-name>

    3. Wait for Propagation

    Objective: Allow time for role assignment changes to propagate.

    • Wait Time: Changes in role assignments can take a few minutes to become effective. Be patient and wait for a few minutes before retrying the key creation operation.

    4. Retry Key Creation

    Objective: Attempt to create the key again after ensuring correct role assignments.

    • Azure CLI:
    az keyvault key create --vault-name <YourKeyVaultName> --name <YourKeyName> --protection software

    Additional Troubleshooting Tips

    • Check Subscription or Resource Group Issues: Ensure there are no broader issues with your subscription or resource group that might affect permissions.
    • Consult Azure Documentation: Refer to Azure’s official documentation for more detailed information on RBAC and Key Vault operations.
    • Contact Azure Support: If the issue persists, consider reaching out to Azure Support for further assistance.

    Business Use Case

    Consider a scenario where your company needs to manage sensitive keys for encryption and decryption operations. You recently migrated your key management to Azure Key Vault and assigned roles to various team members. After a role assignment change, you encounter the RBAC error while trying to create new keys.

    By following the steps outlined above, you ensure that all team members have the necessary permissions and can manage keys without interruptions. Properly handling RBAC settings ensures secure and efficient key management, crucial for maintaining the integrity of your company’s encryption practices.

    Conclusion

    Encountering RBAC errors when creating keys in Azure Key Vault can be frustrating, but understanding the root cause and following the resolution steps can help you overcome these issues. By verifying and updating role assignments, waiting for propagation, and retrying the operation, you can ensure smooth key management in Azure Key Vault.

    If you have any questions or need further assistance, feel free to leave a comment below or check out additional resources on Azure Key Vault and RBAC.

    For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.

    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.