Skip to content

Instantly share code, notes, and snippets.

@jikuja

jikuja/00-generic.md

Last active September 27, 2024 07:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jikuja/9e3f1589ff51c1961ac858f3c51ce88a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jikuja/9e3f1589ff51c1961ac858f3c51ce88a to your computer and use it in GitHub Desktop.
Download ZIP
Azure SQL notes
Raw
00-generic.md

Generic notes

Visibility limitations on resource changes

Change Analysis

Change analysis captures only server and database creations.

Missing:

  • Diagnostics settings
  • Auditing settings
  • etc

ARG

Azure resource Graph lists only server and database resources.

Missing:

  • Diagnostics settings
  • Auditing settings
  • etc
Raw
01-generic-server.md

Generic notes

master database resource

  • Created automatically when server resource is created. See 99-master.json

At some point (after database creation) currentServiceObjectiveName and requestedServiceObjectiveName are changed to System0

Portal Logical Server creation

This standalone logical server creation Wizard is basically useless because database Wizard will overwrite multiple features and provides more features present only on server-level.

Server Portal UX features

  • Server resource
    • version
    • minimalTlsVersion
    • publicNetworkAccess
    • administrators
    • primaryUserAssignedIdentityId
    • federatedClientId
    • servicePrincipal | Undocumented | Not available on UX
  • Server resource/resources array (child resources)
    • firewallRules | condition: allowAzureIPs
      • Conditionally AllowAllWindowsAzureIps
    • advancedThreatProtectionSettings | condition: enableADS | UX: Enable Microsoft Defender for SQL: Start free trial
    • vulnerabilityAssessments | condition: enableVA | Not toggleable on Portal?
    • sqlVulnerabilityAssessments | condition: vaStoragelessEnabled | UX: Enable Microsoft Defender for SQL: Start free trial
    • keyID: CMK TDE key vault reference | No condition. Present on templated only when configured
  • Extra resources
    • Storage account for VA | condition: enableVA | Not toggleable on Portal?
      • RBAC for server MSI | condition: useVAManagedIdentity | Not toggleable on Portal?

VA

https://learn.microsoft.com/en-us/azure/defender-for-cloud/sql-azure-vulnerability-assessment-overview

  • vaStoragelessEnabled: Express
  • enableVA: Classic
Raw
02-generic-database.md

Generic notes

Portal Database creation

Deployments for unknown scope:

  • sql ledger remote template | condition: enableDigestStorage
    • database or server?
    • sqlLedgerTemplateLink

