SqlTraceFlag: "An exception occurred in SMO while trying to manage a service."

seanwhitent commented 10 months ago

Problem description

SqlTraceFlag is not working in my configuration. I am running Start-DscConfiguration as the local administrator and passing the same credentials in to $SqlAdministratorCredential. I am attempting to use Example 1 from the wiki:

https://github.com/dsccommunity/SqlServerDsc/wiki/SqlTraceFlag#example-1

         SqlTraceFlag 'SetSqlTraceFlags'
         {
             #ServerName           = 'localhost'
             InstanceName         = 'MSSQLSERVER'
             TraceFlags           = @(834, 1117, 1118, 2371, 3226)
             RestartService       = $true

             PsDscRunAsCredential = $SqlAdministratorCredential
         }

However, it throws an error with every run, shown in the 'Verbose logs' section.

I am generating the .mof as follows

. C:\temp\SQLServerInstall.ps1

$password = "redacted" | ConvertTo-SecureString -asPlainText -Force
$username = ".\Administrator"
[PSCredential] $credential = New-Object System.Management.Automation.PSCredential($username,$password)

$ConfigData = @{
    AllNodes = @(
        @{
            NodeName = 'localhost'
            PSDscAllowPlainTextPassword = $true
        }
    )
}

SQLServerInstall -OutputPath C:\DSC -ConfigurationData $ConfigData -SqlAdministratorCredential $credential

(Currently working with "plain text" credentials while tinkering about, and the examples in the wiki use credentials from parameters, not certificates)

Verbose logs

VERBOSE: [servername]: LCM:  [ Start  Resource ]  [[SqlTraceFlag]SetSqlTraceFlags]
VERBOSE: [servername]: LCM:  [ Start  Test     ]  [[SqlTraceFlag]SetSqlTraceFlags]
VERBOSE: [servername]:                            [[SqlTraceFlag]SetSqlTraceFlags] Determining the current state for the instance 'MSSQLSERVER'.
VERBOSE: [servername]:                            [[SqlTraceFlag]SetSqlTraceFlags] Getting the current state for the instance MSSQLSERVER.
The following exception occurred while trying to enumerate the collection: "An exception occurred in SMO while trying to manage a service.".
    + CategoryInfo          : NotSpecified: (:) [], CimException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator
    + PSComputerName        : localhost

VERBOSE: [servername]: LCM:  [ End    Test     ]  [[SqlTraceFlag]SetSqlTraceFlags]  in 0.7960 seconds.
PowerShell DSC resource DSC_SqlTraceFlag  failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Was unable to connect to WMI information 'MSSQLSERVER' in
'servername'.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost

VERBOSE: [servername]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost

DSC configuration

<#
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-powershell-desired-state-configuration?view=sql-server-ver16
#>

Configuration SQLServerInstall
{

    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName SqlServerDsc
    Import-DscResource -ModuleName 'PSDesiredStateConfiguration'

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

         SqlSetup 'InstallDefaultInstance'
         {
              InstanceName        = 'MSSQLSERVER'               #The name of the instance. Use MSSQLSERVER for a default instance.
              Features            = 'SQLENGINE'                 #The features to install.
              SourcePath          = 'C:\SQLServer2017Media\Developer_ENU'  #The path to the SQL installation media.
              SQLSysAdminAccounts = @('servername\Administrator')        #The users or groups who are to be a member of the sysadmin role.
              #SQLUserDBDir        = ''
              #SQLUserDBLogDir     = ''
              #SQLTempDBDir        = ''
              #SQLTempDBLogDir     = ''
              #SQLBackupDir        = ''
              SqlSvcStartupType    = 'Automatic'
              AgtSvcStartupType    = 'Automatic' 
              DependsOn           = '[WindowsFeature]NetFramework45'
         }

         SqlMemory 'Set_SQLServerMemoryBy90Percent'
         {
             InstanceName         = 'MSSQLSERVER'
             Ensure               = 'Present'
             DynamicAlloc         = $false
             MaxMemoryPercent     = 90

             #PsDscRunAsCredential = $SqlAdministratorCredential
         }

         SqlMaxDop 'SetMaxDop'
         {
            <#
            https://github.com/dsccommunity/SqlServerDsc/wiki/SqlMaxDop

            Formula for dynamically allocating max degree of parallelism
            - If the number of configured NUMA nodes configured in SQL Server
              equals 1, then max degree of parallelism is calculated using 
              number of cores divided in 2 (numberOfCores / 2), then rounded up
              to the next integer (3.5 > 4).

            - If the number of cores configured in SQL Server are greater than
              or equal to 8 cores then max degree of parallelism will be set to
              8.

            - If the number of configured NUMA nodes configured in SQL Server 
              is greater than 2 and the number of cores are less than 8 then
              max degree of parallelism will be set to the number of cores.
            #>
            InstanceName         = 'MSSQLSERVER'
            Ensure               = 'Present'
            DynamicAlloc         = $true
            PsDscRunAsCredential = $SqlAdministratorCredential
         }

         SqlConfiguration 'CostThresholdForParallelismTo50'
         {
            InstanceName         = 'MSSQLSERVER'
            OptionName           = 'cost threshold for parallelism'
            OptionValue          =  50
            PsDscRunAsCredential = $SqlAdministratorCredential
         }

         SqlConfiguration 'SetBackupChecksum'
         {
            InstanceName         = 'MSSQLSERVER'
            OptionName           = 'backup checksum default'
            OptionValue          =  1
            PsDscRunAsCredential = $SqlAdministratorCredential
         }

         SqlConfiguration 'SetBackupCompression'
         {
            InstanceName         = 'MSSQLSERVER'
            OptionName           = 'backup compression default'
            OptionValue          =  1
            PsDscRunAsCredential = $SqlAdministratorCredential
         }

         SqlConfiguration 'OptimizeForAdHocWorkloads'
         {
            InstanceName         = 'MSSQLSERVER'
            OptionName           = 'optimize for ad hoc workloads'
            OptionValue          =  1
            PsDscRunAsCredential = $SqlAdministratorCredential
         }

         SqlTraceFlag 'SetSqlTraceFlags'
         {
             #ServerName           = 'localhost'
             InstanceName         = 'MSSQLSERVER'
             TraceFlags           = @(834, 1117, 1118, 2371, 3226)
             RestartService       = $true

             PsDscRunAsCredential = $SqlAdministratorCredential
         }
    }

}

