Getting terraform null_resource local-exec to run a powershell install-module

Question

this probably evil to do, but in order to setup a managed identity in our azure db we are using the null_resource like this:

# https://www.terraform.io/docs/providers/null/resource.html
# This technique was stolen from https://stackoverflow.com/a/54523391/442773
resource "null_resource" "create-sql-user" {

  triggers = {
    db = azurerm_sql_database.x.id
  }

  # https://www.terraform.io/docs/provisioners/local-exec.html
  provisioner "local-exec" {

    # https://learn.microsoft.com/en-us/powershell/module/sqlserver/Invoke-Sqlcmd?view=sqlserver-ps
    # Adding the Managed Identity to the database as a user and assign it the roles of db_datareader and db_datawriter
    # NOTE: This is using the executing users credentials to connect to the db, this may not work if this is executed from a service principal within a devops pipeline
    # NOTE: this requires powershell to have the SqlServer module installed.  We tried a bunch of things to make it so it'd auto install the module but couldn't get it to work
    command = <<EOF
     Invoke-Sqlcmd `
       -Query "CREATE USER [${azurerm_app_service.x.name}] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [${azurerm_app_service.x.name}]; ALTER ROLE db_datawriter ADD MEMBER [${azurerm_app_service.x.name}];" `
       -ConnectionString "Server=tcp:${azurerm_sql_server.x.fully_qualified_domain_name},1433;Initial Catalog=${azurerm_sql_database.x.name};Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Integrated;" `
    EOF

    interpreter = ["PowerShell", "-Command"]
  }

}

the problem is the requirement to have Invoke-Sqlcmd available, but that's there only by Install-Module SqlServer out of band w/ terraform. I tried a few different things in the command to make this happen. like:

  # https://www.terraform.io/docs/provisioners/local-exec.html
  provisioner "local-exec" {

    # https://learn.microsoft.com/en-us/powershell/module/sqlserver/Invoke-Sqlcmd?view=sqlserver-ps
    # Adding the Managed Identity to the database as a user and assign it the roles of db_datareader and db_datawriter
    command = "Install-Module -Name SqlServer -AcceptLicense -SkipPublisherCheck -Force -AllowClobber -Scope CurrentUser;"

    interpreter = ["PowerShell", "-ExecutionPolicy", "Bypass", "-Command"]
  }

Error: Error running command 'Install-Module -Name SqlServer -AcceptLicense -SkipPublisherCheck -Force -AllowClobber -Scope CurrentUser;': exit status 1. Output: Install-Module : The 'Install-Module' command was found in the module 'PowerShellGet', but the module could not be loaded. For more information, run 'Import-Module PowerShellGet'.

so switching the command to

command = "Import-Module PowerShellGet; Install-Module -Name SqlServer -AcceptLicense -SkipPublisherCheck -Force -AllowClobber -Scope CurrentUser;"

but that lead to this output

Error: Error running command 'Import-Module PowerShellGet; Install-Module -Name SqlServer -AcceptLicense -SkipPublisherCheck -Force -AllowClobber -Scope CurrentUser;': exit status 1. Output: Import-Module : The specified module 'C:\program
files\powershell\6\Modules\PackageManagement\fullclr\Microsoft.PackageManagement.dll' was not loaded because no valid
module file was found in any module directory.
At line:1 char:1
+ Import-Module PowerShellGet; Install-Module -Name SqlServer -AcceptLi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (C:\program file...eManagement.dll:String) [Import-Module], FileNot
   FoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

PackageManagement\Get-PackageProvider : The term 'PackageManagement\Get-PackageProvider' is not recognized as the name
of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
verify that the path is correct and try again.
At C:\program files\powershell\6\Modules\PowerShellGet\PSModule.psm1:2926 char:26
+ ...        $nugetProvider = PackageManagement\Get-PackageProvider -ErrorA ...
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (PackageManagement\Get-PackageProvider:String) [], CommandNotFoundExcept
   ion
    + FullyQualifiedErrorId : CommandNotFoundException

PackageManagement\Get-PackageProvider : The term 'PackageManagement\Get-PackageProvider' is not recognized as the name
of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
verify that the path is correct and try again.
At C:\program files\powershell\6\Modules\PowerShellGet\PSModule.psm1:2940 char:40
+ ... ailableNugetProviders = PackageManagement\Get-PackageProvider -Name $ ...
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (PackageManagement\Get-PackageProvider:String) [], CommandNotFoundExcept
   ion
    + FullyQualifiedErrorId : CommandNotFoundException

Exception calling "ShouldContinue" with "2" argument(s): "Object reference not set to an instance of an object."
At C:\program files\powershell\6\Modules\PowerShellGet\PSModule.psm1:3115 char:8
+     if($Force -or $psCmdlet.ShouldContinue($shouldContinueQueryMessag ...
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : NullReferenceException

Install-Module : NuGet provider is required to interact with NuGet-based repositories. Please ensure that '2.8.5.201'
or newer version of NuGet provider is installed.
At line:1 char:30
+ ... erShellGet; Install-Module -Name SqlServer -AcceptLicense -SkipPublis ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Install-Module], InvalidOperationException
    + FullyQualifiedErrorId : CouldNotInstallNuGetProvider,Install-Module

one thing i'm wondering is if powershell versions 6 vs 5 is getting in the way here somehow...

Answer 1

EDIT: I believe you're using the wrong intepreter, try switching Powershell to pwsh to use powershell 6 as an interpreter.

  provisioner "local-exec" {
    ...
    interpreter = ["pwsh", "-Command"]
    ...
  }

---

I'm unsure about the underlying infrastructure where you require powershell to run. It seems like you're using powershell 6.

I also use a null-provider resource, calling into a script, passing in arguments and then creating a user. One advantage of that is that I know which powershell verson I am running (core) as the trigger command is pwsh.

I'll show you how I'm creating the null resource and a snippet of the script, in the hopes it might help.

Null resource for calling a script responsible for creating the user

resource "null_resource" "create_sql_user" {
  provisioner "local-exec" {
    command     = ".'${path.module}\\scripts\\create-sql-user.ps1' -password \"${random_password.sql_password.result}\" -username \"${var.sql_username}\" -sqlSaConnectionString \"${var.sql_server_connectionstring}\" -databaseName \"${azurerm_sql_database.db.name}\" "
    interpreter = ["pwsh", "-Command"]
  }
  depends_on = [azurerm_sql_database.db]
}

create-sql-user.ps1

[CmdletBinding()]
param (
    [Parameter(Mandatory = $true)]
    [string]
    $password,
    [Parameter(Mandatory = $true)]
    [string]
    $username,
    [Parameter(Mandatory = $true)]
    [string]
    $sqlSaConnectionString
)

Install-Module -Name SqlServer -Force

$sqlCmd = "CREATE LOGIN $username WITH PASSWORD = '$password'; ALTER LOGIN $username enable"
Invoke-Sqlcmd -ConnectionString $sqlSaConnectionString -Query $sqlCmd

...

Extras:

In this case I'm generating the sql password using the random resource. One can use similar approach for username:

resource "random_password" "sql_password" {
  length           = 54
  special          = true
  override_special = "$%@&*()"
}