column level encryption in microsoft sql server

Column Level Encryption

Presented byBehnam Mohammadi

[email protected]

Database Security Course byDr. Meghdad Mirabi

Fall 2016

Microsoft SQL ServerIslamic Azad University South Tehran Branch

Table of content

• Problem

• Encryption

• Database security tools

• Encryption Hierarchy

• Encryption Mechanisms

• Transact-SQL functions

• Asymmetric keys2 28

Table of content

• Symmetric keys

• Certificates

• Transparent Data Encryption (TDE)

• Column Level Encryption

• Advantages and Disadvantages

• References

3 28

Problem

• For example financial firms have sensitive data

• Credit card numbers

• Financial history

• etc.

• Needs to be protected from unauthorized access

4 28

Encryption • Most effective way to achieve data security

• Cannot be easily understood by anyone except authorized

parties

• Main types of encryption in databases

• Database level encryption

• Column level encryption5 28

Database Security Tools

• Data stored in tables

• Views

• Column level permissions

• Database encryption

• Database

• Column

6 28

Encryption Hierarchy

7 28

Encryption Mechanisms

• Transact-SQL functions

• Asymmetric keys

• Symmetric keys

• Certificates

• Transparent Data Encryption (TDE)

8 28

Transact-SQL functions

• SQL Server built-in encryption function

• SQL Server built-in decryption function

9 28

Transact-SQL functions

• The encryption function password is plain text

• It can be stolen by a sniffer in case

• SQL Server profiler

1028

Certificates

• A public key certificate, usually just called a certificate

• Digitally-signed statement

• Certificates are issued and signed by a certification authority

• Subject is entity that receives a certificate form CA

• Relieve need to maintain a set of passwords for individual

subjects for hosts 1128

Certificates

• Certificate contents

• Subject public key

• Subject identifier information [name, e-mail and etc.]

• Validity period

• Issuer identifier information and digital signature

1228

Symmetric Keys

• Used for both encryption and decryption

• Encryption and decryption by using a symmetric key is fast

1328

Asymmetric Keys

• Made up of a private key and the corresponding public key

• Each key can decrypt data encrypted by the other

• Higher level of security than symmetric encryption

1428

Transparent Data Encryption (TDE)

• Transparent to end-user

• Protect data and log files

• Performed at page level

• Real time I/O Encryption and Decryption

• Impact on performance between 3% to 5%

1528

Transparent Data Encryption (TDE)

• Use a symmetric key called the database encryption key

• Protected by certificate that

it also protected by the DMK

• Protected by an asymmetric

key stored in an EKM

1628


1. Create SQL Server Database Master Key (DMK)

2. Create a Self Signed SQL Server Certificate

3. Create SQL Server Symmetric Key

4. Using Encryption

1728


1. Create SQL Server Database Master Key (DMK)

1828


2. Create a Self Signed SQL Server Certificate

1928


3. Create SQL Server Symmetric Key

2028


4. Using Encryption Encrypt

2128

Column Level Encryption 4. Using Encryption Decrypt

2228


• A user need to have permission to symmetric key and

certificate to decrypt data

• Decrypt with wrong key or certificate gives NULL for encrypted

values

2328


• Grant Permissions to the Encrypted Data

2428

Advantages and Disadvantages

• High level security

• Requires no maintenance password for end-user

• Impact on performance

• Database migration is difficult

2528

References

• www.msdn.microsoft.com

• www.searchsecurity.techtarget.com [Margaret Rouse]

• www.mssqltips.com [Nitansh Agarwal]

2628

xx

END

SIMPLICITY IS POWER

Thanks 2727

column level encryption in microsoft sql server

Software