SQL Server edition and version

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

Name      Version    Path
----      -------    ----
SqlServer 21.1.18256 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psd1

Operating system

OsName               : Microsoft Windows Server 2019 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value                                                                                                                                                                                  
----                           -----                                                                                                                                                                                  
PSVersion                      5.1.17763.3770                                                                                                                                                                         
PSEdition                      Desktop                                                                                                                                                                                
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                                                
BuildVersion                   10.0.17763.3770                                                                                                                                                                        
CLRVersion                     4.0.30319.42000                                                                                                                                                                        
WSManStackVersion              3.0                                                                                                                                                                                    
PSRemotingProtocolVersion      2.3                                                                                                                                                                                    
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version Path                                                                            
----         ------- ----                                                                            
SqlServerDsc 16.5.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.5.0\SqlServerDsc.psd1

johlju commented 10 months ago

Most likely this line that fails for some reason.

https://github.com/dsccommunity/SqlServerDsc/blob/85f85231f7132e9707e1912867e606d66f9dfc40/source/DSCResources/DSC_SqlTraceFlag/DSC_SqlTraceFlag.psm1#L43

Can you verify that this line works together with the SqlServer module you are using on the node?

seanwhitent commented 10 months ago

@johlju

This does not throw any errors:

 Import-Module SqlServer
 $ServerName = 'localhost'
 $sqlManagement = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList $ServerName

PS C:\Users\Administrator> $sqlManagement

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : 
ClientProtocols    : 
ServerInstances    : 
ServerAliases      : 
Urn                : ManagedComputer[@Name='localhost']
Name               : localhost
Properties         : {}
UserData           : 
State              : Existing

I tried adding Import-DscResource -ModuleName SqlServer to the beginning of the Configuration definition as a sanity check, but I'm still getting the same error

johlju commented 10 months ago

Services property is blank there. Try running it as elevated user if you didn’t already did that. Services should be populated otherwise there are another issue (no SQL services on the node). 🤔

johlju commented 10 months ago

Did the error occur prior to SQL Server was installed or after?

Is the Database Engine installed on the node now?

Can you run Invoke-DscResource with the method Get and Test to verify if that throws the same error?

seanwhitent commented 10 months ago

The services show up with Get-Service:

PS C:\Users\Administrator> Get-Service | Where-Object -Property Name -Like "*sql*"

Status   Name               DisplayName
------   ----               -----------
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)
Stopped  SQLBrowser         SQL Server Browser
Running  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)
Running  SQLTELEMETRY       SQL Server CEIP service (MSSQLSERVER)
Running  SQLWriter          SQL Server VSS Writer

I uninstalled SQL Server, rebooted, and then ran the DSC again, with the same result: all commands, including the SQL Server installation, work except the trace flags command.

I'm not super familiar with DSC, so it'll take me a moment to figure out the right Invoke-DscResource command to verify.

johlju commented 10 months ago

You see here how to use Invoke-DscResource.

What you did in https://github.com/dsccommunity/SqlServerDsc/issues/1977#issuecomment-1792666834, did you run that in an elevated PowerShell window? Also, try using SQLPS instead of SqlServer. With SqlServer also try different versions.

If you do not get property Services filled in the above comment then there is no issue with the DSC resource, then there is something else on your box that do not work.

seanwhitent commented 10 months ago

I did a full re-install of Windows, ran Install-Module SqlServer, and ran the DSC configuration for SQL Server 2022 instead of 2017, including trace flags, without any issue. I can see that SQLPS is showing up in my PowerShell modules when it was not before. I cannot say what issue that fixed, but it is fixed. Maybe a difference between major versions.

System info for good measure

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)   Oct  8 2022 05:58:25   Copyright (C) 2022 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 

OsName               : Microsoft Windows Server 2019 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US

Name                           Value                                                                                                               
----                           -----                                                                                                               
PSVersion                      5.1.17763.4974                                                                                                      
PSEdition                      Desktop                                                                                                             
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                             
BuildVersion                   10.0.17763.4974                                                                                                     
CLRVersion                     4.0.30319.42000                                                                                                     
WSManStackVersion              3.0                                                                                                                 
PSRemotingProtocolVersion      2.3                                                                                                                 
SerializationVersion           1.1.0.1                                                                                                             

Name      Version Path                                                                                     
----      ------- ----                                                                                     
SqlServer 22.1.1  C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.1.1\SqlServer.psd1               
SQLPS     16.0    C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Name         Version Path                                                                            
----         ------- ----                                                                            
SqlServerDsc 16.5.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.5.0\SqlServerDsc.psd1

dsccommunity / SqlServerDsc