Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- How to list logins mapped to a database in SQL Server
- How to drop login on AWS RDS SQL Server without sysadmin using Hashicorp Vault
- How to get Cluster File Share Witness sharepath with Powershell
- A simple guide to Transparent Data Encryption in AWS RDS SQL Server
- A simple guide to Hashicorp and SQL Server Secrets Engine
- How to fix Warning: The certificate you created is expired
- SQLCMD uses OLEDB as a client interface
- A summary of creating a trust relationship between on-premises domain and AWS Directory Service
- Best practices for running Microsoft Active Directory Services on AWS
- SQL Server Blocking Reading List
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: |