SqlSetup: PowerShell DSC resource MSFT_SqlSetup failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to import SqlServer module. (SQLCOMMON0031)

masroorac commented 4 years ago

SQL Server edition and version the target node is running

SQL Server PowerShell modules present on the target node

Name Version Path

SqlServer 21.1.18221 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18221\SqlServer.psd1 SQLPS 1.0 C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Datacenter OsOperatingSystemSKU : DatacenterServerEdition OsArchitecture : 64-bit WindowsBuildLabEx : 14393.3564.amd64fre.rs1_release.200303-1942 OsLanguage : en-US OsMuiLanguages : {en-US}

Version of the DSC module that was used

Name Version Path

SqlServerDsc 13.4.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.4.0\SqlServerDsc.psd1

I am trying to install the MSSQL 2016 on Microsoft 2016 Datacenter OS. My installation goes fine and Summary.txt file state Final result: Passed, but I am receiving below error:

PowerShell DSC resource MSFT_SqlSetup failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to import SqlServer module. (SQLCOMMON0031) ---> System.IO.FileNotFoundException: Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. at System.Reflection.RuntimeAssembly.GetExportedTypes(RuntimeAssembly assembly, ObjectHandleOnStack retTypes) at System.Reflection.RuntimeAssembly.GetExportedTypes() at System.Management.Automation.Runspaces.PSSnapInHelpers.GetAssemblyTypes(Assembly assembly, String name) at System.Management.Automation.Runspaces.PSSnapInHelpers.AnalyzeModuleAssemblyWithReflection(Assembly assembly, String name, PSSnapInInfo psSnapInInfo, PSModuleInfo moduleInfo, Boolean isModuleLoad, Dictionary2& cmdlets, Dictionary2& aliases, Dictionary2& providers, String helpFile, Type& randomCmdletToCheckLinkDemand, Type& randomProviderToCheckLinkDemand) at System.Management.Automation.Runspaces.PSSnapInHelpers.AnalyzePSSnapInAssembly(Assembly assembly, String name, PSSnapInInfo psSnapInInfo, PSModuleInfo moduleInfo, Boolean isModuleLoad, Dictionary2& cmdlets, Dictionary2& aliases, Dictionary2& providers, String& helpFile) at System.Management.Automation.Runspaces.InitialSessionState.ImportCmdletsFromAssembly(Assembly assembly, PSModuleInfo module) at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadBinaryModule(PSModuleInfo parentModule, Boolean trySnapInName, String moduleName, String fileName, Assembly assemblyToLoad, String moduleBase, SessionState ss, ImportModuleOptions options, ManifestProcessingFlags manifestProcessingFlags, String prefix, Boolean loadTypes, Boolean loadFormats, Boolean& found, String shortModuleName, Boolean disableFormatUpdates) at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadModule(PSModuleInfo parentModule, String fileName, String moduleBase, String prefix, SessionState ss, Object privateData, ImportModuleOptions& options, ManifestProcessingFlags manifestProcessingFlags, Boolean& found, Boolean& moduleFileFound) at Microsoft.PowerShell.Commands.ImportModuleCommand.ImportModule_LocallyViaName(ImportModuleOptions importModuleOptions, String name) at Microsoft.PowerShell.Commands.ImportModuleCommand.ProcessRecord() at System.Management.Automation.CommandProcessor.ProcessRecord() --- End of inner exception stack trace ---

CategoryInfo : InvalidOperation: (:) [], CimException
FullyQualifiedErrorId : ProviderOperationExecutionFailure
PSComputerName : localhost

My DSC configuration is as below:

The DSC configuration that is used to reproduce the issue (as detailed as possible)

