Azure Synapse Linked Service Connection Issues

I have set up a linked service on Azure Synapse it is like this:

The problem I get is that when I try to preview the data for a particular company, I get this error:

Error details
Error code
The HttpStatusCode 400 indicates failure.
Request URL:
Response payload:{“error”:“Invalid Authorization header”,“type”:“ch:service”}
Activity ID: 33f5e8f4-d01f-4261-8647-3f22ead08a08

Can anyone let me know where I am going wrong please?

I have worked it out:

The APIKey goes in the box next to Authorization Header

1 Like

The error message suggests your issue is with the http basic authentication.
This often causes people problems. Partly due to the choice by Companies House to have a username which many people mistake for the password - the CH API key goes in the “username” field and there is no password e.g. it’s blank. Also due to people not being familiar with REST / http / http Basic and finally not knowing how that works with the system they’re using works with it.
As always I suggest making sure that your API key is correct using curl. It likely is but if you use curl it’s easier to sort out this or other errors. See the http Basic bit in Companies House documentation: Authentication

I don’t know anything about Azure Synapse but there is documentation here:

Firstly - assuming the “Authentication Type” section applies to the connection to Companies House API (and not to Azure Synapse itself) then you should not have anything in “Password”. The API key is what goes in the the http basic “username” - which you may have done here - and the password should be blank. It looks like you’ve put in a password - don’t. (If the Azure interface here doesn’t let you do that you’ll have to do this manually, read on).

Second you may or may not be adding an Authorization header here. If Azure lets you have a blank in the password above, you shouldn’t send any additional Authorization header. Just let Azure do this for you. If you found that you couldn’t put nothing in the password then you will have to switch off / remove / choose no Authentication type and manually create an Authorization header. That will be (per the documentation above) Basic base64 ( your api key + “:” ) - although exactly how you do this depends on Azure’s interface of course!

Good luck.

Thanks for this. I am working on something similar and I used this method. it works

1 Like

Thanks for letting me know Hijesh.
I kept getting the wrong advice on here.

1 Like

Hi there.
I worked it out as shown in my screenshot above.
This is the synapse linked service configuration and it works - hurray!
I hope this can help others. I am not sure why it this solution hasn’t been posted before.
One of the points of synapse (in my opinion) is that you shouldn’t need a degree in computer science in order to grab data for your BI project.

Glad to hear it. Thanks for posting for the benefit of others!

(Editorial) Microsoft has a reputation for doing things in a different way to some of the computer science (or just computer engineering) “standards”. This may be helpful for people but can mean there is a bit of a cliff edge for those going beyond the Microsoft environment. Also the ideal of being able to “simply unify the world of APIs and services” is just that - an ideal. In my limited experience trying to use a range of APIs from across the net you’ll often soon be wading through detailed specifications or even source code. For large public data sets there’s often “learning by experiment” too. There are still a wide range of methods of authentication and ways of organising and presenting external interfaces to the world.

Fortunately we live in an information-rich age. Once you’ve learnt the uses and limitations of Google, Wikipedia, StackOverflow etc. the computer science degree may prove to just be a bonus.