DSC PowerShell MSFT_xSQLServerScript error on Test-TargetResource #252

emitodiaz · 2016-12-21T19:00:13Z

Hi, I'm trying the xSQLServerScript and I don't know if it's an error or I'm making a mistake calling the resource.
Thanks in advance, Emiliano.

Error:

DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] SQLPS module imported. DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Popping location back to what it was before importing SQLPS module. DETALLADO: [SERVER26]: LCM: [ FinalizarProbar ] [[xSQLServerScript]SqlSettings] en 0.5780 segundos. El recurso de DSC de PowerShell MSFT_xSQLServerScript generó uno o más errores de no terminación al ejecutar la funcionalidad de Test-TargetResource. Estos errores se registran en el canal ETW llamado Microsoft-Windows-DSC/operativo. Consulte este canal para obtener más detalles. + CategoryInfo : InvalidOperation: (:) [], CimException + FullyQualifiedErrorId : NonTerminatingErrorFromProvider

Details
`Powershell version on Windows 2012 R2, 64 BITS, Spanish.
PSVersion 4.0
WSManStackVersion 3.0
SerializationVersion 1.1.0.1
CLRVersion 4.0.30319.36366
BuildVersion 6.3.9600.17400
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0}
PSRemotingProtocolVersion 2.2

Module: xSQLServer versión: 4.0.0.0
`

PS C:\Windows\system32> Start-DscConfiguration -Path "E:\DSC Resources" -Wait -Verbose -Force -Debug
DETALLADO: Realice la operación 'Invocar método de CIM' con los parámetros siguientes, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' =
root/Microsoft/Windows/DesiredStateConfiguration'.
DETALLADO: Se recibió una llamada de método de LCM desde el equipo DES001 con el SID de usuario S-1-5-21-3370056077-3368664693-1737765451-1238.
DETALLADO: [SERVER26]: LCM: [ IniciarEstablecer]
DETALLADO: [SERVER26]: LCM: [ IniciarRecurso ] [[xSQLServerScript]SqlSettings]
DETALLADO: [SERVER26]: LCM: [ IniciarProbar ] [[xSQLServerScript]SqlSettings]
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Unloading SQLPS module.
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] SQLPS module changes CWD to SQLSERVER:\ when loading, pushing location to pop it when module is loaded.
DETALLADO: [SERVER26]: [[xSQLServerScript]SqlSettings] 2016-12-21_13-12-54: Importing SQLPS module.
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Called Initialization method
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension DAC
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension DataCollection
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension SQLPolicy
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension Utility
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension SQLRegistration
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension SQLSERVER
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension SQL
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension SSIS
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension XEvent
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension DatabaseXEvent
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Starting extension SQLAS
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] ItemExists: SQLSERVER:
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] ItemExists: SQLSERVER:
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] SQLPS module imported.
DEPURACIÓN: [SERVER26]: [[xSQLServerScript]SqlSettings] Popping location back to what it was before importing SQLPS module.
DETALLADO: [SERVER26]: LCM: [ FinalizarProbar ] [[xSQLServerScript]SqlSettings] en 0.5780 segundos.
El recurso de DSC de PowerShell MSFT_xSQLServerScript generó uno o más errores de no terminación al ejecutar la funcionalidad de Test-TargetResource. Estos errores se registran en el canal
ETW llamado Microsoft-Windows-DSC/operativo. Consulte este canal para obtener más detalles.
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : NonTerminatingErrorFromProvider
+ PSComputerName : SERVER26

La función SendConfigurationApply no se completó correctamente.
+ CategoryInfo : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
+ FullyQualifiedErrorId : MI RESULT 1
+ PSComputerName : SERVER26

DETALLADO: La operación 'Invocar método de CIM' se completó.
DETALLADO: El tiempo que tarda el trabajo de configuración en completarse es de 0.688 segundos

On the EventViewer I see the next two errors:
`Trabajo {43D6E94B-C7AB-11E6-80D1-00155D03053C}:
Este evento indica que se generó un error de no terminación cuando DSCEngine ejecutaba Test-TargetResource en el recurso de DSC MSFT_xSQLServerScript. FullyQualifiedErrorId: System.IO.FileLoadException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand. ErrorMessage: 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..

Trabajo {43D6E94B-C7AB-11E6-80D1-00155D03053C}:
Este evento indica un error cuando LCM procesa la configuración. ErrorId: 0x1. ErrorDetail: La función SendConfigurationApply no se completó correctamente.. ResourceId: [xSQLServerScript]SqlSettings. SourceInfo: ::14::9::xSQLServerScript. ErrorMessage: El recurso de DSC de PowerShell MSFT_xSQLServerScript generó uno o más errores de no terminación al ejecutar la funcionalidad de Test-TargetResource. Estos errores se registran en el canal ETW llamado Microsoft-Windows-DSC/operativo. Consulte este canal para obtener más detalles..
`

