I have looked at many of the post for this but don't find something quite like what I need. This may sound like a weird request but:
We have a partner with a SQL server on their network and we need to build reports (Both Excel and Power BI) that are on our network. We are building a IPSec tunnel to their server for connectivity but we can't open their network up to 1000 different connections besides the IPSec tunnel would be a monster and change constantly since excel is on the local PC.
For legal reason we can't simply replicate the data into a SQL server on our network (We don't own the data -and while we can see it it is not legally the same as being stored on our network – Already checked with the legal department.)
This leads us to a bit of an awkward arrangement. I need to have reports point to a single server on our network and that server will forward the requests to the remote SQL server and pull the data back into the report WITHOUT actually storing the data. I have set up an alias and linked server but am having issues with the forward without storing data locally.
Any ideas on how we can achieve this? All thoughts are welcome.
Best Answer
What about a SSRS server with a connection to the remote SQL server.
Not the same as yours, but I've managed a SQL server where I imported data from a remote Postgres server two firewalls away. The networking guys had to set something up to route the traffic there.