How To Automate SQL Server Vulnerability Assessment

Powershell SQLVulnerabilityScan cmdlet is a great addition to the Powershell cmdlet libraries. Most of you know it's available through Microsoft Azure - using the interactive Advanced security pane .

The powershell cmdlet -  SQLVulnerabilityScan - is available through the SQL Server modules , the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. 

The first step is to check that you can execute the cmdlet , point to a SQL Server and extract the report from the Vulnerability Scan. This powershell snippet - connects to a SQL Server and runs the scan over a specific database - although you can also scan over all the databases. 

In the example - I've used the Invoke-SqlVulnerabilityAssessmentScan method and then apply the Export-SqlVulnerabilityAssessmentScan  method  and save out to an Excel spreadsheet 

$myscan = Invoke-SqlVulnerabilityAssessmentScan -Credential "sa" -ServerInstance "myserver.net,1407" -Database "Jack_test" -ScanId "MyScan"
$myscan | Export-SqlVulnerabilityAssessmentScan -FolderPath "E:\ScanResult.xlsx"

Using this snippet as a basis - it is not many extra steps to iterate through a bunch of SQL Server instances - producing these reports. Depending on your requirements - you may want to:

1) Place into a database and scan for categories of issues - fulfilling regulatory , such as GDPR requirements and other specific corporate  audit requirements or Database Server Security Audit Process

Placing the results in a database could allow categorisation of vulnerabilities, and other patterns used by DBAs , devops or operations teams 

2) Create the reports and make available to interested teams , such as security , platform owners or application owners. for example - an application owners may require a scheduled regular  report sent =>  Send email using Powershell

Read more on protecting SQL Server

Database Security Countermeasures against hacker attacks

Is AntiVirus Software required on a SQL Server

SQL Server , Meltdown and Spectre FAQ