Unable to add windows login using DSC

Question

After I build the Azure VM and joined to a domain, I need to add a windows user to SQL Server login. I am using DSC for this, the code is

Configuration SQLConfig 
{
    param(
        # Parameter help description
        [Parameter(Mandatory =$true)][string]$ServerName,
        [System.Management.Automation.PSCredential]  $Credential

    )

    Import-DscResource -ModuleName SqlServerDsc
    Import-DscResource -ModuleName PSDesiredStateConfiguration

    Node localhost
    {
        SqlServerLogin addsysadmin
        {
            Ensure               = 'Present'
            Name                 = $Credential.UserName
            LoginType            = 'WindowsUser'
            ServerName           = $ServerName
            InstanceName         = 'MSSQLSERVER'
            PsDscRunAsCredential = $Credential
            LoginMustChangePassword = $false

        }

    }
}

However, the deployment fails with the following error.

PowerShell DSC resource MSFT_SqlServerLogin failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance 'localhost'. + CategoryInfo : InvalidOperation: (root/Microsoft/...gurationManager:String) [], CimException + FullyQualifiedErrorId : ProviderOperationExecutionFailure + PSComputerName : localhost

It looks like the DSC is trying to connect to SQL server as the user I am trying to add. What I mean is, if I try to add MyDomain\xxx.zzz, then I get the error in SQL Log

"Login failed for user 'MyDomain\xxx.zzz'. Reason: Could not find a login matching the name provided. [CLIENT: ]"

What am I doing wrong?

Thanks

Answer 1

It looks like the DSC is trying to connect to SQL server as the user I am trying to add.

Your conclusion is right. PsDscRunAsCredential = $Credential instructs the DSC Local Configuration Manager to use $credential to execute the commands. You might want to specify an existing SQL login with sufficient permission to add additional logins to PsDscRunAsCredential.