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

Looking at the question again. Maybe the interpreter you're using is wrong. Maybe you can use pwsh to specify you want to use powershell core? I'll edit the answer. — pijemcolu
– pijemcolu, Commented Nov 27, 2019 at 19:15

pijemcolu · Accepted Answer · 2019-11-27 19:16:54Z

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 = "$%@&*()"
}

sorry took a while to circle back and confirm, but it worked much better once i used ps core!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related