SQL Server 2016: Hide column data from DBAs but specific users can view data through application

Question

I'm trying to enable access to team leaders of salary information through PowerBI, but encrypt this data from other users and the DBAs. Users denied access to this column data should still be able to execute the query but only see encrypted characters for the salary information.

I'm using SQL Server 2016.

I have tested the new 'Always Encrypted' functionality, and this works perfectly... but with the exception that I'm unable to pass the 'column encryption setting=enabled' parameter to the PowerBI connection string. By all accounts PowerBI does not support this functionality at present.

I am currently testing the use of column encryption via the use of Column Level encryption and Symmetric Keys, but the problem with this is that I am hard coding the OPEN SYMMETRIC KEY SymmetricKey1 & DECRYPTION BY CERTIFICATE Certificate1 code into the SQL and if users do not have access then an error causes SQL to fail when tested by a user.

I'm new to certificates and encryption and I'm currently on a steep learning curve... so go easy on me.

Thanks

Answer 1

you can use dynamic data masking.

Dynamic data masking works by masking column output to users,who don't have permissions .Below examples have been tested on 2016 based on demo provided here :Exploring SQL Server 2016 Dynamic Data Masking – Part One - Creating a Table that uses Dynamic Data Masking

--create a table

CREATE TABLE ClientInfo
  (ClientID int IDENTITY,
   FirstName varchar(65),
   LastName varchar(65),
   PhoneNum bigint 
      MASKED WITH (FUNCTION = 'default()'),
   EmailAddr varchar(100) 
      MASKED WITH (FUNCTION = 'email()'),
   CreditCardNum varchar(19) MASKED 
      WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)'),
   BirthDT date MASKED 
      WITH (FUNCTION = 'default()'));

INSERT Clientinfo (FirstName, LastName, PhoneNum, EmailAddr,CreditCardNum,BirthDT) VALUES 
('George', 'Washington', 5555814441, 
'[email protected]', '0123-4567-8901-2345','02/22/1732'),
('Thomas', 'Jefferson', 5559841298, 
'[email protected]', '9999-9999-9999-9999', '04/13/1743'),
('Abraham', 'Lincoln', 5554070123, 
'[email protected]','0000-1111-2222-3333', '02/12/1809');

Now try to just select and see the data ,since you are an admin ,you will be see all data

select * from clientinfo

now try to restrict permissions to users for whom ,you want to restrict viewing

CREATE USER user1 WITHOUT LOGIN;
GRANT SELECT ON ClientInfo TO user1;

now lets try to execute as this user

EXECUTE AS USER = 'AppReader';
SELECT * FROM ClientInfo;
REVERT;

executing above query ,will not show all data and will be masked differently based on masked functions.See below screenshot

To provide access to users,you can use below query

CREATE USER AppAdmin WITHOUT LOGIN;
GRANT SELECT ON ClientInfo TO AppAdmin;
GRANT UNMASK TO AppAdmin; 

Answer 2

Unfortunately, AE is the only existing built-in solution that can prevent unauthorized access by any user, including DBAs/sysadmins.

Dynamic data masking protects against regular users. The sample provided above is easily side-stepped by any user with admin level access.

Column level encryption generally does not protect against users with admin level permissions either. A DB owner or sysadmin can always open the key or replace it. There are workarounds to this via ekm but nothing scalable or usable in your scenario.

Rogue admins is one of the use cases Always Encrypted was designed to address so it is the right solution. It is something the PowerBI team needs to implement so if the feature is important to you, suggest you add your vote and comments to their feedback forum: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14732184-sql-server-2016-always-encription-features