DSC Script Resource Error

DSC
1

Hi!.
I’m having the following error:

El recurso de DSC de PowerShell MSFT_ScriptResource no pudo ejecutar la funcionalidad de Set-TargetResource con el mensaje de error: El ensamblado de modo mixto se ha compilado con la
versión ‘v2.0.50727’ del runtime y no se puede cargar en el runtime 4.0 sin información de configuración adicional.
+ CategoryInfo : InvalidOperation: (:slight_smile: , CimException
+ FullyQualifiedErrorId : ProviderOperationExecutionFailure
+ PSComputerName : serverXX

El servicio WS-Management no puede procesar la solicitud. El servicio WMI o el proveedor de WMI devolvió un error desconocido: HRESULT 0x800706be
+ CategoryInfo : NotSpecified: (root/Microsoft/…gurationManager:String) , CimException
+ FullyQualifiedErrorId : HRESULT 0x800706be

The Script execute : invoke-sqlcmd its very simple:

Configuration ScriptTest
{
param (
[string] $ComputerName
)
Import-DscResource –ModuleName ‘PSDesiredStateConfiguration’

node $ComputerName {

    Script SQLEmito {

        GetScript = {
            @{
                GetScript = $GetScript
                SetScript = $SetScript
                TestScript = $TestScript
                Result = $True
            }
        }

        SetScript = {
            write-verbose "running ConfigurationFile.TestScript";
            try {
                Import-Module -Name SQLPS -DisableNameChecking -Verbose:$False -ErrorAction Stop # SQLPS has unapproved verbs, disable checking to ignore Warnings.
            }
            catch
            {
                throw $_.Exception
            }
            #$sql = "ALTER LOGIN [pepito] WITH DEFAULT_DATABASE=[PRUEBASDB], DEFAULT_LANGUAGE=[us_english];"
            $sql = "SELECT GETDATE();"
            Invoke-Sqlcmd -Query $sql -ServerInstance "SERVER26\SQL2014SE" -Database Master

        }

        TestScript = { return $False }
    }
}

}

Any Ideas?
If I try the Invoke-sqlcmd on the server works.
Thanks in advance.
Emito.

2

Unfortunately, you’re going to have to translate the error into English for me ;). And it would help if you formatted your code in PRE blocks, as indicated in the bullet list above the reply textbox.

3

Error Message 1

PowerShell provider MSFT_ScriptResource failed to execute Test-TargetResource functionality with error message:
Mixed mode assembly is built against version 'v2.0.50727' of the runtime
    and cannot be loaded in the 4.0 runtime without additional configuration information.

Error Message 2

"The WS-Management service cannot process the request. The WMI service or the WMI provider returned an unknown error: HRESULT 0x800706be"
4

So, it looks like you’ve got a very old SQLPS module that isn’t built against v4 of the .NET Framework, so the LCM can’t load the module. SQLPS wasn’t ever designed to be used in this context, so there might be some trouble getting it to work.

But you shouldn’t need SQLPS. It’s just as easy to use the .NET classes to directly connect to your database.

$connection = New-Object -Type System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "insert connection string here"
$connection.Open()
$command = New-Object -Type System.Data.SqlClient.SqlCommand
$command.Connection = $connection
$command.CommandText = "INSERT SQL QUERY HERE"
$command.ExecuteNonQuery()
$connection.Close()

Something along those lines. I’m typing that off the top of my head, so please pardon any typos.

5

Hi Don!
I’m using the SQLPS included in Microsoft® SQL Server® 2016 Feature Pack.

Finally I’m solve the error using: sqlcmd.exe, but your code it’s valid too.

Thanks you for your time and happy new year :slight_smile:
Emito.