1 / 0

Under Lock and Key: Encryption with SQL Server

Under Lock and Key: Encryption with SQL Server. Jim McLeod MyDBA Jim.mcleod@mydba.com. Who Am I?. SQL Server Performance Tuning Consultant with MyDBA Microsoft Certified Trainer with SQLskills Australia MCITP:Database Administration. Aims. Examine the basics of encryption

felton
Download Presentation

Under Lock and Key: Encryption with SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Under Lock and Key:Encryption with SQL Server

    Jim McLeod MyDBA Jim.mcleod@mydba.com
  2. Who Am I? SQL Server Performance Tuning Consultant with MyDBA Microsoft Certified Trainer with SQLskills Australia MCITP:DatabaseAdministration
  3. Aims Examine the basics of encryption Determine when to use encryption Explore how SQL Server handles encryption technology Examine how to structure your tables in order to encrypt information efficiently Consider how to protect your data from the DBA
  4. What is Encryption? Plaintext “This is a plaintext message.” Codes “Fred likes to eat cake.” Fred = This, likes = is, to = a, eat = plaintext, cake = message. Not terribly useful in computing Ciphertext “Guvfvf n cynvagrkgzrffntr.” Operates on a group of letters, individual letters or bits
  5. Key-based Algorithms Key based encryption uses mathematical algorithms to encrypt data by use of a key Typically non-deterministic Symmetric Keys The same key will encrypt and decrypt the message Asymmetric Keys A public key is used to encrypt the message Only the private key can be used to decrypt the message For a given key length: Symmetric algorithms are faster to encrypt/decrypt Asymmetric algorithms are more secure Compromise: encrypt data with a symmetric key, and encrypt the symmetric key with an asymmetric key.
  6. Why Encrypt Data? Business Requirements Hide sensitive data Passwords, Credit Card numbers, etc Hide sensitive data from the DBA Protect against unauthorised changes
  7. What to Encrypt? On disk encryption Encrypted file system Transparent Data Encryption – SQL Server 2008 Backup encryption – 3rd Party Client Connections (SSL) Network Traffic Individual Columns Stored Procedures, Triggers, Functions, Views, etc
  8. When to Encrypt? Data can be encrypted at one of two points Before it gets to the database engine (Application) After it gets to the database engine (Database) Application encryption: Key must be stored in the application/another tier Prevents Profiler from sniffing unencrypted data passed into stored procedures Database cannot search efficiently Database Encryption Unencrypted data accessible inside the database Unencrypted data passed to the database engine Key may be held in an Hardware Security Module via Extensible Key Management (2008) via Extended Stored Procedures/SQLCLR (2005)
  9. Hashing A deterministic, one-way method of obfuscating data Original source data cannot be retrieved Easy to compute Extremely difficult to construct a string with a given hash A single bit changed in the source string will radically alter the result Extremely unlikely that two source strings will produce the same hash Deterministic means dictionary attacks are possible
  10. Hashing Functions SUM() is a hashing function SELECT SUM(1 + 50 + 20 + 25) = 96 HashBytes() function 128 bit - MD2, MD4, MD5 160 bit - SHA, SHA1
  11. Why Use Hashing Functions? Passwords and authentication Salting Data integrity Protect your data from changes via Management Studio Detecting changes in a table Creating indexes for types that cannot be indexed Images, varchar(max) Demonstration – enforcing row integrity
  12. Simple Encryption EncryptByPassphrase() DecryptByPassphrase() Uses a Triple-DES symmetric key AES128, AES192, AES256 are stronger No Database Master key, or certificate management required If the passphrase is lost, the data is lost Demonstration
  13. Profiler Visibility Profiler can expose data that should be encrypted Stored Procedures Passphrase or unencrypted data can be passed through as a parameter in plaintext Passphrase can be hardcoded into the stored procedure Ad Hoc Queries Passphrase may be visible over the network Profiler will hide the event details Application must supply the passphrase
  14. SQL Server Key Architecture
  15. Service/Database Master Keys Service Master Key Instance scope Is created the first time it is required to encrypt something Can be regenerated. Database Master Key Database scope Created by the DBA when the database needs to use encryption
  16. Encrypting with Asymmetric Keys/Certificates Asymmetric Keys If VIEW DEFINITION is granted, the user can encrypt data If CONTROL is granted, the user can encrypt and decrypt data Public Key Certificates Expiry Dates Self Signed X.509 Standard (1024 bit) Signed by a Certification Authority (384-3456 bit) Used for encryption and authentication Demonstrations Asymmetric Key Encryption Certificate Encryption
  17. Encrypting with Symmetric Keys A symmetric key is similar to Passphrase Not restricted to Triple-DES Fast to encrypt, but if you know how to encrypt, you can also decrypt Symmetric Key with Certificate Demonstration
  18. Searching Encrypted Data Encrypted data cannot be searched on without decrypting the data, resulting in a table scan Need some way of narrowing the search Reduce the number of rows requiring decryption from 1 million to 100. AdventureWorks comes with an index on Email, indicating that they search for customers based on an Email Address Non-deterministic encryption means we can’t index the encrypted value Create a hash of the first part of the email Doesn’t give away any information about the Contact Can be indexed Demonstration
  19. Hiding Data from the DBA DBA has control over all certificates in all databases Database Master Keys encrypted via the Service Master Key will be wide open to the DBA Certificates/keys protected by passwords will be hidden from the DBA via Profiler Demonstration
  20. Transparent Data Encryption (TDE) Feature of SQL Server 2008 Enterprise Edition Encrypts data on disk, and is unencrypted when loaded into memory Uses a symmetric key called the Database Encryption Key Replication or memory dumps may expose unencrypted data No application architecture changes required. Small performance hit on every disk I/O Recovery of an encrypted database MUST be tested on a separate server before a disaster Backup your keys/certificates Backups of encrypted database will not compress well, but Page Compression will still work
  21. Options for SQL Server 2000 Encryption Roll your own encryption functions Try the SQL 2000 DBA Toolkit Uses extended stored procedures http://www.sqlservercentral.com/articles/Security/sql2000dbatoolkitpart1/2361/ Encrypt data at the application layer Put database files on an encrypted partition
  22. Summary Examine the basics of encryption Determine when to use encryption Disk, Connection, Column, Network, Backups Explore how SQL Server handles encryption technology Hashing, Certificates, Symmetric Keys, Asymmetric Keys, Transparent Data Encryption, Extensible Key Management Examine how to structure your tables in order to encrypt information efficiently Indexes of hashes Consider how to protect your data from the DBA
More Related