Invoke-SqlVulnerabilityAssessmentScan

Reference

Module:: SQLServer

Invokes a new Vulnerability Assessment scan.

Syntax

Invoke-SqlVulnerabilityAssessmentScan
      [-ScanId <String>]
      [-Baseline <SecurityCheckBaselineSet>]
      [-OmitMetadata]
      [<CommonParameters>]

Invoke-SqlVulnerabilityAssessmentScan
      -ConnectionString <String>
      [-ScanId <String>]
      [-Baseline <SecurityCheckBaselineSet>]
      [-OmitMetadata]
      [<CommonParameters>]

Invoke-SqlVulnerabilityAssessmentScan
      -ServerInstance <PSObject>
      -DatabaseName <String>
      [-Credential <PSCredential>]
      [-ScanId <String>]
      [-Baseline <SecurityCheckBaselineSet>]
      [-OmitMetadata]
      [<CommonParameters>]

Invoke-SqlVulnerabilityAssessmentScan
      -Path <String>
      [-ScanId <String>]
      [-Baseline <SecurityCheckBaselineSet>]
      [-OmitMetadata]
      [<CommonParameters>]

Invoke-SqlVulnerabilityAssessmentScan
      -InputObject <Database>
      [-ScanId <String>]
      [-Baseline <SecurityCheckBaselineSet>]
      [-OmitMetadata]
      [<CommonParameters>]

Description

The Invoke-SqlVulnerabilityAssessmentScan cmdlet runs a scan on your database. The cmdlet employs a knowledge base of security checks that flag security vulnerabilities and highlight deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. The security checks are based on Microsoft's recommended best practices, and focus on the security issues that present the biggest risks to your database and its valuable data. These security checks also represent many of the requirements from various regulatory bodies to meet their compliance standards.

Results of the scan include actionable steps to resolve each issue and provide customized remediation scripts where applicable. An assessment report can be customized for your environment by setting an acceptable baseline for permission configurations, feature configurations and database settings.

Module requirements: version 21+ on PowerShell 5.1; version 22+ on PowerShell 7.x.

Examples

Example 1: Invoke a Vulnerability Assessment scan using Windows authentication on master database

PS C:\> Invoke-SqlVulnerabilityAssessmentScan -ServerInstance "MyComputer\MainInstance" -Database "master" -ScanId "MyScan"

Id              : MyScan
ScanType        : BoxDatabase
ScanTriggerType : OnDemand
Server          : MyComputer\MainInstance
Database        : master
Platform        : SqlServer2017
SqlVersion      : 14.0.3015
StartTimeUtc    : 3/17/2018 8:58:02 PM
EndTimeUtc      : 3/17/2018 8:58:41 PM
ToolName        : PowerShell
ToolVersion     : 1.0
Results         : {VA1017, VA1019, VA1020, VA1021...}
SecurityChecks  : {[VA1017, Microsoft.SQL.VulnerabilityAssessment.SecurityCheck], [VA1019, Microsoft.SQL.VulnerabilityAssessment.SecurityCheck], [VA1020,
                  Microsoft.SQL.VulnerabilityAssessment.SecurityCheck], [VA1021, Microsoft.SQL.VulnerabilityAssessment.SecurityCheck]...}

In this example we invoke a Vulnerability Assessment scan on master database using Windows authentication.

Example 2: Invoke a Vulnerability Assessment scan using current path context

PS C:\> Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance\Databases\MyDatabase"
PS SQLSERVER:\SQL\MyComputer\MainInstance> Invoke-SqlVulnerabilityAssessmentScan
WARNING: Using provider context. Server = MyComputer\MainInstance, Database = MyDatabase.

