Search sqlserver-dba.com
Follow sqlserver-dba.com
For any SQL Server issue send email to: jack@sqlserver-dba.com
SQLServer-DBA.com Links
Recent Posts
- SQL Server 2019 upgrade considerations
- How to use Database Compatibility to increase your migration success
- How to start SSMS as a different Windows login
- How to capture SQL login details with Extended Events
- How to add members to the Administrators group with Powershell
- How to view the current Powershell session security permissions
- How to get Active Directory Group Scope - Local Domain, Global Group, Universal Group with Powershell
- How to resolve a hostname or ip address with Powershell
- How can I execute t-sql with a Proxy account
- AWS-RDS SQL Server limitations
How To Automate SQL Server Vulnerability Assessment
27 March,2020 by Tom Collins
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
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |