Transcript A look at Always Encrypted

SQL SATURDAY #444 – Kansas City, MO
A LOOK AT ALWAYS ENCRYPTED
SQL SATURDAY #444 – KANSAS CITY, MO
DAVE WALDEN
PRINCIPAL SOLUTIONS ARCHITECT
DB BEST TECHNOLOGIES, LLC
[email protected]
DISCLAIMER…
PLEASE NOTE We’re talking about a pre-release
technology and features today.
All information is up to date as of CTP2.4,
however Microsoft may change anything up to
and after RTM
DATABASE ENCRYPTION… A BRIEF OVERVIEW
• SQL Server 2000 and before – No native tools for encryption
• Data at rest encryption could be done with 3rd party tools, or by
encrypting the entire drive (TrueCrypt,etc)
• Cell Level Encryption – Introduced with SQL Server 2005
• Transparent Data Encryption (TDE) Introduced with SQL Server 2008
• Certificate based transport encryption
• Always Encrypted – introduced with SQL Server 2016
SQL ENCRYPTION NOW AND THEN
WHAT IS ALWAYS ENCRYPTED?
From MSDN:
“Always Encrypted is a feature designed to protect sensitive data, such as credit
card numbers or national identification numbers (e.g. U.S. social security
numbers), stored in SQL Server databases. Always Encrypted allows clients to
encrypt sensitive data inside client applications and never reveal the encryption
keys to SQL Server. As a result, Always Encrypted provides a separation between
those who own the data (and can view it) and those who manage the data (but
should have no access).”
WHAT IS ALWAYS ENCRYPTED?
From MSDN:
“Always Encrypted makes encryption transparent to applications. An Always
Encrypted-enabled driver installed on the client computer achieves this by
automatically encrypting and decrypting sensitive data in the SQL Server client
application. The driver encrypts the data in sensitive columns before passing the
data to SQL Server, and automatically rewrites queries so that the semantics to
the application are preserved. Similarly, the driver transparently decrypts data,
stored in encrypted database columns, contained in query results.”
WHAT IS ALWAYS ENCRYPTED?
• Always Encrypted provides transparent, end-to-end encryption for you
sensitive columns
• All encryption and decryption is handled transparently by the driver library on
the client
• 2 different encryption types depending on your requirements (random and
deterministic)
• Deterministic type can be used for WHERE equality comparisons, as well as
DISTINCT, JOIN, and GROUP BY
WHAT IS ALWAYS ENCRYPTED?
• Data is never in plain text while being stored or accessed while on SQL
Server (including while in memory)
• Only clients with the proper Column Master Key will have access to
decrypt the data
• Can be combined with TDE for a full encryption solution
• Provides gap coverage for regulatory compliance wince data is
encrypted in flight, and DBAs have no access
• Supported in SQL Server 2016 and Azure SQL Database (soon)
ENCRYPTION TYPES
Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption.
• Deterministic encryption uses a method which always generates the same encrypted value for any
given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining
tables based on encrypted values, but can also allow unauthorized users to guess information about
encrypted values by examining patterns in the encrypted column. This weakness is increased when
there is a small set of possible encrypted values, such as True/False. Deterministic encryption must use
a column collation with a binary2 sort order for character columns.
• Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized
encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted
columns.
• Choose randomized for data that will just be returned, and deterministic for data that will be searched
KEYS
Always Encrypted uses keys of two types: Column Encryption Keys and Column Master Keys
• Column master keys are protecting keys used to encrypt column encryption keys. Column
master keys must be stored in a trusted key store. Information about column master keys,
including their location, is stored in the database in system catalog views.
• Column encryption keys are used to encrypt sensitive data stored in database columns. All
values in a column can be encrypted using a single column encryption key. Encrypted values
of column encryption keys are stored in the database in system catalog views. You should
store column encryption keys in a secure/trusted location for backup.
• Each CEK can have 2 encrypted values from 2 CMKs to allow master key rotation
• Column Master Keys must be deployed on each client machine that needs access to the
unencrypted data. SQL Server does not contain the keys needed to decrypt data
HOW IT WORKS
• A Column Master Key (CMK) is created from a certificate on a client machine
• Column Encryption Keys (CEK) are then created from the CMK
• Create a new column specifying encryption type and CEK
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1)
• Add new “Column Encryption Setting=Enabled;” option to connection string
• Driver interrogates SQL Server to see if requested columns are encrypted, and uses CMK to
decrypt data returned from SQL Server using standard .NET encryption libraries
• Data remains encrypted in flight until being decrypted by client
KEY PROVISIONING OVERVIEW
1. Generate CEKs and Master Key
2. Encrypt CEK
Security
Officer
3. Store Master Key Securely
4. Upload Encrypted CEK to DB
Column
Encryption Key
(CEK)
Column
Master Key
(CMK)
Encrypted
CEK
CMK Store:
• Certificate Store
• HSM
• Azure Key Vault
• …
Encrypted
CEK
Database
CMK
HOW IT WORKS (FROM MSDN)
Encrypted sensitive data and
corresponding keys are never seen in
plaintext in SQL Server
SQL Server or SQL Database
Client
"SELECT Name FROM Customers WHERE SSN = @SSN",
0x7ff654ae6d
"SELECT Name FROM Customers WHERE SSN = @SSN",
"111-22-3333"
ciphertext
Result Set
ADO .NET
Result Set
Name
Name
Wayne Jefferson
0x19ca706fbd9a
dbo.Customers
trust boundary
Name
SSN
Country
0x19ca706fbd9a
0x7ff654ae6d
USA
ciphertext
TYPES OF ENCRYPTION IN V1 RELEASE
•
Encrypt('123-45-6789') = 0x17cfd50a
•
Repeat: Encrypt('123-45-6789') = 0x9b1fcf32
•
Allows for transparent retrieval of encrypted data but NO OPERATIONS
•
More secure due to the more random nature of the encryption
•
Encrypt('123-45-6789') = 0x85a55d3f
•
Repeat: Encrypt('123-45-6789') = 0x85a55d3f
•
Allows for transparent retrieval of encrypted data AND equality comparison
•
E.g. in WHERE clauses and joins, distinct, group by
• LIKE and ranges not supported in V1
WHY WOULD I USE IT?
• To provide additional security for sensitive data (PHI, PII, etc.) in
flight from SQL Server directly to client
• Running database and/or application in the cloud
• Prevent high-privileged users from having access to sensitive data
• Regulatory Compliance and audits
LIMITATIONS
• NOT available for use on the following:
IDENTITY, text/ntext/image, XML/hierarchyid/geography/geometry, alias
types/user-defined data types SQL_VARIANT, rowversion (timestamp),
Computed columns, Sparse columns and in-memory tables (hekaton)
• Cannot ALTER a column and make it encrypted; must add new column and
add/import data
• No LIKE or other non-equality operations allowed
• Queries must be passed with properly typed parameters (sqlcommand,
sqlparameter). Ad-Hoc queries against data will raise an exception
LIMITATIONS
• Currently only supported using ADO.NET as part of framework 4.6
• ODBC and JDBC may be supported in the future
• CDC does not work on encrypted columns, but change tracking does. Only
tracks changes of encrypted values
• Replication is not officially supported – however Availability Groups and
Mirroring/Log shipping is
• Temporal tables cannot include encrypted columns
• Triggers may fail if they reference encrypted columns
OTHER THINGS TO CONSIDER
• Performance - Still pre-release but initial findings show that performance is
significantly slower compared to non-encrypted inserts and updates. More on
this later
• Troubleshooting data issues become more complicated
• Additional management overhead of having to install certificates on all clients
PERFORMANCE (COMPLIMENTS OF SQLPERFORMANCE.COM)
PERFORMANCE
MORE INFORMATION
• MSDN - https://msdn.microsoft.com/en-us/library/mt163865.aspx
• Aaron Bertrand - http://blogs.sqlsentry.com/aaronbertrand/t-sql-tuesday-69-alwaysencrypted-limitations/ , http://sqlperformance.com/2015/08/sql-server-2016/perf-impactalways-encrypted
• Getting started with Always Encryption http://blogs.msdn.com/b/sqlsecurity/archive/2015/06/25/10619747.aspx
• 6 things you need to know about Always Encryption http://www.wintellect.com/devcenter/paulballard/6-things-you-should-know-about-sqlserver-2016-always-on-encryption
• Channel 9 - https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-AlwaysEncrypted
QUESTIONS?
THANK YOU!