Automate SQL Server Vulnerability Assessment

27 March,2020 by Jack Vamvas

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. 

 

Powershell_vulnerability_scan

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Automate SQL Server Vulnerability Assessment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer