Azure SQL databases created in SSMS not complying with policy

Question

In Azure, we have an issue with databases created in SSMS (right-click, new database) being deployed as General Purpose 5 with 2 vCores. This is the default configuration for databases: https://azure.microsoft.com/en-au/updates/azure-sql-database-default-configuration-changing/.

This is, of course, needlessly expensive.

We created and assigned the following Azure Policy. Only Basic, Standard, and ElasticPool allowed. This denied deployments from the portal that didn't comply.

However, creating databases from SSMS is still allowed and they are deployed as Gen 5 with 2 vCores.

We have the same issue when running migrations on databases that don't exist. The databases are also created as Gen 5 with 2 vCores.

Is there a way to force all database creation to confirm to the policy? Alternatively, is there a way to change the default database sku?

Otherwise, it looks like we need to solve this via account permissions (accounts cannot deploy databases).

"parameters": {
  "allowedSkuNames": {
    "type": "Array",
    "metadata": {
      "displayName": "Allowed SKU Names",
      "description": "List of allowed SKU names for SQL databases."
    },
    "allowedValues": [
      "Basic",
      "Standard",
      "Premium",
      "ElasticPool",
      "GP_Gen5_2",
      "BC_Gen5_2"
    ]
  }
},
"policyRule": {
  "if": {
    "allOf": [
      {
        "field": "type",
        "equals": "Microsoft.Sql/servers/databases"
      },
      {
        "field": "Microsoft.Sql/servers/databases/sku.name",
        "notIn": "[parameters('allowedSkuNames')]"
      }
    ]
  },
  "then": {
    "effect": "deny"
  }
}

Answer 1

I have tried creating the policy in Azure by restricting the SQL database creation only to Basic tier. As you can see in the below screenshot, I tried with BC_Gen5_2vcores but received the Custom Policy error:

Policy Code:

"parameters": {
"allowedSkuNames": {
"type": "Array",
"metadata": {
"displayName": "Allowed SKU",
"description": "The list of allowed SKU for resources."
},
"allowedValues": [
"Basic",
"Standard",
"Premium",
"ElasticPool"
]
}
},
"policyRule": {
"if": {
"allOf": [
{
"field": "type",
"equals": "Microsoft.Sql/servers/databases"
},
{
"field": "Microsoft.Sql/servers/databases/sku.name",
"notIn": "[parameters('allowedSkuNames')]"
}
]
},
"then": {
"effect": "deny"
}
}
}

Even you write the defaultValue code in the above policy, it will adhere to/within the Azure environment not to the On-premises Systems.

Applying Azure Policy directly to a local system outside of Azure is not possible, as Azure Policy is designed to work within the Azure cloud environment.

• If you need specific policies for enforcing the local system, you need to choose:
• Group Policy or PowerShell Desired State Configuration for Windows
• Shell Script or Configuration Management tools like Ansible, Puppet for Linux Systems.
• If your local systems are running on Virtual Machines, you can utilize the Azure Policy Guest Configuration extensions to non-Azure VMs.

Updated Alternative Solution:

Yes, however you create the database using Portal or CLI or PowerShell or SSMS (Local) in the Serverless compute tier. By default, it shows the Gen 5 with 2 vcores is configured in the Azure as given in this MS Doc.

Alternatively, is there a way to change the default database sku?

You can change the default database SKU from the local SSMS tool.

1. Login to your SQL Server in SSMS and Right Click on the databases to select New Database.
2. Then, navigate to the “Configure SLO” option in the left index as you can see the current SLO is “GP_Gen_5” so that the databases are creating by default in Gen5 Versions.

3. To edit that SKU, login to your Azure account from SSMS as shown in above Screenshot. After login, you get the option to change the database SKU
   

Even you can select the Backup Storage Redundancy, Current SLO, Edition which is equal to Configuration of database from the Azure portal.

Here i have selected the Basic SKU for the database and creating from the SSMS:

If you see in the Azure Portal after creation of data from the SSMS, you can see the Basic SKU tier of the database created from the SSMS:

Also, proof from the SSMS > Databases > Generate “Create To” script: