Powershell Invoke-Sqlcmd hits wrong database - system, why?

Question

Im deploying mssql server, firewall, database and sql logins (and more) via terraform.

Server, firewall and database are deployed correctly, while logins via powershell fails with following error:

': exit status 1. Output: Invoke-Sqlcmd : Database 'System' does not exist. Make sure that the name is entered correctly.

My terraform code looks like this:

  resource "azurerm_mssql_server" "itan-mssql-server" {
  administrator_login = "itanadmin"
  administrator_login_password = random_password.itan-mssql-admin-password.result
  location = var.location
  name = "itan-mssql-server"
  resource_group_name = var.resource-group-name
  version = "12.0"

  depends_on = [
    azurerm_resource_group.itan-west-europe-resource-group]
}


resource "azurerm_sql_firewall_rule" "itan-mssql-server-firewall-rule" {
  start_ip_address = "${chomp(data.http.myip.body)}"
  end_ip_address = "${chomp(data.http.myip.body)}"
  name = "itan-tf-client-executer-ip"
  resource_group_name = var.resource-group-name
  server_name = azurerm_mssql_server.itan-mssql-server.name
  depends_on = [azurerm_mssql_server.itan-mssql-server]
}

resource "azurerm_mssql_database" "itan-mssql-database" {
  name = "itan"
  server_id = azurerm_mssql_server.itan-mssql-server.id
  sku_name = "basic"
  max_size_gb = "2"

  depends_on = [
    azurerm_mssql_server.itan-mssql-server]
}

resource "null_resource" "itan-mssql-logins" {
  provisioner "local-exec" {
    command =<<EOT

      $server = Get-AzSqlServer
           
      $createReaderLogin = @{
        'ServerInstance' = "${azurerm_mssql_server.itan-mssql-server.fully_qualified_domain_name}"
        'Database' = "itan"
        'Username' = "${azurerm_mssql_server.itan-mssql-server.administrator_login}"
        'Password' = "${random_password.itan-mssql-admin-password.result}"
        'Query' = "SELECT * FROM Students";
      }

      Invoke-Sqlcmd $createReaderLogin

    EOT
    interpreter = ["PowerShell", "-Command"]
  }
  
  depends_on = [azurerm_sql_firewall_rule.itan-mssql-server-firewall-rule]
}

The last part, the null_resource is being parsed into something like this:

  $server = Get-AzSqlServer

      $createReaderLogin = @{
        'ServerInstance' = "server.database.windows.net"
        'Database' = "database"
        'Username' = "admin"
        'Password' = "***********"
        'Query' = "SELECT * FROM Students";
      }

Already destroyed, no worries about the password.
I know I need to create login on master db and next user on the proper db, this select * is just an example of failing query

I have tested and after server, firewall, db - has beed created I was able to connect azure mssql with mssql. Yet running Invoke-SqlCmd fails with "Database 'System' does not exist"

WHY? Been struglling with this for like 2 days already.

Basically Im trying to create logins/users with dedidicated sql rights (db_read/db_write).

Sorry, I don't understand. How to check? How to make it behave properly? — Jaroslaw Stadnicki
Did you previously have a database named System that has subsequently been deleted? Does the Default Database setting of the admin login still reference that System database? — AlwaysLearning
Nope, never. By default, I have master. Next im creating mine. No mention of system anywhere. — Jaroslaw Stadnicki
Aren't you missing the instance name? server.database.windows.net\MyITANInstanceName instead of just server.database.windows.net — Mathias R. Jessen

Jaroslaw Stadnicki Jaroslaw Stadnicki · Accepted Answer · 2020-08-02T20:02:29

I solved this with single long line instead of multi line: New code for null_resource

resource "null_resource" "itan-mssql-logins" {
  provisioner "local-exec" {
    command = "Invoke-Sqlcmd -ServerInstance ${azurerm_mssql_server.itan-mssql-server.fully_qualified_domain_name} -Database master -Username itanadmin -Password '${random_password.itan-mssql-admin-password.result}' -Query \"CREATE LOGIN [itanreader] WITH PASSWORD=N'${random_password.itan-mssql-reader-password.result}'\""
    interpreter = ["PowerShell", "-Command"]
  }
  
  depends_on = [azurerm_sql_firewall_rule.itan-mssql-server-firewall-rule, azurerm_mssql_database.itan-mssql-database]
}

All params looks the same, yet this one works. Don't know why thou.

Powershell Invoke-Sqlcmd hits wrong database - system, why?

1 Answers