To attackers, databases are the new banks; they want to break into them, because that’s where the valuable information is. If you’re using SQL Server, you benefit from the lowest number of vulnerabilities of any major database over the last six years (according to the National Institute of Standards and Technology, compared to systems like Oracle, MySQL and SAP HANA). The way you configure your database – and especially access to it – is how most database breaches actually occur. As well as updates to the latest version of TLS (1.2) to protect data in transit against ‘man-in-the-middle’ attacks, SQL Server 2016 includes 3 new security features designed to protect your information from common attacks.
SQL Server has supported transparent data encryption since the 2008 release, letting you encrypt data files so an attacker can’t copy a database and restore it on another server. The disadvantage of an encrypted database in the past was that you needed to decrypt it to work with the data; client applications in particular often hold data unencrypted, even if the connection to the database is itself secured. With Always Encrypted, you can encrypt data inside the client application, so the data is encrypted as soon as it’s created. This can be done without passing the encryption keys to SQL Server, because you can still query the data without decrypting it first.
The way this works is that a column you set is encrypted with the column encryption key. This leads to an encrypted column master key, and what you upload to the database server is the encrypted copy of the column encryption key. Parameters that you want to query are in turn encrypted and this encrypted value is what can be used to run the query. This means there isn’t a performance hit because instead of encrypting and decrypting each value, it’s the cipher text that’s used for the query. Neither the data nor the column encryption key is ever seen in plaintext inside the database server.
The client application can use the column encryption key to decrypt the data into plain text that users can work with. That’s transparent to developers; they don’t have to work directly with the encryption features because they’re taken care of by client libraries.
There are two options for encryption: deterministic and randomized. Deterministic encryption always generates the same encrypted value for any given plaintext value. Randomized encryption offers more security but it does restrict the type of queries you can run, and it can degrade performance on the client side.
This does require proper management and secure storage of the column master key; if you store that in the wrong place, anyone who has access to it can also get unencrypted access to the database.
Appropriate Access
You also need to secure data against your own employees, whether that’s to comply with regulations on personally identifiable information or to protect against malicious insiders. A disturbing 71% of employees in one survey said that they had access to company data they shouldn’t be able to see, and 54% of those said they accessed that inappropriate data frequently.
Row Level Security (RLS) and Dynamic Database Masking (DDS) give you ways to restrict who can see what information in your database. With RLS, you can give different users access to the same tables in the database, but show them only the data their job requires when they run queries or view reports. This gives you fine-grained access control, using centralized security logic inside the database (written in the T-SQL that’s familiar to database admins and managed in SQL Server Management Studio or SQL Server Data Tools) that’s bound to the database schema. You don’t need to maintain security policies in any applications that work against the database; you will automatically get the correct level of access based on the policies you set up. You can also use RLS to protect multi-tenant databases.
Letting your developers use live data when they develop database applications can be beneficial because test data doesn’t always behave in the same way. With Dynamic Data Masking, you can obfuscate the data they see, so a developer or even a database admin can work with live data but not be able to see the details inside a record. The masking is done on the fly, so that data in the database engine isn’t changed and the relationship between columns isn’t affected. When you run a query, view or export data, or look at it in a report, it’s masked. You can have different masking functions for different levels of sensitive data. This creates a separate policy used to mark logins as privileged, which can help users see unmasked data, and help admins who need to see the content of the database, not just the structure. By default, that includes the database owner.
Taken together, these let you enforce proper separation of duties, so you can safely store sensitive personal or commercial data, without worrying that a database admin will see information they’re not authorized to, or a developer will leave their laptop in a coffee shop complete with a copy of your live customer data.
To learn more about SQL Server 2016, read our related articles, or visit our dinSQL page to find out more about hosting your SQL server in the cloud with dinCloud.
Mary Branscombe is a freelance technology journalist for a wide range of sites. She has been a technology writer for more than two decades, covering everything from early versions of Windows and Office to the first smartphones, the arrival of the Web, and most things in between, from consumer and small business technology, to enterprise architecture and cloud services. She also dabbles in mystery fiction about the world of technology and startups. Visit www.marybranscombe.com or follow @marypcbuk on Twitter.
