Recently I have been having quite a few discussions around security of data inside of SQL Server and how to prevent the massive data breaches that we have been hearing about on the news. Like most things some people want “THE ANSWER” or THE SOLUTION to securing the data inside of SQL Server. Unfortunately there isn’t a single solution that solves all of the problems that are potentially out there. Security of data requires defense in depth, starting with a secure configuration and installation of SQL Server. Often times, defense in depth also means changes to the database, the application, and how you do business.
Features for Securing Data
SQL Server offers multiple options and features that help with securing data and since SQL Server 2016 Service Pack 1, many of them are available in Standard Edition:
-
SSL/TLS Protocol Encryption
- SQL Server 2005+
- Uses a SSL certificate to encrypt the network connection between clients and SQL Server, securing data from watching over the wire
- Can be forced by SQL Server during the handshake when certificate properly installed on SQL Server certificate store
- Only requires a SSL certificate from a CA for Server Authentication to implement
- Requires certificate rotation before expiration
- Generally does not require application changes but may under specific conditions
-
Database Column Level Encryption
- SQL Server 2005+
- Uses a certificate or key to encrypt a column securing data from being queried without knowing how to decrypt the values
- Data remains encrypted at the column level even while the database is online
- Encryption keys are maintained inside of the SQL Server encryption hierarchy and must be opened properly to decrypt data
- May require application changes to support encryption
- Not required if handled by stored procedure access to data but opens risks of decrypted data in process memory
- Performance impacts when comparing column data in WHERE clause
- May be mitigated by encrypting value to compare first and performing binary filtering of encrypted values
-
Transparent Data Encryption
- SQL Server 2012+
- Encrypts data at rest using database encryption key stored in the boot record of the database and a certificate stored in master
- Prevents someone copying files or stealing a database backup from restoring the data without the certificate
- Does not require application changes for securing data at rest
- Does not protect data from being queried through SQL Server once the database is open
- If someone has access to the master database or a backup of master, they can get the certificate that encrypts other backups (https://simonmcauliffe.com/technology/tde/)
- Without the certificate that encrypts the database you cannot recover from a disaster
- Certificate expiration is not enforced or checked and does not require certificate rotation once used for encryption
-
Backup Encryption
- SQL Server 2014+
- Encrypts the backup file using a certificate or asymmetric key, securing data backups from being restored
- Prevents someone stealing a database backup from restoring the data without the certificate or key
- Same potential risks as TDE since certificate or key is stored in the master database
-
Always Encrypted
- SQL Server 2016+
- Data remains encrypted at the column level even while the database is online
- Column encryption keys are stored in the database to encrypt data
- Column master keys are used to encrypt the column encryption keys in the database and are maintained outside of SQL Server and are not available to the DB, securing data from DBAs
- Requires installing column master keys for SSMS to query and decrypt data – Windows Certificate Store, Azure Key Vault, or HSM
- Requires application changes to support the encryption
- Protects data in use from memory dumps and maintains encryption
How do we apply this for Securing Data?
Generally speaking, the first thing that we need to define is what specifically we are trying to protect against? If we need to prevent someone from monitoring or intercepting network packets containing data in clear text then we need to implement protocol encryption for connections. If we are concerned about someone opening the database on another system or stealing a copy of the database or backups using TDE might be a good solution, but only if we also maintain a defense in depth strategy that separates our backups of master and the certificates used by TDE from the database backup files. If we don’t separate our backups then TDE is very easy to hack around and is simply checking the box without actually securing our data. If we want to protect the data at rest and from prying eyes while open, then we need to implement some form of column level encryption of the data, whether that is key based inside of SQL Server or using Always Encrypted. The important thing is that there isn’t a single solution that is going to protect every situation, you have to consider the risks and what a specific feature is designed to protect against and then layer them together to meet the requirements. Even then you might not be protected.
Take as an example, the Microsoft Customer Support Database data exposure that happened December 2019 and Microsoft provided public information about after securing the data. While we don’t know all the security measures that were in place specifically on the database level, none of the above features would have protected the data from being publicly viewable through an application level security misconfiguration. As a part of any data security discussion, auditing and regular reviews of business practices, and any changes, have to be implemented to ensure that the data remains secured.
12 thoughts on “Securing Data in SQL Server”
I think Dynamic Data Masking can be also leveraged to increase the data security as it doesn’t make sense to encrypt each and every column. Data Masking will make sensitive information safe from unauthorized access.
Also, Row Level Security can be leveraged to further secure the data access.
Both these features are available in SQL Server 2016 and future versions.
Dynamic Data Masking doesn’t increase data security at all, it is simply obfuscating data being returned in results, the actual raw data is still stored in memory and on disk and can be exploited with minimal knowledge. Unless it is used with all of the other features in this post the data is absolutely unprotected, the same goes with Row-Level Security. These two “security” features are more application simplification features than anything else because the data isn’t encrypted in anyway. Don’t get me wrong, I’m not saying they shouldn’t be used, but they aren’t the basis for any kind of data security policy that seriously cares about protecting data. They can add to an appropriate defense in depth approach using all of the other features along with principal of least privilege, stored procedure usage to block adhoc access, etc.
As far I know you can bypass the data masking e.g. by selecting all saleries between 4500 and 4600 bucks, so it is not really safe.
What the “protect” things in dynamic data masking is the permissions… I believe that, before implement, advanced encryption techniques and features, is important to check if the permissions policy in the instance is appropriate. Jonathan, do you agree with this?
Dynamic data masking doesn’t protect anything, it is just obfuscation. Defense in Depth is required to protect data, not just one thing/feature. It’s a fully layered approach starting with installation and configuration, principal of least privilege, encrypting data that requires encryption, encrypting communications between servers, etc.
I have enabled SSL/TLS Protocol Encryption on an 2016 SQL failover cluster. I have noticed that if I perform a failover, it takes about ten minutes for the application to re-establish a connection. Has anyone noticed this as well? I don’t know what is causing it.
Have you confirmed that the cluster resources are all up and available, and checked the ERRORLOG for SQL Server to verify that crash recovery completed and the database is open? Do you have IMOLTP enabled and a large table that has to be loaded from disk to memory, and then indexed before the database can open up and that is causing the delay? If you do ipconfig /flushdns on the application side does it reconnect immediately?
All cluster resources are online and no errors in the sql log or windows log. I just specified kerberos delegation to the sql engine service account. I did a flushdns with no avail. I also checked the kerberos configuration with the Microsoft kerberos configuration manager tool. All looks good. It is a named instance. Thinking about opening Microsoft ticket.
Cause: Networking team disabled gratuitous arp on router and switch
Each of the points you raised is important. The most important of all is probably backup encryption. But from what I have seen in the last 20+ years fixing SQL Servers for customers, the typical security breaches happen due to careless handling. If every DBA would enforce the principle of least privilege, we would have only a fraction of sysadmin and db_owner permissions. And privilege escalation would be much harder, resulting in less security breaches in the first place. But until DBAs stand up against this and developers let the DBA team handle things instead of granting sysadmin permissions because it “fixes” their issues with code not working, we will see more and more of those breaches. But who am I to complain when business is only willing to spend money to develop new applications and the database environment and staff is only seen as a cost factor that comes as an afterthought when development is almost always completed. After all, there is always money to hire the angular crack, but a great DBA is seen as a nuisance at best.
I am not ready to give up hope yet..
Andreas
Andreas, I don’t particularly agree with the statement “DBAs stand up against this”. From my experience, these types of issues are usually
overruled by upper management. Management are the ones pushing for faster release and not wanting to add additional development hours to address these types of issues.
Couldn’t find a private channel to let you know, hence this message. TDE came with SQL Server 2008, it’s said 2012+ in the blog post.