sql server - How to join multiple azure databases without rights to configure external tables?

Question

In my current setup I connect to an Azure SQL Server using Authentication=Active Directory - Integrated. This method of access only allows access to a single database at a time. The architecture was migrated from an on premises SQL server environment with changes to make cloud development feasible, but still analytics and debugging must occur across databases. Typically one would simply do a cross database join with a legacy SQL Server configuration, possibly involving link servers if the databases were on separate servers. In the cloud or azure you are supposed to use Elastic search or avoid the architecture designs that make this needed. On the source data I only have read access which is a problem since you need ALTER ANY EXTERNAL DATA SOURCE permission so I am thinking I may have to resort to an ETL tool to perform this type of join, but wondering if there are other options or tools that allow one to essentially perform cross database or cross server joins in azure with only read access.

What are some ideas for performing these types of joins given only read access?

Answer 1

I was hoping a savant would answer this one and tell me I was dumb for asking, but here are my current thoughts maybe this will bring her out... path forward as I see it below.

Given only read access it is best to pull all data at the onset from the different sources to an environment that I have full development rights (i.e. a PostgreSQL or MySQL server on localhost if small... or potentially a single development place on azure that the enterprise allows for full development rights). Create this in an ETL tool, i.e. SSIS, and run as updated data is needed for any delta in the source. Subsequently perform joins on this consolidated data.

This makes sense since I need to experiment with the joins, dealing with a small enough or transferable amount of data that the duplication of the data is not a resource hog, and would perform the experimental joins directly in a SQL editor instead of an ETL tool.

If I had a much larger dataset I would attempt to sample down, perform the same exercise and then once the cross server join was understood execute it at scale in an ETL tool, probably in some sort of batch fashion that ideally could be eventually executed in parallel.