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

Hi ChrisOnesBroken,

There are some proposals for you troubleshooting this issue.

1. Ensure that you download the latest version of Power BI Gateway from this link.
2. Make sure  that you add required ports and whitelist the IP addresses described in this article in your firewall.
3. Uninstall the current gateway, restart your machine, then install gateway and check if it is successful.

Thanks,
Lydia Zhang

Dear ChrisOnesBroken

WHether this issue got resolved.

We are also facing the same issue.

Can Anonymous help?

Br,

SHams

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 }