sql-server - Terraform 中的基本 SQL 命令

Question

我正在使用 Terraform 构建 Azure DB 并设置正确的 Azure AD 管理员等 - 一切正常。

我现在需要创建

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

如果这在 Terraform 中可能的话有什么想法 - 认为这是最简单的方法，因为用户已经被授权创建数据库。

Answer 1

不可能直接运行您在问题中提到的命令，但您可以使用Invoke-sqlcmd 并使用您的 AAD 管理员凭据进行身份验证并运行命令。

我使用以下代码测试了该场景:

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 [<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="10656375625064757e717e647e717d753e7f7e7d7973627f637f76643e737f7d" rel="noreferrer noopener nofollow">[email protected]</a>] 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 [<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6f1a1c0a1d2f1b0a010e011b010e020a41000102060c1d001c00091b410c0002" rel="noreferrer noopener nofollow">[email protected]</a>];
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
]
}

输出:

注意:请确保有Azure Powershell Module 和 SQLServer Powershell Module 。