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

[seanwhitent]

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

[johlju]

Most likely this line that fails for some reason.

SqlServerDsc/source/DSCResources/DSC_SqlTraceFlag/DSC_SqlTraceFlag.psm1

Line 43 in 85f8523

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

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

[seanwhitent]

@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]

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]

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]

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]

You see here how to use Invoke-DscResource.

What you did in #1977 (comment), 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]

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