Always Encrypted in SQL Server & Azure SQL Database
This post was authored by Joseph Sirosh, Corporate Vice President of the Data Group at Microsoft.
Most data breaches involve the theft of critical data such as social security or credit card numbers. What if you could store these sensitive data elements encrypted in your database, allowing decryption only at the point of use by people or applications that need to access or process that data? For example, an admitting nurse at a doctor’s office may have a business need to access a patient’s unencrypted social security number, but that data may not need to be visible anywhere else in the system.
With the introduction of the Always Encrypted capability in SQL Server 2016 and Azure SQL Database you can now do exactly that. Your database data remains encrypted at all times during computations and query processing. This industry-first technology was developed jointly by Microsoft Research and the Data Group to offer our customers unparalleled data security.
We have enhanced the SQL Server client drivers to work in conjunction with SQL Server to decrypt and encrypt data at the point of use, requiring only minimal modifications to your applications. Encryption keys are managed outside of the database for maximum safety and separation of duties. Only authorized users with access to decryption keys can see unencrypted data while using your applications.
As illustrated in the schematic below, the client driver encrypts the data on the client side using the keys only the client knows before sending encrypted data to the database. When the client application retrieves data from an encrypted column, the driver transparently decrypts the data before returning it to the application.
The security guarantees provided by Always Encrypted represent a shift in the industry and will help customers protect critical data in their on-premises, hosted and cloud databases.
Take the example of Financial Fabric, an ISV that offers the ‘DataHub’ aggregation and analytics service to hedge funds. They are an early adopter and beneficiary, relying on Always Encrypted to help protect sensitive data about their investors and their holdings, including investors’ names, addresses and their assets. Subhra Bose, the CEO of Financial Fabric, has referred to Always Encrypted as nothing less than a “game changer for the financial industry.”
To simplify the adoption of Always Encrypted, SQL Server Management Studio now includes the new Column Encryption Wizard. With just a few clicks this wizard will:
- Create the necessary encryption keys and store them in a secure location of the user’s choice,
- Generate the encryption metadata based on the choice of columns to be encrypted, and, if applicable,
- Encrypt any existing data without the user ever having to call a single encryption function or worry about new encryption syntax.
For more information on Always Encrypted including how to get started today, be sure to check out these additional resources:
- Channel 9 videos on SQL Server 2016 Always Encrypted and Getting Started with Always Encrypted with SSMS
- Documentation on MSDN
At Microsoft we are working hard to keep our customers’ data safe, both on premises and in the cloud. Always Encrypted in SQL Server and Azure SQL DB represents an important step in that journey. We hope many of you give it a spin and share your thoughts and experiences with us.
– Joseph
Follow me on Twitter
