MS SQL Data Collection Status and Errors

Overview

The Cloudamize Windows Agent (WA) or Agentless Data Collector (ADC) collects Basic and Advanced SQL Data.

Basic SQL data is required for Cloudamize to deliver full and accurate results and recommendations. See below for troubleshooting issues with basic SQL data collection. See this article for the specific data collected by each.

Advanced SQL data is only required to populate certain sections of the AWS MPA Export report, so is an optional requirement for assessments. See below for troubleshooting for advanced SQL data collection.

The status for the SQL data collection is displayed under the data column "MS SQL Data Collection Status" on the Inventory Settings page.

Note: To collect SQL Data Successfully from a node, the Windows Agent and Agentless Data Collector will require Powershell Version 2.0 for Basic SQL data and PowerShell Version 5.1 for Advanced SQL Data. Advanced data also requires the SqlServer Powershell module (minimum version 20.0); if this is not present the collector will attempt to install the module from https://go.microsoft.com - this may require a firewall exception to be added, if the advanced SQL data is required for your assessment.

The "MS SQL Data Collection Status" column will have the status as below,

• N/A or Blank Cell: The machine doesn’t have an MS SQL server instance.

• Collecting: MS SQL data is being successfully collected.

• An Error: related to any issues encountered while collecting the Basic or Advanced SQL data. See troubleshooting below.

Basic SQL Data Collection Troubleshooting

Failed to get the first MS SQL data

This error means Agentless Data Collector failed to get MS SQL basic data or it’s uncertain that an MS SQL server instance exists.

Note: The Windows Agent will almost always get the basic MS SQL data successfully without using a PowerShell script, but the Agentless Data Collector may face issues since it is trying to run a PowerShell script remotely. The minimum PowerShell version required to collect the Basic MS SQL data is 2.0.

You may also see below errors in the MS SQL Data Collection Status column      

• Error 1: PowerShell script output = PowerShell version is less than 2.0.

  • Solution: Upgrade the PowerShell version on the remote machine.

• Error 2: PowerShell script output = Add-Type: Could not load file or assembly 'System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'' or one of its dependencies.

  • Solution: Install .NET Framework 3.5 on the remote machine.

• Error 3: PowerShell script output = Add-Type: There is not enough space on the disk.\nAt line:1

  • Solution: Increase disk space or delete unnecessary files on the remote machine.

• Error 4: PowerShell script output = Add-Type : (0) : Source file ''C:\\Windows\\TEMP\\0morwrz3.0.cs'' could not be found

  • Solution: Allow write permission on the C:\Windows\TEMP directory for the account provided in ADC UI. Check if any anti-virus software (e.g., McAfee, CrowdStrike) prevents this.

• Error 5: PowerShell script output = PowerShell script output is empty. It could be a PowerShell version issue, no PowerShell installed, a shared folder access issue, or a security software issue.

  • Solution 1: Log into the remote host and check the system environment variable, Path, includes a path to powershell.exe. If not, add the path (e.g., %SYSTEMROOT%\System32\WindowsPowerShell\v1.0\) to Path.

  • Solution 2: Run the following program (GetMsSqlInfo.exe) on the remote host with the same account provided in ADC UI. Note that Utility.dll is needed to run GetMsSqlInfo.exe. Send us the output of the program on the helpdesk email.

Utility.dll GetMsSqlInfo.exe NB/ Include the Utility.dll file in the same directory as the GetMsSqlInfo.exe application before running it.

Advanced SQL Data Collection Troubleshooting

Failed to install SqlServer PowerShell module: This error means Windows Agent or Agentless Data Collector successfully collected basic MS SQL data but can’t get advanced data shown in our KB here. To collect Advanced MS SQL data, we need to install the PowerShell module, SqlServer, on the (remote) machine. This installation failed. The minimum PowerShell version required for Advanced MS SQL data collection is 5.1. Windows Agent or Agentless Data Collector will try to install the module automatically again every 20 hours.

You may also see below errors in the MS SQL Data Collection Status column 

• Error 1: Exit code = 1. Output: PowerShell version is less than 5.1.0.0.

  • Solution: Upgrade the PowerShell version on the remote machine.

