SqlServerDsc/SqlScriptQuery Execution with permission denied

[ognif]

Problem description

I try to execute some SQL Command on an different Server (MSX). With DSCv3 I get always permission denied error. Also if I use Credential parameter i get the same result. The Resource looks like:

If I use the same with DSCv1 and a fresh generated MOF file and the same credentials I get success and the job was added. The MOF file was also executed with DSCv3.

/*
@TargetNode='MSSQL176'
@GeneratedBy=xxx
@GenerationDate=02/16/2026 17:56:54
@GenerationHost=MSSQL176
*/

instance of MSFT_Credential as $MSFT_Credential1ref
{
Password = "1234567890";
 UserName = "domain/installuser";

};

instance of DSC_SqlScriptQuery as $DSC_SqlScriptQuery1ref
{
Id = "Unit_Test";
 ServerName = "MSSQL002";
 QueryTimeout = 300;
 PsDscRunAsCredential = $MSFT_Credential1ref;
 ResourceID = "[SqlScriptQuery]MSXTest";
 SourceInfo = "C:\\Temp\\set-mofmsx.ps1::22::9::SqlScriptQuery";
 TestQuery = "IF(NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers JOIN msdb.dbo.sysjobs ON sysjobs.job_id = sysjobservers.job_id JOIN msdb.dbo.systargetservers ON systargetservers.server_id = sysjobservers.server_id WHERE server_name = 'MSSQL176' AND name = 'agent_alert_tempdb_size')) THROW 51000,'Job agent_alert_tempdb_size not published on MSSQL176',1;";
 GetQuery = "SELECT name,server_name FROM msdb.dbo.sysjobservers JOIN msdb.dbo.sysjobs ON sysjobs.job_id = sysjobservers.job_id JOIN msdb.dbo.systargetservers ON systargetservers.server_id = sysjobservers.server_id WHERE server_name = 'MSSQL176' AND name = 'agent_alert_tempdb_size'";
 SetQuery = "use msdb; exec sp_add_jobserver @job_name = 'agent_alert_tempdb_size' , @server_name = 'MSSQL176' ;";
 ModuleVersion = "17.5.1";
 ModuleName = "SqlServerDsc";
 Encrypt = "Optional";
 InstanceName = "MSSQLSERVER";

 ConfigurationName = "SqlMSXTest";

};
instance of OMI_ConfigurationDocument


                    {
 Version="2.0.0";
 

                        MinimumCompatibleVersion = "2.0.0";
 

                        CompatibleVersionAdditionalProperties= {"Omi_BaseResource:ConfigurationName"};
 

                        Author="xxxx";
 

                        GenerationDate="02/16/2026 17:56:54";
 

                        GenerationHost="MSSQL176";
 

                        Name="SqlMSXTest";


                    };

Executed with

		{
		  "type": "Microsoft.DSC.Transitional/WindowsPowerShellScript",
		  "apiVersion": "0.1.0",
		  "name": "ConfigureMSX1",
		  "properties": {
			"getScript": "C:\\Temp\\get-mofmsx.ps1",
			"setScript": "C:\\Temp\\set-mofmsx.ps1",
			"testScript": "C:\\Temp\\test-mofmsx.ps1"
		  }
		}

PS1:

# Korrekte Verwendung von ConfigurationData
$ConfigData = @{
    AllNodes = @(
        @{
            NodeName = "MSSQL176"
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser = $true
        }
    )
}

Configuration SqlMSXTest {
    param(
        [Parameter(Mandatory = $true)]
        [PSCredential]$SqlCredential
    )
    
    Import-DscResource -ModuleName SqlServerDsc -ModuleVersion 17.5.1
    
    # WICHTIG: Node muss den tatsächlichen Namen verwenden
    Node "MSSQL176" {
        SqlScriptQuery 'MSXTest' {
			SetQuery = "use msdb; exec sp_add_jobserver @job_name = 'agent_alert_tempdb_size' , @server_name = 'MSSQL176' ;"
            GetQuery = "SELECT name,server_name FROM msdb.dbo.sysjobservers JOIN msdb.dbo.sysjobs ON sysjobs.job_id = sysjobservers.job_id JOIN msdb.dbo.systargetservers ON systargetservers.server_id = sysjobservers.server_id WHERE server_name = 'MSSQL176' AND name = 'agent_alert_tempdb_size'"
            ServerName                    = "MSSQL002"
            InstanceName                  = "MSSQLSERVER"
            TestQuery = "IF(NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers JOIN msdb.dbo.sysjobs ON sysjobs.job_id = sysjobservers.job_id JOIN msdb.dbo.systargetservers ON systargetservers.server_id = sysjobservers.server_id WHERE server_name = 'MSSQL176' AND name = 'agent_alert_tempdb_size')) THROW 51000,'Job agent_alert_tempdb_size not published on MSSQL176',1;";
			QueryTimeout = "300"
			Encrypt = "Optional"
			Id = "Unit_Test"
            PsDscRunAsCredential          = $SqlCredential
        }
    }
}

# Credential erstellen
$cred = New-Object System.Management.Automation.PSCredential(
    "domain\installuser", 
    (ConvertTo-SecureString "1234567890" -AsPlainText -Force)
)

# Output-Verzeichnis
$outputPath = "C:\Temp\DSC\Output"
New-Item -ItemType Directory -Path $outputPath -Force -ErrorAction SilentlyContinue

# Konfiguration mit ConfigData ausführen
SqlMSXTest -SqlCredential $cred -ConfigurationData $ConfigData -OutputPath $outputPath

# Prüfe und wende an
if (Test-Path "$outputPath\MSSQL176.mof") {
    Start-DscConfiguration -Path $outputPath -Wait -Force -Verbose
    Write-Host "Success!" -ForegroundColor Green
} else {
    Write-Error "MOF file was not created correctly"
}
````

So why the DSC Resource SqlServerDsc/SqlScriptQuery even with Credential Properties cannot connect to the server? But a MOF file can do.

### Verbose logs

```text
2026-02-16T17:14:58.652838Z ERROR PID 10288: Exception: PowerShell DSC resource DSC_SqlScriptQuery  failed to execute Test-TargetResource functionality with error message: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: Login failed for user 'mSQLDSCinstall@dst.tk-inline.net'.
2026-02-16T17:14:58.733703Z ERROR Failed to run process 'powershell': Command: Resource 'powershell' [exit code 1] manifest description: Error
2026-02-16T17:14:58.738354Z ERROR Error: Command: Resource 'powershell' [exit code 1] manifest description: Error
```

### DSC configuration

```powershell
{
            "type": "SqlServerDsc/SqlScriptQuery",
            "name": "SqlScript 3026 error",
            "properties": {
                "Id": "Unit_Test",
                "ServerName": "MSSQL002",
                "InstanceName": "MSSQLSERVER",
                "SetQuery": "use msdb; exec sp_add_jobserver @job_name = 'agent_alert_tempdb_size' , @server_name = 'MSSQL176' ;",
                "TestQuery": "IF(NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers JOIN msdb.dbo.sysjobs ON sysjobs.job_id = sysjobservers.job_id JOIN msdb.dbo.systargetservers ON systargetservers.server_id = sysjobservers.server_id WHERE server_name = 'MSSQL176' AND name = 'agent_alert_tempdb_size')) THROW 51000,'Job agent_alert_tempdb_size not published on MSSQL176',1;",
                "GetQuery": "SELECT name,server_name FROM msdb.dbo.sysjobservers JOIN msdb.dbo.sysjobs ON sysjobs.job_id = sysjobservers.job_id JOIN msdb.dbo.systargetservers ON systargetservers.server_id = sysjobservers.server_id WHERE server_name = 'MSSQL176' AND name = 'agent_alert_tempdb_size'",
                "QueryTimeout": 300,
                "Encrypt": "Optional",
                "Credential": {
                    "UserName": "domain\\installuser",
                    "Password": "1234567890"
                }
            }
        },
```

### Suggested solution

I report an issue not a solution. So the solution have to be a working Credentail function.

### SQL Server edition and version

```text
MS SQL Server 16
DSCv1 and also DSCv3
```

### SQL Server PowerShell modules

```text
SqlServerDsc -ModuleVersion 17.5.1
sqlserver 22.4.5.1
```

### Operating system

```text
Windows Server 2025
```

### PowerShell version

```text
Powershell 7.5.3
```

### SqlServerDsc version

```text
SqlServerDsc -ModuleVersion 17.5.1
```

[johlju]

In the https://github.com/dsccommunity/SqlServerDsc/wiki/SqlScript is says that Credential is used only for sing SQL Server Authentication. Is the user you trying to connect with is a SQL Authentication login? Since you have domain\ in the configuration above my guess the login is Windows Authentication and that is why it fails. In the MOF-file you assign the credentials to PsDscRunAsCredential and that is why it works using the MOF.

The credentials to authenticate with, using SQL Server Authentication. To authenticate using Windows Authentication assign the credentials to the built-in parameter PsDscRunAsCredential. If neither of the parameters Credential or PsDscRunAsCredential are assigned, then the SYSTEM account will be used to authenticate using Windows Authentication.

[johlju]

Maybe this function Invoke-SqlScript need to support passing Credential to Invoke-Sqlcmd's Credential parameter depending on what time of credential it is (support a LoginType too?). Unclear if that Credential parameter in Invoke-Sqlcmd supports Windows authentication though, but maybe as those used now clearly says SQL Authentication.

[ognif]

What does this mean for me? No matter which properties I use, "PsDscRunAsCredential" or "Credential," I can't execute this on another server where I need a login. The login is there, because DSCv1 can execute the query, but DSCv3 can't. It's getting ridiculous that DSCv3 can't do anything and you constantly have to build workarounds. Connecting to an MSSQL server is, in my opinion, the fundamental function of DSC. The fact that DSCv3 can't do this, and apparently none of the developers think it's necessary to fix it, means to me as a user that DSCv3 is a complete design flaw.