Encryption and the SQL South West User Group

It is a long and beautiful drive from Reading to Exeter. As the miles crank up, the scenery becomes more lovely. The motorways signs to Glastonbury and Bodmin Moor are a reminder that I really should spend less time in front of my PC, and more time in my hiking boots. But as Exeter cathedral appears in the distance, my thoughts turn to encryption, and SQL Server. I’m here to talk to the SQL South West SQL Server User Group about Always Encrypted, the new encryption feature in SQL Server 2016.

The group is attentive and interested. The discussion soon turns to key management, an important issue when considering encrypting data. No one was sleep walking through the presentation: the young stars of tomorrow had already realized that the inside threat of losing keys was at least as great as the outside threat of being hacked. The balance between security and availability is an issue that faces anyone with an interest in data security. Yet the unrelenting wave after wave of cybercrime makes it hard to ignore the message that the only safe data, is encrypted data. Particularly where personal data is concerned.

So we munched pizza as we talked about backing up encryption keys, azure key vault, and the way the data world is changing.

After eating far too much anchovy pizza we then listened to Rob Sewell’s presentation on PowerShell, and in particular the new SQL PowerShell module. As always, I learnt much, and my head was spinning with ideas as I drove back through Taunton, headed to Bristol, and then back on terra firma as roadworks on the M4 reared their cone-like head. It was a late night, but worth it. You are a lovely lot in Exeter, and I’ll be back. And next time I’ll have a lighter lunch so I can manage a bit more pizza!

Writing Microsoft Official Curriculum for SQL Server 2016

SQL Server 2016 was a big new release, with a host of eagerly awaited new features. And like many people, we installed the various beta releases. But for this release we had a bit more of an incentive to try out all the features, including the corner cases, and generally put SQL Server through its paces. The reason? I was on the team writing the Microsoft Official Curriculum to accompany the new release. What a fun project!

Now I don’t know about you, but I’m always conscious of the massive development effort that goes into a new release. We know from the blogs and videos released by Microsoft that there are quite a few development teams, managers, and others working across the globe developing SQL Server. What I don’t think about quite so much, is all the other activity that is required. The marketing, the training courses, and the pile of technical documentation that is also needed.

Microsoft Official Curriculum is just a part of the training documentation needed, but it was still a big job, with a team of writers, a dozen or more courses, with each course containing a dozen or more modules.  There was a lot of researching, a lot of writing, a lot of testing, and more than a few late nights. The team came from different backgrounds including data warehousing, SQL Server development, report writing, and programming. There was a great team spirit, with everyone happy to help who were struggling with something. There was also pressure to get everything written on time – pressure that wasn’t always welcome when something wasn’t working. With pre-release software it isn’t always obvious whether it is a bug, whether you have missed something, or plain and simply have made a mistake.

I wrote a number of different courses including part of the Upgrade Your Skills to SQL Server 2016 course. This course is designed for people who already know SQL Server, and just want to learn about what’s new. It was a challenging course to write because features were still in development, and the documentation was incomplete. And just occasionally something would change after a lab exercise or demo had been written, but before it was tested. It didn’t happen very often, but it did happen, and it added a bit of not always welcome added spice.


The module I enjoyed working on the most was security. In our digital and cloud world, security has become a hot topic, with cybercrime showing no signs of abating. The new features in SQL Server 2016 are much needed, and will help a huge number of organizations to better protect their data.

5 Awesome Benefits of PowerShell

Jeffrey Snover, Microsoft. Inventor of PowerShell

If you thought that the command line had been confined to history, think again. There’s a not-so-new kid on the block that is getting a lot of attention. It’s called PowerShell, and was first released with Windows 7 and Windows Server 2008 R2. The latest release, version 5.0, will be included in Windows Server 2016 which is currently in technical preview.

PowerShell is a scripting language that is designed to automate server tasks. It can run interactively or in scripts, and is super useful for all sorts of things. It was a ground-up redesign, and has a refreshing elegance to it.

First, a few basics. PowerShell is based on .Net classes and is implemented using cmdlets. A cmdlet has a verb-noun syntax that is designed to be descriptive, and as far as is possible, intuitive. Examples of cmdlets are get-help and get-command.

Cmdlets are organized into modules, with each module containing cmdlets for a particular product. There is a SQL Server module, an Azure module, and an Active Directory module, plus many, many more. Modules are either loaded, or unloaded.

But why would you use PowerShell rather than the GUI? There are a number of awesome benefits:

1. A PowerShell script is testable. Once developed, it can be tested and signed off to say it does what it says on the tin.
2. A PowerShell script is repeatable. It may be quick to do something once using the GUI, but it is slow and error-prone to do it many times.
3. A PowerShell script can be saved. For tasks that are not done very often, or need to be done by different people, having a script ensures that the job gets done in the same way every time.
4. You can do more with PowerShell. Not everything is built into the GUI, so PowerShell lets you do more stuff than is pre-built into the GUI.
5. PowerShell is less exciting than the GUI. If you’ve got complex changes to put into production, PowerShell takes much of the risk out of the deployment. You can test the scripts multiple times, and in different situations. You can be sure that the script does exactly what is intended, without any variation. And even if it is run at 3am, it will do exactly what it was intended to do. So much less exciting than hoping it is all going to go OK.

As the move to the cloud is gaining momentum, and Microsoft Azure is increasing in popularity, PowerShell is proving its worth. It is ideal for working with Azure, enabling resources to be commissioned or removed easily.

 

But perhaps the most awesome aspect of PowerShell is its irrepressible inventor, Jeffrey Snover. With his bulging wardrobe of bright ties and impish grin, you would think he had just invented ice cream. Maybe he has – the Windows version of it anyway.

Designing for Data Protection

The rise in cybercrime cannot have escaped many people’s attention. The national news regularly includes stories about organizations that have been the victims of hacking. LinkedIn and TalkTalk are two recent high profile incidents, but there are many more. So prevalent is cybercrime that the Office for National Statistics now includes it in the crime statistics.

Smaller companies often believe that they are less vulnerable than their larger corporate siblings, but this is not the case. Smaller companies often have fewer resources, and are less well educated in the issues surrounding cybercrime. At the same time, many small companies keep personal data about customers and staff in database systems, which is exactly the sort of data that cybercrime is targeting.  According to the Federation of Small Businesses, over 40% of its members have been a victim of cybercrime in the last year, at a cost of £4,000 to each business.

So how can organizations protect personal or sensitive data?

As a first step, identify the sensitive and personal data that is being held in databases, either on-premises or in the cloud. Organizations have a responsibility to protect data that an individual considers personal, such as email addresses, date of birth, telephone numbers, etc. If you have personal data in more than one system, consider whether that data could be held in a single database, and then securely accessed from other systems when needed. It may be easier to increase the level of protection for one database, rather than ensure that multiple spreadsheets and local databases held on laptops are all secure.

Then consider whether all the sensitive data that is being held actually needs to be stored. Credit card information, for example, often should not be stored in a company database. Although customers may be asked to provide credit card details multiple times, this is small beer compared to the trauma of credit card data being compromised. Read one of the recent stories about hacking, and then look at the list of personal data that you hold. You may find that some of that data is not being used sufficiently for the risk it posts.

Passwords should never, ever, ever be stored in plaintext. They should always be stored using salted hashing. If you are storing passwords in plaintext please take steps to amend your systems. Now.

Security is a multi-layered problem, which means that you need to employ a multi-pronged approach. Ensure staff understand the importance of using strong passwords to secure workstation and servers, and that passwords are changed regularly. Personal data can and should be encrypted, ensuring that not even database administrators have access to sensitive data. Keeping database software up to date also means you have access to the latest encryption technologies.

Data protection is a big subject, but thinking defensively gives you a head start. If you think it could never happen to you, you probably don’t have the right mind-set. We design SQL Server databases, upgrade them, and migrate data to the cloud, all with data protection issues firmly in mind. If you are considering developing a database for your organization, or migrating an existing database to the cloud, contact us for a free 2-hour Data Protection Review. We can advise you about sensible steps you can take to protect sensitive data from hackers, as well as advising on the new security features included in SQL Server 21016.

Always Encrypted. What it is, and why it matters

Always Encrypted is the new encryption feature introduced with Microsoft SQL Server 2016.  Always Encrypted was first introduced in Azure SQL Database V12, the SaaS version of SQL Server that is hosted in Azure. So although Always Encrypted is new, it has already been road-tested by paying customers.

Always Encrypted is in addition to Transparent Data Encryption (TDE), the whole database encryption feature which was introduced with SQL Server 2008. Always Encrypted differs from TDE in a number of ways:

• Always Encrypted encrypts specific columns, in contract to TDE which encrypts the entire database.
• Always Encrypted encrypts data at rest, in motion, and in memory. TDE encrypts the database at rest, but not as the data moves from the server to the client.
• Always Encrypted columns are encrypted when displayed in SQL Server Management Studio (SSMS). TDE displays data in plaintext in SSMS. With Always Encrypted, sensitive data is never displayed in plaintext on the server.
• Always Encrypted data is decrypted on the client, not the server.  Cryptographic keys are held on the client side, making it impossible for a rogue insider to decrypt sensitive data. TDE, in comparison, is encrypted using the Database Master Key (DMK) which is stored on the server.
• Always Encrypted uses authenticated encryption with the AEAD cipher. So not only is the data encrypted, but the cipher provides additional security by authenticating the data source.
• Always Encrypted provides two ways of encrypting data: deterministic or randomized. This means that even though the server only sees encrypted data, joins and equality comparisons can be done on encrypted columns.

So when and why would you use Always Encrypted? And how does it fit with the other security features of SQL Server?

 

Always Encrypted is designed to protect sensitive data such as National Insurance numbers, name and address, date of birth, email address, postcode, health condition, and political views, including trade union membership, etc. It’s the type of data you would want to be is encrypted if the data were ever stolen. It is worth noting that businesses have a responsibility to keep staff data secure, as well as customer data.

 

There are a couple of examples of sensitive data that are best handled in other ways: passwords and credit card numbers. Passwords are best managed using salted hashing, which is a special type of encryption that is never decrypted. Passwords should never be stored in plain text, so if you are not using salted hashing, then passwords should certainly be encrypted. Credit card numbers should not normally be stored within a business system. Very large businesses that have very good security might be an exception, but smaller businesses would be better of thinking about how they can operate efficiently without storing credit card information.

 

For the data you do want to encrypt, Always Encrypted allows you to encrypt specific columns in one of two ways: deterministic or randomized. If you need to use data in a query, use deterministic encryption as it allows equality joins. If you would never use the data in a query, then use randomized.

 

Always Encrypted can be used together with TDE, so it’s not an either/or decision. TDE protects the whole database, whilst still keeping it usable for day to day operations. TDE protects on-premises data in the event of physical media being stolen, but does not protect against rogue insiders, or data in flight. Always Encrypted provides strong encryption for specific personal data, at rest and in motion, and protects data on the server so even administrators cannot see data they are not authorized to see.

Always Encrypted represents a big step forward in keeping private data private, regardless of whether data is stored on-premises or in the cloud. If you are interested in encrypting data within your database, or moving your data to the cloud in a secure way, contact us for a no-obligation chat.

SQL Server 2016 Introduces New Data Protection Features

With almost 80% of the adult UK population now online, cyber security is an issue that affects everyone. A recent report commissioned by the Information Commissioner’s Office (ICO) shows that concerns about data security are real, with only a quarter of adults trusting businesses to keep their personal information secure.

With last year's hacking of TalkTalk still in people’s minds, when potentially millions of records containing personal data were stolen, it is not surprising that mobile networks score badly on trust. The ICO report shows that UK banks do a bit better, trusted by just over half the adults surveyed. More worrying is the mistrust of internet brands and businesses to handle personal information, with only 22% and 23% of adults respectively indicating any confidence in keeping data secure. 1 in 3 adults say they trust high street retailers, technology brands, and energy providers to look after their data. Which means most people - 2 out of 3 adults - do not trust them to protect their data. As cybercrime grows, consumers look to businesses to protect their sensitive data. Clearly, we are not there yet.

Most businesses now store personal data, yet many are unconcerned about data security. A shocking 43% of data loss comes from internal personnel, including staff, contractors, and suppliers, with half of the incidents being accidental. But no matter how data breaches occur, it is the responsibility of the business to keep data securely.

SQL Server is widely used by businesses to hold their data. It is already the world’s most trusted database management system, and the release of SQL Server 2016 adds three important security features: Dynamic Data Masking, Row Level Security, and Always Encrypted. Used as part of your security strategy, they will reduce the attack area, from both inside and outside a company, and make control of sensitive data more robust. 

Whilst the motives for cybercrime vary, the need for proactive vigilance with security remains constant. Encrypting sensitive data, restricting access to personal data, and separating responsibilities all contribute to better security. Keeping software up to date reduces security vulnerabilities, and encourages businesses to review their security.

As SQL Server specialists we are focused on data security, and helping businesses build consumer trust through better data protection. Staying out of the headlines, as far as data breaches are concerned, should be a priority for all forward-thinking businesses.

Sadly, the business world is dividing into two; those that have been hacked, and those that are about to be hacked. If you are interested in upgrading your database, or moving your data to the cloud in a secure way, contact us for a no-obligation chat.