• Error 2: Exit code = 1. Output: Installing the NuGet package provider. WARNING: Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID ~~~ Check your internet connection.

  • Solution: Install NuGet manually by running the following. An Internet connection is probably needed. A proxy or firewall may block the connection to the URL (https://go.microsoft.com).

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12;
#==== Set proxy if needed
[System.Net.WebRequest]::DefaultWebProxy = New-Object System.Net.WebProxy('your_proxy.com:8443', $true);
[System.Net.WebRequest]::DefaultWebProxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials; # Use DefaultNetworkCredentials if your proxy don't require proxy credentials.
[system.net.webrequest]::DefaultWebProxy.Credentials = Get-Credential;  # Run this if your proxy requires proxy credentials (user name and password).
#====
Install-PackageProvider -Name 'NuGet' -MinimumVersion '2.8.5' -Scope CurrentUser -Force -ErrorAction Stop;

• Error 3: Exit code = 1. Output: NuGet package provider was already installed. Setting PSGallery InstallationPolicy to Trusted. Failed to set PSGallery InstallationPolicy to Trusted. No repository with the name 'PSGallery' was found. Error: PackageManagement\Get-PackageSource: Unable to find repository 'PSGallery'. Use Get-PSRepository to see all available repositories.

  • Solution: Register the PS repository by running the following.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12;
Register-PSRepository -Default;

Then check the repository, PSGallery, by running “Get-PSRepository”.

Get-PSRepository;
Name                      InstallationPolicy   SourceLocation
----                      ------------------   --------------
PSGallery                 Trusted              https://www.powershellgallery.com/api/v2

Try to set PSGallery InstallationPolicy to Trusted.

Set-PSRepository -Name 'PSGallery' -InstallationPolicy 'Trusted' -ErrorAction Stop;

• Error 4: NuGet package provider was already installed. PSGallery InstallationPolicy was already set to Trusted. Installing the SqlServer PowerShell module. WARNING: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'.

  • Solution: Fix the connection issue. A proxy or firewall may block the connection to the URL (https://www.powershellgallery.com). Here is the cmdlet to check.

Invoke-WebRequest 'https://www.powershellgallery.com/api/v2/'
# Example of an error message: 
  Example 1: Invoke-WebRequest : The underlying connection was closed: An unexpected error occurred on a send.
  Example 2: Invoke-WebRequest : Unable to connect to the remote server
  Example 3: WARNING: Source Location 'https://www.powershellgallery.com/api/v2/package/SqlServer/22.2.0' is not valid.   <--- Proxy blocked the connection to the URL.
  Fix this connection issue.
# Example of a success message:
  StatusCode        : 200
  StatusDescription : OK
  ....

Try again to install the SqlServer PowerShell module after fixing the connection issue.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12;
Install-Module 'SqlServer' -AllowClobber -Scope CurrentUser -Force -ErrorAction Stop;

• Error 5: Detail: Win32Exception = The system cannot find the file specified

  • Solution: Add the PowerShell.exe path (e.g., C:\Windows\System32\WindowsPowerShell\v1.0\) to the system environment variable, Path. Restart Cloudamize Agent for the Windows Agent case.
    Installing the SqlServer PowerShell module on the remote: Run the following program (InstallSqlServerPsModule.exe) on the remote host. Note that Utility.dll is needed to run InstallSqlServerPsModule.exe. Send us the output of the program.

InstallSqlServerPsModule.exe Utility.dll NB/ Include the Utility.dll file in the same directory as the InstallSqlServerPsModule.exe application before running it.

• Error 6: Failed to get SQL info from the PowerShell module. No data in the JSON out key. This error means Windows Agent or Agentless Data Collector successfully collected basic MS SQL data but can’t get advanced data. The cmdlet, Get-SqlInstance, in the SqlServer module, didn’t work.

  • Solution for Agentless Data Collector case: Go to the remote host and run the following commands in PowerShell Prompt (Windows PowerShell) with the account used in ADC UI:

  • Solution for Windows Agent case: Download PsTools from PsTools - Sysinternals and run Command Prompt as Administrator and run the command, psexec -i -s cmd.exe, which will show a new pop-up Command Prompt with the account, NT AUTHORITY\SYSTEM. Run the command, PowerShell, on the new Command Prompt and then run the following commands:

Commands:

• CODE

  Get-SqlInstance -ServerInstance "hostName"

  • hostName is the host name on the machine (the command ‘hostname’ in Command Prompt will give you the machine’s host name).

• CODE

  Get-SqlInstance -ServerInstance ".\sqlInstanceName"

  • The machine may have multiple SQL instance names. Use one of them. If the SQL instance name is MSSQLSERVER, then use host name instead of the SQL instance name.

    • Common error message from the command above:

      • Get-SqlInstance : Failed to connect to server hostName.

      • Get-SqlInstance : Failed to connect to server .\SQLEXPRESS.

• ADC: Add the account provided in ADC UI to Security/Logins in SQL Server Management Studio and try the commands above again.

• WA: make sure that Security/Logins includes the account, NT AUTHORITY\SYSTEM, which is included by default. See screenshot:

If you have any queries, please get in touch with the helpdesk via our Helpdesk Portal or by email at helpdesk@cloudamize.com.