Deployment for given private endpoint resource group (subnet's Rg?)

  • private endpoint | condition: enablePrivateEndpoint for selected subnet do:
    • set address prefix
    • set privateLinkServiceNetworkPolicies to Enabled
  • call external template to create private endpoint
    • privateEndpointTemplateLink

Deployment for given private link private DNS zone RG

  • Create Microsoft.Network/privateDnsZones
  • Create Microsoft.Network/privateDnsZones/virtualNetworkLinks
  • Call template to "EndpointDnsRecords-parameters('privateEndpointDnsRecordUniqueId')"
    • privateDnsForPrivateEndpointTemplateLink

Server-level injections

  • Turns on System-assigned MSI if enableVA and useVAManagedIdentity
  • VA storage account creation if enableVA
    • Server MSI RBAC if useVAManagedIdentity
  • advancedThreatProtectionSettings if enableADS
  • vulnerabilityAssessments if enableVA
  • sqlVulnerabilityAssessments if vaStoragelessEnabled

Database

Properties:

  • "collation": "[parameters('collation')]",
  • "maxSizeBytes": "[parameters('maxSizeBytes')]",
  • "sampleName": "[parameters('sampleName')]",
  • "zoneRedundant": "[parameters('zoneRedundant')]",
  • "licenseType": "[parameters('licenseType')]",
  • "readScale": "[parameters('readScaleOut')]",
  • "highAvailabilityReplicaCount": "[parameters('numberOfReplicas')]",
  • "minCapacity": "[parameters('minCapacity')]",
  • "requestedBackupStorageRedundancy": "[parameters('requestedBackupStorageRedundancy')]",
  • "isLedgerOn": "[parameters('enableSqlLedger')]",
  • "availabilityZone": "[parameters('availabilityZone')]",
  • "useFreeLimit": "[parameters('useFreeLimit')]",
  • "freeLimitExhaustionBehavior": "[parameters('freeLimitExhaustionBehavior')]",
  • "maintenanceConfigurationId": "[parameters('maintenanceConfigurationId')]"
  • "federatedClientId": "[parameters('databaseFederatedClientId')]",
  • "encryptionProtector": "[parameters('databaseEncryptionProtector')]",
  • "encryptionProtectorAutoRotation": "[parameters('databaseEncryptionProtectorAutoRotation')]",
  • "preferredEnclaveType": "[parameters('databasePreferredEnclaveType')]"

SKU:

  • name
  • tier
Raw
04-Logging.md

Logging

Default

Default Portal creation UX does not provide any means to enable logging. Even templates powering deployment does not cover resource creartion at all

Diagnostics log categories

SQLSecurityAuditEvents and DevOpsOperationsAudit are special categories and Portal will create own diagnostics setting configs for those when auditing is enabled.

Diagnostics setting is created with name SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1 or SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1_1 or SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1_11

Server

Portal does not reveal server diagnostics settings logging at all

Category
AllMetrics

Master database

Master database diagnostics config is writeable through Azure Monitor but not directly from resource UX.

Category
SQLInsights
AutomaticTuning
QueryStoreRuntimeStatistics
QueryStoreWaitStatistics
Errors
DatabaseWaitStatistics
Timeouts
Blocks
Deadlocks
DevOpsOperationsAudit
SQLSecurityAuditEvents
Basic
InstanceAndAppAdvanced
WorkloadManagement

DTU based database

Database-level diagnostics loggins setting are available on resource UX and on Azure Monitor

Category
SQLInsights
AutomaticTuning
QueryStoreRuntimeStatistics
QueryStoreWaitStatistics
Errors
DatabaseWaitStatistics
Timeouts
Blocks
Deadlocks
DevOpsOperationsAudit
SQLSecurityAuditEvents
Basic
InstanceAndAppAdvanced
WorkloadManagement

SQL DW

TBD

Auditing

Auditing on server

Log analytics workspace, no MS Devops audit

  • Portal installs SQLAuditing solution on LAW
  • Portal creates Microsoft.Sql/servers/auditingSettings resource
    • ATTN: Powershell tooling uses extendedAuditingSettings resource type
  • Portal creates microsoft.insights/diagnosticSettings resource
Portal creates Microsoft.Sql/servers/auditingSettings resource:
{
  "properties": {
    "state": "Enabled",
    "storageEndpoint": "",
    "storageAccountAccessKey": "",
    "auditActionsAndGroups": [
      "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
      "FAILED_DATABASE_AUTHENTICATION_GROUP",
      "BATCH_COMPLETED_GROUP"
    ],
    "retentionDays": 0,
    "storageAccountSubscriptionId": null,
    "isStorageSecondaryKeyInUse": false,
    "isAzureMonitorTargetEnabled": true
  }
}
Portal also creates microsoft.insights/diagnosticSettings on master database:
{
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName//providers/Microsoft.Sql/servers/server-server/databases/master/providers/microsoft.insights/diagnosticSettings/SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1",
  "name": "SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1",
  "properties": {
    "logs": [
      {
        "category": "SQLSecurityAuditEvents",
        "enabled": true,
        "retentionPolicy": {
          "days": 0,
          "enabled": false
        }
      }
    ],
    "metrics": [],
    "workspaceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourcegroups/RgName/providers/microsoft.operationalinsights/workspaces/law-law"
  }
}

Devops

auditingSettings default
{
  "properties": {
    "state": "Enabled",
    "storageEndpoint": "",
    "storageAccountAccessKey": "",
    "storageAccountSubscriptionId": null,
    "isStorageSecondaryKeyInUse": false,
    "isAzureMonitorTargetEnabled": true
  }
}
Microsoft.Sql/servers/devOpsAuditingSettings/ default
{
  "properties": {
    "state": "Enabled",
    "storageEndpoint": "",
    "storageAccountAccessKey": "",
    "storageAccountSubscriptionId": null,
    "isStorageSecondaryKeyInUse": false,
    "isAzureMonitorTargetEnabled": true
  }
}
SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1:
{
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName/providers/microsoft.insights/diagnosticSettings/SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1",
  "name": "SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1",
  "properties": {
    "logs": [
      {
        "category": "SQLSecurityAuditEvents",
        "enabled": true,
        "retentionPolicy": {
          "days": 0,
          "enabled": false
        }
      }
    ],
    "metrics": [],
    "workspaceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourcegroups/RgName/providers/microsoft.operationalinsights/workspaces/law-law"
  }
}
Create new diagnostics setting for devops category
{
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName/providers/microsoft.insights/diagnosticSettings/SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1_0",
  "name": "SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1_0",
  "properties": {
    "logs": [
      {
        "category": "DevOpsOperationsAudit",
        "enabled": true,
        "retentionPolicy": {
          "days": 0,
          "enabled": false
        }
      }
    ],
    "metrics": [],
    "workspaceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourcegroups/RgName/providers/microsoft.operationalinsights/workspaces/law-law"
  }
}

Auditing on database

Microsoft recommends to turn on auditing on server level only.

Raw
91-server.json
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"administratorLogin": {
"type": "string",
"defaultValue": ""
},
"administratorLoginPassword": {
"type": "securestring",
"defaultValue": ""
},
"administrators": {
"type": "object",
"defaultValue": {}
},
"location": {
"type": "string"
},
"serverName": {
"type": "string"
},
"enableADS": {
"type": "bool",
"defaultValue": false
},
"useVAManagedIdentity": {
"type": "bool",
"defaultValue": false,
"metadata": {
"description": "To enable vulnerability assessments, the user deploying this template must have an administrator or owner permissions."
}
},
"vaStoragelessEnabled": {
"type": "bool",
"defaultValue": false,
"metadata": {
"description": "Flag for enabling vulnerability assessments with express configuration (storage less), the user deploying this template must have administrator or owner permissions."
}
},
"publicNetworkAccess": {
"type": "string",
"defaultValue": ""
},
"minimalTlsVersion": {
"type": "string",
"defaultValue": ""
},
"allowAzureIps": {
"type": "bool",
"defaultValue": true
},
"enableVA": {
"type": "bool",
"defaultValue": false
},
"serverTags": {
"type": "object",
"defaultValue": {}
},
"identity": {
"type": "object",
"defaultValue": {}
},
"primaryUserAssignedIdentityId": {
"type": "string",
"defaultValue": ""
},
"federatedClientId": {
"type": "string",
"defaultValue": ""
},
"servicePrincipal": {
"type": "object",
"defaultValue": {}
},
"keyId": {
"type": "string",
"defaultValue": ""
}
},
"variables": {
"subscriptionId": "[subscription().subscriptionId]",
"resourceGroupName": "[resourceGroup().name]",
"uniqueStorage": "[uniqueString(variables('subscriptionId'), variables('resourceGroupName'), parameters('location'))]",
"storageName": "[tolower(concat('sqlva', variables('uniqueStorage')))]",
"uniqueRoleGuid": "[guid(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), variables('storageBlobContributor'), resourceId('Microsoft.Sql/servers', parameters('serverName')))]",
"StorageBlobContributor": "[subscriptionResourceId('Microsoft.Authorization/roleDefinitions', 'ba92f5b4-2d11-453d-a403-e96b0029c9fe')]"
},
"resources": [
{
"condition": "[parameters('enableVA')]",
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "2019-04-01",
"name": "[variables('storageName')]",
"location": "[parameters('location')]",
"sku": {
"name": "Standard_LRS"
},
"kind": "StorageV2",
"properties": {
"minimumTlsVersion": "TLS1_2",
"supportsHttpsTrafficOnly": "true",
"allowBlobPublicAccess": "false"
},
"resources": [
{
"condition": "[parameters('useVAManagedIdentity')]",
"type": "Microsoft.Storage/storageAccounts/providers/roleAssignments",
"apiVersion": "2018-09-01-preview",
"name": "[concat(variables('storageName'), '/Microsoft.Authorization/', variables('uniqueRoleGuid') )]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
"[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]"
],
"properties": {
"roleDefinitionId": "[variables('StorageBlobContributor')]",
"principalId": "[reference(resourceId('Microsoft.Sql/servers', parameters('serverName')), '2018-06-01-preview', 'Full').identity.principalId]",
"scope": "[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]",
"principalType": "ServicePrincipal"
}
}
]
},
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2021-05-01-preview",
"name": "[parameters('serverName')]",
"location": "[parameters('location')]",
"properties": {
"version": "12.0",
"minimalTlsVersion": "[parameters('minimalTlsVersion')]",
"publicNetworkAccess": "[parameters('publicNetworkAccess')]",
"administrators": "[parameters('administrators')]",
"primaryUserAssignedIdentityId": "[parameters('primaryUserAssignedIdentityId')]",
"federatedClientId": "[parameters('federatedClientId')]",
"servicePrincipal": "[parameters('servicePrincipal')]",
"keyId": "[parameters('keyId')]"
},
"identity": "[parameters('identity')]",
"tags": "[parameters('serverTags')]",
"resources": [
{
"condition": "[parameters('allowAzureIPs')]",
"type": "firewallRules",
"apiVersion": "2021-11-01",
"name": "AllowAllWindowsAzureIps",
"location": "[parameters('location')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"properties": {
"endIpAddress": "0.0.0.0",
"startIpAddress": "0.0.0.0"
}
},
{
"condition": "[parameters('enableADS')]",
"type": "advancedThreatProtectionSettings",
"apiVersion": "2021-11-01-preview",
"name": "Default",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"properties": {
"state": "Enabled"
}
},
{
"condition": "[parameters('enableVA')]",
"type": "vulnerabilityAssessments",
"apiVersion": "2018-06-01-preview",
"name": "Default",
"dependsOn": [
"[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
"[concat('Microsoft.Storage/storageAccounts/', variables('storageName'))]",
"[concat('Microsoft.Sql/servers/', parameters('serverName'), '/advancedThreatProtectionSettings/Default')]"
],
"properties": {
"storageContainerPath": "[if(parameters('enableVA'), concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))).primaryEndpoints.blob, 'vulnerability-assessment'), '')]",
"storageAccountAccessKey": "[if(and(parameters('enableVA'),not(parameters('useVAManagedIdentity'))), listKeys(variables('storageName'), '2018-02-01').keys[0].value, '')]",
"recurringScans": {
"isEnabled": true,
"emailSubscriptionAdmins": false
}
}
},
{
"condition": "[parameters('vaStoragelessEnabled')]",
"type": "sqlVulnerabilityAssessments",
"apiVersion": "2022-02-01-preview",
"name": "Default",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"properties": {
"state": "Enabled"
}
}
]
}
]
}
Raw
92-server-status.json
// Brand new server resource after creation
{
"content": {
"id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName/providers/Microsoft.Sql/servers/server-server",
"name": "server-server",
"type": "microsoft.sql/servers",
"location": "eastus",
"tags": {},
"properties": {
"administratorLogin": "CloudSA934e",
"administrators": {
"administratorType": "ActiveDirectory",
"azureADOnlyAuthentication": true,
"login": "foo@bar.example.com",
"principalType": "User",
"sid": "00000000-0000-0000-0000-000000000000",
"tenantId": "00000000-0000-0000-0000-000000000000"
},
"externalGovernanceStatus": "Disabled",
"fullyQualifiedDomainName": "server-server.database.windows.net",
"minimalTlsVersion": "1.2",
"privateEndpointConnections": [],
"publicNetworkAccess": "Enabled",
"restrictOutboundNetworkAccess": "Disabled",
"state": "Ready",
"version": "12.0"
},
"kind": "v12.0",
"identity": {
"principalId": "00000000-0000-0000-0000-000000000000",
"type": "SystemAssigned",
"tenantId": "00000000-0000-0000-0000-000000000000"
}
},
"apiVersion": "2024-05-01-preview"
}
Raw
93-database.json
// sqlLedgerTemplateLink: https://sqlazureextension.hosting.portal.azure.net/sqlazureextension/Content/2.1.02819228/DeploymentTemplates/SqlLedger.json
// privateEndpointTemplateLink: https://sqlazureextension.hosting.portal.azure.net/sqlazureextension/Content/2.1.02819228/DeploymentTemplates/PrivateEndpoint.json
// privateDnsForPrivateEndpointTemplateLink: https://sqlazureextension.hosting.portal.azure.net/sqlazureextension/Content/2.1.02819228/DeploymentTemplates/PrivateDnsForPrivateEndpoint.json
// Following are in parameters but not used on templating
// privateDnsForPrivateEndpointNicTemplateLink: https://sqlazureextension.hosting.portal.azure.net/sqlazureextension/Content/2.1.02819228/DeploymentTemplates/PrivateDnsForPrivateEndpointNic.json
// privateDnsForPrivateEndpointIpConfigTemplateLink: https://sqlazureextension.hosting.portal.azure.net/sqlazureextension/Content/2.1.02819228/DeploymentTemplates/PrivateDnsForPrivateEndpointIpConfig.json
//
{
"$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"collation": {
"type": "string"
},
"databaseName": {
"type": "string"
},
"tier": {
"type": "string"
},
"skuName": {
"type": "string"
},
"maxSizeBytes": {
"type": "int"
},
"serverName": {
"type": "string"
},
"serverLocation": {
"type": "string"
},
"sampleName": {
"type": "string",
"defaultValue": ""
},
"zoneRedundant": {
"type": "bool",
"defaultValue": false
},
"licenseType": {
"type": "string",
"defaultValue": ""
},
"readScaleOut": {
"type": "string",
"defaultValue": "Disabled"
},
"numberOfReplicas": {
"type": "int",
"defaultValue": 0
},
"minCapacity": {
"type": "string",
"defaultValue": ""
},
"autoPauseDelay": {
"type": "int",
"defaultValue": 0
},
"databaseTags": {
"type": "object",
"defaultValue": {}
},
"enableADS": {
"type": "bool",
"defaultValue": false
},
"enableVA": {
"type": "bool",
"defaultValue": false
},
"useVAManagedIdentity": {
"type": "bool",
"defaultValue": false,
"metadata": {
"description": "To enable vulnerability assessments, the user deploying this template must have an administrator or owner permissions."
}
},
"vaStoragelessEnabled": {
"type": "bool",
"defaultValue": false,
"metadata": {
"description": "Flag for enabling vulnerability assessments with express configuration (storage less), the user deploying this template must have administrator or owner permissions."
}
},
"enablePrivateEndpoint": {
"type": "bool",
"defaultValue": false
},
"privateEndpointNestedTemplateId": {
"type": "string",
"defaultValue": ""
},
"privateEndpointSubscriptionId": {
"type": "string",
"defaultValue": ""
},
"privateEndpointResourceGroup": {
"type": "string",
"defaultValue": ""
},
"privateEndpointName": {
"type": "string",
"defaultValue": ""
},
"privateEndpointLocation": {
"type": "string",
"defaultValue": ""
},
"privateEndpointSubnetId": {
"type": "string",
"defaultValue": ""
},
"privateLinkServiceName": {
"type": "string",
"defaultValue": ""
},
"privateLinkServiceServiceId": {
"type": "string",
"defaultValue": ""
},
"privateEndpointVnetSubscriptionId": {
"type": "string",
"defaultValue": ""
},
"privateEndpointVnetResourceGroup": {
"type": "string",
"defaultValue": ""
},
"privateEndpointVnetName": {
"type": "string",
"defaultValue": ""
},
"privateEndpointSubnetName": {
"type": "string",
"defaultValue": ""
},
"enablePrivateDnsZone": {
"type": "bool",
"defaultValue": false
},
"privateLinkPrivateDnsZoneFQDN": {
"type": "string",
"defaultValue": ""
},
"privateLinkPrivateDnsZoneRG": {
"type": "string",
"defaultValue": ""
},
"privateEndpointDnsRecordUniqueId": {
"type": "string",
"defaultValue": ""
},
"privateEndpointTemplateLink": {
"type": "string",
"defaultValue": ""
},
"privateDnsForPrivateEndpointTemplateLink": {
"type": "string",
"defaultValue": ""
},
"privateDnsForPrivateEndpointNicTemplateLink": {
"type": "string",
"defaultValue": ""
},
"privateDnsForPrivateEndpointIpConfigTemplateLink": {
"type": "string",
"defaultValue": ""
},
"requestedBackupStorageRedundancy": {
"type": "string",
"defaultValue": ""
},
"maintenanceConfigurationId": {
"type": "string",
"defaultValue": ""
},
"enableSqlLedger": {
"type": "bool",
"defaultValue": false
},
"enableDigestStorage": {
"type": "string",
"defaultValue": ""
},
"digestStorageOption": {
"type": "string",
"defaultValue": ""
},
"digestStorageName": {
"type": "string",
"defaultValue": ""
},
"blobStorageContainerName": {
"type": "string",
"defaultValue": ""
},
"retentionDays": {
"type": "string",
"defaultValue": ""
},
"retentionPolicy": {
"type": "bool",
"defaultValue": true
},
"isPermissionAssigned": {
"type": "bool",
"defaultValue": false
},
"digestAccountResourceGroup": {
"type": "string",
"defaultValue": ""
},
"digestRegion": {
"type": "string",
"defaultValue": ""
},
"storageAccountdigestRegion": {
"type": "string",
"defaultValue": ""
},
"isNewDigestLocation": {
"type": "bool",
"defaultValue": false
},
"sqlLedgerTemplateLink": {
"type": "string",
"defaultValue": ""
},
"availabilityZone": {
"type": "string",
"defaultValue": "NoPreference"
},
"useFreeLimit": {
"type": "bool",
"defaultValue": false
},
"freeLimitExhaustionBehavior": {
"type": "string",
"defaultValue": ""
},
"databaseIdentity": {
"type": "object",
"defaultValue": {}
},
"databaseFederatedClientId": {
"type": "string",
"defaultValue": ""
},
"databaseEncryptionProtector": {
"type": "string",
"defaultValue": ""
},
"databaseEncryptionProtectorAutoRotation": {
"type": "bool",
"defaultValue": false
},
"databasePreferredEnclaveType": {
"type": "string",
"defaultValue": ""
}
},
"resources": [
{
"condition": "[and(parameters('enableVA'), parameters('useVAManagedIdentity'))]",
"type": "Microsoft.Sql/servers",
"apiVersion": "2019-06-01-preview",
"name": "[parameters('serverName')]",
"identity": {
"type": "SystemAssigned"
}
},
{
"condition": "[parameters('enableVA')]",
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "2019-04-01",
"name": "[variables('storageName')]",
"location": "[parameters('serverLocation')]",
"sku": {
"name": "Standard_LRS"
},
"kind": "StorageV2",
"properties": {
"minimumTlsVersion": "TLS1_2",
"supportsHttpsTrafficOnly": "true",
"allowBlobPublicAccess": "false"
},
"resources": [
{
"condition": "[parameters('useVAManagedIdentity')]",
"type": "Microsoft.Storage/storageAccounts/providers/roleAssignments",
"apiVersion": "2018-09-01-preview",
"name": "[concat(variables('storageName'), '/Microsoft.Authorization/', variables('uniqueRoleGuid') )]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
"[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]"
],
"properties": {
"roleDefinitionId": "[variables('StorageBlobContributor')]",
"principalId": "[reference(resourceId('Microsoft.Sql/servers', parameters('serverName')), '2018-06-01-preview', 'Full').identity.principalId]",
"scope": "[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]",
"principalType": "ServicePrincipal"
}
}
]
},
{
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2022-08-01-preview",
"location": "[parameters('serverLocation')]",
"tags": "[parameters('databaseTags')]",
"name": "[concat(parameters('serverName'), '/', parameters('databaseName'))]",
"properties": {
"collation": "[parameters('collation')]",
"maxSizeBytes": "[parameters('maxSizeBytes')]",
"sampleName": "[parameters('sampleName')]",
"zoneRedundant": "[parameters('zoneRedundant')]",
"licenseType": "[parameters('licenseType')]",
"readScale": "[parameters('readScaleOut')]",
"highAvailabilityReplicaCount": "[parameters('numberOfReplicas')]",
"minCapacity": "[parameters('minCapacity')]",
"autoPauseDelay": "[parameters('autoPauseDelay')]",
"requestedBackupStorageRedundancy": "[parameters('requestedBackupStorageRedundancy')]",
"isLedgerOn": "[parameters('enableSqlLedger')]",
"availabilityZone": "[parameters('availabilityZone')]",
"useFreeLimit": "[parameters('useFreeLimit')]",
"freeLimitExhaustionBehavior": "[parameters('freeLimitExhaustionBehavior')]",
"maintenanceConfigurationId": "[parameters('maintenanceConfigurationId')]",
"federatedClientId": "[parameters('databaseFederatedClientId')]",
"encryptionProtector": "[parameters('databaseEncryptionProtector')]",
"encryptionProtectorAutoRotation": "[parameters('databaseEncryptionProtectorAutoRotation')]",
"preferredEnclaveType": "[parameters('databasePreferredEnclaveType')]"
},
"sku": {
"name": "[parameters('skuName')]",
"tier": "[parameters('tier')]"
},
"identity": "[parameters('databaseIdentity')]"
},
{
"condition": "[parameters('enableADS')]",
"apiVersion": "2021-11-01-preview",
"type": "Microsoft.Sql/servers/advancedThreatProtectionSettings",
"name": "[concat(parameters('serverName'), '/Default')]",
"dependsOn": [
"[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/', parameters('databaseName'))]"
],
"properties": {
"state": "Enabled"
}
},
{
"condition": "[parameters('enableVA')]",
"apiVersion": "2018-06-01-preview",
"type": "Microsoft.Sql/servers/vulnerabilityAssessments",
"name": "[concat(parameters('serverName'), '/Default')]",
"properties": {
"storageContainerPath": "[if(parameters('enableVA'), concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))).primaryEndpoints.blob, 'vulnerability-assessment'), '')]",
"storageAccountAccessKey": "[if(and(parameters('enableVA'),not(parameters('useVAManagedIdentity'))), listKeys(variables('storageName'), '2018-02-01').keys[0].value, '')]",
"recurringScans": {
"isEnabled": true,
"emailSubscriptionAdmins": false,
"emails": []
}
},
"dependsOn": [
"[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/', parameters('databaseName'))]",
"[concat('Microsoft.Storage/storageAccounts/', variables('storageName'))]",
"[concat('Microsoft.Sql/servers/', parameters('serverName'), '/advancedThreatProtectionSettings/Default')]"
]
},
{
"condition": "[parameters('vaStoragelessEnabled')]",
"type": "Microsoft.Sql/servers/sqlVulnerabilityAssessments",
"apiVersion": "2022-02-01-preview",
"name": "[concat(parameters('serverName'), '/Default')]",
"dependsOn": [
"[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/', parameters('databaseName'))]"
],
"properties": {
"state": "Enabled"
}
},
{
"condition": "[parameters('enablePrivateEndpoint')]",
"type": "Microsoft.Resources/deployments",
"apiVersion": "[variables('deploymentTemplateApi')]",
"name": "[variables('subnetPoliciesTemplateName')]",
"properties": {
"mode": "Incremental",
"template": {
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"resources": [
{
"apiVersion": "[variables('privateEndpointApi')]",
"name": "[concat(parameters('privateEndpointVnetName'), '/', parameters('privateEndpointSubnetName'))]",
"location": "[parameters('privateEndpointLocation')]",
"properties": {
"privateEndpointNetworkPolicies": "Disabled",
"provisioningState": "Succeeded",
"addressPrefixes": [
"10.0.0.0/24"
],
"delegations": [],
"privateLinkServiceNetworkPolicies": "Enabled"
},
"type": "Microsoft.Network/virtualNetworks/subnets"
}
]
}
},
"subscriptionId": "[if(parameters('enablePrivateEndpoint'), parameters('privateEndpointVnetSubscriptionId'), variables('subscriptionId'))]",
"resourceGroup": "[if(parameters('enablePrivateEndpoint'), parameters('privateEndpointVnetResourceGroup'), variables('resourceGroupName'))]"
},
{
"condition": "[parameters('enablePrivateEndpoint')]",
"type": "Microsoft.Resources/deployments",
"apiVersion": "[variables('deploymentTemplateApi')]",
"name": "[variables('privateEndpointTemplateName')]",
"dependsOn": [
"[resourceId(variables('subscriptionId'), variables('resourceGroupName'), 'Microsoft.Sql/servers/databases/', parameters('serverName'), parameters('databaseName'))]",
"[variables('subnetPoliciesTemplateName')]"
],
"properties": {
"mode": "Incremental",
"parameters": {
"privateEndpointName": {
"value": "[parameters('privateEndpointName')]"
},
"privateEndpointConnectionId": {
"value": ""
},
"privateEndpointConnectionName": {
"value": "[parameters('privateLinkServiceName')]"
},
"privateEndpointId": {
"value": "[variables('privateEndpointId')]"
},
"privateEndpointApiVersion": {
"value": "[variables('privateEndpointApi')]"
},
"privateLinkServiceId": {
"value": "[parameters('privateLinkServiceServiceId')]"
},
"groupId": {
"value": "SqlServer"
},
"subnetId": {
"value": "[variables('privateEndpointSubnetResourceId')]"
},
"location": {
"value": "[parameters('privateEndpointLocation')]"
},
"tags": {
"value": {}
}
},
"templatelink": {
"contentVersion": "1.0.0.0",
"uri": "[parameters('privateEndpointTemplateLink')]"
}
},
"subscriptionId": "[if(parameters('enablePrivateEndpoint'), parameters('privateEndpointSubscriptionId'), variables('subscriptionId'))]",
"resourceGroup": "[if(parameters('enablePrivateEndpoint'), parameters('privateEndpointResourceGroup'), variables('resourceGroupName'))]"
},
{
"condition": "[equals(parameters('enableDigestStorage'), 'Enabled')]",
"type": "Microsoft.Resources/deployments",
"apiVersion": "2019-05-01",
"name": "[concat('ledger-', parameters('digestStorageName'))]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
"[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/', parameters('databaseName'))]"
],
"properties": {
"mode": "Incremental",
"parameters": {
"enableDigestStorage": {
"value": "[parameters('enableDigestStorage')]"
},
"digestStorageOption": {
"value": "[parameters('digestStorageOption')]"
},
"digestStorageName": {
"value": "[parameters('digestStorageName')]"
},
"blobStorageContainerName": {
"value": "[parameters('blobStorageContainerName')]"
},
"retentionDays": {
"value": "[parameters('retentionDays')]"
},
"retentionPolicy": {
"value": "[parameters('retentionPolicy')]"
},
"serverName": {
"value": "[parameters('serverName')]"
},
"digestAccountResourceGroup": {
"value": "[parameters('digestAccountResourceGroup')]"
},
"databaseName": {
"value": "[parameters('databaseName')]"
},
"serverLocation": {
"value": "[parameters('serverLocation')]"
},
"digestRegion": {
"value": "[parameters('digestRegion')]"
},
"storageAccountdigestRegion": {
"value": "[parameters('storageAccountdigestRegion')]"
},
"isNewDigestLocation": {
"value": "[parameters('isNewDigestLocation')]"
},
"isPermissionAssigned": {
"value": "[parameters('isPermissionAssigned')]"
}
},
"templateLink": {
"contentVersion": "1.0.0.0",
"uri": "[parameters('sqlLedgerTemplateLink')]"
}
},
"subscriptionId": "[variables('subscriptionId')]",
"resourceGroup": "[ variables('resourceGroupName')]"
},
{
"condition": "[and(parameters('enablePrivateEndpoint'), parameters('enablePrivateDnsZone'))]",
"type": "Microsoft.Resources/deployments",
"apiVersion": "[variables('deploymentTemplateApi')]",
"name": "[concat('PrivateDns-', parameters('privateEndpointNestedTemplateId'))]",
"dependsOn": [
"[variables('privateEndpointTemplateName')]"
],
"properties": {
"mode": "Incremental",
"template": {
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"resources": [
{
"type": "Microsoft.Network/privateDnsZones",
"apiVersion": "2018-09-01",
"name": "[parameters('privateLinkPrivateDnsZoneFQDN')]",
"location": "global",
"tags": {},
"properties": {}
},
{
"type": "Microsoft.Network/privateDnsZones/virtualNetworkLinks",
"apiVersion": "2018-09-01",
"name": "[concat(parameters('privateLinkPrivateDnsZoneFQDN'), '/', uniqueString(variables('privateEndpointVnetId')))]",
"location": "global",
"dependsOn": [
"[parameters('privateLinkPrivateDnsZoneFQDN')]"
],
"properties": {
"virtualNetwork": {
"id": "[variables('privateEndpointVnetId')]"
},
"registrationEnabled": false
}
},
{
"apiVersion": "[variables('deploymentTemplateApi')]",
"name": "[concat('EndpointDnsRecords-', parameters('privateEndpointDnsRecordUniqueId'))]",
"type": "Microsoft.Resources/deployments",
"dependsOn": [
"[parameters('privateLinkPrivateDnsZoneFQDN')]"
],
"properties": {
"mode": "Incremental",
"templatelink": {
"contentVersion": "1.0.0.0",
"uri": "[parameters('privateDnsForPrivateEndpointTemplateLink')]"
},
"parameters": {
"privateDnsName": {
"value": "[parameters('privateLinkPrivateDnsZoneFQDN')]"
},
"privateEndpointNicResourceId": {
"value": "[if(parameters('enablePrivateEndpoint'), reference(concat('Microsoft.Resources/deployments/', variables('privateEndpointTemplateName'))).outputs.networkInterfaceId.value, '')]"
},
"nicRecordsTemplateUri": {
"value": "[parameters('privateDnsForPrivateEndpointNicTemplateLink')]"
},
"ipConfigRecordsTemplateUri": {
"value": "[parameters('privateDnsForPrivateEndpointIpConfigTemplateLink')]"
},
"uniqueId": {
"value": "[parameters('privateEndpointDnsRecordUniqueId')]"
},
"existingRecords": {
"value": {}
}
}
}
}
]
}
},
"subscriptionId": "[if(parameters('enablePrivateEndpoint'), parameters('privateEndpointVnetSubscriptionId'), variables('subscriptionId'))]",
"resourceGroup": "[if(parameters('enablePrivateEndpoint'), parameters('privateLinkPrivateDnsZoneRG'), variables('resourceGroupName'))]"
}
],
"variables": {
"subscriptionId": "[subscription().subscriptionId]",
"resourceGroupName": "[resourceGroup().name]",
"uniqueStorage": "[uniqueString(variables('subscriptionId'), variables('resourceGroupName'), parameters('serverLocation'))]",
"storageName": "[tolower(concat('sqlva', variables('uniqueStorage')))]",
"privateEndpointContainerTemplateName": "[concat('PrivateEndpointContainer-', if(parameters('enablePrivateEndpoint'), parameters('privateEndpointNestedTemplateId'), ''))]",
"subnetPoliciesTemplateName": "[concat('SubnetPolicies-', if(parameters('enablePrivateEndpoint'), parameters('privateEndpointNestedTemplateId'), ''))]",
"privateEndpointTemplateName": "[concat('PrivateEndpoint-', if(parameters('enablePrivateEndpoint'), parameters('privateEndpointNestedTemplateId'), ''))]",
"deploymentTemplateApi": "2018-05-01",
"privateEndpointApi": "2019-04-01",
"privateEndpointId": "[if(parameters('enablePrivateEndpoint'), resourceId(parameters('privateEndpointSubscriptionId'), parameters('privateEndpointResourceGroup'), 'Microsoft.Network/privateEndpoints', parameters('privateEndpointName')), '')]",
"privateEndpointVnetId": "[if(parameters('enablePrivateEndpoint'), resourceId(parameters('privateEndpointVnetSubscriptionId'), parameters('privateEndpointVnetResourceGroup'), 'Microsoft.Network/virtualNetworks', parameters('privateEndpointVnetName')), '')]",
"privateEndpointSubnetResourceId": "[if(parameters('enablePrivateEndpoint'), resourceId(parameters('privateEndpointVnetSubscriptionId'), parameters('privateEndpointVnetResourceGroup'), 'Microsoft.Network/virtualNetworks/subnets', parameters('privateEndpointVnetName'), parameters('privateEndpointSubnetName')), '')]",
"uniqueRoleGuid": "[guid(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), variables('storageBlobContributor'), resourceId('Microsoft.Sql/servers', parameters('serverName')))]",
"StorageBlobContributor": "[subscriptionResourceId('Microsoft.Authorization/roleDefinitions', 'ba92f5b4-2d11-453d-a403-e96b0029c9fe')]"
}
}
Raw
94-database-status.json
// Brand new database resource after creation
{
"content": {
"id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName/providers/Microsoft.Sql/servers/server-server/databases/db-db",
"name": "db-db",
"type": "microsoft.sql/servers/databases",
"location": "eastus",
"tags": {},
"properties": {
"availabilityZone": "NoPreference",
"catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
"collation": "SQL_Latin1_General_CP1_CI_AS",
"creationDate": "2024-09-26T09:38:37.67Z",
"currentBackupStorageRedundancy": "Geo",
"currentServiceObjectiveName": "S0",
"currentSku": {
"capacity": 10,
"name": "Standard",
"tier": "Standard"
},
"databaseId": "00000000-0000-0000-0000-000000000000",
"defaultSecondaryLocation": "westus",
"isInfraEncryptionEnabled": false,
"isLedgerOn": false,
"maintenanceConfigurationId": "/subscriptions/00000000-0000-0000-0000-000000000000/providers/Microsoft.Maintenance/publicMaintenanceConfigurations/SQL_Default",
"maxSizeBytes": 268435456000,
"readScale": "Disabled",
"requestedBackupStorageRedundancy": "Geo",
"requestedServiceObjectiveName": "S0",
"status": "Online",
"zoneRedundant": false
},
"kind": "v12.0,user",
"sku": {
"name": "Standard",
"tier": "Standard",
"capacity": 10
}
"apiVersion": "2024-05-01-preview"
}
Raw
99-master.json
{
"content": {
"id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName/providers/Microsoft.Sql/servers/server-server/databases/master",
"name": "master",
"type": "microsoft.sql/servers/databases",
"location": "eastus",
"properties": {
"availabilityZone": "NoPreference",
"catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
"collation": "SQL_Latin1_General_CP1_CI_AS",
"creationDate": "2024-09-26T09:24:04.263Z",
"currentBackupStorageRedundancy": "Local",
"currentServiceObjectiveName": "System2",
"currentSku": {
"capacity": 0,
"name": "System",
"tier": "System"
},
"databaseId": "00000000-0000-0000-0000-000000000000",
"defaultSecondaryLocation": "westus",
"isInfraEncryptionEnabled": false,
"isLedgerOn": false,
"maintenanceConfigurationId": "/subscriptions/00000000-0000-0000-0000-000000000000/providers/Microsoft.Maintenance/publicMaintenanceConfigurations/SQL_Default",
"maxSizeBytes": 53687091200,
"readScale": "Disabled",
"requestedBackupStorageRedundancy": "Local",
"requestedServiceObjectiveName": "System2",
"status": "Online",
"zoneRedundant": false
},
"kind": "v12.0,system",
"managedBy": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName/providers/Microsoft.Sql/servers/server-server",
"sku": {
"name": "System",
"tier": "System",
"capacity": 0
}
},
"apiVersion": "2024-05-01-preview"
}
Raw
99-resource-types.md

Resource types

Logging

type name REST
Microsoft.Sql/servers/auditingPolicies Auditing Policies
Microsoft.Sql/servers/auditingSettings Auditing Settings Server Blob Auditing Policies
Microsoft.Sql/servers/devOpsAuditingSettings@2023-05-01-preview Dev Ops Auditing Settings Server DevOps Audit Settings
Microsoft.Sql/servers/extendedAuditingSettings Extended Auditing Settings Extended Server Blob Auditing Policies
Microsoft.Sql/servers/databases/auditingPolicies Auditing Policies
Microsoft.Sql/servers/databases/auditingSettings Auditing Settings Database Blob Auditing Policies
Microsoft.Sql/servers/databases/extendedAuditingSettings Extended Auditing Settings Extended Database Blob Auditing Policies

Microsoft.Sql/servers/auditingPolicies and icrosoft.Sql/servers/databases/auditingPolicies are retiring 31 October 2025 with API version 2014-04-01. Full mapping: https://learn.microsoft.com/en-us/rest/api/sql/retirement?view=rest-sql-2021-11-01

resource symbolicname 'Microsoft.Sql/servers/auditingPolicies@2014-04-01' = {
  name: 'default'
  parent: resourceSymbolicName
  properties: {
    auditingState: 'string'
    auditLogsTableName: 'string'
    eventTypesToAudit: 'string'
    fullAuditLogsTableName: 'string'
    retentionDays: 'string'
    storageAccountKey: 'string'
    storageAccountName: 'string'
    storageAccountResourceGroupName: 'string'
    storageAccountSecondaryKey: 'string'
    storageAccountSubscriptionId: 'string'
    storageTableEndpoint: 'string'
  }
}

resource symbolicname 'Microsoft.Sql/servers/auditingSettings@2023-05-01-preview' = {
  name: 'default'
  parent: resourceSymbolicName
  properties: {
    auditActionsAndGroups: [
      'string'
    ]
    isAzureMonitorTargetEnabled: bool
    isDevopsAuditEnabled: bool
    isManagedIdentityInUse: bool
    isStorageSecondaryKeyInUse: bool
    queueDelayMs: int
    retentionDays: int
    state: 'string'
    storageAccountAccessKey: 'string'
    storageAccountSubscriptionId: 'string'
    storageEndpoint: 'string'
  }
}

resource symbolicname 'Microsoft.Sql/servers/devOpsAuditingSettings@2023-05-01-preview' = {
  name: 'Default'
  parent: resourceSymbolicName
  properties: {
    isAzureMonitorTargetEnabled: bool
    isManagedIdentityInUse: bool
    state: 'string'
    storageAccountAccessKey: 'string'
    storageAccountSubscriptionId: 'string'
    storageEndpoint: 'string'
  }
}

resource symbolicname 'Microsoft.Sql/servers/extendedAuditingSettings@2023-05-01-preview' = {
  name: 'default'
  parent: resourceSymbolicName
  properties: {
    auditActionsAndGroups: [
      'string'
    ]
    isAzureMonitorTargetEnabled: bool
    isDevopsAuditEnabled: bool
    isManagedIdentityInUse: bool
    isStorageSecondaryKeyInUse: bool
    predicateExpression: 'string'
    queueDelayMs: int
    retentionDays: int
    state: 'string'
    storageAccountAccessKey: 'string'
    storageAccountSubscriptionId: 'string'
    storageEndpoint: 'string'
  }
}

resource symbolicname 'Microsoft.Sql/servers/databases/auditingPolicies@2014-04-01' = {
  name: 'default'
  parent: resourceSymbolicName
  properties: {
    auditingState: 'string'
    auditLogsTableName: 'string'
    eventTypesToAudit: 'string'
    fullAuditLogsTableName: 'string'
    retentionDays: 'string'
    storageAccountKey: 'string'
    storageAccountName: 'string'
    storageAccountResourceGroupName: 'string'
    storageAccountSecondaryKey: 'string'
    storageAccountSubscriptionId: 'string'
    storageTableEndpoint: 'string'
    useServerDefault: 'string' // only on database level
  }
}

resource symbolicname 'Microsoft.Sql/servers/databases/auditingSettings@2023-05-01-preview' = {
  name: 'default'
  parent: resourceSymbolicName
  properties: {
    auditActionsAndGroups: [
      'string'
    ]
    isAzureMonitorTargetEnabled: bool
    isManagedIdentityInUse: bool
    isStorageSecondaryKeyInUse: bool
    queueDelayMs: int
    retentionDays: int
    state: 'string'
    storageAccountAccessKey: 'string'
    storageAccountSubscriptionId: 'string'
    storageEndpoint: 'string'
  }
}

resource symbolicname 'Microsoft.Sql/servers/databases/extendedAuditingSettings@2023-05-01-preview' = {
  name: 'default'
  parent: resourceSymbolicName
  properties: {
    auditActionsAndGroups: [
      'string'
    ]
    isAzureMonitorTargetEnabled: bool
    isManagedIdentityInUse: bool
    isStorageSecondaryKeyInUse: bool
    predicateExpression: 'string'
    queueDelayMs: int
    retentionDays: int
    state: 'string'
    storageAccountAccessKey: 'string'
    storageAccountSubscriptionId: 'string'
    storageEndpoint: 'string'
  }
}

VA

API version 2014-04-01 retirement:

/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/securityAlertPolicies => Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advancedThreatProtectionSettings

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment