Using Foglight User Defined Collections to check SQL Server Security Best Practices

I was reviewing the article 13 SQL Server Best Practices recently and it occurred to me that 3 of the tips could be put in Foglight to continually check that your SQL Server instances are compliant.  Tip #3 in the article suggests that you should reduce your surface area in SQL Server by turning off certain unused database features. Specifically, there are 6 major components that you should review and disable.  In Foglight, you can create a User Defined Collection (UDC) to continually check that your SQL Server instances are compliant by entering the following query into a UDC for this check:

select name as component_name,
 value as  config_value, 
 value_in_use as run_value, 
 is_dynamic, description
 from sys.configurations where name = 'scan for startup procs'
 or name = 'Ole Automation Procedures'
 or name like 'clr%' 
 or name = 'cross db ownership chaining'
 or name = 'xp_cmdshell'
 or name = 'Database Mail XPs'

If you've never created a UDC in Foglight before, the following videos will show you step by step details of creating these security checks. 

Another security best practice is to adjust your SQL Server authentication (tip #5). Ideally, only Windows authentication should be used but in some cases that's not possible. If you have to use SQL Server logins, there are items that should be set up to enforce password policy, password expiration,  and the changing of the password on first login. The query to enter in a UDC to check if these items are enforced is listed below:

select 
    @@SERVERNAME as servername, 
    name as username,
    create_date,
    case is_policy_checked when 0 then 'No' end as is_policy_checked,
    case  is_disabled when 1 then  'YES' when 0 then 'No' end as is_disabled,
    case is_expiration_checked when 1 then  'YES' when 0 then  'No' end as is_expiration_checked,
    case PWDCOMPARE(name, password_hash) when 1 then 'YES' when 0 then 'No' end as UserAsPasswd
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY name

The following video shows how to set this up in Foglight:

The last tip (tip #9) talks about SQL Server encryption and how important it is to secure sensitive data. By default, SQL Server is not encrypted. If you don't have encryption, review this Microsoft article: Enable encrypted connections to the database engine 

The query to enter in a UDC to check if your SQL Servers have encrypted connections is listed below:

SELECT
  SUM(CASE WHEN encrypt_option='FALSE' THEN 1 ELSE 0 END) 
AS  [Number_of_unencrypted_connections]
 ,SUM(CASE WHEN encrypt_option='TRUE' THEN 1 ELSE 0 END)
 AS [Number_of_encrypted_connections]
FROM   sys.dm_exec_connections

Here is a step by step video that shows how to create this encryption check in Foglight: