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.

CodePudding user response：

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.