Basic SQL commands in Terraform

Question

I am using Terraform to build an Azure DB and set the correct Azure AD Admin etc - all working well.

I now need to create

CREATE LOGIN [XXX-XXX] FROM EXTERNAL PROVIDER;
CREATE USER [XXX-XXX] FOR LOGIN [XXX-XXX];
ALTER ROLE db_datareader ADD MEMBER [XXX-XXX]

Any ideas if this is possible within Terraform - thinking its the easiest way as the user is already authorised to create the database.

Answer 1

Its not possible to directly run the commands that you have mentioned in the question but you can use Invoke-sqlcmd and authenticate with your AAD admin credentials and run the commands .

I tested the scenario with the below code :

provider "azurerm" {
  features {}
}

data "azurerm_client_config" "current" {}

resource "azurerm_resource_group" "example" {
  name     = "example-resources"
  location = "West Europe"
}

resource "azurerm_sql_server" "example" {
  name                         = "ansumansqlserver"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "admin"
  administrator_login_password = "password"

  tags = {
    environment = "production"
  }
}

resource "azurerm_storage_account" "example" {
  name                     = "ansumansacc"
  resource_group_name      = azurerm_resource_group.example.name
  location                 = azurerm_resource_group.example.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_sql_database" "example" {
  name                = "ansumansqldatabase"
  resource_group_name = azurerm_resource_group.example.name
  location            = azurerm_resource_group.example.location
  server_name         = azurerm_sql_server.example.name

  extended_auditing_policy {
    storage_endpoint                        = azurerm_storage_account.example.primary_blob_endpoint
    storage_account_access_key              = azurerm_storage_account.example.primary_access_key
    storage_account_access_key_is_secondary = true
    retention_in_days                       = 6
  }
  tags = {
    environment = "production"
  }
}

resource "azurerm_sql_active_directory_administrator" "example" {
  server_name         = azurerm_sql_server.example.name
  resource_group_name = azurerm_resource_group.example.name
  login               = "sqladmin"
  tenant_id           = data.azurerm_client_config.current.tenant_id
  object_id           = data.azurerm_client_config.current.object_id
}
## creating Login in master database first
resource "null_resource" "master" {
  provisioner "local-exec" {
    command     = <<EOT
    Set-AzContext -SubscriptionId "<SubscriptionID>"
    $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
    Invoke-SqlCmd -ServerInstance ${azurerm_sql_server.example.fully_qualified_domain_name} -Database master -AccessToken $token -Query "CREATE LOGIN [[email protected]] FROM EXTERNAL PROVIDER"
    EOT
    interpreter = ["PowerShell", "-Command"]
  }
  depends_on = [
    azurerm_sql_active_directory_administrator.example,
    azurerm_sql_database.example
  ]
}

## creating the user from the login created in master and assigning role
resource "null_resource" "database" {
  provisioner "local-exec" {
    command     = <<EOT
    Set-AzContext -SubscriptionId "<SubscriptionID>"
    $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
    $query= @'
    CREATE USER [AJAY] FOR LOGIN [[email protected]];
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [AJAY];
    GO
    '@
    Invoke-SqlCmd -ServerInstance ${azurerm_sql_server.example.fully_qualified_domain_name} -Database ${azurerm_sql_database.example.name} -AccessToken $token -Query $query
    EOT
    interpreter = ["PowerShell", "-Command"]
  }
  depends_on = [
    null_resource.master
  ]
}

Output:

Note: Please make sure to have Azure Powershell Module and SQLServer Powershell Module.