In today’s Ask the Admin, I’ll explain what Always Encrypted technology is in Microsoft SQL Server and how it works with Azure SQL Database.
As Brad Sams reported back in October 2015, Azure SQL Database – Microsoft’s SQL Server as a Service offering in the cloud – has received an upgrade with the addition of Always Encrypted technology.
Always Encrypted is a feature of SQL Server 2016, which also reached general availability recently on June 1, 2016, and protects sensitive data. Unlike other database encryption technologies, Always Encrypted allows data to be encrypted in the client application without sending the encryption keys to SQL Server, proving a separation of roles between data owners and those that are charged with managing the data. I.e., SQL Server administrators.
Always Encrypted can be useful in different scenarios, but has clearly been designed to allow organizations to store sensitive data on database servers that are not within their direct control, for instance, when SQL Server is hosted in the cloud. Because the encryption keys are never sent to the server, organizations can be sure that data stored in the cloud cannot be decrypted by unauthorized actors.
Transparent to client applications, Always Encrypted uses a driver installed on client computers that automatically encrypts and decrypts data as it passes between the server and client application, ensuring that no changes to queries are required for client applications to work with this new feature. At release, Always Encrypted is supported by the .NET Framework Data Provider for SQL Server, and JDBC and ODBC support is planned for the near future.
Scenarios suited to Always Encrypted include when an organization wants to hire a third-party to manage on-premise SQL servers. Encryption keys are stored in a location where the organization’s database client applications can access them, but can’t be accessed by admins hired to manage SQL Server. In the case of Azure SQL Database, Always Encrypted encryption keys can be stored in a trusted location on premise, preventing Microsoft employees from accessing organizational data stored in the cloud.
If the client app is hosted in the cloud, and data is stored in Azure SQL Database or SQL Server 2016 running in an Azure VM, Always Encrypted can be enabled but won’t provide the same level of isolation as when the client app is hosted on premise. But Microsoft says even though data and keys are accessible to Microsoft staff administering Azure, Always Encrypted still provides a reduced attack surface in this scenario because the data is encrypted in the database.
For more information on Azure SQL Database, see Getting Started with Azure SQL Database on the Petri IT Knowledgebase.