Back to List

6 Practical Data Protection Features in SQL Server (Pros & Cons)

Tony Ropson Tony Ropson  |  
Aug 25, 2020
About the author: Tony Ropson has been developing solutions in .Net and SQL Service since 2011. He holds an Azure Data Engineer Associate certification from Microsoft.
At Skyline, we have a moral (and oftentimes legal) responsibility to build software and data solutions that can properly protect our client’s confidential data because it can be detrimental to an organization if this data falls into the wrong hands.
As part of Skyline’s Solutions Protection program, we use many different tools to help keep our client’s confidential information secure. Some of the tools we leverage are the data protection features within SQL Server to protect confidential data and make it available to only those authorized to see it. This blog will cover the practical applications of these features, as well as a few pros and cons of each. 

1. Dynamic Data Masking

Adding a dynamic data mask to a column in SQL Server blocks out part of the information column. This is useful if an employee needs to see only part of some sort of ID number or even part of a phone number for verification purposes.
sql server dynamic data masking

  • It’s easy to add dynamic data masking to a column so end users will just see the masked data.
  • It doesn’t really protect the underlying data, and (if enough data is shown) malicious attackers can try enough combinations to guess at the data (this is called brute-forcing the data).

2. Different Database Schemas

Schemas are a way to restrict access to tables within a database. For instance, if we want to restrict access to tables containing an employee’s salary information, we can create a new schema called SensitiveEmployeeInformation and place the table containing the sensitive employee information into that schema. This way, only those with access to the SensitiveEmployeeInformation schema would be able to see the table containing the employee salaries. 
  • Schemas are a lightweight way to restrict access to sensitive information that have negligible performance downsides when querying the data.
  • Placing the tables in a different schema does not encrypt the underlying data (if that is a regulatory requirement).
  • Great care must be taken to ensure only authorized users are added to the groups that can access the schema.

3. Row Level Security

Row level security allows for the same users to access the same table, but SQL Server will automatically filter out rows the user is not allowed to see.
For instance, if we only want agents to see their fellow agents but not anyone on the director level, a filtering predicate would be created to filter out anyone higher than the agent level. 
Any agent querying the data would see a subset like this:
sql server row level security

Anyone in a director level or higher position would see this full data set:
sql server row level security

  • There is no need to put higher scarcity data in different schemas or tables. The data follows the same pathways for all users.
  • There are extra steps in adding or removing columns on a table with row level security. 
  • Any of the columns that are referenced in row level security can’t be included in an index.
The next three options involve enabling encryption on the database. This involves creating a database master key and database encryption keys (among other steps). This will add additional steps if a database needs to be restored from a backup.
Before enabling these next features, it is highly recommended to do a risk assignment on where the keys are stored and how to access them if a database restore is needed.

4. Transparent Data Encryption

Transparent data encryption is a feature that encrypts any data that is being saved to the hard drive/disk. So, if any data is updated in a table, that data becomes transparently encrypted immediately upon save. When the data is pulled back, SQL Server will unencrypt it for you.
  • This satisfies the regulatory requirement that any data “at rest” be encrypted.   All data within the database is encrypted.
  • In the past a popular hacking technique has been to steal the database backups and restore the database to a server controlled by the malicious attacker.  Without access to the database key, this is impossible. 
  • Since all data saved to disk is encrypted, the CPU has more work to do by encrypting and decrypting the data in the background.  Be sure to plan for extra CPU capacity to account for this. 
  • While all at rest data is encrypted, transparent data encryption does not set any access controls regarding who can see the data once the data is back “in motion”.

5. Encrypted Columns

Encrypted Columns in SQL Server are columns within a table that have been encrypted to hide whatever sensitive data the column contains. This is a good way to both hide sensitive data like a social security number or a date of birth and have the data encrypted “at rest”. To read the data, special permissions are needed to access the necessary keys.
To anyone who doesn’t have the access to decrypt the columns, the data would look like this:
sql server encrypted columns
To those with access to decrypt the columns, the data would look like this:
sql server encrypted columns
  • The data is encrypted so this satisfies any sort of regulatory requirement of “encrypting data at rest”.
  • Since not all the columns are encrypted, this does not have the same CPU requirements as Transparent Data Encryption.
  • Access controls must still be implemented to determine who can read the data and who cannot.

6. Always Encrypted

The Always Encrypted feature even prevents those who manage the database from accessing or decrypting sensitive data, while still allowing end users to read and interact with the same data. In this case, a web or desktop application is set up to encrypt or decrypt the data without the SQL Server being able to read the data. 

For instance, if a Database Administrator looks in this table, all he or she would see is:
sql server always encrypted
However, an end user using an application outside of SQL Server would see the following:
sql server end user
  • Always Encrypted is useful if the people working in the database are not always authorized to see the data inside the database (dates of birth, SSN’s, salaries).
  • Depending on how much of the data is encrypted, any data that needs to be manipulated (added, updated, deleted) must happen in a custom-built third-party application outside of SQL Server. Thoroughly determine whether the third-party application can handle the data manipulation requirements for production scenarios.    


In summary, we have looked at some of the possible ways that SQL Server can be used to protect an organization’s sensitive data. Each method has its own specific use case that needs to be weighed against the security and compliance needs of your organization. 
If you need help unraveling the sometimes-complicated knot of which security tools to use, contact us. Our Solutions Protection program can help secure your organization and create a path forward to meet your security and compliance goals.      
SecuritySQL Server


Love our Blogs?

Sign up to get notified of new Skyline posts.


Related Content

Blog Article
Power BI Governance: Sharing and Deploying Power BI Content
Marcus RadueMarcus Radue  |  
Sep 15, 2020
In this blog series, Marcus Radue, Data Analytics Engineer at Skyline Technologies, offers high-level guidance for implementing Power BI effectively in your organization. For a full overview on this topic, check out the original Power BI Governance A-Z Webinar.  In my previous blog in this...
Blog Article
Power BI Governance: Delivery Strategy and Licensing
Marcus RadueMarcus Radue  |  
Sep 01, 2020
In this blog series, Marcus Radue, Data Analytics Engineer at Skyline Technologies, offers high-level guidance for implementing Power BI effectively in your organization. For a full overview on this topic, check out the original Power BI Governance A-Z Webinar. This blog series will give...
Blog Article
Mitigating Security Challenges in the Professional Services Supply Chain
Brian MorganBrian Morgan  |  
Jun 02, 2020
In this blog, Brian Morgan, Director of Security at Skyline Technologies, explores how to protect your business from cybersecurity threats in the professional services supply chain. Click here to view the full webinar.   Cybersecurity threats are a mounting concern for the professional...
Blog Article
“Bye, Bye, Bye” to AES in ECB Mode
Tony RopsonTony Ropson  |  
May 05, 2020
Early last month, a group operating out of the University of Toronto released a report highlighting some of the security flaws found in the popular online meeting app Zoom. Their report highlighted a few concerning things. However, the one area I want to highlight is the type of encryption...
Blog Article
Power BI’s Latest Features and How to Use Them
Marcus RadueMarcus Radue  |  
Apr 14, 2020
[Updated 09/02/20]  In this regularly updated blog, Marcus Radue (Data Analytics Engineer) highlights key features from his monthly Power BI Office Hours webinar series so you know how to capitalize on Power BI’s latest enhancements.   Power BI Features (August 2020 Update...