Dynamically adding SQL Logins to DSC for Azure Automation Credentials

Question

I have a SQL Server on a VM being setup using a DSC file. I have been able to use SQL Login in the past (https://github.com/dsccommunity/SqlServerDsc/wiki/SqlLogin) but that only allows me to use it one at a time. I am trying to take all logins attached to my Azure Automation Credentials account and ensure the SQL Server has a login for each credential but I have not been able to figure out how to make it dynamic. I tried the following

$logins = Get-AzAutomationCredential -ResourceGroupName "resourceGroup" -AutomationAccountName "automationAccount"

    foreach($login in $logins.Where{$_.UserName -like "*dbuser*"})
    {
        Write-Output "Creating Login"
        Write-Output $login.Name
        SqlLogin 'Add_User'
        {
            Ensure = 'Present'
            Name = $login.Name
            LoginType = 'SqlLogin'
            InstanceName = 'Instance'
            LoginCredentials = Get-AutomationPSCredential -Name $login.Name
            LoginMustChangePassword = $false
            LoginPasswordExpirationEnabled = $false
            LoginPasswordPolicyEnforced = $true
        }
    }

But, it seems like I cannot use Powershell when I am setting up the DSC so I am wondering if it is even possible to make this dynamic. Does anyone know how to do this dynamically?

Answer 1

Every DSC-resource that is added to your configuration needs to have a unique name, so that might be where the problem is. Could you change your SqlLogin 'Add_User' to maybe have a username part of its name, so it's SqlLogin "Add_User_$($login.Username)" and see if that helps. It should be able to work as you have set it up. Though it is not completely dynamic, as it will create a mof-file that will then contain the variables you push in with the extra powershell scripting you are using.