Encryption Options for Azure SQL

Overview:  With all IT storage, we are looking for encryption at rest and making sure the data is encrypted “over the wire” until it is stored storage.  For encryption in transit, Azure SQL supports TLS/SSL versions 1.0, 1.1,and 1.2.  If possible got for TLS 1.2.

Azure SQL Server Transparent Data Encryption (TDE) related to encryption at rest by encrypting the log and data files on the storage; Azure enforces TDE as the default on databases.  TDE can be turned off on your Azure SQL instance.  The disks that the database files and backups are block encrypted automatically by Azure.

Backups should also be encrypted, and if TDS is enabled on Azure, your backups are also automatically encrypted.  Tip: Validate your restore of Azure Backups to another instance.

Column encryption is useful for encrypting a column within a table.  I prefer to use a Key Vault and use a SQL column to point to the database for things like tokens and secrets, but something like credit card numbers column encryption is ideal.

Always Encrypted allows for one or more columns to be encrypted within a database.  Client application shall decrypt and provides for separation where database owners/access cannot validate/view the encrypted column/columns.

Encryption at Rest on Azure SQL Server (PASS) Summary:

1. Disk Encryption - Always can't change
2. TDE - Server-Side - On by default (can be turn off)
3. Column level encryption - Server-Side (Needs configuration, encryption done inside SQL for columns)
4. Always Encrypted - Client-Side.  Columns are encrypted inside the db and only the application can unencrypt the column.

Tree Testing and Heuristic Reviews - Ux for dummies

I was speaking to two Ux experts in a meeting and they referred to Tree Testing when discussing Information Architecture and the users working there way around a new SaaS product.  "Tree Testing" is not a term I had heard before so they showed me this site and it fantastic.

https://www.optimalworkshop.com/learn/101s/tree-testing/

I'm more familiar with Heuristic Reviews - that I find useful for improving UI/UX using an iterative approach that suits Agile nicely.

API Economy Technology Breakdown for Strategy Leadership

 

XaaS - Everything as a Service.  Objects can be used as a service e.g. renting cars by the hour

CX - Customer Experience

AIP and Sensitivity Labels

Overview:  AIP has had many names and twists over the past few years.  The functionality has been improving, but the naming and changes made it difficult to implement well.  Finally, I feel Microsoft Azure Information Protection is implementable at scale.

Summary: Sensitivity labels have have the ability to allow documents and email to be classified to protect email and files.  One can track, and encrypt documents/email.  You can also use sensitivity labels to protect SharePoint sites, Teams sites and Microsoft 365 Groups.  Within AAD (B2C) I can assign sensitivity labels to Microsoft 365 Groups.

ISO 27001 Certification & OWASP

Overview:  I have been thru several ISO and security audits over the years for various companies offering SaaS products.  This post outlines a some of my note around the latest ISO 27001 audit I touched on.

ISO 27001 covers Information Security Management (ISMS) which is about protecting and managing your businesses information assets to reduce your business risks.  It demonstrates that your organisation has good security practices in place.

Note: ISO 27001 is a management of systems standard for an organisation, it is not done for a particular product.  

An ISO 27001 ISMS consists of policies, procedures and other controls involving people, processes and technology".  https://www.itgovernance.co.uk/iso27001

Parts to an ISO 27001 audit:

• Part 1 - Check you have the correct documentation.  

            Output is a go ahead and get a visit plan from the auditor.

• Part 2 - Checks you as a business are complying/working to the documentation.  Basically evidence based reporting based on visual confirmations and discussing with the staff using interviews to verify compliance (sample based auditing).  Findings normally grouped into 3 types of findings: 1)   Opportunity for improvement = suggestions, need to review before next audit to see if this is worth implementing 2) Non conformance - Minor = can have a few of these, look to fix 3) Non conformance - Major - won't get certification with a major.  There is a period to address/fix major issue/issues.  Always complete the phase 2 audit as they may discover other majors.

            Output Findings report and several weeks latter the certification.

• Certification
• Yearly: Need to repeat and show you are improving based on the findings and the audit will generally go into specific areas in more detail.

More Info:

Data Protection and Regulation note - see bottom of post for ISO27001

Notes: 

Business Continuity quarter check

Annual Security Policy & Standard Review 

Security training - different roles need different training

Annual penetration testing

Audit annual re-certification days

Risk Information: Non conformity & root cause analysis

Technical:  Encryption and REST, Encryption in Transit, DAST/SAST on code, =logically secure customer data/security, Azure Defender to harden infra and continuously monitor, vulnerability or external penetration testing, ASVS/OWASP.

ISO 27701 - "ISO 27701 extends the meaning of “information security” detailed in ISO 27001. While the privacy and protection of personal data is part of ISO 27001, the newer standard extends the scope to include the “protection of privacy as potentially affected by the processing of PI" source: https://www.learningcert.com/information-security/iso-27001-vs-iso-27701/

ISO 27017 - is a security standard developed for cloud service providers and users to make a safer cloud-based environment and reduce the risk of security source Wikipedia.  I think ISO27017 is now part of ISO27001 extended.

