6

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

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
Mako
  • 83
  • 6

2 Answers2

3

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

SQLmojoe
  • 1,924
  • 1
  • 11
  • 15
  • Almost 6 years later and this is still the correct and only acceptable method today. Always Encrypted is the only way to prevent db_owners and other db admins from viewing the data in specific columns. Dyanmic data masking is easily bypassed and (almost) not applicable to db_owners. – Arbiter Dec 19 '22 at 14:08
2

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, 
'GeorgeW@datanbasejournal.com', '0123-4567-8901-2345','02/22/1732'),
('Thomas', 'Jefferson', 5559841298, 
'ThomasJ@datanbasejournal.com', '9999-9999-9999-9999', '04/13/1743'),
('Abraham', 'Lincoln', 5554070123, 
'AbrahamL@datanbasejournal.com','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

enter image description here

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;
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • Thanks TheGameiswar, but I've created the steps with standard Windows Account user (Admin privleges) without problem but when I log on as the main Admin 'sa' this user is able to view all of the data. – Mako Mar 16 '17 at 14:29
  • Admin can see all data,one sysadmin can't be differentiated from another sysadmin.for this to work,you have to create users with least privelages as mentioned above – TheGameiswar Mar 16 '17 at 14:31
  • Thanks, but this is the entire concept of the 'Always Encrypted' functionality and hiding data from external DBAs, sys admins or cloud admins. – Mako Mar 16 '17 at 14:53
  • 2
    I am not sure about always encrypted,but as per my understanding,it hides data at rest and in motion and i am not sure,whether this can be usefull for your to hide columns – TheGameiswar Mar 16 '17 at 14:55
  • 1
    @Mako you are correct. Always Encrypted will protect the data from DbAdmin, sysAdmin and all other admins of SQL Server, in addition to protecting data in rest and in motion. I explain the security guarantee and how it is achieved here http://stackoverflow.com/questions/42157845/always-encrypted-behavior-in-sql-server-2016/42235099#42235099 – Nikhil Vithlani - Microsoft Mar 16 '17 at 17:33
  • Thanks Nikhil. Unfortunately I'm also unable to find a solution that works in either PowerBI or SSRS. PowerBI doesn't take the parameters from Always Encrypted, and Always Encrypted can only be applied dynamically to different users within the Reports Server environment. The only other method that I can think is by capturing the Error Message in a Table Function when utilising symmetric keys and returning a SELECT statement without the 'Decryption by Certificate' text contained within. – Mako Mar 17 '17 at 12:14