SQL Server Encryption
Transparent Data Encryption
This encrypts the
physical data + log files + backup (all at the server end). Only protects the data at rest and stops
access if the physical files / backups are copied off.
Uses windows data
protection key (os level) to decrypt service master key (instance level) to
encrypt database master key (database level).
This creates certificate in the master database used to encrypt
database. This certificate + password is
needed then to restore database anywhere else.
This will not
encrypt the network traffic.
Will not protect
against sql injection or elevated privileges.
Will not protect
against the entire image of the OS / datafiles being stolen as technically the
database is still accessible if elevated privileges are obtained against the
same OS / Instance.
What this will do is
prevent the data files / logs or backups being copied off and restored to
another server or data files being accessed outside of the instance.
Transparent (the
application does not need to know about this).
All decryption is done at the server side.
Commonly used,
mature (since since 2008), easy to implement.
Enterprise only
feature, needs enterprise to restore onto another server as well
May have a slight
effect on performance as tempdb is also encrypted but this impact should be
small.
Uses encryption key
/ certificates which need to be backed up (to more than one physical location),
in order to be able to recover database.
Can be added to
availability groups (outside of wizard)
SQL Always Encrypted
Client side, needs a
driver on the client to decrypt. Object
level encryption
Data itself is
stored encrypted so DBA's would not have access to that data.
Not encrypted at a
network packet level but the transported data is encrypted anyway.
Limited SQL Feature
set (there are limitations with sql commands so may not work with all
applications).
Major performance
implications with index searching
Application would
need to be designed to use this.
Best set up on
limited objects for protecting specific data fields, credit card numbers etc.
Standard edition
supported
SSL Encryption
Can be used to
compliment TDE for end to end network packet encryption.
Will require
certificated to be set up on server and copied to the client machines. SQL Server requires configuring to use
certificates
Article with a
summary of different encryption technologies with sql server.
Auditing
Common Criteria Compliance (enterprise only)
audits EVERY
successful and unsuccessful attempt to access a database object.
HAS to be written
(db will stop if it cannot write to this due to space etc)
Requires a db
restart / config change and script to enable.
C2 Audit (enterprise only superceeded by common
criteria compliance)
For C2 Government
Certified Systems, similar to ccc but now superceeded
Server Audit (standard edition)
Configurable
targeted Auditing
Configure to force
logging or continue running instance on error
Requires
configuration to target objects etc
Can write output to
flat file or windows event logs
Comments