My testing script:
`configuration SQLSettings
{
param
(
[Parameter(Mandatory=$false)]
[ValidateNotNullorEmpty()]
[PsCredential] $CredencialUSR
)

Import-DscResource -ModuleName 'xSQLServer'

Node $AllNodes.NodeName
{ 
     xSQLServerScript SqlSettings
    {
        ServerInstance = $Node.InstanceName
        SetFilePath = "C:\temp\Set-SQlsettings.sql"
        TestFilePath = "C:\temp\Test-SQlsettings.sql"
        GetFilePath = "C:\temp\Get-SQlsettings.sql"
        Credential = $CredencialUSR
        Variable = @("FilePath=C:\temp\")
    } 
}

}

$configData = @{
AllNodes = @(
@{
NodeName = 'SERVER26'
InstanceName = 'SERVER26\SQL2014SE'
PsDscAllowPlainTextPassword= $true
PSDscAllowDomainUser = $true
}
)
}

SQLSettings -ConfigurationData $configData -OutputPath "E:\DSC Resources" -CredencialUSR $cred`

The text was updated successfully, but these errors were encountered:

johlju · 2016-12-22T15:58:12Z

I will look into this after Christmas, unless someone else have had time to help you with this. 😄

johlju · 2016-12-29T13:17:56Z

I think I see the problem. In the parameter ServerInstance it seems you only set the instance name? If so, you should also set server name. See below snippet from the README.md.

ServerInstance: (Required) The name of an instance of the Database Engine. For default instances, only specify the computer name. For named instances, use the format ComputerName\InstanceName.

Please let me know if this helped, otherwise I dig in further.

emitodiaz · 2016-12-29T14:01:24Z

Hi @johlju .
I already use "ServerInstance = $Node.InstanceName" in the Script.

I'm even trying with the Script Resource to execute "invoke-sqlcmd" but is not working either. I have a similar error.

Any ideas?

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: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : server26
 
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

johlju · 2016-12-29T14:31:31Z

What are $Node.InstanceName set to? Is it a string containing something like 'localhost\myInstance'?

emitodiaz · 2016-12-29T14:36:00Z

exactly: 'serverXX\instance'.

johlju · 2016-12-29T14:37:26Z

Okay, thank you. I'm gonna test your config in my lab. See why/what are failing. 😄

johlju · 2016-12-29T17:23:23Z

@emitodiaz I got the same error. If I run this row by row directly on the server I get a error message saying 'Login failed...'.

It seems by adding parameter Credential the resource will do SQL authentication. It says that in the README also.

Credential: Specifies the credentials for making a SQL Server Authentication connection to an instance of the Database Engine.

What it does not say is that If the parameter is left out (or set to $null) then the resource will do Windows Authentication. Also, by default the resource is run under account SYSTEM, but if you set built-in parameter PsDscRunAsCredential to $CredencialUSR then the resource is run under that credential and will logon using Windows Authentication.

So there are two thing we need to fix (I will submit a new issue for this)

Add to the description of the parameter Credential how to use Windows Authentication. And that it is controlled by parameter PsDscRunAsCredential
Secondly see if we can get a better error message from the resource than "CIM error".

If you actually want to use SQL Authentication, then this is not the issue. Then I have to dig further. 😄

johlju · 2016-12-29T18:02:52Z

@emitodiaz Above I wrote that I got the same error, that is not entirely correct. I got the following error when trying to use SQL Authentication.

PowerShell DSC resource MSFT_xSQLServerScript  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 u
ser 'COMPANY\sqladm'. 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure

When removing Credential parameter the resource works.

johlju · 2016-12-29T18:13:37Z

The error you are getting:

Este evento indica que se generó un error de no terminación cuando DSCEngine ejecutaba Test-TargetResource en el recurso de DSC MSFT_xSQLServerScript. FullyQualifiedErrorId: System.IO.FileLoadException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand. ErrorMessage: 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.

Translated to English:

This event indicates that a non-termination error was generated when DSCEngine was running Test-TargetResource on the MSFT_xSQLServerScript DSC resource. FullyQualifiedErrorId: System.IO.FileLoadException, Microsoft.SqlServer.Management.PowerShell.GetScriptCommand. ErrorMessage: The mixed-mode assembly has been compiled with the 'v2.0.50727' version of the runtime and can not be loaded into runtime 4.0 without additional configuration information.

This is very strange.

Could you run this directly on the server? Do you get any errors?

Import-Module SQLPS
Invoke-Sqlcmd -ServerInstance 'localhost\MyInstance' -Inputfile 'C:\temp\Get-SQlsettings.sql' -ErrorAction Stop

emitodiaz · 2016-12-29T18:25:10Z

If I run directly from the server

emitodiaz · 2016-12-29T18:31:42Z

By the way, I also tried with the Script Resource (not using xSQLServer), executing directly invoke-sqlCmd and I get the same 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: (:) [], 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
`

Maybe something with SQLPS module? (I'm using the one inside Microsoft® SQL Server® 2016 Feature Pack)

johlju · 2016-12-30T16:38:33Z

I set up a new server with Windows Server 2012 R2 (English), SQL Server 2014 and PowerShell 4.0. Using SQLPS from SQL Server 2014.
And it seems it is the combination of Invoke-Sqlcmd with PowerShell 4.0 from DSC (LCM) that is the culprit. I do not why. 😞

Running the following configuration

xSQLServerScript 'SQL2014-RunSQLScript'
{
    ServerInstance = 'sqltest3.company.local\SQL2014'
    SetFilePath = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript.sql'
    TestFilePath = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript.sql'
    GetFilePath = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript.sql'
}

I got these errors:

Event ID: 4103
This event indicates that a non-terminating error was thrown when DSCEngine was executing Test-TargetResource on MSFT_xSQLServerScript provider. FullyQualifiedErrorId is System.IO.FileLoadException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand. ErrorMessage is 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..

Event ID: 4097
This event indicates that failure happens when LCM is processing the configuration. ErrorId is 0x1. ErrorDetail is The SendConfigurationApply function did not succeed.. ResourceId is [xSQLServerScript]SQL2014-RunSQLScript and SourceInfo is C:\DSCTemp\SQLConfig.ps1::157::13::xSQLServerScript. ErrorMessage is The PowerShell provider MSFT_xSQLServerScript threw one or more non-terminating errors while running the Test-TargetResource functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details..

Event ID: 4131
Job {78E73D56-5423-42A7-A2B6-99F2C7E49D3E} : DSC Engine Error : Error Message The SendConfigurationApply function did not succeed. Error Code : 1

I tested to execute the resource functions manually directly from PowerShell prompt (ISE). That worked without any problem, so that rules out problems using the SQLPS-module with PowerShell 4.0.
After debugging the resource, the error occurs only when LCM is running the resource and Invoke-Sqlcmd is called in the Invoke-SqlScript helper function.
@emitodiaz you also experienced the same behavior when testing from the Script resource.

After installing PowerShell 5.0 everything works as expected

VERBOSE: [SQLTEST3]: LCM:  [ Start  Resource ]  [[xSQLServerScript]SQL2014-RunSQLScript]
VERBOSE: [SQLTEST3]: LCM:  [ Start  Test     ]  [[xSQLServerScript]SQL2014-RunSQLScript]
VERBOSE: [SQLTEST3]:                            [[xSQLServerScript]SQL2014-RunSQLScript] 2016-12-30_17-15-50: Importing SQLPS module.
VERBOSE: [SQLTEST3]:                            [[xSQLServerScript]SQL2014-RunSQLScript] Did not find database [MyScriptDatabase1]
VERBOSE: [SQLTEST3]: LCM:  [ End    Test     ]  [[xSQLServerScript]SQL2014-RunSQLScript]  in 2.0900 seconds.
VERBOSE: [SQLTEST3]: LCM:  [ Start  Set      ]  [[xSQLServerScript]SQL2014-RunSQLScript]
VERBOSE: [SQLTEST3]:                            [[xSQLServerScript]SQL2014-RunSQLScript] 2016-12-30_17-15-53: Importing SQLPS module.
VERBOSE: [SQLTEST3]: LCM:  [ End    Set      ]  [[xSQLServerScript]SQL2014-RunSQLScript]  in 1.9220 seconds.
VERBOSE: [SQLTEST3]: LCM:  [ End    Resource ]  [[xSQLServerScript]SQL2014-RunSQLScript]
VERBOSE: [SQLTEST3]: LCM:  [ End    Set      ]

As of this time I have no solution how to use Invoke-Sqlcmd with a DSC resource with PowerShell 4.0. Only solution so far is to install PowerShell 5.0.

PlagueHO · 2017-01-01T03:24:53Z

@johlju - I haven't run into anything like this before unfortunately. But there are lot of different behaviors with the LCM between WMF 4.0 and WMF 5.0. If in doubt, upgrade to WMF5.0 is our general solution to this sort of thing. Sorry I can't be of more assitance. 😢

johlju · 2017-01-01T11:36:41Z

@emitodiaz I'm sorry we can't solve this one for you. Could you try installing PowerShell 5.0 and verify that it works for you?

@PlagueHO Thanks for taking the time to look at this! You helped! Now I know this probably isn't some basic error.

I did more testing yesterday and came up short again. To know whats going on here I would need more knowledge, so I have to put this on the "unsolvable today" pile for now. 😞

PlagueHO · 2017-01-01T20:08:35Z

Always a pleasure @johlju ! I do hate having to put things on the unsolvable pile too as well. But if WMF 5.0 is a solution to a problem 😁 ...

emitodiaz · 2017-01-03T11:48:31Z

Thanks You @johlju and @PlagueHO for your time!!!.
I'll try with WMF 5 and verify if it's work.
Thanks again, good year!

emitodiaz · 2017-01-09T15:52:42Z

Hi!, sorry for the delay.
After trying with WMF 5 I could say that: IT'S WORKING!
Thanks @johlju

johlju · 2017-01-09T18:54:39Z

@emitodiaz Glad to hear it! Happy to help. Closing this issue.

Thanks!

johlju added the question The issue is a question. label Dec 21, 2016

emitodiaz closed this as completed Dec 29, 2016

vors removed the question The issue is a question. label Dec 29, 2016

emitodiaz reopened this Dec 29, 2016

johlju added the question The issue is a question. label Dec 29, 2016

johlju mentioned this issue Dec 30, 2016

xSQLServerScript: Fails when calling Invoke-Sqlcmd using PowerShell 4.0 from LCM #273

Closed

johlju closed this as completed Jan 9, 2017

vors removed the question The issue is a question. label Jan 9, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

DSC PowerShell MSFT_xSQLServerScript error on Test-TargetResource #252

DSC PowerShell MSFT_xSQLServerScript error on Test-TargetResource #252

emitodiaz commented Dec 21, 2016

johlju commented Dec 22, 2016

johlju commented Dec 29, 2016

emitodiaz commented Dec 29, 2016 •

edited

Loading

johlju commented Dec 29, 2016

emitodiaz commented Dec 29, 2016

johlju commented Dec 29, 2016 •

edited

Loading

johlju commented Dec 29, 2016

johlju commented Dec 29, 2016

johlju commented Dec 29, 2016

emitodiaz commented Dec 29, 2016

emitodiaz commented Dec 29, 2016

johlju commented Dec 30, 2016 •

edited

Loading

PlagueHO commented Jan 1, 2017

johlju commented Jan 1, 2017

PlagueHO commented Jan 1, 2017

emitodiaz commented Jan 3, 2017

emitodiaz commented Jan 9, 2017

johlju commented Jan 9, 2017

DSC PowerShell MSFT_xSQLServerScript error on Test-TargetResource #252

DSC PowerShell MSFT_xSQLServerScript error on Test-TargetResource #252

Comments

emitodiaz commented Dec 21, 2016

johlju commented Dec 22, 2016

johlju commented Dec 29, 2016

emitodiaz commented Dec 29, 2016 • edited Loading

johlju commented Dec 29, 2016

emitodiaz commented Dec 29, 2016

johlju commented Dec 29, 2016 • edited Loading

johlju commented Dec 29, 2016

johlju commented Dec 29, 2016

johlju commented Dec 29, 2016

emitodiaz commented Dec 29, 2016

emitodiaz commented Dec 29, 2016

johlju commented Dec 30, 2016 • edited Loading

PlagueHO commented Jan 1, 2017

johlju commented Jan 1, 2017

PlagueHO commented Jan 1, 2017

emitodiaz commented Jan 3, 2017

emitodiaz commented Jan 9, 2017

johlju commented Jan 9, 2017

emitodiaz commented Dec 29, 2016 •

edited

Loading

johlju commented Dec 29, 2016 •

edited

Loading

johlju commented Dec 30, 2016 •

edited

Loading