On-Premises data gateway - the remote certificate is invalid according to the validation procedure

Make sure you install the root- and intermediate CA certificates in the computer certificate store, in the correct containers (trusted root certification authorities and Intermediate Certification Authorities)

You can get them from my drive:

https://drive.google.com/drive/folders/1gOt-dp2-WkfzGmMCZiZNn_-i2Xutanfa?usp=sharing

---------------------------------------------------------------------------------------------------

Alternatively, you get them yourself using the scripts below. 

1. Create a file named:  Check-SSLCerts.cmd with this content:

pushd "%~dp0"
echo Generating report...
SET URLs="https://api.powerbi.com"
SET ReportFile=SslReport.txt
PowerShell -ExecutionPolicy bypass -Command ./Check-SSLCerts.ps1 -URLs %URLs% -ReportFile %ReportFile%
echo Report is at %ReportFile%
pause
rem -----------------------------end of file-------------------------------

create a second file named Check-SSLCerts.ps1 with this content, en then start Check-SSLCerts.cmd

# Check SSL certs by @chentiangemalc
# Source : http://chentiangemalc.wordpress.com
#
# - Runs through all URLs specified in URLs parameter, checks their cert, and downloads entire cert chain to %TEMP%\SslCerts in PCS7 format (.P7B)
# - If specified URLs redirect, the redirects will be followed
# - Outputs reasons for certificate failures
# - If report file parameter is specified log is generated
# - Specify UserAgent if desired
#
# If URLs redirect back & forth to each other
[CmdletBinding()]
param(
[string[]]$urls=@("https://api.powerbi.com"),
[string]$ReportFile="",
[string]$UserAgent="Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko")

if (![string]::IsNullOrEmpty($reportfile)) { Start-Transcript $ReportFile }

$global:currentURI = $null

# using GLOBAL so we display SSL error info to console
$global:RemoteCertificateValidationCallback = [System.Net.Security.RemoteCertificateValidationCallback]{
param(
[object]$sender,
[System.Security.Cryptography.X509Certificates.X509Certificate]$certificate,
[System.Security.Cryptography.X509Certificates.X509Chain]$chain,
[System.Net.Security.SslPolicyErrors]$sslPolicyErrors)

# save certificate
$certs = New-Object Security.Cryptography.X509Certificates.X509Certificate2Collection
$chain.ChainElements | ForEach-Object {[void]$certs.Add($_.Certificate)}

$sslCertPath = $env:TEMP + "\SslCerts"
if (!(Test-Path $sslCertPath))
{
mkdir $sslCertPath
}
$certFilename = $sslCertPath + "\" + $uri.DnsSafeHost + ".p7b"
Write-Host "Certificate saved to $certFilename"
Set-Content -Path $certFilename -Value $certs.Export("pkcs7") -Encoding Byte

if ($sslPolicyErrors -eq [System.Net.Security.SslPolicyErrors]::None)
{
Write-Host "SSL Certificate OK!" -ForegroundColor Green
}
else
{
Write-Host "SSL Certificate Errors!" -ForegroundColor Red
}

if ($sslPolicyErrors.HasFlag([System.Net.Security.SslPolicyErrors]::RemoteCertificateChainErrors))
{
Write-Host "- Remote Certificate Chain Errors" -ForegroundColor Red
ForEach ($status in $chain.ChainStatus) { Write-Host "- $($status.StatusInformation)" -ForegroundColor Red }
}

if ($sslPolicyErrors.HasFlag([System.Net.Security.SslPolicyErrors]::RemoteCertificateNameMismatch))
{
Write-Host "- Remote Certificate Name Mismatch" -ForegroundColor Red
}

if ($sslPolicyErrors.HasFlag([System.Net.Security.SslPolicyErrors]::RemoteCertificateNotAvailable))
{
Write-Host "- Remote Certificate Not Available" -ForegroundColor Red
}

# ignore invalid certificates
return $true
}

