sql-serverazureterraform

Basic SQL commands in Terraform


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.


Solution

  • 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 [user@tenantname.onmicrosoft.com] 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 [user@tenantname.onmicrosoft.com];
        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:

    enter image description here

    enter image description here

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