Always Encrypted:

• This is a new feature which is useful for managing highly sensitive data
• Unlike TDE it encrypts data at rest means physical files (Data, Log and Backup), data in memory and data in communication channels.
• TEMPDB is uninvolved from encryption
• Encryption can be applied to column level.
• A driver that encrypts and decrypts the data as it is sent to the database SERVER is installed on the client.
• Application connection string must be changed.

Row Level Security:

• This is first introduced in Azure SQL Database. Now it’s part of on-premises feature from SQL Server 2016.
• Data NEED not be encrypted but we can restrict the users to see the sensitive data. No master keys or certificates required as there is no encryption
• Row-level security is based on a table-valued function which evaluates user access to the table based on a security policy that is applied to the table.
• The access levels only applies to SELECT, UPDATE, DELETE operations but anyone who is having INSERT permissions can insert rows.
• Only problem with this is using user defined functions to control user access which is a huge disadvantage from performance prospect.

Dynamic Data Masking:

• Masks data at select TIME based on user or database roles (Mainly for Read-only Users).
• It actually doesn’t change the data but mask data based on the user who access that data.

For example:

I have a columns called “CredNo” to store customer creditcard NUMBER. If I mask this column then it will be viewed as 22XXXXXXXXXX56.

But as I said data is not modified only this logic applied and data is masked based on the user/role.

A SYSADMIN or db_owner can VIEW the actual data.

We can use 4 different types of functions to mask data: Email, Partial, Default, Random

Always Encrypted:

Row Level Security:

Dynamic Data Masking:

For example:

I have a columns called “CredNo” to store customer creditcard number. If I mask this column then it will be viewed as 22XXXXXXXXXX56.

But as I said data is not modified only this logic applied and data is masked based on the user/role.

A SYSADMIN or db_owner can view the actual data.

We can use 4 different types of functions to mask data: Email, Partial, Default, Random