'SQL Server Linked Server to Azure using Certificate-based Authentication

I have a client whose CRM backend is replicated to an Azure SQL database. I have an on premises SQL Server instance and would like to set up their database as a linked server in my environment. Unfortunately, all the documentation I can find for accomplishing this assumes basic username/password authentication. That is not a possibility here. For authentication, they have given me:

  • Database Type: Azure SQL Database
  • Server Name: [redacted].database.windows.net
  • Application ID
  • Certificate Thumbprint
  • Private Key
  • Certificate Public Key

I understand that I could use these pieces to essentially write API code to grab from the database the things that I need, but I would really rather have live SQL query access to this data if at all possible. I'm sure this is doable, I am just coming up short with how to do it. In my head, it is as simple as setting up my own Azure SQL Database that points to their instance and then setting up the on prem linked server to my own Azure SQL Database, basically using my Azure instance as a bridge. But I am not sure if that's possible or how to do it, or if that's indeed the best option.

If someone could point me in the right direction, even to some documentation or similar examples, that would be great. Googling this for a couple days has resulted only in frustration. Thanks.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source