How to secure SQL Server in an enterprise environment?

Posted on

Question :

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?


Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *