Tuesday, 20 September 2016

Encryption and the SQL South West User Group





Wednesday, 3 August 2016

Writing Microsoft Official Curriculum for SQL Server 2016






Wednesday, 27 July 2016

5 Awesome Benefits of PowerShell

Jeffrey Snover, Microsoft. Inventor of PowerShell





Thursday, 21 July 2016

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.

Wednesday, 13 July 2016

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.