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.
