I’ve decided to join in the SQL Server #tsql2sday 87 community blog to talk about four of the less sexy, but nevertheless important security features available to SQL developers, namely HashBytes, Row level Security, Dynamic Data Masking , and Always Encrypted.

Rather than offering this contribution as a ‘How-To’, I’m framing it as a ‘Why we should do it’, because when the Why is sorted out, the ‘How-To’ will follow.

Security and Privacy

Security and privacy are two of the most important facets of providing a trusted presence online.

Although it can be argued that they are two sides of the same coin, sensitive data handling is required, and dynamic and nuanced data-centric security and privacy measures are needed in order to account for the myriad uses (and abuses) of information.

Security relates to the domain of the organisation that is tasked with maintaining the data store, and Privacy is about ensuring that each participant in that Organisation does not feel that their personal space is being compromised, that their information is not there for all to see.

cybersec

Privacy is a luxury without the security of the holding systems, and Security cannot be enforced whilst a persons’ privacy is compromised.

Security is the superset that influences and is built on the subsets of privacy. Security should seek to reduce the surface area of risk for all, and privacy should seek to get the right information to only the right people.

Taking an Assertive Security Stance

The following new(-ish) features of SQL Server will help developers to regain the initiative in the battle for customer trust, but there is a learning curve involved, and there are design decisions that need to be taken on behalf of both your organisation and customers. Look into the following new features to reassure others you are taking cybersecurity seriously.

HashBytes function : Fix your old website password functionality.

Okay, it’s not new (It’s been in since SQL Server 2008), but storing plain text passwords in your database is not on. SQL Server 2016 has beefed up the hashing algorithms you can use to secure passwords using the SHA2_256 and SHA2_512 algorithms.

Although password hashing is best done within applications, and I have included some links here, using the HashBytes() function is a great way to ensure you do not hold sensitive data at all, reducing the risk completely.

When implementing a password functionality:

  • Make sure you use a salt in the hash to add extra security.
  • Don’t restrict users to short passwords, as the adoption of strong passwords using password managers is on the increase.
  • Username and password should always be on the same webpage, not separate ones.

HashBytes documentation on MSDN.

OWASP application guidelines for Passwords

 Row Level Security : Implementing user specific functionality whilst protecting privacy.

RLS is a way to simplify the security model in your database by allowing access to data using security policies in the database that allow only relevant data to be returned, based on the user or their execution context. RLS can be implemented for access control to internal users, or implemented in application code.

Implementing RLS is another great way to reduce your attack surface area and to ensure the implementation of privacy based on user identity (as in the diagram above). RLS development can tailor results for individual users, and assists in the implementation of more complex privacy policies.

RLS Documentation on MSDN.

Dynamic Data Masking : Reduce attack surface area

Masking data to non-privileged users is another of the features is another way in which you can use the inbuilt SQL Server functionality to protect data. It allows developers and DBA’s to set policies as to how data is presented to end users to maintain the privacy of sensitive items (by displaying, say, credit card numbers as ‘XXXX XXXX XXXX 1234’).

Using policy to mask data means that it is still accessible to those that really need to know, but allows such details to be kept from those who do not need access. Data centric policies based on DDM are a big plus as part of an active Data Loss Prevention stance, where lists of card numbers could be transferred accidentally via a shared spreadsheet or report.

DDM Documentation on MSDN

Always Encrypted : Tailored Strong Encryption serving both security and privacy goals.

The introduction of Always Encrypted into the SQL tool set should see both the privacy and security of data protected, at rest, in use and in transit. I’ve done a few presentations of the concepts and practice, and my slides are available HERE.

After all, the introduction of Transparent Data Encryption (TDE) in SQL Server 2008 has been a big plus for the protection of data at rest, and is widely adopted in the installations I have seen. The adoption of Always Encrypted appears to be slower, although the benefits of strong encryption on data loss are well documented.

Developers I have talked to still have reservations about the impact of AE on their solutions and usually cite the following as reasons for not encrypting:

  • Key Management. Paranoia about losing keys, and how to rotate keys without loss of service is a training issue they do not feel comfortable with.
  • Although it’s not everything, the speed of retrieval and transferring data has been raised as an issue.
  • Lack of integration with other technologies, like nHibernate or Java.
  • Being able to use that data for analysis. Although I point out that good analysts shouldn’t really be using Personally Identifiable Information linking findings back to individual accounts is harder when encryption is in place.

The problem is, when offered encryption of account details as an option, 100% of online customers say ‘Yes, we want it’. I’m guessing the problems of the adoption of encrypting technology outside of the military has some way to travel yet.

Always Encrypted documentation on MSDN

Why Security and Privacy are Crucial

Data breaches seen in organisations like Yahoo! have meant that it is consumers that have been left vulnerable and mistrustful and have had to clean up the mess, and this makes engagement harder for all parties.

Companies are beginning to realise that they may soon have to face up to mandatory data breach disclosure, and face stiff penalties under new legislation like the GDPR.

Consumers are increasingly relying on masking their identities and using other mechanisms to protect themselves. This has a knock on effect on the quality of your Business Intelligence and the reliability of your analyses of customer behaviour. Sometimes consumers just turn away from online and live reduced but safer lives offline.

I’m guessing most people wouldn’t have security as their favourite shiny features, but when the old problems involve insecurity and lack of data privacy then the best features you can invest in are security based, because giving back confidence to business stakeholders and consumers is what all the other shiny reports and analysis is predicated on.

Have a happy and secure T-SQL Tuesday !