Power query to connect to localserver

[rotalwar]

Hi

I am new to any such community help - thanks in advance if there's someone who knows about connecting to a localserver instance.

specifically, I am trying to connect to an authenticated local instance of my connection to endpoints that interactive brokers provide. I start their service, using credentials provided. I can then connect to their DB/Endpoints via links for example:

https://localhost:5000/v1/api/trsrv/secdef/schedule?assetClass=STK&symbol=MSFT to get security information. I have set up a postman account, to get some familiarity with the HTTP request (GET, and at some stage POST).

I get a response with postman, which for all intents and purposes appears complete and valid.

in excel power query I get some SSL/TLS secure channel error:

 All other "queries" i have tried, which are standard ones from IB, seem to work (in a browser, when I allow  "Access control allow origin" via a chrome extension.

Is there anyone out there that may be able to help me troubleshoot the connection. Ultimately I would like this within Power Query within Power BI, but just trying it out on the Excel version.

[rotalwar]

Hi

I found what seems a solution. I created a local certificate using a java based keytool, and point the gateway config to that cert (locally).

Then, under the covers, there must be some interchange with PQ (in Excel), and the various security methods embedded, to check SSL/TLS, which seems to meet it's [PQ] validity/security requirements. This then allows PQ to retrieve successfully. I have a little more work to do to understand this, as SSL is not something I have familiarity with, including any authentication. 

Regards

RT

[rotalwar]

Hi

I went to the post suggested, which suggests the clearing of ssl state.

There doesnt seem to be any Chrome equivalent. the post is from 2016 or so, and Chrome ((Version 98.0.4758.102 (Official Build) (32-bit)) doesnt have anything coming up in a settings search (SSL state), that would allow me to clear anything.

IE is now deprecated vs Edge - and again, there doesn;t seem to be such an option. I am also possibly not getting the connection to the message from Power Query, and the SSL state in the respective browsers. Or is there a central windows option relating to the SSL certs that these browsers write to, and PQ reads ? (Or I misunderstand). Perhaps the settings search I use is too specific, or lacks changed terminology.

So problem seems to persist, but I also cannot follow the suggested solutions.

Perhaps 

[rotalwar]

Hi

Thanks for your response. I am not using IE, but Chrome, however the error message is from Excel, within the power query interface. It seems there is a certificate to create. I dont know how the localhost will know it's there.

If there's a config elsewhere in PQ, please let me know, but I don't think it is an IE setting clearing that solves it. Perhaps a chrome SSL clearing?

regards

Rohan 

[v-stephen-msft]

Hi @rotalwar ,

If you can, both Chrome and IE can clear it up a bit.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-stephen-msft]

Hi @rotalwar ,

Try the following solution from this post:

Clear the Secure Sockets Layer (SSL) state. To do this, follow these steps:

1. In Internet Explorer, click Tools, and then click Internet Options.
2. Click the Content tab, and then click Clear SSL state

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.