Downloaded 18 times
Uploaded byDavid Dye
SQL Server Column Based Encryption
The document discusses cryptographic services in SQL, focusing on various encryption methods, keys, and best practices for securing data. It highlights the implementations of symmetric and asymmetric key encryption, the use of service and database master keys, and the importance of adhering to regulatory standards for data protection. Additionally, it addresses the limitations of encryption methods and the need for careful consideration of data storage and access protocols.
More Related Content
Similar to SQL Server Column Based Encryption
SQL Server Column Based Encryption
- 1.
- 2. Introduction Cryptographic Servicesin SQL SMK DMK Keys Certificates Encryption
- 3. David Dye derekman1@msn.com` HTTP://WWW.SQLSAFETY.COM
- 4. ◦First implemented inSQL 2005 Foundation based on Windows operating system Windows DPAPI Data Protection Application Programming Interface Algorithms based on the OS
- 5. ◦Column level encryptionis based on a single column DOES NOT encrypt the rest of the database ◦Database encryption done using Transparent Database Encryption, TDE More here: http://www.sqlsafety.com/?p=468 ◦BOTH take resources and protect data at rest and NOT over the wire
- 7. ◦Every business sectorrequires obfuscating/hiding data that must be stored Payment Card Industry Security Standard (PCI) Health Insurance Portability and Accountability (HIPPA) Sarbanes-Oxley(SOX) Criminal Justice Information System Standards (CJIS) ◦“Data at rest” Data stored in a database
- 8. ◦Resources Storage Encrypteddata takes more disk space than unencrypted CPU Encrypting and decrypting data takes additional CPU ◦Index SQL can not use index seek on encrypted data
- 9. ◦Does the datahave to be stored? Can it be utilized in a set based solution in temp tables or memory and then dropped when done? ◦Can the data be stored as a salted secure hash
- 10. ◦Encryption The processif hiding clear text data, obfuscation, in a way that provides the ability to re-present the clear text at a later time Referred to as cyphertext ◦Decryption Transforming data back to its original state Referred to as cleartext
- 11. ◦Means to encryptcolumn level data Passphrase Data is encrypted with a passphrase Less secure and absent SSL passphrase is sent clear text over the wire Symmetric key Same key is used for encryption and decryption Asymmetric key Public key is used to encrypt private key is used to decrypt Considered more secure than symmetric Certificate Digitally-signed security object that binds the public key to the principal who holds the private key
- 12. ASYMKEY_ID ASYMKEYPROPERTY CERTPROPERTY CERT_ID CRYPT_GEN_RANDOM DECRYPTBYASYMKEY DECRYPTBYCERT DECRYPTBYKEY DECRYPTBYKEYAUTOASYMKEY DECRYPTBYKEYAUTOCERT DECRYPTBYPASSPHRASE ENCRYPTBYASYMKEY ENCRYPTBYCERT ENCRYPTBYKEY ENCRYPTBYPASSPHRASE HASHBYTES IS_OBJECTSIGNED Key_GUID Key_ID KEY_NAME SIGNBYASYMKEY SIGNBYCERT SYMKEYPROPERTY VERIFYSIGNEDBYCERT VERIFYSIGNEDBYASYMKEY
- 13. SELECT CRYPT_GEN_RANDOM(5000) ; GO SELECT CRYPT_GEN_RANDOM(4, 0x25F18060); GO SELECT HASHBYTES('SHA1', 'Pa$$w0rd'); GO
- 14.
- 15.
- 16. Service Master Key ◦Protected by the DPAPI Data Protection Programming Interface OS specific Based on the operating system will dictate the algorithms available ◦Generated using the SQL service security account on first start up of the SQL Service ◦Best Practices Changing the security context of the SQL service should be done using SQL Server Configuration Manager If the Services Microsoft Management Console is used insure that a new key is generated using the ALTER SERVICE MASTER KEY T-SQL command Backup the Service Master Key
- 17.
- 18. Created in thedatabase, user or system Only one Database Master Key per database ◦Database backups include the Database Master Key ◦Used to protect database-level resources Keys Certificates ◦Two keys are created by default One encrypted by the Service Master Key One encrypted by the password used during creation ◦Access is through the Service Master Key or password used during creation This means than anyone with access to either the Service Master Key, such as sysadmins, or the password also has access to the Database Master Key
- 19. Can be loadedfrom certificate, or asymmetric key created externally When created in SQL it is self signed ◦Requires that the Database Master Key is created first Little difference between asymmetric key created in SQL ◦SQL certificates can be exported and SQL asymmetric keys cannot be exported Certificate with private keys commonly require passwords to open them, password must be provided separately Certificates, or asymmetric keys, private key can be protected with a password or with the Database Master Key ◦This choice determines how the key must be opened for use ◦Only one algorithm can be used at one time Algorithm can be changed using the ALTER T-SQL command
- 20. Securable entity atthe database level Encrypted using ◦Certificate ◦Password ◦Symmetric key ◦Asymmetric key ◦PROVIDER Encryption ◦DES, ◦TRIPLE_DES, ◦TRIPLE_DES_3KEY, ◦RC2, ◦RC4, ◦RC4_128, ◦DESX, ◦AES_128, ◦AES_192, ◦AES_256 Can have more than one encryption of each type ◦When encrypted with a password instead of the public key of the database master key, the TRIPLE DES encryption algorithm is used ◦Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm. http://technet.microsoft.com/en-us/library/ms188357.aspx
- 21. Securable entity atthe database level Contains both a public key and a private key By default private key is protected by the database master key ◦If no database master key has been created, a password is required to protect the private key. Private key can be 512, 1024, or 2048 bits long. http://msdn.microsoft.com/en- us/library/ms174430.aspx
- 22. CREATE DMK BACKUPDMK CREATE SYMMETRIC KEY CREATE CERTIFICATE BACKUP CERTIFICATE
- 23. What to encrypt? ◦Define the scope Based on governance, oversight, organizational policies Database All files? TDE-Transparent Data Encryption Over the wire? SSL Specific columns SQL Cryptographic services Client based encryption Extended store procedures –NOT!!
- 24. Foundation has beenbuilt for us!! ◦SQL Cryptographic Services Built on the DPAPI SMK ◦Native to SQL Can be implemented in T-SQL No longer requires extended stored procedures or CLR Easy for developers NOT SO MUCH for DBA supporting ISV databases THIS IS NOT A REPLACEMENT TO SSL Data is still unencrypted cross the wire
- 25. Easily implemented ◦Createsymmetric key ◦Open key to encrypt/decrypt data
- 26. Encryption Using SymmetricKey Decryption Using Symmetric Key Transactions Using Symmetric Key
- 27. Symmetric Key Encryptionworks but what next? ◦More secure requirements ◦Encryption by certificate Internal/external certs Integrating SQL security with column level encryption THIS IS NOT A REPLACEMENT TO DATABASE ENCRYPTION OR ENCRYPTION OVER THE WIRE!!!!
- 28. Provides security byassigning permission(s) to User(s) Alone requires that a password is used to encrypt/decrypt ◦Without SSL password is easily revealed ◦When used in T-SQL object, stored procedure, view, function, password is revealed Provide encryption but has inherent security issues ◦Consider previous demonstration using key with password
- 29. ◦CREATE SYMMETRIC ◦ENCRYPT/DECRYPTDATA USING KEY(S)
- 30. Can be internallyor externally created ◦SQL allows the import of certificates Can provide a means of authentication ◦Additional steps required for certificate login authentication Provide a securable object at the database and server level Does not require passing the argument of a password ◦If the certificate specified does not exist in the database or the current user is not authorized to use it, the EncryptByCertand DecryptByCertfunctions return NULL and no error message is returned
- 31. ◦Encryption Using Certificate ◦Encryption Using Certificate in Proc