How to Query Azure DB From Another Azure DB on a Different Server

Question

How do you query an Azure DB from another Azure DB on different server?

Answer 1

You can use elastic queries as shown on this article. You just need to create an "external data source" and after that create an "external table" and you are ready to go.

Here you will find more detailed example.

In the comments, you mention you only have read only permissions on the remote database, in that case you can create a Linked Service in Azure Data Factory to perform incremental data loading. Below how to create the linked service in Azuer Data Factory.

{  
    "name": "MyAzureSQL",  
    "properties": {  
        "description": "",  
        "hubName": "my_hub",  
        "type": "AzureSqlDatabase",  
        "typeProperties": {  
            "connectionString": "Data Source=tcp:[yourdatabase].database.windows.net,1433;Initial Catalog=[yourdatabase];Integrated Security=False;User ID=[youruser]@[yourdatabase];Password=[yourpassword];Connect Timeout=30;Encrypt=True"  
        }  
    }  
}  

Linked Servers, as we know them on SQL Server and Azure Managed Instance, are not supported on Azure SQL Database.

Answer 2

Hi @Pat Snow Thank you for posting your question on Microsoft Q&A and for using Azure services.

My understanding is that you are trying to do a cross database query in Azure SQL Database. As @Alberto Morillo mentioned above elastic query will help to achieve that.
You also mentioned that you have only read permissions on the remote database. You will need to be granted a RemoteDB user the rights to SELECT on the table. This will allow the OriginDB query to authenticate against RemoteDB with the correct credentials and avoid bumping into permission issues later on.

GRANT SELECT ON [RemoteTable] TO RemoteLogger; Please see example here Cross-database Query in Azure SQL Database.

Please let us know if this is not clear.

Regards,
Oury