Adding SQL Logins to DSC Dynamically for Azure Automation Credentials

Problem

I’m using a DSC file to start up a SQL Server on a virtual machine. I’ve previously been able to utilize SQL Login (https://github.com/dsccommunity/SqlServerDsc/wiki/SqlLogin), but only one at a time. I’m wanting to take all of the logins associated with my Azure Automation Credentials account and ensure that each credential has its own login on the SQL Server, but I’m not sure how to make it dynamic. The following is what I tried:

$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 appears that I won’t be able to utilize Powershell when configuring the DSC, so I’m not sure whether this is really doable. Is there a way to accomplish this dynamically?

Asked by lottol

Solution #1

Every DSC-resource you add to your configuration needs to have its own name, therefore that could be the issue. Could you update the name of your SqlLogin’Add User’ to include a username, such as SqlLogin “Add User $($login.Username)” and see if it helps? It should be able to function in the manner that you have set it up. Though it is not totally dynamic, as it will build a mof-file that will include the variables you add with the additional powershell scripting.

Answered by Setorica

Post is based on https://stackoverflow.com/questions/71013004/dynamically-adding-sql-logins-to-dsc-for-azure-automation-credentials