Missing newer configurations from ServerConfigurationFacet

[rsql-dba]

When investigating whether Policy Based Management could help to check the CIS Security Benchmark (https://www.cisecurity.org/benchmark/microsoft_sql_server), "clr strict security" could not evaluated via the facet Server Configuration. Going deeper, it seemed none of the newer configuration options are available through SMO.

[rsql-dba]

There is a work-around with an ExecuteSQL expression for PBM; `Declare @condition_id int EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'CLR Not enabled OR Secure', @description=N'', @facet=N'IServerConfigurationFacet', @expression=N'

Bool OR 2 Bool EQ 2 Bool ClrIntegrationEnabled Bool False Bool 0 Bool EQ 2 Bool ExecuteSql Bool 2 String System.String Bool String System.String SELECT IIF(value = 1 AND value_in_use = 1,1,0) AS evaluation FROM sys.configurations WHERE name = ''''clr strict security'''' Bool True Bool 0

', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT Select @condition_id

GO `

[shueybubbles]

thx for opening an issue! PBM has largely been neglected since SQL 2014. Maybe we can update it for the next major SQL release. Since policy evaluations on the server require an updated SMO in the SQL installation itself it's too late to update SQL 2022.