SQL Server Print Output to Chef inspec

Question

The SQL Server print messages are not taken into chef inspec for validation. Do we have an option to validate.

Below chef inspec sample code donot take the SQL Server print message.

sql = mssql_session(user: 'sa', password: 'Test')
describe sql.query("
-- #5.2 - Set the ''default trace enabled'' Server Configuration Option to 1
IF EXISTS (SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'default trace enabled' and (value=1 and value_in_use=1))
BEGIN
PRINT 'OK'
END
ELSE
PRINT 'NOT OK'") do
its("value") { should eq 'OK' }
end
end

score 1 · Accepted Answer · answered Nov 22 '20 at 20:10

Instead of PRINT 'OK' use SELECT 'OK' AS value / SELECT 'NOT OK' AS value.

Programmatically getting a hold of PRINT messages can be done but you need code to listen to the connection's InfoMessage event. Basically you'd need to dig into the code for either mssql_session or sql.query... which you probably don't want to do.

score 0 · Answer 2 · answered Nov 23 '20 at 04:07

You can modify your statement to directly return OK or NOT OK, as given below. SELECT returns the resultset back. Print does not return result set back. The drivers have to read it differently using InfoMessage event. Read more.

Better to go for SELECT statement, as suggested by @user2845090.

SELECT CASE WHEN 
EXISTS (SELECT name
, CAST(value as int) as value_configured
, CAST(value_in_use as int) as value_in_use 
FROM sys.configurations WHERE name = 'default trace enabled' 
and (value=1 and value_in_use=1)) THEN 'OK' ELSE 'NOT OK' 
END

Or

SELECT IIF(EXISTS((SELECT name
, CAST(value as int) as value_configured
, CAST(value_in_use as int) as value_in_use 
FROM sys.configurations WHERE name = 'default trace enabled' 
and (value=1 and value_in_use=1)),'OK','NOT OK')

SQL Server Print Output to Chef inspec

2 Answers2