Transcript Cryptography

Building Defense in Depth using the Full Spectrum
of SQL Server Encryption
Presented by Mike Keleher
•
•
•
•
20 years of software development experience
3 years DBA experience
2 time Bisk Presidents Club Award winner
MIS Degree from University of South Florida
"Cybersecurity must mean more than protecting the
system -- it must also include protecting data," said
John Cohen, former acting undersecretary for
intelligence and analysis at DHS. "If information
contained within government and private-sector
systems is encrypted, then the harm caused by
cyberattacks such as this one would be minimal."
Security experts: OPM breach shows Einstein isn't enough
FCW.com
Article dated 6/15/2015
Topics
•
•
•
•
•
•
•
•
•
Encryption terminology
Column level encryption
Code encryption
SQL Server connection encryption
Transparent Data encryption
Reporting Services encryption
TLS and SSL
Problems and solutions
SQL Server 2016 Always Encrypted
Keys
A value supplied to an algorithm to encrypt or decrypt data
• Symmetric keys
• Uses only one key to both encrypt and decrypt data
• Asymmetric keys
• Uses a pair of mathematically related keys
• Private key can decrypt
• Public key can encrypt
Algorithms
A mathematical function used with a key to encrypt or
decrypt a message
• AES (widely endorsed as the most secure selection)
• Triple DES
• RSA
• BLOWFISH
Hash function
A hash function takes in data and returns back a fixed length block of bits such
that any change to the data should result in a different block.
Certificates
A package that contains a user's
Public Key, which has been signed
and encrypted using the private key of
a well-known Certificate Authority and
based on the x.509 standard.
• Enables SSL
Certificate Authority
Certificate Authority is the issuer of the certificate. It is denoted in the “Issued
by” section of the certificate and can be based on one of the following
• Self signed
• Domain certificate
• Public CA certificate
Certificate Trust and trust chains
• There are two types of certificate authorities (CAs), root CAs and
intermediate CAs
• To facilitate this process of verifying a chain of trust, every certificate
includes the fields «Issued To» and «Issued By»
• Certificate validation commonly called “Walking the certificate chain”
Key protection hierarchy
Key scheme in SQL Server
• 1. Service Master Key
• Symmetric key in the master database encrypted by the Windows Data
Protection API. Used to encrypt linked server connection information,
credentials and proxy accounts.
• 2. Database Master Key
• Symmetric key created within a database and encrypted by password supplied
during creation using AES_256 algorithm for 2012 and above, triple DES for
earlier versions
• 3. Asymmetric keys and Certificates
• Use public\private key pairs. Certificates generated within SQL are all self
signed. Private key encrypted by password or Database Master Key.
• 4. Symmetric keys
• Can be encrypted by password, certificate, asymmetric key, another symmetric
key, or Extensible Key Management provider. Can’t be protected directly by the
DMK.
Service master Key
• Created automatically by SQL Server the first time there is
a need for it
• Can only be opened by the service account
• Regenerating or restoring is resource intensive
• Protects linked server logins and credentials
• Not accessible by the server if you restore master
database to a different system and change service
accounts
Database master key
• Encrypted by Service Master Key using AES 256
• Two copies exist. One in the user database and one in the
master
Asymmetric key
• Can be encrypted by password, certificate or symmetric
key
• Can be created from a file
• Cannot be backed up separately from the database
• It is possible to use ALTER to remove the private key so
the Asymmetric key can only be used to encrypt and not
decrypt
Certificates
• By default, certificate private key is encrypted by
Database Master Key
• Can be altered to remove private key
• Certificate and private key backed up to separate files
• Every Certificate in SQL Server is a self signed certificate
that adheres to the x.509 standard
• Can be used to encrypt data
Symmetric key
•
•
•
•
No backup
No private key removal
Can be reproduced on different servers from script
SQL Server will allow changes to methods for encrypting
the key, but will never allow the key to be unencrypted on
the server
Certificate Encryption Example
Certificate Encryption Example
Extensible Key Management
• Provides an interface for Hardware
Security Modules to perform
cryptography
• Introduced in 2008
• Only available in … Enterprise Edition
Encryption System tables
Example Encryption function
• Each key has a key guid that must be used
• Encryption and decryption functions can have
authenticators
Authenticators
• Helps deter whole value substitution
• Integrated into the encrypted column and required to
decrypt
Column Encryption
• Encrypted data must be stored in Varbinary column
• Remains encrypted in memory and in storage
Anatomy of an encrypted column
• Symmetric key cipher text message format
Indexing encrypted columns
• Encryption functions on server are non-deterministic,
therefore the output is not repeatable
Indexing that won’t work
• EncryptByKey returns different values for the same message
Resolving the indexing problem
• Be careful. If a workaround is implemented incorrectly it could compromise
the encrypted data
• Hashing the message makes it output predictable and can lead to a
dictionary attack
Replicating Encrypted Columns
• Transfer data without decrypting
• Data is encrypted on publisher, in transit and at
subscriber and only decrypted when used.
• Same symmetric or asymmetric key must be
created on both databases
Permissions needed to decrypt
• Grant REFERENCES on symmetric keys
• Grant CONTROL to those who will manage these objects
• Anyone with CONTROL can run BACKUP CERTIFICATE
• CONTROL permission on a certificate is required to use it for decryption
either directly or indirectly
Warning: The db_owner role, db_securityadmin and the db_ddladmin role have CONTROL permissions
on certificates and the db_backupoperator role can back up the database.
Procedure Code signing with
Certificates
• Can generally be used to solve some granular permission
issues
• There are five steps of module signing:
• Create a signing certificate
• Sign the module using the signing certificate
• Create a certificate user and map to the certificate
• Grant the permissions to the certificate user
• Grant permission to the module for the users/groups
Code signing
example
• Create a signing certificate
• Sign the module using the signing
certificate
• Create a certificate user and map to the
certificate
• Grant the permissions to the certificate
user
• Grant permission to the module for the
users/groups
Results
Code signing concept
It’s like telling the user “You don’t have permissions to operate the motor, but
you do have permission to knock over the first domino”
Where is my encryption?
• using System
views to find
encryption
artifacts
Stored procedure encryption
• Only available in SQL Server 2008 and above
• Converts text of procedures to obfuscated form, even in
system tables
• Encrypted procedures can’t be replicated
• Text still available to administrators using the Dedicated
Administrative Connection
• Can be used with functions, views and triggers as well
Connection encryption
• Install Certificate on Server
• Configure server to use Certificate and force connection encryption using SQL
Server Configuration Manager
Man in the middle Attack
• Possible when using self-signed certificates or certificate obtained using
identity theft
Potential issues with Connection Encryption
• Java VMs handle their own connection encryption
• Replication agents can be affected
• Set trust server certificate to yes and use the
encryptionlevel option for the distributor
Transparent Data Encryption
• Encrypts Data files and log files
• Encrypts and protects backup files
• Only available in ... Enterprise Edition, however, backup encryption is
available on all versions of SQL Server starting in 2014
• To implement, create a Database Master Key and Certificate in the master
database, then create a Database Encryption Key in the user database and
set the user database encryption property to ON.
• If you want to encrypt your data and log files and don’t have Enterprise
Edition there is good news…
Encrypting database files without TDE
• EFS Encrypting File
System
• Requires a Certificate
on the system
encrypting the files
Reporting Services Encryption
• If you have
any
instances of
Reporting
Services,
then the
encryption
keys and
password
needs to be
backed up
TLS and SSL
• How channel encryption is negotiated between systems
• Determine the highest level protocol mutually
supported by the client and the server.
• The currently recognized protocols are, from highest to
lowest: TLS1.2,TLS1.1, TLS1.0, SSL3.0, SSL2.0
How to cause Trouble...
and recover from it
Changing systems and service accounts in one move
Changing systems and service accounts during a restore of
master, msdb and user databases will cause the Service
Master Key to be inaccessible
What you will lose if you can't recover:
a. Encrypted backups
b. TDE Encrypted files
c. Encrypted Column data
d. Linked Server Logins
e. Credentials
Service Master Key recovery
If your system reports a problem…
There is a way to recover…
2012 and earlier
2014 and later use SSCM
Warning: Do not regenerate the Service Master Key Until you have recovered it first
Only taking backups of databases without backups of keys
and certificates • Inhibits recovering user database to other servers
• Solutions
• restore problematic user databases from a good
backup to same server under a different name
• Build a new server, restore master, msdb and user
databases and it's imperative to use the same service
account and password
Warning: If you don’t backup either the master database or the Service Master Key you
could lose it all
Changing the service account outside of the SQL Server
Configuration Manager
• SQL Server will re-encrypt the Service Master Key when the account
information is changed using SQL Server Configuration Manger.
• Do not use Computer Manager or other command line options as they are
not aware of the encryption requirements
• Back up the Service Master Key
Warning: If you change the password for the service account outside of SQL Server
Configuration Manager and the server crashes before it can be restarted, you could
lose access to the Service Master Key
Using encrypting file system without backing up the
certificate
• Only one important point to make about this slide
The Title says it all
• Work with your system administrator
A Practical Example of Solving Encryption
Problems
The Scenario
• A SQL Server instance implemented by a group outside of IT had their instance
crash
• Unrecoverable system board error occurred
• You are a Database Administrator assigned to rebuilding the instance and provided
with the following facts
• One of their team members decided to encrypt a substantial number of columns
in a mission critical database
• The person who set up the instance had a backup job that ran every day. It
deleted the previous days backups and created a current backup set.
• They have the domain user and password used as the service account
• However, they only backed up the user databases and not the master
database.
More Details about the situation
When you inquire about how
the encryption was
implemented, you are sent a
hyperlink to an StackOverflow
blog page and were told that
they copied the script from
this page and ran it.
... no one saved the script
He did mention that he changed all of the parameters, but forgot to
change the Key_Source
The Solution
We can solve this by applying knowledge of the key
Hierarchy, system tables and format of the encrypted data
Facts we know
• We have a backup of the user database
• We know the script that was used to create the keys
• We also know that they Key_Source is used to create the
symmetric key and the identity_value is used to generate
the key_guid
• We know that the symmetric key was encrypted by the
certificate
• We know the format of the encrypted column
Decryption attempt
If you only had the password for the database
master key
… but you don’t in this situation
Documentation for symmetric key
The script
One last item before we can decrypt
SQL Server 2016 Always Encrypted
Back up Keys and certificates
• Back up all Database Master Keys, Service Master Keys
and Certificates
• Symmetric and Asymmetric keys are backed up with the
database, however, if you want to share a symmetric key
among several systems it can be created from a file or
using the algorithm, key_source and identity_value
parameters
• If you have any column encryption, it is important to back
up the Database Master Key and keep the password used
to encrypt it safe and accessible
Reference material
• Raul Garcia’s Blog
• Indexing Encrypted Columns
• Encrypted Message Description
• Channel 9 Always Encrypted Video
• Technet SQL Server Encryption
• MSDN SQL Server Encryption
• X.509 Certificate Format and Validation
• How to Crack the Symmetric Keys in the Database Wide
Open
Contact Information
LinkedIn: Michael Keleher
Twitter: @MAKeleher
Email: [email protected]
Thank you for attending