Inspec test cases for windows authentication

Question

I am trying to validate (mssql) Sql authentication mode "Integrated" using inspec. I was not able find any reference. How to pass sql query using ruby as i am having sql query which displays the current sql authentication mode.

Answer 1

You can check the authentication mode with powershell, see this article for a reference on the powershell code and this article on how the Microsoft.SqlServer.Management.Smo assembly is loaded:

# Connect to the instance using SMO $s = new-object 
('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
[string]$nm = $s.Name [string]$mode = $s.Settings.LoginMode

write-output "Instance Name: $nm"
write-output "Login Mode: $mode"

Therefore you can write a powershell test to achieve what you want:

control 'sql auth type' do
  impact 1.0
  ps_script = <<-EOH
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
  $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $env:COMPUTERNAME
  [string]$nm = $s.Name
  [string]$mode = $s.Settings.LoginMode

  write-output $mode
  EOH

  describe powershell(ps_script) do
    its('stdout') { should match (/Integrated/)}
  end
end