invoke-sqlassessment returns warning

[Schutzhund3]

PS 5.1, Windows 2019 , SQL 2019, SQLSERVER module 21.1.18245 "sqlag000" is the local server name / default instance. Execution policy is unrestricted (to test).

Get-SqlInstance -ServerInstance sqlag000 | invoke-sqlassessment

WARNING: Checks 'AgentSvcStoped', 'BrowserSvcStoped', 'DtsSvcStoped', and 2 more have been skipped because they need access to PowerShell

In the SQL Server logs, I see "Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed." All of the servers use integrated authentication.

I get the same warning message from all of the 2019 servers I'm testing.

[aguzev]

The warning tells that some checks have been skipped because the assessment engine had no access to some data. In this case, there was an attempt to use PowerShell scripts to get some data not present in SQL Server Dynamic Management Views. The assessment engine runs PowerShell scripts with xp_cmdshell procedure. The warning may appear if the procedure was disabled on the target system or for the current user. This article tells about xp_cmdshell in detail: xp_cmdshell (Transact-SQL)

Assessment cmdlets do not attempt to connect with 'sa' or any other login. An established connection comes from Get-SqlInstance cmdlet which gets credentials from the user. In your example, the connection was open for the current user. That message on failed 'sa' login attempt is not related to Invoke-SqlAssessment.

[Schutzhund3]

The warning tells that some checks have been skipped because the assessment engine had no access to some data. In this case, there was an attempt to use PowerShell scripts to get some data not present in SQL Server Dynamic Management Views. The assessment engine runs PowerShell scripts with xp_cmdshell procedure. The warning may appear if the procedure was disabled on the target system or for the current user. This article tells about xp_cmdshell in detail: xp_cmdshell (Transact-SQL)

Assessment cmdlets do not attempt to connect with 'sa' or any other login. An established connection comes from Get-SqlInstance cmdlet which gets credentials from the user. In your example, the connection was open for the current user. That message on failed 'sa' login attempt is not related to Invoke-SqlAssessment.

that's unfortunate as the xp_cmdshell is a security concern and can't be enabled across an enterprise 'on-demand' or via proxy.

[aguzev]

Yes, xp_cmdshell is a security concern. Unfortunately, SQL Server cannot provide all the system information useful for assessment. That is why some checks were skipped.

Would it be more practical if assessment cmdlets got the data by accessing remote WMI or registry directly via COM API? This scenario would need permission to access WMI or registry on the server remotely.

[aguzev]

Hi @Schutzhund3, could you close the issue if there are no more questions or suggestions?

[jeffrey-yao]

Many thanks @aguzev , after turning on xp_cmdshell in sql server, it indeed solves the same issue for me. I tend to agree with @Schutzhund3 that xp_cmdshell may not be allowed to be on across the enterprise (due to security protocols). It would be great if MS can come up with a solution without needing to turn on xp_cmdshell yet still allow PowerShell probe to be used by invoke-sqlassessment

[aguzev]

Sure, @jeffrey-yao, there is an option. But this is not related to 'sa' login. SQL Assessment API does not try to login as 'sa'.

SQL Assessment cmdlets could accept a CIM session open to the computer which hosts the target SQL Server. How would you pass the session object to the cmdlet?

$serverName = 'SqlServer1'
$server = Get-SqlInstance $serverName
$cimSession = New-CimSession $serverName
Invoke-SqlAssessment $server -CimSession $cimSession

What about multiple targets?

$serverNames = @('SqlServer1', '10.0.42.11')
$targets = $serverNames | %{Get-SqlInstance}
$targets += $targets[0].Databases[0]
$cimSessions = $serverNames | %{New-CimSession}

Invoke-SqlAssessment $targets[0] -CimSession $cimSessions[0] 

$targets | Invoke-SqlAssessment … ? …

[aguzev]

Would the following be comfortable for scripting?

@('.', '10.0.42.11')
| %{ New-Object PSObject -Property @{
    InputObject = $(Get-SqlInstance $_ …);
    CimSession = $(New-CimSession $_ …)
}}
| Invoke-SqlAssessment

[jeffrey-yao]

@aguzev , it absolutely is a great idea if invoke-sqlassessment could accept a cim session parameter. When shall we see this implementation? as currently there is no -cimsession parameter as of sqlserver module version 21.1.18256.

[aguzev]

… it absolutely is a great idea if invoke-sqlassessment could accept a cim session parameter. When shall we see this implementation? as currently there is no -cimsession parameter as of sqlserver module version 21.1.18256.

This is how SQL Assessment API works on Azure SQL portal.

For PowerShell, this parameter opens a door for frequent hard-to-notice errors. The following code is an example.

$theCimSessions = New-CimSession 'SqlServer1'

'SqlServer1', '10.0.42.11'
| % { Get-SqlInstance -Server $_ }
| Invoke-SqlAssessment -CimSession $theCimSession

The challenging part is ensuring that the CIM session is open to the very computer hosting the target SQL Server. Probably, the code above will not produce an error but will analyze wrong data for the second SQL Server '10.0.42.11'.

That's the only reason we don't have the -CimSession parameter yet.