Connect to SQL Server instance over SSH with continual access

[LaBicicleta]

Good evening,

Our company just got given "the keys to the castle" to the SQL Server database that runs our instance of our production software. Up to this point we've been manually exporting XLSX's and CSV's into a folder and refreshing and re-publishing the dashboard ourselves twice a week. Now that we have direct SQL access, management would like this to stay populated and current continually.

The caveat is that our software vendor is only letting us connect to the SQL Server with an SSH connection. From everything I'm reading, the only idea I have is to run PuTty on a terminal server where Power BI Desktop resides, connect via SSH, and then pull the SQL Server as localhost. But this will not really help us: we'd still have to re-establish the PuTty connection and refresh and republish the dashboard when we want it updated.

Am I correct in saying this is our only choice? Is there another mechanism by which I could get a "mirror" of the SQL Server database that I'd have carte blanche access with? I'm open to buying Azure resources but haven't the foggiest clue on how to make the most effective choice here.

[amitchandak]

@LaBicicleta , I checked a couple of options and they talk about, putty.

In past I have used this method of SSH tunneling using putty for long

https://www.a2hosting.com/kb/developer-corner/mysql/remote-mysql-connections

https://towardsdatascience.com/connecting-to-aws-remote-mysql-server-via-an-ssh-tunnel-6669a84cee91