PowerShell Script to Refresh PowerBI Dataset is failing

[Jayasreeb]

We use PowerShell script to refresh PowerBI Datasets. We run these scripts from SQL agent job.

The powershell script was running fine from our server for many days. But all of a sudden it started failing.I checked with Infrastructure team to see if any firewall settings are blocking the call to PowerBI. We couldn't find anything .

The same powershell script when ran from my desktop works fine and refreshes dashboard, but when it runs from server it gives me following error 

Invoke-RestMethod : The underlying connection was closed: An unexpected error occurred on a send.At 
+ ... $response = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

Any help to troubleshoot this issue is greatly appreciated.

[v-xulin-mstf · Accepted solution]

Hi @Jayasreeb 

It seems that the error is coming from Office 365 change were TLS 1.0 is being superseded by TLS 1.2. Since it's still working for my default configuration, I guess my tenant is not yet forced on TLS 1.2, but others already switched. The fix is simple. Before executing Invoke-RestMethod, just run this:

[Net.ServicePointManager]::SecurityProtocol=[Net.SecurityProtocolType]::Tls12

If you want to learn more, please refer:

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-restmethod?vi...

https://evotec.xyz/invoke-restmethod-the-underlying-connection-was-closed-an-unexpected-error-occurr...

Best Regards,

Link

[Jayasreeb]

Hi @v-xulin-mstf ,

 The solution you provided worked. My powershell script is not throwing error anymore after adding that piece of line. I appreciate your help with fixing this outstanding issue that I am working on from many days.

[GilbertQ]

Hi @Jayasreeb 

I would chat to your network Admin and make sure it can connect to the internet.

[GilbertQ]

Hi @Jayasreeb 

From the error it would appear to me that the server does not have access to the Power BI REST APIs?

[Jayasreeb]

Thanks @GilbertQ  for the reply. How can I give my server access to PowerBI rest API's.

my process was working fine for many days. All of a sudden on one day,it started failing.