So I've been tasked with learning how to secure our SQL Servers. Here's the scenario:
Individual users have read access to a table in SQL Server. This table have apx. 33 million rows, and growing (telemetry data).
Some genius user discovered Power BI and installed it (Desktop), along with the on-prem data gateway on their workstation. Then they did the same thing for 40 of their friends.
THEN they created a .pbix report to query this table.
THEN they published the report to their personal workspace.
THEN they emailed the report to their 40 friends with instructions on how to set up the gateway on each workstation – and how to enable the data sync schedule for twice a day.
So now our 33 million records are exiting our network (40 users * twice a day = 80 times per day). Besides being a strain on the SQL Server – we just don't want that data being published to a service we don't have any control over by users that aren't authorized to be doing that.
So the questions are:
How can we prevent this scenario? What's the proper way to set up a database so a user can access the data, but not be able to publish the data off-site? Are there firewall rules we can set up to block the gateway service? My understanding was it just needed port 443 outbound – which we obviously can't block.
Any recommended training courses that cover this kind of stuff?