'How to retrieve data from SQL Server 2005 on Windows Server 2008 R2 on static IP, in a secured manner to Power BI? [closed]
Situation
My company software is running on SQL Server 2005 on Windows Server 2008 R2 with a static IP.
Requirement
I wanted to access data for reporting into Power BI.
What I have tried so far
Direct Access: connect to company's SQL Server instance directly. For it to work, I need to open the ports which might compromise security (which is not allowed).
Backup Transfer: transfer my SQL Server backup to Google Drive or OneDrive. Then import my backup into a different SQL Server in the cloud. But Google Drive Sync or OneDrive are not supported on Windows Server 2008 R2. So no further process.
Replication: created a SQL Server 2019 on Digital Ocean (10$ per month) Ubuntu 20. When I started replication from my company SQL Server Management Studio 2005, at the time of selecting subscriber, it said that the version 15 (SQL Server 2019) is not supported. Now version SQL Server 2012 might be supported for replication but it is not there on Ubuntu. I do not think that Power Bi Supports version less than SQL Server 2012.
How to do it?
- Is there a way to allow Direct Access without compromising Security?
- Is there way to send SQL Server 2005 backups to the cloud and import them into SQL Server 2017 or above?
- Is there any other thing like SQL Server Reporting or Analysis Services which might be there for this purpose which I am missing completely.
Budget around 10 -> 15$/monthly.
Solution 1:[1]
That is what the Power BI Gateway is for. You install it on a machine in the same network as your SQL Server, and it makes an outbound connection to Power BI, listens for data requests and manages the connections to your private data sources.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | David Browne - Microsoft |