Configuration SQLInstall {
    [CmdletBinding()]
    param (
        [PSCredential] $Credentials,
        [PSCredential] $SQLCredentials
    )

    Import-Module -Name PSDesiredStateConfiguration
    Import-Module -Name SqlServerDsc

    Import-DscResource -Module PSDesiredStateConfiguration
    Import-DscResource -Module SqlServerDsc

    Node 'localhost'{
        WindowsFeature 'NetFramework35'{
            Name   = 'NET-Framework-Core'
            Ensure = 'Present'
        }

        WindowsFeature 'NetFramework45'{
            Name   = 'NET-Framework-45-Core'
            Ensure = 'Present'
        }

        SqlSetup 'InstallDefaultInstance'{
            InstanceName           = 'MSSQLSERVER'
            Features               = 'SQLENGINE,Replication,FullText,Conn'
            SQLCollation           = 'SQL_Latin1_General_CP1_CI_AS'
            SQLSvcAccount          = $SQLCredentials
            AgtSvcAccount          = $SQLCredentials
            SQLSysAdminAccounts    = $ClusterAdminUser, $SQLAdminUser
            ASSysAdminAccounts     = $ClusterAdminUser, $SQLAdminUser
            InstallSharedDir       = 'C:\Program Files\Microsoft SQL Server'
            InstallSharedWOWDir    = 'C:\Program Files (x86)\Microsoft SQL Server'
            InstanceDir            = 'C:\Program Files\Microsoft SQL Server'
            InstallSQLDataDir      = 'D:\MSSQL\Data'
            SQLUserDBDir           = 'D:\MSSQL\Data'
            SQLUserDBLogDir        = 'E:\MSSQL\Log'
            SQLTempDBDir           = 'F:\MSSQL\Temp'
            SQLTempDBLogDir        = 'F:\MSSQL\Temp'
            SQLBackupDir           = 'F:\MSSQL\Backup'
            SourcePath             = $SQLInstallDir
            UpdateEnabled          = 'False'
            ForceReboot            = $false
            PsDscRunAsCredential   = $Credentials
            DependsOn              = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
        }

        Service 'SqlInstanceRunning'{
            Name        = 'MSSQLSERVER'
            StartupType = 'Automatic'
            State       = 'Running'
            DependsOn   = '[SqlSetup]InstallDefaultInstance'
        }

        Service 'SqlAgentRunning'{
            Name        = 'SQLSERVERAGENT'
            StartupType = 'Automatic'
            State       = 'Running'
            DependsOn   = '[SqlSetup]InstallDefaultInstance', '[Service]SqlInstanceRunning'
        }
    }
}

Verbose logs showing the problem

Attached is the file with verbose output from the run. DCSVeroboseOutput.txt

Any help is greatly appreciated.

johlju commented 4 years ago

It seems there is an issue with the SqlServer module which is not part of SqlServerDsc.

... Failed to import SqlServer module. (SQLCOMMON0031) ---> System.IO.FileNotFoundException: Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified.

Try using the SqlServer module from the PowerShell Console and make sure it works there.

masroorac commented 4 years ago

I ran the below command from SqlServer module and it is working as expected.

Get-SqlInstance -ServerInstance "CCDBE1DEV1"
Instance Name                   Version    ProductLevel UpdateLevel  HostPlatform HostDistribution
-------------                   -------    ------------ -----------  ------------ ----------------
CCDBE1DEV1                      13.0.1601  RTM          n/a          Windows      n/a

I am deploying this in AWS, the configuration work when I have internet connectivity on EC2 but when I remove the internet connectivity I get the state error. All the dependencies have been setup during the initial OS image build (AMI) process. But it works when internet connectivity is present fail otherwise.

Is there a hard dependencies on internet to be present for this to work?

johlju commented 4 years ago

No, that sounds very strange that it should be dependent on having access to Internet. Maybe if it is trying to download a dependency as net standard https://devblogs.microsoft.com/dotnet/announcing-net-standard-2-0/

Can you monitor and see what the node tries to do on the internet since that seems to be the issue? 🤔

stale[bot] commented 4 years ago

This issue has been automatically marked as stale because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 10 days. If the issue is labelled with any of the work labels (e.g bug, enhancement, documentation, or tests) then the issue will not auto-close.

stale[bot] commented 4 years ago

This issue has been automatically closed because it is has not had activity from the community in the last 40 days.

alexlazarciuc commented 4 years ago

Hello @johlju I know this is a closed issue, but it's the only one that relates very much to the one I am having. I get similar error when executing our script and that is: Job {EFA48038-CA9F-11EA-ACCB-00155D075015} : This event indicates that a non-terminating error was thrown when DSCEngine was executing Test-TargetResource on MSFT_SqlSetup DSC resource. FullyQualifiedErrorId is PathNotFound,Microsoft.PowerShell.Commands.GetItemPropertyCommand. Error Message is Cannot find path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\150\ConfigurationState' because it does not exist.. My use case is: on the VM I already have SQL 2012 installed, so we want to install the SQL 2019, after changing the script to indicate the correct path of the installer I get above error when executing the dsc. Would you have any tips on how to / where to look for a fix? Very new into this :(

Thanks

johlju commented 4 years ago

@alexlazarciuc can you please open a new issue to track you problem? I would need the verbose logs from the run to see how far it gets, but please provide them in a new issue since I think you found a bug when using the 'Upgrade' action.

dsccommunity / SqlServerDsc