4

I want to run SQL Server Vulnerability Assessment from a SQL Server Agent Job. Currently, I am attempting a job with a PowerShell script and am running a command like the one below.

Invoke-SqlVulnerabilityAssessmentScan -ServerInstance $(ESCAPE_DQUOTE(SRVR)) -Database AdventureWorks

I have confirmed that Invoke-SqlVulnerabilityAssessmentScan is available on the SQL Server (I can run it from the PowerShell command prompt there), but when I run my job, I receive an error stating that

The term 'Invoke-SqlVulnerabilityAssessmentScan' is not recognized as the name of a cmdlet

After looking at this Microsoft article, I am wondering if SQL Agent only has a subset of PowerShell cmdlets that it can access.

How can I run the vulnerability assessment scan from a SQL Job?

5

You are getting that error because the module is not imported.

You have to use

import-module -name sqlserver  # or path to the module

see : Running PowerShell in a SQL Agent Job

  • Thanks for responding. I added this to my SQL Job and it resulted in the following error: "The error information returned by PowerShell is: 'The following error occurred while loading the extended type data file: Error in TypeData "Microsoft.SqlServer.Management.Smo.NamedSmoObject": The member DefaultKeyPropertySet is already present. Error in TypeData "Microsoft.SqlServer.Management.Smo.NamedSmoObject": The member DefaultDisplayPropertySet is already present. Error in TypeData "Microsoft.SqlServer.Management.Smo.NamedSmoObject": The member DefaultDisplayProperty is already present." – Scott Aug 20 at 16:24
  • Thanks again for your help on this. What I gather from the articles is that sqlps is already imported and cannot be removed. As a result, you cannot also import sqlserver, which causes conflicts like the error message that I mentioned above. – Scott Aug 20 at 17:41
5

The articles referenced in @Kin Shah's answer helped me to come to a final solution. I had to switch from the "PowerShell" SQL Job Type to the "Operating system (CmdExec)" Type. I moved my PowerShell command into a script and referenced that using command text like the following in a SQL Job.

powershell.exe d:\data\dev\VulnerabilityScanTest.ps1

The command inside the script ended up looking like this:

Invoke-SqlVulnerabilityAssessmentScan -ServerInstance "myserver\myinstance" -Database AdventureWorks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.