​
More Info:

Enable automatic tuning for Microsoft Azure SQL servers in order to monitor database queries and improve database workload performance. Automatic Tuning is a built-in intelligence feature that automatically tunes your Azure SQL databases to optimize their performance.

​
Risk Level

Medium

​
Address

Security

​
Compliance Standards

HITRUST, SOC2

​
Triage and Remediation

​
Remediation

Using Console

To remediate the misconfiguration “Enable Automatic Tuning for SQL Database Servers” in Azure using the Azure console, follow these steps:
  1. Log in to the Azure portal (https://portal.azure.com/).
  2. Navigate to the SQL databases page.
  3. Select the SQL database server that needs to be remediated.
  4. Click on the “Automatic tuning” option in the left-hand menu.
  5. Click on the “Configure” button to enable automatic tuning for the server.
  6. In the “Automatic tuning” pane, select the “Basic” option to enable automatic tuning for all databases on the server.
  7. Click on the “Apply” button to save the changes.
Once you have completed these steps, automatic tuning will be enabled for the selected SQL database server, which will help optimize query performance and improve overall database performance.

​

To remediate the misconfiguration “Enable Automatic Tuning For SQL Database Servers” for AZURE using AZURE CLI, you can follow the below steps:Step 1: Open the Azure CLI in your system or use the Azure Cloud Shell.Step 2: Run the following command to enable automatic tuning for a specific database server:
az sql server configuration set --name automaticTuning --resource-group <resource_group_name> --server <server_name> --value Enabled
Note: Replace <resource_group_name> with the name of the resource group where your database server is located and <server_name> with the name of your database server.Step 3: If you want to enable automatic tuning for all the database servers in your subscription, run the following command:
az sql server configuration set --name automaticTuning --value Enabled --subscription <subscription_id>
Note: Replace <subscription_id> with the ID of your subscription.Step 4: After running the above command, automatic tuning will be enabled for your SQL database servers, and Azure will automatically tune your database to improve its performance.That’s it! You have successfully remediated the misconfiguration “Enable Automatic Tuning For SQL Database Servers” for AZURE using AZURE CLI.
To remediate the misconfiguration of enabling automatic tuning for SQL database servers in Azure using Python, you can follow the below steps:
  1. Install the Azure SDK for Python using the following command: 
    pip install azure-mgmt-sql
  2. Authenticate and create a client object to interact with the Azure SQL Database using the following code: 
    from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.sql import SqlManagementClient

subscription_id = 'your_subscription_id'
credentials = ServicePrincipalCredentials(
    client_id='your_client_id',
    secret='your_client_secret',
    tenant='your_tenant_id'
)

sql_client = SqlManagementClient(credentials, subscription_id)
  3. Get the resource group and server name where the SQL database is located using the following code: 
    resource_group_name = 'your_resource_group_name'
server_name = 'your_server_name'
  4. Enable automatic tuning for the SQL database server using the following code: 
    from azure.mgmt.sql.models import ServerAutomaticTuningProperties

sql_client.servers.create_or_update(
    resource_group_name=resource_group_name,
    server_name=server_name,
    parameters={
        'properties': {
            'automatic_tuning': {
                'state': 'Enabled',
                'options': {
                    'createIndex': 'Auto',
                    'dropIndex': 'Auto',
                    'forceLastGoodPlan': 'Enabled'
                }
            }
        }
    }
)
    In the above code, we are setting the state of automatic tuning to ‘Enabled’ and also setting the options for createIndex, dropIndex, and forceLastGoodPlan to ‘Auto’, ‘Auto’, and ‘Enabled’ respectively.
  5. Verify the automatic tuning is enabled for the SQL database server using the following code: 
    server = sql_client.servers.get(resource_group_name, server_name)
print(server.properties.automatic_tuning.state)
    This will print the current state of automatic tuning for the SQL database server.
By following the above steps, you can remediate the misconfiguration of enabling automatic tuning for SQL database servers in Azure using Python.