ISO 28000 - is the spec for security management systems for the supply chain (partner dependancies e.g. software vendor, hosting company service)

ISAE 3402/SOC 2/ISO 27001 - about verification of business processes/internal controls of the business of of a high standard.

Testing your home Internet Speed using your IPhone

Problem:  Broadband offers various speeds options when purchasing, the actual speeds you get are usually well below and depend on you specific instance. 

Initial Hypothesis:  iOS has multiple apps to monitor speed to your iPhone.  

Resolution:  Download "Speedtest" using the app store an any Apple device.   5G performance is fantastic.

Below are my Results, I live in South West London (Zone 4)

Sky broadband - SW London

Broadband	Download (Mbps)	Up Speed (Mbps)	Location
Sky phone	34.80	5.72	SW London
EE 4G - LTE	13.00	0.13	SW London
O2 4G - LTE	16.90	10.20	SW London
EE - 5G	372.00	19.80	Newcastle

EE 4G - SW London

O2 4G - SW London

5G - Newcastle

Thoughts:

Speed tests vary greatly, so worth doing at least 3 to get an average. 70 Mbps download on EE4G is very possible so the download speeds can be as good as my Sky broadband.  5G performance is fantastic - the MIFI/5G routers are going to be awesome when 5G rolls out to my area.  Using O2 and EE at my home, O2 is way faster down but interesting the upload speed is amazing using O2 (The O2 tower is way better positioned).

Update 15/03/2023

Nice package to check Internet upload and download speed on Mac, Windows and Linux that I got from Tobias Zimmergren

Install cmd PS>  npm install --location=global fast-cli

Run cmd> fast -u

Azure SQL Basic Options Summary

Overview:  Azure SQL is incredible.  There are a lot of options when choosing how to host database and performance good.  "handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades, and other maintenance tasks", from Microsoft Docs and Azure SQL.

Azure SQL is the PaaS database service that does the same functions as SQL Server did for us for many years as the workhorse for many organisations.  Microsoft initially only offered creating VM's and then installing SQL Server on-prem.   Azure SQL is Microsoft's PaaS SQL database as a Service offering on the cloud.  Azure SQL is a fully managed platform for SQL databases that Microsoft patches managed backups and high availability.  All the features that are available in the on-prem. Edition are also built into Azure SQL with minor exceptions.  I also like that the minimum SLA provide by Azure SQL is 99.99%.

Three SQL Azure PaaS Basic Options:

1. Single Database - This is a single isolate database with it's own guaranteed CPU, memory and storage.
2. Elastic Pool - Collection of single isolate databases that share DTUs (CPU, Memory & I/O) or Virtual Cores.
3. Manage Instance - You mange a set of databases, with guaranteed resources.  Similar to IaaS with SQL installed but Microsoft manage more parts for me.  Can only purchase using Virtual Core model (No DTU option).

Thoughts: Managed Instances recommend up to 100TB but can go higher.  Individual databases under elastic pools or single databases are limited to a respectable 4 TB.

Two Purchasing Options:

1. DTU - A single metric that Microsoft use to calculate CPU, memory and I/O.  
2. Virtual Cores - Allows you to choose you hardware/infrastructure.  One can optimise more memory than CPU ratio over the generalist DTU option.

Thoughts:  I prefer the DTU approach for SaaS and greenfield projects.  I generally only consider Virtual Cores, if I a have migrated on-prem. SQL onto a Managed Instance or for big workloads virtual cores can work out cheaper if the load is consistent.  There are exceptions but that is my general rule for choosing the best purchasing option.

Three Tiers:

1. General Business/Standard (There is also a lower Basic Level)
2. Business Critical/Premium
3. Hyperscale

Backups

Point in time backups are automatically stored for 7 to 35 days (default is 7 days), protected using TDE, full, differential and transaction log backups are used to point in time recovery.  The backups are stored in blob storage RA-GRS (meaning in the primary region and all the read-only backups are stored in a secondary Azure region).  £ copies of the data in the active Azure Zone and 3 read only copies of the data.

Long Term Retention backups can be kept for 10 years, these are only full backups.  The smallest retention is full backups retained for each weeks full backup.  LTR is in preview available for Managed Instances.

Azure Defender for SQL 

Monitors SQL database servers checking vulnerability assessments (best practice recommendations) and Advance Threat Protection which monitors traffic for abnormal behavior.

Checklist:

1. Only valid IP's can directly access the database, Deny public Access,
2. AAD security credentials, use service principals
3. Advanced Threat Protection has real time monitoring of logs and configuration (it also scans for vulnerabilities), 
4. Default is to have encryption in transit (TLS 1.2) and encryption at rest (TDE) - don't change,
5. Use Dynamic data masking inside the db instance for sensitive data e.g. credit cards
6. Turn on SQL auditing,

Note: Elastic Database Jobs (same as SQL Agent Jobs).

Azure offers MySQL, Postgre and MariaDB as hosted PaaS offerings. 

Note: The Azure SQL PaaS Service does not support the filestream datatype : use varbinary or references to blobs.