terraform 本地预配程序中的 Sqlcmd 多行脚本-解网

问：

当我运行此配置程序时，它给了我错误：

exit status 2. Output: Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login failed for user 'User'..
│ /bin/sh: 2: Syntax error: "(" unexpected

TF代码：

resource "null_resource" "init" {
  
depends_on = [azurerm_mssql_firewall_rule.creator]

  provisioner "local-exec" {
    command = <<EOT
sqlcmd -S tcp:someserver.database.windows.net -d master -U ${var.mssql_admin_username} -P ${var.mssql_admin_password}
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'poweruser')
BEGIN
CREATE USER poweruser;
ALTER USER poweruser WITH PASSWORD '${var.mssql_admin_password}';
END;
QUIT
    EOT
  }
}

看起来它把它作为一个命令。如何将其拆分为两个命令？我不能将 sql 部分用于单独的文件，因为我需要获取值。'${var.mssql_admin_password}'

Azure Terraform azure-sql-database sqlcmd

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "3.37.0"
    }
  }
}

provider "azurerm" {
  # Configuration options
  subscription_id = "sub-id"
  tenant_id       = "tenant-id"
  client_id       = "client-id"
  client_secret   = "client-secret"
  features {}
}

My sqldatabase.tf:-

resource "azurerm_mssql_server" "sqlserver" {
  name                         = "sqlserver400908"
  resource_group_name          = local.resource_group_name
  location                     = local.location
  version                      = "12.0"
  administrator_login          = "adminuser"
  administrator_login_password = "Admin@123"  
  depends_on = [
    azurerm_resource_group.appgrp
  ]
}

resource "azurerm_mssql_database" "appdb" {
  name           = "appdb"
  server_id      = azurerm_mssql_server.sqlserver.id
  collation      = "SQL_Latin1_General_CP1_CI_AS"
  license_type   = "LicenseIncluded"
  max_size_gb    = 2  
  sku_name       = "S0"  

 lifecycle {
    ignore_changes = [
      license_type
    ]
  }
  
  depends_on = [
    azurerm_mssql_server.sqlserver
      ]
  
}


resource "azurerm_mssql_firewall_rule" "allowmyclient" {
  name             = "AllowClientIP"
  server_id        = azurerm_mssql_server.sqlserver.id
  start_ip_address = "103.133.65.143"
  end_ip_address   = "103.133.65.143"
  depends_on = [
    azurerm_mssql_server.sqlserver
  ]
}

My sqlcmd.tf:-

resource "null_resource" "database_setup" {
  provisioner "local-exec" {
      command = "sqlcmd -S ${azurerm_mssql_server.sqlserver.fully_qualified_domain_name} -U ${azurerm_mssql_server.sqlserver.administrator_login} -P ${azurerm_mssql_server.sqlserver.administrator_login_password} -d appdb -i 01.sql"
  }
  depends_on=[
    azurerm_mssql_database.appdb
  ]
}

01.sql 是我的 SQL 脚本文件，与上面的 Terraform 文件位于同一文件夹中。

01.sql 脚本：-

CREATE TABLE Products
(
     ProductID int,
     ProductName varchar(1000),
     Quantity int
)


INSERT INTO Products(ProductID,ProductName,Quantity) VALUES (1,'Mobile',100)

INSERT INTO Products(ProductID,ProductName,Quantity) VALUES (2,'Laptop',200)

INSERT INTO Products(ProductID,ProductName,Quantity) VALUES (3,'Tabs',300)

参考

输出：-

enter image description here

@Michael 尝试添加 IF NOT EXISTS （SELECT * FROM sys.database_principals WHERE NAME = 'poweruser'） BEGIN CREATE USER poweruser;使用密码“${var.mssql_admin_password}”更改用户 poweruser;结束;在 01.sql 命令文件中退出 sql 命令，然后运行此设置程序 “local-exec” { command = “sqlcmd -S ${azurerm_mssql_server.sqlserver.fully_qualified_domain_name} -U ${azurerm_mssql_server.sqlserver.administrator_login} -P ${azurerm_mssql_server.sqlserver.administrator_login_password} -d appdb -i 01.sql” } 你的命令应该可以工作

上一个：如何使用sqlcmd从SQL Server将数据导出为CSV格式？

下一个：使用 sqlcmd 的破折号在 its-name 中还原数据库

terraform 本地预配程序中的 Sqlcmd 多行脚本

Sqlcmd multiline script in terraform local provisioner

评论

评论