We have MSSQL DB in EC2, where we are using generic user (Active Directory User) for SQL to perform any activity inside the server. This user is having "sysadmin" permission and it requires to perform all admin tasks .But, as xyz company being GDPR complaint, We should not have access to customer's data. So, we need solution that this user can perform any admin activity but it should not able to query any data from tables.
1 Answer
The SQL Server feature Always Encrypted is for this:
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.
...
Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.
-
1