Masking data for SA users

Question

I'd like to able to mask data for all users including SA's. I'm not trying to guard against malicious intent, but rather, stop people unintentionally seeing data they shouldn't.

Dynamic Data Masking seemed like a good solution but, from what I've read, it doesn't apply to admin users.

Is there a good alternative?

This is for a database that has not yet been created. I'll be importing data from other data sources. Our default version\edition is SQL 2017 Enterprise. Azure PaaS is also a possibility.

Answer 1

As far as I'm aware if you need to hide data from everyone, including SA/Sysadmins then there's only 1 real option.

Always Encrypted (Microsoft Learn | SQL)

Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national/regional identification numbers (for example, U.S. social security numbers), stored in Azure SQL Database, Azure SQL Managed Instance, and SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. This provides a separation between those who own the data and can view it, and those who manage the data but should have no access - on-premises database administrators, cloud database operators, or other high-privileged unauthorized users. As a result, Always Encrypted enables customers to confidently store their sensitive data in the cloud, and to reduce the likelihood of data theft by malicious insiders.

The thing that makes it unique is that you need a certificate + permissions on the SQL to be able to see the database, so just having admin rights on the SQL Server will not be enough to view the data.

It has a lot of downsides and limitations that you need to double check if they work for you. The list is a bit too long to go over here and also depends if you want to use Randomized or Deterministic encryption.

I'd personally stay away from this unless encryption is of the highest importance, due to the many downsides, but it does provide what you need.

Answer 2

Azure PaaS is also a possibility.

One of the cool things about Azure SQL Database in this scenario is that you don't need any production DBAs. So the owner of the Azure resource can be the data owner.

Or you can give an AAD group admin rights, but not have any users with standing access to the server, eg using Privileged Identity Management.