- name: allowed_sql_alter_permissions
    description: 'List of user with permissions -  ALTER ANY SERVER AUDIT, ALTER ANYDATABASE AUDIT, ALTER TRACE; or EXECUTE'
    type: array
    default: [
            'SERVER_AUDIT_MAINTAINERS',
            '##MS_AgentSigningCertificate##',
            'NT AUTHORITY\SYSTEM',
            '##MS_AgentSigningCertificate##',
            '##MS_PolicyEventProcessingLogin##',
            'public']

W, [2018-11-21T01:53:22.904812 #73824]  WARN -- : You are setting a key that conflicts with a built-in method Hashie::Mash#class defined in Kernel. This can cause unexpected behavior when accessing the key as a property. You can still access the key via the #[] method.

  query_audits = %(
    SELECT * FROM sys.server_audit_specification_details WHERE audit_action_name IN
  (
  'DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP',
  'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP',
  'DATABASE_OWNERSHIP_CHANGE_GROUP',
  'DATABASE_PERMISSION_CHANGE_GROUP',
  'DATABASE_ROLE_MEMBER_CHANGE_GROUP',
  'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP',
  'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP',
  'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP',
  'SERVER_OBJECT_PERMISSION_CHANGE_GROUP',
  'SERVER_PERMISSION_CHANGE_GROUP',
  'SERVER_ROLE_MEMBER_CHANGE_GROUP',
  'SCHEMA_OBJECT_ACCESS_GROUP'
  );
  )

  query_audits = %(
    SELECT audited_result FROM sys.server_audit_specification_details WHERE audit_action_name IN
  (
  'DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP',
  'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP',
  'DATABASE_OWNERSHIP_CHANGE_GROUP',
  'DATABASE_PERMISSION_CHANGE_GROUP',
  'DATABASE_ROLE_MEMBER_CHANGE_GROUP',
  'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP',
  'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP',
  'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP',
  'SERVER_OBJECT_PERMISSION_CHANGE_GROUP',
  'SERVER_PERMISSION_CHANGE_GROUP',
  'SERVER_ROLE_MEMBER_CHANGE_GROUP',
  'SCHEMA_OBJECT_ACCESS_GROUP'
  );
  )

V-67769
V-67771
V-67903
V-67917
V-67919
V-67921
V-67923
V-67925
V-67927
V-67929
V-67931
V-67933
V-67935
V-67937
V-67939

$ inspec exec microsoft_sql_2014_server_stig_baseline-t ssh://user@password:example.com --attrs attributes.yml --reporter cli json:output.json

$inspec exec microsoft_sql_2014_server_stig_baseline -t winrm://$winhostip --user 'Administrator' --password=Pa55w0rd --attrs attributes.yml --reporter cli json:output.json

  ×  V-67843: SQL Server must have the Data Quality Services software component
    removed if it is unused. (2 failed)
     ×  Master Data Services is in use should equal true

     expected true
          got false

     ×  List if data quality services is used should be empty
     expected `["----", "-------------"].empty?` to return true, got false
     ✔  File C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\DQSInstaller.exe should not exist

  query = %(
  SELECT name FROM sys.databases WHERE name in ('DQS_MAIN', 'DQS_PROJECTS', 'DQS_STAGING_DATA');

  )

  !  ./inspec.yml:0:0: Missing profile summary in inspec.yml
  !  ./controls/V-67905.rb:13: Control V-67905 has no tests defined
  !  ./controls/V-67791.rb:1: Control V-67791 has no tests defined
  !  ./controls/V-67861.rb:13: Control V-67861 has no tests defined
  !  ./controls/V-67901.rb:35: Control V-67901 has no tests defined
  !  ./controls/V-67851.rb:12: Control V-67851 has no tests defined
  !  ./controls/V-67795.rb:18: Control V-67795 has no tests defined
  !  ./controls/V-67767.rb:14: Control V-67767 has no tests defined
  !  ./controls/V-67815.rb:23: Control V-67815 has no tests defined
  !  ./controls/V-67805.rb:14: Control V-67805 has no tests defined
  !  ./controls/V-67859.rb:15: Control V-67859 has no tests defined
  !  ./controls/V-67789.rb:1: Control V-67789 has no tests defined
  !  ./controls/V-67797.rb:15: Control V-67797 has no tests defined
  !  ./controls/V-67863.rb:34: Control V-67863 has no tests defined
  !  ./controls/V-67793.rb:1: Control V-67793 has no tests defined
  !  ./controls/V-67803.rb:12: Control V-67803 has no tests defined

  get_accounts = command("Invoke-Sqlcmd -Query \"SELECT name FROM sys.sql_logins WHERE type_desc = 'SQL_LOGIN' AND is_disabled = 0;\" -ServerInstance '#{SERVER_INSTANCE}'").stdout.strip.split("\n")
  get_accounts.each do | account|  
    a = account.strip
    describe "#{a}" do
      it { should be_in SQL_MANAGED_ACCOUNTS }
    end  
  end if get_accounts != [] >>>> redundant

  describe "There are no sql managed accounts, control not applicable" do
    skip "There are no sql managed accounts, control not applicable"
  end if get_accounts == []

  query = %(
    SELECT
        name
    FROM
        sys.sql_logins
    WHERE
        type_desc = 'SQL_LOGIN'
        AND is_disabled = 0;
  )

 sql_session = mssql_session(user: attribute('user'),
                              password: attribute('password'),
                              host: attribute('host'),
                              instance: attribute('instance'),
                              port: attribute('port'),
                              db_name: attribute('db_name'))

 account_list = sql_session.query(query).column('name')

  if account_list.empty?
    impact 0.0
    desc 'There are no sql managed accounts, control not applicable'

    describe "There are no sql managed accounts, control not applicable" do
      skip "There are no sql managed accounts, control not applicable"
    end
  else
    account_list.each do |account|
      describe "sql managed account: #{account}" do
        subject {account}
        it { should be_in SQL_MANAGED_ACCOUNTS }
      end
    end
  end

EXECUTE  xp_instance_regread 
       N'HKEY_LOCAL_MACHINE',
       N'SOFTWARE\Microsoft\MSSQLServer\\MSSQLServer',
       N'LoginMode';

V-67765
V-67767
V-67789
V-67791
V-67793
V-67795
V-67797
V-67815
V-67859
V-67885
V-67899
V-67901
V-67905

  query_sa = %(
    SELECT * FROM sys.sql_logins WHERE [name] = 'sa';
  )

  query_sa2 = %(
   SELECT * FROM sys.sql_logins WHERE [name] = 'SA';
  )

Affected Controls:
V-67825
V-67827
V-67829
V-67831
V-67833
V-67835

describe service('CryptSvc') do
  it { should be_installed }
end

https://www.inspec.io/docs/reference/resources/service/

describe service('clamd') do
  it { should_not be_enabled }
  it { should_not be_installed }
  it { should_not be_running }
end

Affected controls:
V-67757.rb
V-67769.rb
V-67771.rb
V-67773.rb
V-67775.rb
V-67777.rb
V-67779.rb
V-67781.rb

    describe 'Audited Result for Defined Audit Actions' do
        subject { sql_session.query(query).column('name').uniq }
        it { should_not eq '' }
    end

    describe 'Audited Result for Defined Audit Actions' do
        subject { [] }
        it { should_not eq ''" } # this will return true
    end

    describe 'Audited Result for Defined Audit Actions' do
        subject { sql_session.query(query).column('name').uniq }
        it { should_not be_empty }
    end