SQL Server 2016: hide column data from database administrators, but specific users can view data through the application

Question

I am trying to allow access to payroll group leaders through PowerBI, but encrypt this data from other users and database administrators. Users who are denied access to the data in this column can still fulfill the request, but see only encrypted characters for salary information.

I am using SQL Server 2016.

I tested the new Always Encrypted functionality, and it works fine ... but except that I cannot pass the encryption setting = enabled parameter to the PowerBI connection string. By all accounts, PowerBI does not currently support this functionality.

I am currently testing the use of column encryption using column level encryption and symmetric keys, but the problem is that I hardcode the OPEN SYMMETRIC KEY SymmetricKey1 and DECRYPTION BY CERTIFICATE Certificate1 code in SQL, and if users do not have access, then the error results SQL crash during user validation.

I am new to certificates and encryption, and currently I am on a steep learning curve ... so it is easy on me.

thanks

Answer 1

You can use dynamic data masking.

Masking dynamic data works by masking column output for users who do not have permissions. The sample examples were tested in 2016 based on the demo presented here: Exploring SQL Server 2016. Masking dynamic data. Part one. Create a table using a dynamic data mask .

--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, ' [e-mail] ', '0123-4567-8901-2345','02/22/1732'), ('Thomas', 'Jefferson', 5559841298, ' [e-mail] ', '9999-9999-9999-9999', '04/13/1743'), ('Abraham', 'Lincoln', 5554070123, ' [e-mail] ','0000-1111-2222-3333', '02/12/1809'); 

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

 select * from clientinfo 

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

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

now try to run as this user

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

Performing the above request, it will not display all the data and will be masked differently based on masked functions. See below screenshot

To provide access to users, you can use the query below

 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 for any user, including database administrators / sysadmins.

Dynamic data masking protects against ordinary users. The example presented above is easily complemented by any user with administrator level access.

Column-level encryption usually does not protect users from administrative rights. The database administrator or system administrator can always open the key or replace it. There are workarounds for this via ekm, but nothing is scaled or used in your scenario.

Rogue administrators are one use case. Encrypted was designed to be a solution, so this is the right solution. This is what the PowerBI team should implement, so if this feature is important to you, suggest adding your vote and comments to your feedback forum: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions / 14732184-sql-server-2016-always-encription-features