Connecting to an external Azure SQL database that uses a One Time Password for security

[D_PBI]

Hi all,
I need some guidance on extracting data from an external (external to my company) Azure SQL database.
Company ZERO has setup a remote desktop connection (RDC) on my work laptop. By clicking on the RDC and entering UserName and Password and then entering a One Time Password (that I obtain from a fob device), I am able to logon to Company ZERO's portal and, from within that portal, load SSMS and query the tables/views Company ZERO have granted me access to. So far, so good.

The problem arises when I attempt to access Company ZEROs Azure SQL data through Power BI Desktop on my work laptop. I load the PBI Desktop, choose 'Azure SQL database' as the connection method and the attempt the following details:
Azure server location = dcs-xx-zero.uksouth.cloudapp.azure.com (this is seen at the middle/top of the RDC portal)
SQL Server name = XX-ZERO (confirm through looking at the SQL Server property details)
Database name = XX_Live_Data (one of the many Tables I have access to)
View name = XX_Summary_Combined (one of the many Views I can read)

I populate the connection method as follows:

When I click 'OK' it returns the below error:


To note, I attempted the above whilst leaving the RDC session open (the access through the Remote Desktop Connection portal where I need to enter a UserName, Password and One Time Password - just in case leaving it open allows for PBI Desktop, from my laptop, to connect), however as you can see it made no difference - the connection attempt failed.

From the outset, I suspect this PBI Desktop connection attempt wouldn't be successful as they is no where via the PBI Desktop connection attempt, from my laptop, outside of the RDC portal, that I could enter a One Time Password which was needed when logging on to ZERO's Azure SQL Server RDC.

I would like to know if I am missing something in my PBI Desktop attempt?
Have I entered the details in the connection box (first screenshot) correctly?
Was the failed attempt expected and what do I need to suggest to the Company ZERO for them to do to allow me to use Power BI Desktop to extract data from the mentioned database's Tables/Views?

Thanks in advance.

[D_PBI]

@lbendlin 
Yes, I'm suspecting I will need to request the third-party to open up their Azure SQL Server to allow me to extract the required data.
Someone in my organisation, although this person is not a PBI developer, has stated PBI can extract the required Azure SQL Server data via an OData REST API. I have no knowlege of OData REST APIs (just starting to read up on them) but is this statement correct?
My apporach would be the '101' PBI apporach of using an Azure SQL Server connection to extract the data. As this isn't working I would then seek the third-party, to do the necessary, to allow for me to extract data from the Azure SQL Server. This is the apporach I feel I will go down. Just wondering if you aware of why such a statement on using an OData REST API approach would have been made?

Thanks.

[lbendlin]

Even if they would open up the ODATA interface (not sure why they would want to do that) it will be substantially slower than the native database connection.  It would support query folding though.

[D_PBI]

@lbendlin - thanks for your response.
'Jump host' and RDP (not RDC as I've been using) are the better terms.
I doubt I can install Power BI Desktop on the RDP host as it is not my/my company's machine so I'm not sure if the third-party will be happy with that. However, let's say they were, If I install PBI Desktop on the EDP jump host I would be able to create a PBI report but if I wish to publish that report to my company's PBI Service (which is outside of the third-party domain) then I wouldn't be able to do this.
I need to be able to create a PBI report from my domain that connects through Company ZERO's Azure SQL database and publish the same report to the PBI Service within my company's domain. I would then need the scheduled refresh to complete successfully.

With all I've explain in my two posts, is what I'm trying to achieve possible and how is it done?  Am I submitting the correct details to the PBI connection configuration box?
If it is not possible then what are the suggestions to follow when a need like this arises?

Thanks.

[lbendlin]

It won't be possible. You need to arm wrestle their Security IT team into an agreement that allows you to access the SQL database directly somehow. This will become an even greater nightmare once MFA gets rolled out more widely.

[lbendlin]

Can you install Power BI Desktop on that jump host (the RDP machine)?