Sqlcmd multiline script in terraform local provisioner

Question

When I run this provisioner it gives me error:

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 code:

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
  }
}

Looks like it takes it whole as one command. How can I split it into two commands? I cant take sql part to separate file since i need get '${var.mssql_admin_password}' value.

Answer 1

In order for sqlcmd to work in local exec you need to have SSMS downloaded and installed in your Local machine, Once you install SSMS, The sqlcmd is installed by default. Refer below:-

SSMS download reference:-

Now, run the below terraform scripts to deploy Azure SQL DB and use local_exec to run sql command to create DB and and Table.

My locals.tf:-

locals {
  resource_group_name="silicon-app-grp"
  location="North Europe"  
}

My main.tf:-

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 is my sql script file in the same folder as my terraform files above.

01.sql script:-

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)

Reference

Output:-