Id              : 2018-03-17T22-58-02
ScanType        : BoxDatabase
ScanTriggerType : OnDemand
Server          : MyComputer\MainInstance
Database        : MyDatabase
Platform        : SqlServer2017
SqlVersion      : 14.0.3015
StartTimeUtc    : 3/17/2018 8:58:02 PM
EndTimeUtc      : 3/17/2018 8:58:41 PM
ToolName        : PowerShell
ToolVersion     : 1.0
Results         : {VA1017, VA1019, VA1020, VA1021...}
SecurityChecks  : {[VA1017, Microsoft.SQL.VulnerabilityAssessment.SecurityCheck], [VA1019, Microsoft.SQL.VulnerabilityAssessment.SecurityCheck], [VA1020,
                  Microsoft.SQL.VulnerabilityAssessment.SecurityCheck], [VA1021, Microsoft.SQL.VulnerabilityAssessment.SecurityCheck]...}

This example demonstrates how Invoke-SqlVulnerabilityAssessmentScan uses the current path to set the database context to MyDatabase.

Example 3: Invoke a Vulnerability Assessment scan using credential and omit security checks metadata

PS C:\> $cred = Get-Credential
PS C:\> Invoke-SqlVulnerabilityAssessmentScan -ServerInstance "MyComputer\MainInstance" -Database "master" -Credential $cred

Id              : 2018-03-17T22-58-02
ScanType        : BoxDatabase
ScanTriggerType : OnDemand
Server          : MyComputer\MainInstance
Database        : MyDatabase
Platform        : SqlServer2017
SqlVersion      : 14.0.3015
StartTimeUtc    : 3/17/2018 8:58:02 PM
EndTimeUtc      : 3/17/2018 8:58:41 PM
ToolName        : PowerShell
ToolVersion     : 1.0
Results         : {VA1017, VA1019, VA1020, VA1021...}
SecurityChecks  :

This example demonstrates how to invoke a scan using a PSCredential. It also demonstrates the use of -OmitMetadata flag (notice that the result doesn't contain the security checks dictionary - this is the metadata of the checks, such as title, description, etc.).

Example 4: Invoke a Vulnerability Assessment scan with a baseline

PS C:\> [string[][]]$expectedResults = ,("guest", "db_datareader", "SQLUSER", "NONE")
PS C:\> $baseline = New-SqlVulnerabilityAssessmentBaseline -SecurityCheckId "VA2109" -ExpectedResult $expectedResults
PS C:\> $baselineSet = New-SqlVulnerabilityAssessmentBaselineSet -Baselines $baseline
PS C:\> $scanResult = Invoke-SqlVulnerabilityAssessmentScan -ServerInstance "MyComputer\MainInstance" -Database "master" -Baseline $baselineSet
PS C:\> $result2109 = $scanResult.Results.GetEnumerator() | where {$_.Value.SecurityCheckId -eq "VA2109"
}

This example demonstrates how to create a baseline set and invoke a scan with it. The BaselineAdjustedSecurityCheckResult property in the result of 'VA2109' contains the baseline adjusted result. Notice that the original status of this result is Failed, but the baseline adjusted result status is Passed.

Parameters

-Baseline

A Vulnerability Assessment security check baseline set

Type:	SecurityCheckBaselineSet
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ConnectionString

Specifies a connection string to connect to the database. If this parameter is present, other connection parameters will be ignored

Type:	String
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

-Credential

Specifies a credential used to connect to the database.

Type:	PSCredential
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-DatabaseName

Specifies the name of a database. This cmdlet connects to this database in the instance that is specified in the ServerInstance parameter.

If the Database parameter is not specified, the database that is used depends on whether the current path specifies both the SQLSERVER:\SQL folder and a database name. If the path specifies both the SQL folder and a database name, this cmdlet connects to the database that is specified in the path.

Type:	String
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

-InputObject

Specifies the input object for the scan operation.

Type:	Database
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

-OmitMetadata

Whether to omit the security checks metadata (e.g. title, description, etc.) Please notice that Export-VulnerabilityAssessmentScan requires the security checks metadata to execute correctly.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Path

Specifies the path to the instance of SQL Server to execute the scan.

Type:	String
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

-ScanId

The Vulnerability Assessment scan id

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ServerInstance

Specifies a character string or SQL Server Management Objects (SMO) object that specifies the name of an instance of the Database Engine. For default instances, only specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.

Type:	PSObject
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

Inputs

None

Outputs

System.Object