Function Check-SSLCertificate
{
param([parameter(Mandatory=$true)][uri]$uri)

$hostname = $uri.DnsSafeHost
$port = $uri.Port
#Create a TCP Socket to the computer and a port number
$tcpsocket = New-Object Net.Sockets.TcpClient($hostname, $port)

#test if the socket got connected
if(!$tcpsocket)
{
Write-Error "Error Opening Connection: $port on $hostname Unreachable"
exit 1
}
else
{
#Socket Got connected get the tcp stream ready to read the certificate
write-host "Successfully Connected to $hostname on $port" -ForegroundColor Green -BackgroundColor Black
$tcpstream = $tcpsocket.GetStream()
Write-host "Reading SSL Certificate…." -ForegroundColor Yellow -BackgroundColor Black
#Create an SSL Connection 4
$global:currentURi = $uri
$sslStream = New-Object System.Net.Security.SslStream($tcpstream,$false,$global:RemoteCertificateValidationCallback)
#Force the SSL Connection to send us the certificate
$sslStream.AuthenticateAsClient($hostname)

#Read the certificate
$certinfo = New-Object System.Security.Cryptography.x509certificates.x509certificate2($sslStream.RemoteCertificate)
}

return $certinfo
}

Function Write-URL($url)
{
$uri = [System.Uri]$url
Write-Host "$($uri.Scheme)://" -ForegroundColor Gray -NoNewLine
Write-Host $uri.Host -ForegroundColor Yellow -NoNewLine
Write-Host $uri.LocalPath -ForegroundColor Gray -NoNewLine
Write-Host $uri.Query -ForegroundColor DarkGray -NoNewline
}
Function Get-HttpContent($url)
{
try
{
Write-Host "Reading " -NoNewLine
Write-URL $url
Write-Host ""

$request = [System.Net.WebRequest]::Create($url)
$request.UserAgent = $UserAgent
$request.AllowAutoRedirect = $false

# 30 second timeout
$request.Timeout = 30000
$request.Method = "GET"
$request.UseDefaultCredentials = $true

$response = $request.GetResponse()
if ($response.StatusCode -eq "OK")
{
Write-Host "Status Code OK" -ForegroundColor Green
}
else
{
Write-Host "Status Code $($response.StatusCode)" -ForegroundColor Magenta
}

if ($response.StatusCode -eq "Ambiguous" -or
$response.StatusCode -eq "Found" -or
$response.StatusCode -eq "Redirect" -or
$response.StatusCode -eq "Moved" -or
$response.StatusCode -eq "MultipleChoices" -or
$response.StatusCode -eq "RedirectKeepVerb" -or
$response.StatusCode -eq "SeeOther" -or
$response.StatusCode -eq "TemporaryRedirect" -or
$response.StatusCode -eq "RedirectMethod" -or
$response.StatusCode -eq "MovedPermanently")
{
$redirectURL = $response.Headers["location"]
# relative redirect
if ($redirectURL.StartsWith("/"))
{
$redirectURL = $url + $redirectURL
}

Write-URL $url
Write-Host " redirects to "
Write-URL $redirectURL
Write-Host ""
Process-URL($redirectURL)

}
else
{
$stream = $response.GetResponseStream()
$reader = [System.IO.StreamReader]$stream
return $reader.ReadToEnd()
}
}
catch
{
Write-Host "FAILED. Error: $($_.Exception.InnerException.Message)" -ForegroundColor Red
}

}

Function Process-URL($url)
{
$uri = [System.Uri]$url
Write-Host "Checking certificate for " -NoNewLine
Write-Host "$($uri.Scheme)://" -NoNewLine -Foregroundcolor Gray
Write-Host "$($uri.Host)" -ForegroundColor Yellow
if ($uri.Scheme -eq "https")
{
$certInfo = Check-SSLCertificate $uri
Write-Host ""
Write-Host "Certificate Summary for " -NoNewline
Write-Host $($uri.Host) -ForegroundColor Yellow
Write-Host $certInfo
Write-Host ""
}

$content = Get-HttpContent($url)
if (![String]::IsNullOrEmpty($content))
{
if ($content -match '<meta\s{1,}http-equiv(\s)?=(\s)?"Refresh".*url\s{0,}=(.*)"')
{
$redirectURL = $Matches[3].Trim()
# relative redirect
if ($redirectURL.StartsWith("/"))
{
$redirectURL = $url + $redirectURL
}

Write-URL $url
Write-Host " redirects to "
Write-URL $redirectURL
Write-Host ""
Process-URL($redirectURL)
}
}
}

ForEach ($url in $urls)
{
Write-Host "**************************** " -NoNewLine -ForegroundColor Cyan
Write-Host $URL -NoNewline -ForegroundColor Yellow
Write-Host " ****************************" -ForegroundColor Cyan
Write-Host ""
Process-URL($url)
Write-Host ""
}

if (![string]::IsNullOrEmpty($reportfile)) { Stop-Transcript }