Hands-on Lab — DP-300 Microsoft Azure Database Administrator Associate

Overview

Prerequisites

Steps

1. 1.Provider, Resource Group, naming primitives

   Standard Azure opener.

   Copy

   terraform {
     required_version = ">= 1.5"
   
     required_providers {
       azurerm = { source = "hashicorp/azurerm", version = "~> 4.0" }
       random  = { source = "hashicorp/random",  version = "~> 3.6" }
     }
   }
   
   provider "azurerm" {
     features {}
   }
   
   resource "random_id" "suffix" {
     byte_length = 3
   }
   
   data "azurerm_client_config" "current" {}
   
   locals {
     tags = {
       Project   = "certlabpro-dp-300"
       ManagedBy = "terraform"
     }
   }
   
   resource "azurerm_resource_group" "main" {
     name     = "certlabpro-dp-300-rg"
     location = "eastus"
     tags     = local.tags
   }

2. 2.Stand up a storage account dedicated to SQL audit logs

   Provisions:

   • Azure Storage

   DP-300's Implement a secure environment domain tests the separation between workload data and audit data. A common production pattern: a dedicated storage account just for audit logs, with a different retention policy (longer) and stricter access controls than the workload's own storage.

   We provision the audit account here, set 90-day blob retention (matches the SQL audit storage requirement for many regulated industries), and lock down public access. The SQL Server in Step 3 will write its audit log here.

   Copy

   resource "azurerm_storage_account" "audit" {
     name                            = "dp300audit${random_id.suffix.hex}"
     resource_group_name             = azurerm_resource_group.main.name
     location                        = azurerm_resource_group.main.location
     account_tier                    = "Standard"
     account_replication_type        = "LRS"
     account_kind                    = "StorageV2"
   
     https_traffic_only_enabled      = true
     min_tls_version                 = "TLS1_2"
     allow_nested_items_to_be_public = false
   
     blob_properties {
       delete_retention_policy {
         days = 90
       }
     }
   
     tags = local.tags
   }

3. 3.Provision the SQL Server with Entra-only auth + a database

   Provisions:

   • Azure SQL Database
   • Microsoft Entra ID

   Entra-only authentication (azuread_authentication_only = true) is the DP-300 production-best-practice answer — it disables the SQL-auth admin login entirely, forcing every connection through Entra ID. The exam tests this pattern repeatedly.

   The Entra admin block names the current Terraform principal — meaning after terraform apply, you can connect via sqlcmd -G using your az login identity, with no SQL password anywhere. The database underneath is the smallest Basic tier; switch to General Purpose Serverless for production workloads with intermittent load (auto-pause after idle).

   Copy

   resource "azurerm_mssql_server" "main" {
     name                          = "sql-dp300-${random_id.suffix.hex}"
     resource_group_name           = azurerm_resource_group.main.name
     location                      = azurerm_resource_group.main.location
     version                       = "12.0"
     minimum_tls_version           = "1.2"
     public_network_access_enabled = true
   
     # Entra-only mode: no SQL admin login created.
     azuread_administrator {
       login_username              = "sqladmins"
       object_id                   = data.azurerm_client_config.current.object_id
       azuread_authentication_only = true
     }
   
     identity {
       type = "SystemAssigned"
     }
   
     tags = local.tags
   }
   
   resource "azurerm_mssql_database" "main" {
     name        = "app"
     server_id   = azurerm_mssql_server.main.id
     sku_name    = "Basic"
     max_size_gb = 2
   
     tags = local.tags
   }

4. 4.Turn on SQL auditing into the audit storage account

   Provisions:

   • Azure SQL Database

   SQL Server-level auditing captures every DBCC command, schema change, login attempt, and grant/revoke. We wire it to the storage account from Step 2 with 90-day retention. The SQL Server's managed identity from Step 3 is granted Storage Blob Data Contributor on the audit account — the identity-based authentication pattern DP-300 prefers over storage-account-key-based.

   Server-level auditing applies to every database under the server. The exam tests the distinction between server-level (this) and database-level auditing (per-DB override). Server-level is the right answer for all databases must be audited questions.

   Copy

   resource "azurerm_role_assignment" "sql_audit_writer" {
     scope                = azurerm_storage_account.audit.id
     role_definition_name = "Storage Blob Data Contributor"
     principal_id         = azurerm_mssql_server.main.identity[0].principal_id
   }
   
   resource "azurerm_mssql_server_extended_auditing_policy" "main" {
     server_id                               = azurerm_mssql_server.main.id
     storage_endpoint                        = azurerm_storage_account.audit.primary_blob_endpoint
     retention_in_days                       = 90
     log_monitoring_enabled                  = true
   
     depends_on = [azurerm_role_assignment.sql_audit_writer]
   }

5. 5.Enable Defender for SQL + threat detection alerts to Log Analytics

   Provisions:

   • Microsoft Defender for Cloud
   • Azure Monitor

   Microsoft Defender for SQL is the DP-300 Monitor and optimize answer for threat detection — it scans for SQL injection attempts, anomalous login locations, brute-force probes, and unusual privilege escalations. The advanced threat protection resource is the per-server enable; the security alert policy configures who gets emailed when threats are detected.

   We also wire a Log Analytics workspace and pipe the database diagnostics (SQL insights, query store metrics, errors) into it. With this final piece in place, the DP-300 hardened-SQL baseline is shaped: Entra-only auth, server auditing, threat detection, Log Analytics observability. Every DP-300 Manage and configure question lands on one of these primitives.

   Copy

   resource "azurerm_log_analytics_workspace" "main" {
     name                = "log-dp300"
     resource_group_name = azurerm_resource_group.main.name
     location            = azurerm_resource_group.main.location
     sku                 = "PerGB2018"
     retention_in_days   = 30
   
     tags = local.tags
   }
   
   resource "azurerm_mssql_server_security_alert_policy" "main" {
     resource_group_name        = azurerm_resource_group.main.name
     server_name                = azurerm_mssql_server.main.name
     state                      = "Enabled"
     email_account_admins       = true
     email_addresses            = ["dba@example.com"] # replace
     retention_days             = 30
     disabled_alerts            = [] # all alert types enabled
   }
   
   resource "azurerm_monitor_diagnostic_setting" "sql_db" {
     name                       = "diag"
     target_resource_id         = azurerm_mssql_database.main.id
     log_analytics_workspace_id = azurerm_log_analytics_workspace.main.id
   
     enabled_log {
       category = "SQLInsights"
     }
     enabled_log {
       category = "AutomaticTuning"
     }
     enabled_log {
       category = "Errors"
     }
   
     metric {
       category = "AllMetrics"
       enabled  = true
     }
   }

Cleanup