DSC PowerShell MSFT_xSQLServerScript error on Test-TargetResource

emitodiaz commented 7 years ago

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`

johlju commented 7 years ago

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

johlju commented 7 years ago

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 commented 7 years ago

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 commented 7 years ago

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

emitodiaz commented 7 years ago

exactly: 'serverXX\instance'.

johlju commented 7 years ago

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

johlju commented 7 years ago

@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 commented 7 years ago

@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 commented 7 years ago

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 commented 7 years ago

If I run directly from the server

emitodiaz commented 7 years ago

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 commented 7 years ago

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 commented 7 years ago

@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. :cry:

johlju commented 7 years ago

@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 commented 7 years ago

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 :grin: ...

emitodiaz commented 7 years ago

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 commented 7 years ago

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

johlju commented 7 years ago

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

Thanks!

dsccommunity / SqlServerDsc

DSC PowerShell MSFT_xSQLServerScript error on Test-TargetResource #252