0

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?

Thanks!

  • 2
    Well, if the user has permissions to query the data directly, then there is no real way to stop them from doing this kind of thing. – Jonathan Fite Jun 10 at 20:22
0

Here are couple of comments / advice regarding performance / network:

1) Those 40 guys that currently download data daily, they obviously using "Import" mode, and they added this table as a data source, and Power BI pulls this whole table (33 mln row) every time report refreshes. What you can do is to work with them to refactor how they query data. If they need an aggregate values from that table, they can use "Import" mode and run an aggregate query instead of pulling whole table each time. This will be MUCH lighter in terms of performance and network consumption

2) why all 40 users each need to have personal gateways ? If those guys really need this data, 1 user should be enough, this user can then share his report to his colleagues so others 39 won't be hitting your SQL Server - they would just read report data from the 1 guy's PC

  • To answer #2, and shed a little light, this whole thing was done without corporate IT involvement. A rogue operation. Nobody is/was being malicious - they just don't know any better. And instead of yanking the rug out from under them we're trying to understand the available options so that we can begin to work with them. But at some point we need to be able to shut down these rogue operations - and that's what my question is really about I suppose. How can we shut this kind of thing down, yet still maintain an open enough system that users can continue accessing the data they need. – Sam Axe Jun 10 at 21:05
  • Try creating a LOGON trigger on server. This logon trigger must contain code "...if program_name() = 'Mashup Engine' rollback..." – Aleksey Vitsko Jun 11 at 20:17
  • This means that those users will be able to connect to your SQL server using "Microsoft SQL Server Management Studio - Query" or ".Net SqlClient Data Provider", etc., but will NOT be able to connect using "Mashup Engine". Mashup Engine is the program_name when data gateway connects to your SQL Server using those users credentials. This way you will block the data gateway access to your SQL Server – Aleksey Vitsko Jun 11 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.