Powershell调用Sqlcmd命中错误的数据库系统，为什么

Question

Im通过terraform部署mssql服务器、防火墙、数据库和sql登录(以及更多(。

服务器、防火墙和数据库部署正确，而通过powershell登录失败，错误如下：

'：退出状态1。输出：调用Sqlcmd:数据库"System"不存在请确保名称输入正确。

我的地形代码如下：

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

最后一部分，null_resource被解析为如下内容：

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

• 已经销毁，不用担心密码
• 我知道我需要在master数据库上创建登录名，在正确的数据库上创建下一个用户，这个select*只是查询失败的一个例子

我已经测试过，在创建了服务器、防火墙和数据库之后，我能够将azure mssql与mssql连接起来。然而运行CCD_ 2失败；数据库"系统"不存在">

为什么？我已经纠结了两天了。

基本上，我试图创建具有指定sql权限(db_read/db_write(的登录名/用户。

Answer 1

我用单长行而不是多行解决了这个问题：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]
}

所有参数看起来都一样，但这一个有效。不知道你为什么。