Sql-server – How to secure SQL Server in an enterprise environment

powerbiSecuritysql server

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!

Best 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

Related Solutions

MS SQL Server Encryption and Backups – Understanding Security

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION`  
 BY CERTIFICATE MyCertificate`
notice - there is no password here.

Yes, there is. You opened the certificate MyCertificate somehow, didn't you? There could be several cases, but any of them ends up with a password:

you opened the certificate using a password (obviously, there is a password in this case)
the certificate is encrypted with the database master key and you opened the database master key using the DBMK password -> that's the password
the certificate is encrypted with the database master key and the DBMK is encrypted with the service master key. The service master key was in turn opened using the service account DPAPI storage key, which is encrypted with the account password -> and that is the password

In the last key there seem to be no password, but there is one: the service account password. The backup, when restored on a different host, it will lack the service master key encryption of the database master key and this will make the data inaccessible.

See Encryption Hiearchy

Sql-server – SQL Server Sandbox

Another idea would be to simply set up a nightly job that does a copy_only backup and restores it on the dev server (or on the same server, if you only have one, but that might not be a great idea). The nice thing about this is that the restore can go to any server (or multiple servers), and can be completely decoupled from any activity on the primary database.

On server 1:

BACKUP DATABASE db TO DISK = '\\someshare\file.bak' 
  WITH COPY_ONLY, INIT, COMPRESSION;

On server 2:

RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
  WITH REPLACE, RECOVERY;

You may need to also add MOVE commands if the disk layout between the servers is different (or if you're putting the copy on the same server).

RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\somepath\somefile.mdf',
  MOVE 'log_file_name'  TO 'E:\somepath\somefile.ldf';

If you're restoring on the same server, you shouldn't have any issues with users. If you restore to a different server, your users will exist but the server-level logins may not. There are scripts to fix that, and a new feature in SQL Server 2012 (Contained Databases) which eliminates the problem altogether.

Best Answer

Related Solutions

MS SQL Server Encryption and Backups – Understanding Security

Sql-server – SQL Server Sandbox

Related Question